Introduction to Database Transactions: Isolation Levels

A basic knowledge of transactions is required before proceeding with the below article.

I would recommend to go through Introduction to Database Transactions first.

What are isolation levels?

If you have gone through my previous article, you would know the various problems that comes with transactions, i.e., dirty read, non-repeatable read, overwriting uncommitted Data and phantom read. Isolation levels aims to solve these problems. They determine the strategy to be used while a data is accessed by multiple transactions simultaneously. For example, if a data is read by a transaction, should other transaction running in parallel be allowed to read that data. Similarly, what should happen when one transaction has modified some data but not committed as of yet and other transaction has to use that data. All these different scenarios are handled by specifying the correct isolation level.

Types of Isolation Levels

There are four types of isolation levels:

• Read uncommitted
• Read committed
• Repeatable read
• Serializable

Below table describes the salient features of each of them:

Isolation Level	Description
Read uncommitted	Transactions can read rows that have been modified by other transactions but not yet committed. Least restrictive isolation level. Read Uncommitted level are usually read-only.
Read committed	Transactions cannot read data that has been modified but not committed by other transactions. This prevents "dirty read". Transactions can modify data that has been read by another transactions which have not committed yet, resulting in “non-repeatable reads” or “phantom data”.  Generally uses row-lock.
Repeatable read	Transactions cannot read data that has been modified but not committed by other transactions. This prevents "dirty read".   Transactions cannot modify data that has been read by another transactions which have not committed yet. This prevents “non-repeatable reads”.  Other transactions can insert new rows that match the search conditions of statements issued by the current transaction resulting in “phantom data”.  Generally uses row-lock.  Concurrency is lower than READ COMMITTED isolation level since locks are held till the end of a transaction instead of being released at the end of each statement.
Serializable	Transactions cannot read data that has been modified but not committed by other transactions. This prevents "dirty read". Transactions cannot modify data that has been read by another transactions which have not committed yet. This prevents “non-repeatable reads”. Other transactions cannot insert new rows that match the search conditions of statements issued by the current transaction until the current transaction completes. This prevents “phantom data”. Range locks are used that match the search conditions executed in a transaction. This blocks other transactions from updating or inserting any rows that would match the search conditions issued by the current transaction. Most restrictive isolation level. Concurrency is lower.

Following tables summarizes the problems solved by each isolation level:

Transaction isolation level	Dirty reads	Non Repeatable reads	Phantom Data
Read uncommitted	Not Solved	Not Solved	Not Solved
Read committed	Solved	Not Solved	Not Solved
Repeatable read	Solved	Solved	Not Solved
Serializable	Solved	Solved	Solved

Things to note:

• Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed.
• In case of change of isolation level, resources that are read after the change are protected according to the rules of the new level. Resources that are read before the change continue to be protected according to the rules of the previous level.
• Transaction isolation level does not affect a transaction's ability to see its own changes; transactions can always see any changes they make.

That is it! Thanks for reading. Hope you liked the article. Please do post in the comment section for any query.

Do not forget to follow us at +Java Territory to stay updated.

Read More

Introduction to Database Transactions

A transaction is unit of work that must commit or abort as an atomic unit. By unit of work, we mean a sequence of reads and writes of database objects.

Transaction management means controlling the execution of transactions to ensure data integrity and consistency.

Let’s take an example of bank transaction: Rs. 200 are transferred in Transfer Rs.200 from Account A to Account B. It would normally be done in 2 steps:

	Account A (in Rs.)	Account B (in Rs.)
Original Amount	1000	500
STEP 1: Debit Rs.200 from Account A	1000 - 200 = 800	500	SUCCESS
STEP 2: Credit Rs.200 to Account B	800	500	FAIL

Suppose STEP 1 succeeds and STEP 2 fails, final balance in A’s account will be 800. Rs.200 are lost. This is what transaction management aims to solve, i.e., all steps should either succeed or all steps should be rolled-back, otherwise we are left in an inconsistent state. And all these steps (that either should be executed completely or none of them should have any impact on the state of database) are collectively called transactions.

States of Transactions

A transaction in a database can be in one of the following states –

• Active: Initial state of every 
• Partially Committed: When a transaction executes all its operations but hasn’t been committed 
• Failed: If any operation 
• Aborted: If the transaction fails, then the recovery manager rolls back all its write operations on the database to bring the database back to its original state where it was prior to the execution of the transaction. Transactions in this state are called aborted. The database recovery module can select one of the two operations after a transaction aborts-
• Re-start the transaction
• Kill the transaction
• Committed: If a transaction executes all its operations successfully, it is said to be committed. All its effects are now persisted in the database. 

What are ACID properties ?

A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.

• Atomicity: Either all actions in a transaction are carried out, or none are. There must be no state in a database where a transaction is left partially completed.
• Consistency: If each transaction is consistent, and the database is initially consistent, then it must remain consistent after the execution of the 
• Isolation: Transactions are isolated or protected from the effects of other scheduled transactions. When more than one transaction are being executed simultaneously and in parallel, then transactions will be carried out and executed as if it is the only transaction in the system. Final result should be same as if these transactions are executed sequentially.
• Durability: If a transactions completes successfully, then its effects persist. If a transaction commits but the system crashes before the data could be written on to the disk, then that data will be updated once the system is restored.

Schedule

A schedule is a list of actions from a set of transactions. A well-formed schedule is one where the actions of a particular transaction T are in the same order as they appear in T.

• A complete schedule is one that contains an abort or commit action for every transaction that occurs in the schedule.
• serial schedule is one where transactions are executed in a serial manner, one after the other.
• serializable schedule is a schedule whose effect on any consistent database instance is identical to that of some complete serial schedule
• equivalent schedule is a schedule that has same result on database state i.e. effect of executing the first schedule is identical to the effect of executing the second schedule.

Anamolies with interleaved execution

Let us consider ways in which a schedule involving two consistency-preserving transactions can leave a consistent database inconsistent.

• Dirty Read (WR conflict): When a transaction reads a value that is updated by another transaction which has not completed yet.

Example: We have 2 transactions executing in parallel:

T_A: Transfer Rs.200 from Account A to Account B.

T_B: Transfer Rs.100 from Account C to Account A.

	Account A (in Rs.)	Account B (in Rs.)	Account C (in Rs.)
Original Amount	1000	500	200
TA: STEP 1: Debit Rs.200 from Account A	1000 - 200 = 800
TB: STEP 1: Debit Rs.100 from Account C			200 - 100 = 100
TB: STEP 2: Credit Rs.100 to Account A	800 + 100 = 900
TA: STEP 2: Credit Rs.200 to Account B		500 + 200 = 700
Final Amount	900	700	100

Sum of original amount in all accounts = Sum of final amount in all accounts = Rs.1700

Suppose if T_A STEP2 fails, then the recovery manager rolls back T_A

Account A: Back to Rs.1000 as T_A has read original value as Rs.1000.

Account B: Back to Rs.500 as T_A has read original value as Rs.500.

So, final amount in the accounts after transaction T_A has been rolled back due to failure and transaction T_B has been committed.

	Account A (in Rs.)	Account B (in Rs.)	Account C (in Rs.)
Final Amount	1000	500	100

Sum of original amount in all accounts != Sum of final amount in all accounts

So, Rs.100 being transferred by Account C has been lost because transaction T_B has read value updated by transaction T_A which has not been completed yet.

•  Non-Repeatable Read (RW conflict): When in a transaction a value is read twice and two different results are rendered.

Example: We have 2 transactions executing in parallel:

T_A: Reads balance of Account A twice while its execution.

T_B: Transfer Rs.100 from Account C to Account A.

	Account A (in Rs.)	Account B (in Rs.)	Account C (in Rs.)
Original Amount	1000	500	200
TA: STEP 1: Get balance of Account A	1000
TB: STEP 1: Debit Rs.100 from Account C			200 – 100 = 100
TB: STEP 2: Credit Rs.100 to Account A	1000 + 100 = 1100
TA: STEP 2: Get balance of Account A	1100

• Overwriting uncommitted Data (WW conflict): When a transaction overwrites the value which has already been modified by another transaction while is still in progress.

Example: We have 2 transactions executing in parallel:

T_A: Update the number of correct questions to 15 and incorrect questions to 5.

T_B: Update the number of correct questions to 18 and incorrect questions to 2.

	Correct Questions	Incorrect Questions
Original Data	10	10
TA: STEP 1: Update the number of correct questions to 15	15
TB: STEP 1: Update the number of correct questions to 18	18
TB: STEP 2: Update the number of incorrect questions to 2		2
TA: STEP 2: Update the number of incorrect questions to 5		5
Final Data	18	5

• Phantom Read: When a read operation occurs more than one time in same transaction return different number of rows.

Example: We have 2 transactions executing in parallel:

T_A: Get number of account whose balance > Rs.500 (twice during its execution).

T_B: Transfer Rs.100 from Account C to Account B.

	Account A (in Rs.)	Account B (in Rs.)	Account C (in Rs.)
Original Amount	1000	500	200
TA: STEP 1: Get accounts whose balance > Rs.500	1000
TB: STEP 1: Debit Rs.100 from Account C			200 – 100 = 100
TB: STEP 2: Credit Rs.100 to Account B		500 +  100 = 600
TA: STEP 2: Get accounts whose balance > Rs.500	1000	600

Above mentioned anamolies can lead to inconsistent state and may have disastrous impact. To cater these scenarios, isolation levels are used. Read here for more details on Isolation Levels.

That is all on the basics of Transactions. Follow us at +Java Territory to stay updated. 

Read More