Isolation. Read Committed and Uncommitted Levels
In the context of databases, isolation refers to the ability of a database system to control the visibility of changes made by concurrent transactions. It ensures that transactions operate in a manner that is independent of each other, avoiding interference and maintaining data integrity.
There are 4 isolation levels in SQL:
- read uncommitted;
- read committed;
- repeatable read;
- serializable.
Read uncommitted
This is the lowest isolation level where transactions can see changes made by other transactions even before they are committed. This level allows dirty reads, meaning a transaction can read data that has been modified by another transaction but not yet committed.
Dirty reads
For example, imagine two bank transactions happening simultaneously: Transaction A checks your account balance while Transaction B deposits money into your account.
If Transaction A sees the increased balance before Transaction B finishes, it's a dirty read, as the new balance might be rolled back if Transaction B fails.
Implementation
To specify the isolation level for the transaction we can use the following command in our query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;: This statement changes the isolation level for the current transaction to "Read Uncommitted", allowing the transaction to potentially read data modified by other uncommitted transactions;- this command must be used inside the transaction block only! Otherwise, it will have no effect, and a default isolation level will be used.
We can also check the current isolation level using the following command:SHOW TRANSACTION ISOLATION LEVEL;
Read committed
The Read Committed isolation level ensures that a transaction only sees data that has been committed by other transactions.
This means that uncommitted changes made by other transactions are not visible to transactions operating under Read Committed isolation.
As a result, it prevents dirty reads by allowing a transaction to read only committed data. However, this transaction level has problems with non-repeatable reads.
Non-repeatable reads
For example, imagine a transaction querying a customer's account balance twice. The first query returns $1000, but before the transaction completes, another transaction deposits $500 into the account. When the second query is executed, it shows $1500, leading to a non-repeatable read, where the same data is read differently within a single transaction due to changes made by another transaction.
The "Read committed" isolation level allows non-repeatable reads because it locks the read operation on values that are in the process of uncommitted transactions but doesn't lock the write operation.
As a result, we can write new data to the row that is currently being read by another transaction.
Lost update
Due to the absence of the write lock there is one more problem with read committed isolation level - lost updates.
The lost updates occur when two parallel transactions attempt to change the same row. As a result, the transaction that is committed later overwrites the values committed by other transactions.
Implementation
We can also specify this isolation level using the following commands:
It's important to notice that Read Committed is the default isolation level for most Database Management Systems, which is why we can skip specifying it.
Все було зрозуміло?
Зміст курсу
Advanced Techniques in SQL
Advanced Techniques in SQL
Isolation. Read Committed and Uncommitted Levels
In the context of databases, isolation refers to the ability of a database system to control the visibility of changes made by concurrent transactions. It ensures that transactions operate in a manner that is independent of each other, avoiding interference and maintaining data integrity.
There are 4 isolation levels in SQL:
- read uncommitted;
- read committed;
- repeatable read;
- serializable.
Read uncommitted
This is the lowest isolation level where transactions can see changes made by other transactions even before they are committed. This level allows dirty reads, meaning a transaction can read data that has been modified by another transaction but not yet committed.
Dirty reads
For example, imagine two bank transactions happening simultaneously: Transaction A checks your account balance while Transaction B deposits money into your account.
If Transaction A sees the increased balance before Transaction B finishes, it's a dirty read, as the new balance might be rolled back if Transaction B fails.
Implementation
To specify the isolation level for the transaction we can use the following command in our query:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;: This statement changes the isolation level for the current transaction to "Read Uncommitted", allowing the transaction to potentially read data modified by other uncommitted transactions;- this command must be used inside the transaction block only! Otherwise, it will have no effect, and a default isolation level will be used.
We can also check the current isolation level using the following command:SHOW TRANSACTION ISOLATION LEVEL;
Read committed
The Read Committed isolation level ensures that a transaction only sees data that has been committed by other transactions.
This means that uncommitted changes made by other transactions are not visible to transactions operating under Read Committed isolation.
As a result, it prevents dirty reads by allowing a transaction to read only committed data. However, this transaction level has problems with non-repeatable reads.
Non-repeatable reads
For example, imagine a transaction querying a customer's account balance twice. The first query returns $1000, but before the transaction completes, another transaction deposits $500 into the account. When the second query is executed, it shows $1500, leading to a non-repeatable read, where the same data is read differently within a single transaction due to changes made by another transaction.
The "Read committed" isolation level allows non-repeatable reads because it locks the read operation on values that are in the process of uncommitted transactions but doesn't lock the write operation.
As a result, we can write new data to the row that is currently being read by another transaction.
Lost update
Due to the absence of the write lock there is one more problem with read committed isolation level - lost updates.
The lost updates occur when two parallel transactions attempt to change the same row. As a result, the transaction that is committed later overwrites the values committed by other transactions.
Implementation
We can also specify this isolation level using the following commands:
It's important to notice that Read Committed is the default isolation level for most Database Management Systems, which is why we can skip specifying it.
Все було зрозуміло?
