I had a problem with database lock recently. Since I couldn’t find what the problem was we had to talk to our db guys. After some investigation, they asked me why is transaction isolation level on the database set to Seriazible?
My answer was: what the hell is isolation level?
So after some research, I just wanted to post a very high overview of this topic. I didn’t go in any depth on this and if you need to change the transaction's isolation level on your application I would suggest finding more about them.
Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resources or data modification made by other transactions. It basically defines what records your transaction can read from the database and what changes can other transactions do while you are inside your transaction. Transaction isolation levels control the following:
- Whether locks are taken when data is read, and what type of locks are requested.
- How long the read locks are held
- Whether a read operation referencing rows modified by another transaction:
- Blocks until the exclusive lock on the row is freed
- Retrieve the committed version of the row that existed at the time the statement or transaction started
- Read the uncommitted data modification
Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. A transaction always gets an exclusive lock until the transaction completes so other transactions can’t edit data that you didn’t commit yet.
For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.
A lower isolation level increases the ability of many users to access data at the same time but increases the number of concurrency effects, such as dirty reads or lost updates, that users might encounter.
For the examples, we will use a simple SQL Server database with two tables Authors and Books that each has one row.
And we will begin with a transaction that enters a new value in the books table but it doesn’t commit that transaction:
Read Uncommitted
Statements can read rows that have been modified by other transactions but not yet committed.
So when we insert a new value in the books table but don’t commit a transaction we can't read the value of the books table with simple select because the table is locked.
But if we make select from books table in a transaction that specifies read uncommitted as isolation level we can make that query and we will see the value that is not yet committed by this transaction
Read uncommitted is very useful if you need to take a quick look at some data or in some select statements where the block would be very harmful. This isolation level needs to be used very carefully because it can lead to reading corrupted data (e.g. the row that you are reading can be deleted by some operation that is in progress but it still didn’t finish and perform commit)
Read Committed
Statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads
This isolation level depends on the READ_COMMITED_SNAPSHOT database option set to ON. If this is set to OFF (default on SQL Server) database engine will use shared locks to prevent other transactions to modify the rows while the current transaction is running so you won't be able to read data while another transaction is in progress
Now you can read only transactions that are committed to the database.
If you run multiple select statements inside the transaction while the isolation level is set to READ COMMITTED you can get different results because other transactions are allowed to add new rows or update existing rows while you are in this transaction.
Repeatable Read
Statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes
So as expected while the transaction that is inserting the new book is in progress you cant read data from the table if you are using this isolation level.
On the other hand if you start the transaction in the repeatable read first you still can add a new row to a table and next read in the repeatable read transaction will see that updated row once you commit it, but you can’t update the row that the repeatable read transaction already read.
Snapshot
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. The transaction can only recognize data modifications that were committed before the start of the transaction.
Basically, all transactions in this isolation level behave as if they were executed against their own private copy of the database, taken the moment transaction started.
First we make a select statement in a transaction that is using SNAPSHOT isolation level and we only get one row.
Then we insert a new value to the table outside the transaction.
That value is successfully added to the database and we can get it in a select outside transaction that we started with snapshot isolation level
But we don’t see that new row in select that is inside the transaction.
Serializable
Serializable is the most isolated of the standard transaction isolation levels.
Statements cannot read data that has been modified but not yet committed by other transactions.
No other transaction 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 the keys read by any statements in the current transaction until the current transaction completes.
Top comments (0)