JDBC Transaction Management Example

A transaction is a set of SQL operations that need to be either executed all successfully or not at all. Failure to perform even one operation leads to an inconsistent and erroneous database.A database must satisfy the ACID properties (Atomicity, Consistency, Isolation, and Durability) to guarantee the success of a database transaction.1. Atomicity: Each transaction should be carried out in its entirety; if one part of the transaction fails, then the whole transaction fails.2. Consistency: The database should be in a valid state before and after the performed transaction.3. Isolation: Each transaction should execute in complete isolation without knowing the existence of other transactions.4. Durability: Once the transaction is complete, the changes made by the transaction are permanent (even in the occurrence of unusual events such as power loss).

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.

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 the connection is an active connection:

connection.setAutoCommit(false);

Committing Transactions

After the auto-commit mode is disabled, no SQL statements are committed until you call the method to commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit.

JDBC - Transactions Key Points

By default auto-commit mode is set to true, so all changes you make through the connection are committed automatically to the database.

You can use setAutoCommit(false); to enable manual commits. With auto-commit not enabled, you need to explicitly commit or rollback transactions.

If the commit() method does not execute in manual commit mode, there will be no change in the database.

You can divide a big transaction into multiple milestones. These milestones are referred to as savepoints. This way you may save the changes to a database up to a milestone once the milestone is achieved.