[ Home ] [ Up ] [ Relational Databases ] [ Structured Query Language (SQL) ] [ What is JDBC? ] [ Connecting to Databases ] [ SQL Errors and Warnings ] [ Accessing Databases ] [ Creating and Populating a Database ] [ Transactions ] [ Prepared and Callable Statements ] [ Using Metadata ]

Transactions are crucial to proper use of databases. They provide the necessary atomicity and isolation necessary for consistent updates to be performed to a database.

Here are some JDBC policies regarding transactions:

• At most one transaction can exist at any time per database connection.
• Drivers are required to allow concurrent requests from different threads using the same Connection. If the driver cannot perform the requests in parallel, they are serialized.
• You may cancel a statement's execution asynchronously by calling Statement's cancel() method from another thread.
• By default, each (DML) statement encompasses a transaction by itself (autocommit mode). setAutoCommit(false) turns autocommit mode off.
• Deadlock detection behavior is not specified. Most implementations throw an exception, but it could be a SQLException, an RuntimeException, or even an Error.
• If autocommit mode is disabled, and you perform operations within a transaction that opens locks on database entities (such as rows in a table), those locks remain in effect until the transaction ends, either by explicit calls to Connection's commit() or rollback() methods, or when the connection is closed.

Transaction Isolation Levels

Remember the ACID properties that databases must support? The I is for Isolated, and there are a variety of isolation levels supported by various databases. The isolation levels supply a kind of hierarchy that goes all the way from no transactions supported to the most isolated form, serializable.

The Connection interface defines a set of isolation levels

• TRANSACTION_NONE -- Transactions are not supported.
• TRANSACTION_READ_COMMITTED -- Dirty reads are prevented; non-repeatable reads and phantom reads can occur.
• TRANSACTION_READ_UNCOMMITTED -- Dirty reads, non-repeatable reads and phantom reads can occur.
• TRANSACTION_REPEATABLE_READ -- Dirty reads and non-repeatable reads are prevented; phantom reads can occur.
• TRANSACTION_SERIALIZABLE -- Dirty reads, non-repeatable reads and phantom reads are prevented.

Setting Your Isolation Level

You can determine whether your database supports a particular isolation level:

boolean b = DatabaseMetaData.supportsTransactionIsolationLevel(
			Connection.TRANSACTION_REPEATABLE_READ);

and you can then set the isolation level using:

setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);