Forum Stats

  • 3,769,240 Users
  • 2,252,937 Discussions
  • 7,874,958 Comments

Discussions

Connection.setAutoCommit(false); does it creates a transaction at database side?

1020831
1020831 Member Posts: 7

I am curious to know when is the transaction is started at database side? I am using oracle as database and ojdbc6 jar driver to connect to the db.

I am also curious to know what exactly a connection is? can I relate it to a database session? do sockets are used to connect a java application to the database server?

Thank you!

Tagged:
1020831

Best Answer

  • Unknown
    edited Sep 23, 2015 1:10PM Accepted Answer
    I am curious to know when is the transaction is started at database side? I am using oracle as database and ojdbc6 jar driver to connect to the db.
    
    I am also curious to know what exactly a connection is? can I relate it to a database session? do sockets are used to connect a java application to the database server?
    
    Thank you!
    

    No - setting autocommit to false does NOT create a transaction.

    You can EASILY search the web for definitions of basic terms such as transaction, session, connection.

    The Java tutorials has a trail for 'Using Transactions'

    https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

    Disabling Auto-Commit Mode

    When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)
    The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. This is demonstrated in the following code, where con is an active connection:
    on.setAutoCommit(false); 

    The Java API tells you what a 'connection' is

    Connection (Java Platform SE 7 )

    A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection. 
    

    See where it says 'connection (session)'?

    And the Oracle docs can discuss those from the DB point of view

    https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT117

    Introduction to Transactions

    A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID.
    All Oracle transactions obey the basic properties of a database transaction, known as ACID properties. ACID is an acronym for the following:
    . . .
    After one transaction ends, the next executable SQL statement automatically starts the following transaction.
    . . .
    
    

    The Oracle docs also discuss when a transaction begins - that may be different for another database

    http://docs.oracle.com/database/121/SQLRF/statements_10005.htm#SQLRF01705

    Use the SETTRANSACTION statement to establish the current transaction as read-only or read/write, establish its isolation level, assign it to a specified rollback segment, or assign a name to the transaction. 
    A transaction implicitly begins with any operation that obtains a TX lock:
    
     When a statement that modifies data is issued 
     When a SELECT ... FORUPDATE statement is issued 
     When a transaction is explicitly started with a SETTRANSACTION statement or the DBMS_TRANSACTION package 
    
    
    10208311020831

Answers

  • Joe Weinstein-Oracle
    Joe Weinstein-Oracle Member Posts: 516
    edited Sep 23, 2015 10:43AM

    SetAutoCommit(false) sets the DBMS to start a transaction immediately when the next data is read or written.

    Yes, a connection is one-to-one with a DBMS session. Yes, the driver causes a socket to be opened

    between the client and DBMS*.

    (in rare one-machine cases with some drivers, the communication between client and DBMS may be by

    a faster one-machine protocol)

    10208311020831
  • Unknown
    edited Sep 23, 2015 1:10PM Accepted Answer
    I am curious to know when is the transaction is started at database side? I am using oracle as database and ojdbc6 jar driver to connect to the db.
    
    I am also curious to know what exactly a connection is? can I relate it to a database session? do sockets are used to connect a java application to the database server?
    
    Thank you!
    

    No - setting autocommit to false does NOT create a transaction.

    You can EASILY search the web for definitions of basic terms such as transaction, session, connection.

    The Java tutorials has a trail for 'Using Transactions'

    https://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html

    Disabling Auto-Commit Mode

    When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. (To be more precise, the default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved. In almost all cases, however, a statement is completed, and therefore committed, right after it is executed.)
    The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. This is demonstrated in the following code, where con is an active connection:
    on.setAutoCommit(false); 

    The Java API tells you what a 'connection' is

    Connection (Java Platform SE 7 )

    A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection. 
    

    See where it says 'connection (session)'?

    And the Oracle docs can discuss those from the DB point of view

    https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT117

    Introduction to Transactions

    A transaction is a logical, atomic unit of work that contains one or more SQL statements. A transaction groups SQL statements so that they are either all committed, which means they are applied to the database, or all rolled back, which means they are undone from the database. Oracle Database assigns every transaction a unique identifier called a transaction ID.
    All Oracle transactions obey the basic properties of a database transaction, known as ACID properties. ACID is an acronym for the following:
    . . .
    After one transaction ends, the next executable SQL statement automatically starts the following transaction.
    . . .
    
    

    The Oracle docs also discuss when a transaction begins - that may be different for another database

    http://docs.oracle.com/database/121/SQLRF/statements_10005.htm#SQLRF01705

    Use the SETTRANSACTION statement to establish the current transaction as read-only or read/write, establish its isolation level, assign it to a specified rollback segment, or assign a name to the transaction. 
    A transaction implicitly begins with any operation that obtains a TX lock:
    
     When a statement that modifies data is issued 
     When a SELECT ... FORUPDATE statement is issued 
     When a transaction is explicitly started with a SETTRANSACTION statement or the DBMS_TRANSACTION package 
    
    
    10208311020831
  • 1020831
    1020831 Member Posts: 7
    edited Sep 24, 2015 2:09AM

    so the transaction is stated as soon as first statement is executed? and one mote thing is does it require a database trip when I write connection.createStatement(); or just an object is created at java side?

  • Joe Weinstein-Oracle
    Joe Weinstein-Oracle Member Posts: 516
    edited Sep 24, 2015 10:58AM

    a plain statement entails no DBMS-side semantics and therefore no action/roundtrip

    1020831
This discussion has been closed.