Understanding Database Isolation Levels
What is Isolation Level?
As we know, to maintain consistency in a database, it follows ACID properties. Among these four properties (Atomicity, Consistency, Isolation, and Durability) Isolation 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.
Why do we need isolation?
Imagine that you’re implementing a system for large e-commerce. Many operations have to take place at the same time, multiple customers may simultaneously want to purchase the same product, prices of some product may change, new products are still being delivered, etc. As you know, a single action done by a user is run as a transaction in a database, so we need to add some logic to maintain consistency and that’s the role of Isolation because it controls:
- 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 so it Blocks until the exclusive lock on the row is freed or Retrieves the committed version of the row that existed at the time the statement or transaction started it’s depending on the isolation levels
What is an “Isolation Level”?
Database isolation defines the degree to which a transaction must be isolated from the data modifications made by any other transaction(even though in reality there can be a large number of concurrently running transactions). The overarching goal is to prevent reads and writes of temporary, aborted, or otherwise incorrect data written by concurrent transactions.
Transaction isolation level is defined by the following phenomena
Dirty Reads
A transaction reads data written by a concurrent uncommitted transaction. (uncommitted data is called “dirty.”)
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 Reads, and Read Skew
A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that has been committed since the initial read).
Note that this differs from a dirty read in that the other transaction has been committed. Also, this phenomenon requires two reads to manifest.
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 Reads
A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.
This is similar to a non-repeatable read except it involves a changing collection matching a predicate rather than a single item.
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.
Write Skew
Two concurrent transactions each determine what they are writing based on reading a data set that overlaps what the other is writing.
For example, suppose 2 transactions read that x and y have value 100, so individually it’s fine for each transaction to negate one of the values, the total would still be non-negative. However negating both values results in x+y=-200, violating the constraint. For emotional gravity, this is usually framed in terms of bank accounts where account balances are allowed to go negative as long as the sum of commonly held balances remains non-negative.
Based on these phenomena, These four isolation levels have been defined
Read Uncommitted
Read Uncommitted is the lowest isolation level. At this level, makes sure no transaction can update a database row if another transaction has already updated it and not committed. This protects against lost updates, but won’t stand in a way of dirty reads
Read Committed
This isolation level does not allow any other transaction to write or read a row to which another transaction has written to but not yet committed. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevents other transactions from reading, updating, or deleting it.
Repeatable Read
This isolation level makes sure any transaction that reads data from a row blocks any other writing transactions from accessing the same row. This is the most restrictive isolation level that holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transactions cannot read, update or delete these rows, consequently it avoids non-repeatable read.
Serializable
This isolation level is the highest isolation level. serializable isolation level requires a lot more than restricting access to a single row. Typically this isolation mode would lock the whole table, to prevent any other transactions from inserting or reading data from it.
serializable execution is defined to be an execution of operations in which concurrently executing transactions appears to be serially executing.
Snapshot Isolation
This isolation level can greatly increase concurrency at a lower cost than transactional isolation. When data is modified, the committed versions of affected rows are copied to temp and given version numbers. This operation is called copy on write and is used for all inserts, updates, and deletes using this technique. When another session reads the same data, the committed version of the data as of the time the reading transaction began is returned.
Which transaction isolation to choose
The choice of transaction isolation level depends on the details of each specific case. These hints may be helpful, but please consider each situation individually.
When designing your application, you definitely want to ensure that none of your database transactions read uncommitted changes of other transactions.
because changes can easily harm your data integrity, as reverted changes in one transaction can be read and potentially accepted by another. The minimum isolation level to ensure in your application, therefore, is Read Committed.
Most of the time you probably don’t need Serializable isolation, as it can cause big performance issues with a large volume of concurrent transactions.
so it’s always depends on your business requirements
Conclusion
At the end of this article, those are some resources for reading and getting more knowledge
Practical Guide to SQL Transaction Isolation
