As we know that, in order to maintain consistency in a database, it follows ACID properties. Among these four properties (Atomicity, Consistency, Isolation and Durability) Isolation determines how transaction integrity is visible to other users and systems. It means that a transaction should take place in a system in such a way that it is the only transaction that is accessing the resources in a database system.
Isolation levels define the degree to which a transaction must be isolated from the data modifications made by any other transaction in the database system. A transaction isolation level is defined by the following phenomena –

  • Dirty Read – A Dirty read is the situation when a transaction reads a data that has not yet been committed. For example, Let’s say transaction 1 updates a row and leaves it uncommitted, meanwhile, Transaction 2 reads the updated row. If transaction 1 rolls back the change, transaction 2 will have read data that is considered never to have existed.
  • Non Repeatable read – Non Repeatable read occurs when a transaction reads same row twice, and get a different value each time. For example, suppose transaction T1 reads data. Due to concurrency, another transaction T2 updates the same data and commit, Now if transaction T1 rereads the same data, it will retrieve a different value.
  • Phantom Read – Phantom Read occurs when two same queries are executed, but the rows retrieved by the two, are different. For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, Transaction T2 generates some new rows that match the search criteria for transaction T1. If transaction T1 re-executes the statement that reads the rows, it gets a different set of rows this time.

Based on these phenomena, The SQL standard defines four isolation levels :

  1. Read Uncommitted – Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.
  2. Read Committed – This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it.
  3. Repeatable Read – This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read.
  4. Serializable – This is the Highest isolation level. A serializable execution is guaranteed to be serializable. Serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.

The Table is given below clearly depicts the relationship between isolation levels, read phenomena and locks :


Anomaly Serializable is not the same as Serializable. That is, it is necessary, but not sufficient that a Serializable schedule should be free of all three phenomena types.

Isolation Level

From the lowest level of isolation to the highest, they are…

  • read uncommitted
  • read committed
  • repeatable read
  • snapshot
  • serializable

Read uncommitted is the lowest level and only keeps transactions isolated enough to ensure that they aren’t reading physically corrupt data.They will allow transactions to read data that has been modified by another transaction, but not fully committed. Transactions with this isolation level don’t issue locks on the data that they have read, nor do they get blocked by exclusive locks issued by other transactions.They will allow dirty reads, nonrepeatable reads and phantom reads.

Read committed will not allow reading data that has been modified by another transaction but not yet committed. This prevents dirty reads from occurring. However, nonrepeatable and phantom reads can still occur. This is the SQL server database engine’s default level of transaction isolation. 

Setting the isolation level to repeatable read means that transactions cannot read data that has been modified but not committed by another transaction and it also locks data previously read from being modified from other transactions as well.This prevents dirty reads and nonrepeatable reads, but will allow phantom reads as other transactions can still insert new records into the tables that have been read. 

The snapshot isolation level uses row versioning to work only with a version of the data as it existed when the transaction began. Other transactions can read and modify the same records, but the snapshot isolated transaction effectively works on its own separate copy of the data that’s not visible to other transactions. With a snapshot isolation level, dirty reads, nonrepeatable reads and phantom reads are all prevented.

Finally, serializable is the highest isolation level available, where transactionsare completely isolated from one another and effectively run one at a time. When serializable transactions are executed, they will only read committed data, no other transactions can modify data previously read, and other transactions cannot insert records within the range of values already read. Like snapshot, the serializable isolation level will not allow dirty reads, nonrepeatable reads and phantom reads.

Sources:

https://www.linkedin.com/learning/designing-database-solutions-for-sql-server-2016/transaction-isolation-strategy

https://www.geeksforgeeks.org/transaction-isolation-levels-dbms/

Last modified: July 17, 2020

Author

Comments

Write a Reply or Comment