Table of Contents |
5.4 Managing TransactionsMost database programming APIs provide methods of controlling transactions, setting savepoints, and modifying isolation levels. This section covers the mechanisms for controlling transactions from the ADO.NET and JDBC APIs covered in this chapter. 5.4.1 Beginning a TransactionBeginning a transaction is the first step in guaranteeing atomicity when executing multiple SQL statements. After beginning a transaction, the transaction can be committed to make the changes performed by the executed SQL statements permanent or the transaction can be rolled back to abort the changes and leave the database unchanged. 5.4.1.1 Beginning an ADO.NET transactionTo begin a transaction using ADO.NET, invoke the BeginTransaction method on a connection object. A Transaction object is returned that can be used in the creation of ADO.NET Command objects that execute within the same transaction. To execute a Command object within the new transaction, you must first attach the Transaction object to the Command object's Transaction property. Following is the syntax for starting a transaction by creating a Transaction object in ADO.NET: {Odbc|OleDb|Sql}Transaction transaction = connection.BeginTransaction([IsolationLevel. {Chaos | ReadCommitted | ReadUncommitted |RepeatableRead | Serializable | Unspecified}] ); {Odbc|OleDb|Sql}Command statement = connection.CreateCommand( ); statement.Transaction = transaction; When creating the Transaction object, you can optionally specify the isolation level that is used for the transaction. The isolation level controls how much (or little) your database transactions are insulated from the effects of other transactions. The available isolation levels are:
For more information about isolation levels, see Programming Tips and Gotchas under SET TRANSACTION Statement.
5.4.1.2 Beginning a JDBC transactionJDBC starts a connection in AUTO COMMIT mode, which is a setting that automatically commits changes made by each SQL statement when it executes. To gain control over when changes are committed, the AUTO COMMIT setting must be turned off. JDBC's mechanism of beginning a transaction is subtle compared to ADO.NET, which uses a BeginTransaction method to begin a transaction. In JDBC, a transaction is started as soon as the AUTO COMMIT mode is turned off or just after the transaction has been committed or rolled back. Committing or rolling back the transaction doesn't turn the AUTO COMMIT mode back on, so multiple commits or rollbacks will need to be executed to commit/roll back multiple units of work until the connection is closed or AUTO COMMIT is explicitly turned back on. To turn off the AUTO COMMIT mode, execute the setAutoCommit method of the JDBC Connection object with a false argument: connection.setAutoCommit( false );
JDBC also supports different isolation levels through the JDBC Connection interface. To set the isolation level used for new transactions, invoke the setTransactionIsolation method with one of the following isolation levels:
For a more detailed explanation of transaction isolation levels, see Programming Tips and Gotchas under SET TRANSACTION Statement. Following is example code setting the isolation level in JDBC: connection.setIsolationLevel( Connection.TRANSACTION_SERIALIZABLE ); 5.4.2 Committing a TransactionCommitting a transaction is a way to explicitly close the transaction and make its database modifications permanent. 5.4.2.1 Committing an ADO.NET transactionTo commit a transaction using ADO.NET, invoke the Commit method on the Transaction object: transaction.Commit( ); ADO.NET is more object-oriented than many database-programming APIs, as can be seen from the encapsulation of a transaction into a unique object type. This has the drawback of making the methods for explicitly ending a transaction more difficult to find than those to begin a transaction, since they are on different object types. Remember that the method to begin a transaction is on the Connection object, and the methods to commit or roll back a transaction are found on the Transaction object. 5.4.2.2 Committing a JDBC transactionInvoking the commit method on a JDBC Connection object commits a transaction within JDBC and begins a new transaction: connection.commit( ); If the connection is in AUTO COMMIT mode, you will then get a Java exception of type SQLException because the connection will not have a pending transaction. 5.4.3 Rolling Back a TransactionRolling back a transaction is a way to close the transaction explicitly and to discard any database modifications since the transaction was started. 5.4.3.1 Rolling back an ADO.NET transactionTo roll back a transaction using ADO.NET, invoke the Rollback method on the Transaction object: transaction.Rollback( ); After rolling back a Transaction object, the Dispose method should be invoked to free any resources held by the transaction: transaction.Dispose( ); 5.4.3.2 Rolling back a JDBC transactionInvoking the rollback method on a JDBC Connection object rolls back a transaction within JDBC and begins a new transaction: connection.rollback( ); If the connection is in AUTO COMMIT mode, then a Java exception will be thrown of SQLException type. |
Table of Contents |