”;
What is a Transaction?
A transaction is a single logical unit of operations which accesses and updates the contents of a database.
ACID properties of a Transaction
Transactions also have properties that must be maintained in order to keep database consistency, known as ACID properties: A for Atomicity, C for Consistency, I for Isolation, D for Durability.
-
Atomicity − Transactions involve a group of operations performed sequentially. Atomicity says the entire group of operations either succeed or fail.
-
Consistency − This property involves ensuring only consistent data is added to database. If any error occurs, database is
moved to the initial state. -
Isolation − Transactions must be isolated from each other, otherwise the data maybe corrupted. Isolation levels can be
read uncommitted, read committed, repeatable read and serializable. These are described in the next section. -
Durability − This property ensures that completed transaction will persist the data appropriately and also in the event of a server
failure/system crash.
Dirty Read, Non-repeatable read, Phantom Read
Before understanding isolation let us understand the following basic concepts −
-
Dirty Read − User A inserts a row into table. User B selects the row. User A cancels the transaction. Now, B has made changes to the row, but that row is not in the database anymore.
-
Non-repeatable read − User A reads a row. User B updates the row. User A reads the row again, and sees the updated result.
-
Phantom read − User A reads all rows in a table that satisfies a WHERE condition in SQL. User B adds a row to the same query satisfying a WHERE condition. User A does a SELECT again and finds the added row.
Isolation Levels
Following are five major isolation levels present in java.sql package.
| Sr.No. | Isolation Level | Description |
|---|---|---|
| 1 | TRANSACTION_NONE | Transaction is not supported. |
| 2 | TRANSACTION_READ_UNCOMMITTED | Unsaved data is visible to transaction. All data inconsistencies are possible. |
| 3 | TRANSACTION_READ_COMMITTED | Prevents dirty read. All changes to a transaction are visible only after the transaction is committed. |
| 4 | TRANSACTION_REPEATABLE_READ | Prevents dirty read, non-repeatable read. A second transaction cannot read, update or delete the rows selected by the first transaction. Does not prevent phantom reads. |
| 5 | TRANSACTION_SERIALIZABLE | Strictest isolation level. Prevents dirty read, non-repeatable read, phantom read. |
The above isolation levels can be set in the Connection object in the java.sql package, by the setIsolationLevel method. For example,
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/TUTORIALSPOINT", "user", "password");
conn.setIsolationLevel(Connection.TRANSACTION_READ_COMMITTED);
Setting Isolation Level in MySQL
Isolation level can also be set in the database. For MySQL −
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Other permitted values are following
-
READ COMMITTED
-
READ UNCOMMITTED
Default isolation level is REPEATABLE READ.
”;