In database systems, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations. Isolation is one of the ACID (Atomicity, Consistency, Isolation, Durability) properties.
In very simple terms Isolation Levels decide “What happens when some data being referred to (for reading or writing) within an incomplete transaction is also being referred to (for reading or writing) from another connection (or user – actually it is called another ‘transaction lock space)".
Why Transaction Isolation Level required?
- To avoid dirty read.
- To avoid non repeatable read.
- To avoid phantom rows read.
- Dirty read
This is when connections outside the transaction space can read the uncommitted data. You don’t want this to happen in most cases. The only reason when you may want to allow this is when you are creating a report which assumes a certain amount of inaccuracy. You think this situation is rare? Not really. Suppose the report compares the current month sale with last month sale and returns a percentage difference. Consider that the report takes 5 minutes to generate. During these 5 minutes, more transactions may be getting added to the sales data. Typically 5 transactions would get added to the sales table. The average transaction value is Rs. 1000. The total sale for the month is typically 30-40 lacs. In such cases, you really don’t need to worry about absolute accuracy. You can intentionally allow dirty reads because the impact is really not significant from a business perspective.
2. Non-repeatable read
Within a transaction, the same data read any number of times should yield consistent results. If it is not same then the reads are ‘non-repeatable’.
3. Phatom Read
If you have already read a range of data based upon a key value, another transaction inserts a new row which happens to have a value between this range. The original range which was referred to would now become invalid. This is because a “Phantom row” was added. This is also a major problem.
How to set the Transaction Isolation Level?
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
Example : SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Different Types of Isolation Level?
1. Read Uncommitted
All data which is uncommitted is readable from any connection. This should not be used unless you have a very good reason to do so.
Limitations:
- Dirty-reads
- Lost Updates
- Phantom reads
- Non-repeatable reads
Advantages:
- Higher Concurrency
2. Read Committed
Although it is the default, it does not mean that this isolation level is ideal for all transactions. One of the regular tendencies amongst techies is to simply use default values without giving it a second thought! I cannot imagine the number of phantom and non-repeatable reads that must be occurring in the world because someone simply used the ‘default’ value. It is a scary thought to say the least.
This level is obviously more restrictive than the ‘Read Uncommitted’ level.
Limitations:
- Lower Concurrency than ReadUncommitted
- Lost Updates
Advantage:
- Eliminates Dirty Reads
3. Repeatable read
This prevents dirty reads as well as non-repeatable reads. It does not prevent phantom rows. This is more restrictive than Read Committed level. When I say restrictive what does it mean? It means that the chances of other transactions having to wait for this one to finish are INCREASED. Another way of saying this is – Repeatable Read reduces concurrency compared to Read Committed level.
This specifies that transactions cannot read data that has been modified by other transactions but not yet committed and if the current transaction is reading some data then no other transactions can modify that data until the current transaction completes.
Limitations:
- Lower Concurrency
- Phantom Reads
Advantage:
- Eliminates Dirty Reads
- Eliminates Lost Updates
- Eliminates Non-Repeatable Reads
4. Snap Shot
It specifies that the data accessed by any transaction is consistent and valid for that particular transaction and the data will be same throughout the whole transaction. It implements Row Versioning to isolate data for each transaction i.e. it will keep separate version of each modified row in the transaction in the tempdb database totally dedicated to that transaction. Any update of data in the original row will not affect the current transaction.
Limitations:
- Low performance due to versioning in tempdb
Advantage:
- Eliminates Dirty Reads
- Eliminates Lost Updates
- Eliminates Non-Repeatable Reads
- Allows multiple updates by versioning
It is highest level in Isolation levels as a result the concurrency rate is low. But it eliminates all issues related to concurrency like dirty read, non repeatable reads, lost updates and even phantom reads. According to this Isolation Level:
- Statements cannot read data if other transactions are performing update operations on the data and is not committed yet.
- Also no other transactions can perform any update operations until the current transaction completes its read operations.
- And the important point here is that it is performing a Range Lock based on the filters used to get the data from the table i.e. it locks not only the current records but also the new records that are falling under the current filter condition. In simple language, no other transactions can insert new rows that are falling under the current filter condition until the transaction completes.
Limitations:
- Lower Concurrency
Advantage:
- Eliminates Dirty Reads
- Eliminates Lost Updates
- Eliminates Non-Repeatable Reads
- Eliminates Phantom Reads
Reference
http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/
No comments:
Post a Comment