This article mainly covers the concurrency effects and transaction isolation levels we can choose to avoid these effects in our system.
As we know in order to maintain consistency in a database, it follows ACID properties. And out of the 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 its the only transaction 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 transactions. A transaction isolation level is defined by the following phenomena:
Dirty Read
A dirty read is a situation when a transaction reads the rows or data that is not yet committed. For example,
- Transaction 1 ( T1) modifies a row and leaves the data uncommitted.
- Meanwhile, transaction 2 (T2) reads some data including the row modified by T1.
- If T1 now rolls back the update, then T2 would have read data that is considered to have not existed.
Non Repeatable Read
It occurs when a transaction reads the same row twice and gets different data each time. For example,
- If a transaction 1 (T1) reads some data.
- Now transaction 2 (T2) modifies the same data and commits.
- If T1 reads the same data again, now it will get different value.
Phantom Read
A Phantom read occurs when two same queries are executed but the rows retrieved by two are different. For example,
- If transaction 1 (T1) reads a set of rows through search criteria.
- And transaction 2 (T2) now adds a new row that matches the search criteria for T1.
- If T1 re-executes now then it gets different rows.
Isolation Levels
READ UNCOMMITTED
In this level, dirty reads are allowed as the statements can read rows that have been modified by other transactions but not yet committed.
Transactions with READ UNCOMMITTED level do not have shared locks to prevent other transactions from modifying data read by the current transaction.
Shared lock is also called read lock, used for reading data items only. They ensure that a record is not in process of being updated during a read-only request.
- Also, the transactions are not blocked by exclusive locks ( that would prevent the current transaction from reading rows that have been modified but not yet committed by other transactions).
With the Exclusive Lock, a data item can be read as well as written. Also called write lock. An exclusive lock prevents any other locker from obtaining any sort of a lock on the object. They can be owned by only one transaction at a time.
- This is the lowest isolation level. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction.
READ COMMITTED
In this level, statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.
In this isolation level, the behavior depends on the setting of the READ_COMMITTED_SNAPSHOT database option:
- If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation.
- If READ_COMMITTED_SNAPSHOT is set to ON (the default on Azure SQL Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement.
REPEATABLE READ
In this isolation level, statements cannot read data that has been modified but not yet committed by other transactions and no other transactions can modify data that has been read by the current transaction until the current transaction completes.
Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. As the shared locks are held until the transaction completes, concurrency is lower than the default READ COMMITTED isolation level. Hence, this level should be used when necessary.
However, if a query with the same search criteria (the same WHERE clause) is executed more than once, each execution may return different set of rows. This may happen because other transactions are allowed to insert new rows that satisfy the search criteria or update some rows in such way that they now satisfy the search criteria leading to phantom reads.
SNAPSHOT
Here, the effect is as if the statements in a transaction gets a snapshot of the committed data as it existed at the start of the transaction.
It specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.
SERIALIZABLE
This isolation level guarantees that statements cannot read data that has been modified but not yet committed by other transactions. Also, no other transactions can modify data that has been read by the current transaction until the current transaction completes.
Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes (unlike repeatable read isolation level).
This is the highest isolation levels as it locks the entire ranges of keys and holds the locks until the transaction completes.
Since the concurrency is lower, it is recommended to use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Syntax for Set Transaction Isolation Level
--SYNTAX FOR SQL SERVER AND AZURE SQL DATABASE
SET TRANSACTION ISOLATION LEVEL {
READ UNCOMMITTED |
READ COMMITTED |
REPEATABLE READ |
SNAPSHOT |
SERIALIZABLE
}
Choosing an Isolation Level
Choosing a transaction isolation level doesn't affect the locks that are acquired to protect data modifications. A transaction always gets an exclusive lock on any data it modifies. It holds that lock until the transaction completes, whatever the isolation level set for that transaction. For read operations, transaction isolation levels mainly define how the operation is protected from the effects of other transactions.
A lower isolation level increases the ability of many users to access data at the same time, but it increases the number of concurrency effects ( such as dirty reads or phantom reads), that users might see.
A higher isolation level reduces the types of concurrency effects that users might see. But it requires more system resources and increases the chances that one transaction will block another. Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level.
Transactions must be run at an isolation level of at least repeatable read to prevent lost updates that can occur when two transactions each retrieve the same row.
Thank you for making it to the end. Feel free to comment and follow for updates!