Concurrency Series: Basics of Transaction Isolation Levels
Author: Sunil Agarwal
Reviewers: Kun Cheng, Chuck Heinzelman, Shaun Tinline-Jones, Sanjay Mishra, Kevin Liu
In this series of blogs, I want to show some common concurrency questions that we get asked by customers. Before we look into common concurrency issues seen by customers, it will be good to review the transaction isolation levels provided in SQL Server.
Isolation Levels: SQL Server supports all four isolation levels as defined in ANSI SQL standard. The ANSI standard does not dictate how these need to implemented but SQL Server has implemented them using locks with the exception of RCSI/SI that I will discuss later. These isolation levels are:
· Read Uncommitted (aka dirty read): A transaction T1 executing under this isolation level can access data changed by concurrent transaction(s). For example, if a concurrent transaction T2 updates a row R1, it can still be read under T1 even though T2 can potentially roll back later.
o Pros: No read locks needed to read data (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified.
o Cons: Data is not guaranteed to be transactionally consistent.
o Usage: It is typically used in queries/applications where data inconsistency can be tolerated. For example, computing average salary of employees.
· Read Committed: A transaction T1 executing under this isolation level can only access committed data. For example, if a concurrent transaction T2 updates a row R1, it cannot be accessed under T1, in fact T1 will get blocked until T2 either commits or rolls back. The S (i.e. Share) lock is held when accessing the data and is released once the access is complete. So for example, if T1 reads R1, R2, and R3 in a SQL statement, T1 acquires/releases S lock on R1 and then acquires/releases lock on R2. In some cases, the ‘S’ lock is not released until the end of the statement but for now we will not discuss that.
o Pros: Good compromise between concurrency and consistency.
o Cons: Locking and blocking. The data can change when accessed multiple times within the same transaction.
o Usage: Very commonly used isolation level. In fact, this is the default isolation level in SQL Server.
· Repeatable Read: A transaction T1 executing under this isolation level can only access committed data with an additional guarantee that any data read cannot change (i.e. it is repeatable) for the duration of the transaction. SQL Server achieves it by holding an S lock for the duration of the transaction. However, it does not protect phantoms, new data rows that qualify the query predicate can appear.
Example: Assume there is a table ACCOUNTS that stores customer’s account information along with the money balance.
Ø Start a transaction (T1) under repeatable read isolation level. Query the ACCOUNTS table with predicate (account_balance > 1000). Let us say it returns 10 rows
Ø Another transaction (T2) inserts a new row in the ACCOUNTS table with account_balance = 1020 and commits.
Ø Query the ACCOUNTS table again with the same predicate (account_balance > 1000) and it will return 11 rows. The reason is that transaction T1 only locked the 10 qualifying rows but did not lock the predicate range. With the result, the transaction T2 could insert a new row in the same predicate range.
o Pros: Higher data consistency.
o Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency. It does not protect against phantom rows.
o Usage: Not very common.
· Serializable: A transaction T1 executing under this isolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if range lock can’t be acquired (i.e. no index on the predicate column) for the duration of the transaction.
o Pros: Full data consistency including phantom protection. Serializable isolation level guarantees transactions will end up with one possible serial order with an appearance that concurrent transactions did not interfere with each other. For example, if T1, T2, T3 are running under serializable isolation level, the possible serial orders are (T1, T2, T3), (T1, T3, T2), (T2, T1, T3), (T2, T3, T1), (T3, T1, T2), (T3, T2, T1).
o Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.
o Usage: It is used in cases where data consistency is an absolute requirement.
In addition to these isolation level, SQL Server, starting with SQL Server 2005 release, added two changes as follows
· Read-Committed-Snapshot (RCSI): This is not a new isolation level but a new implementation of read committed isolation level that does not take any S lock on the data. The word snapshot stems from the fact that query under RCSI sees the snapshot of the database as of the beginning of the statement. It is a better alternative for applications that must access only committed data but without taking locks. SQL Server implements it using row versioning that has some overhead. You can map blocking implementation of read committed isolation level to RCSI by enabling READ_COMMITTED_SNAPSHOT option at the database level without forcing any changes to applications.
o Pros: Non-blocking access to transactionally consistent data.
o Cons: Some overhead of maintaining row versions.
o Usage: To minimize reader/writer blocking and to replace ‘read uncommitted’ or NOLOCK access to data with transactional consistent data
· Snapshot Isolation (SI) : This is new proprietary isolation level that provides non-blocking access for read operations. The transactions under SI see the snapshot of the database as of the beginning of the transaction. There is no automatic mapping of transaction isolation levels to SI so you must change your application to access data under SI isolation level.
o Pros: Non-blocking access to transactionally consistent data. Higher consistency than repeatable read.
o Cons: Some overhead of maintaining row versions.
o Usage: To get non-blocking access to consistent data across multiple statements within a transaction.
As an aside, you can explicitly set isolation levels by executing ‘set transaction isolation level <isolation>’ or you can override it at an object level in a statement using locking hints.
Thanks
Sunil Agarwal
Comments
Anonymous
February 28, 2011
I have had great time reading your article. It’s actually quiet awesome, it is surprising how you have learn t to write your feelings and thoughts in so precise manner. It's good really. <a href="http://www.byebyebigbrother.com">anonymous transactions </a>Anonymous
March 03, 2011
Thanks Sunil for the Concurrency series. It will help me to explain to my customers what the impact is of all different isolation levels. I was already aware of it. But customers will accept it easier when they can read it from Microsft people. I have put it some links to this series on my blog. www.keepitsimpleandfast.com/.../more-details-about-concurrency-locking.html Gr. AndréAnonymous
March 31, 2011
In some cases, the ‘S’ lock is not released until the end of the statement but for now we will not discuss that. Could you please explain in what scenarios, SELECT will hold until the end of the statement? ThanksAnonymous
May 12, 2011
You said: In some cases, the ‘S’ lock is not released until the end of the statement but for now we will not discuss that. I use default isolation level, and want to know if this is susceptible to deadlocks. thanks.Anonymous
May 13, 2011
To avoid reader/writer blocking/deadlock, we recommend RCSI option on the database. thanks SunilAnonymous
January 16, 2012
Since I am new to transaction processing, I was reading various documents about isolation levels from BOL, SQL Server 2008 Internals and similar books. However, all of them were not very clear and rather confusing. Then I found this blog, which really helped me understand the differences between all transaction isolation levels. Thank you for precisely and concisely explaining this topic!Anonymous
June 25, 2012
Very nicely explained. Thank you.Anonymous
June 28, 2012
The comment has been removedAnonymous
July 01, 2012
When you run query under RCSI or even under 'read uncommitted', SQL Server still takes SCH-M lock. This will conflict with Alter Index rebuild that takes SCH-M lock. For this scenario, I expect blocking not deadlock. Have you analyzed the deadlock graph? what is causing the deadlock? thanks sunilAnonymous
July 01, 2012
Thanks Sunil. When Rebuild index takes place it would have a special lock called SCH-M lock with other locks. But can you please let me know what is there for select Statement to do with this when Snapshot is enabled. Because the data it is looking for, will be in Tempdb. (Pls correct me if i am wrong) Anyhow i will run the trace to collect the deadlock graph. But please tell me, how do we ensure a particular query to have a isolation level = RCSI. If we set database with RCSI, by default will all the processes running on the database will be running with RCSI.Anonymous
July 02, 2012
RCSI setting will map all queries under read committed to use row versioning. Please note, the queries running under any isolation level will take SCH-S (schema stability lock). The fact that row versions are in TempDB is more of an implementation detail. Please analyze your deadlock graph. It will provide you the clues thanks SunilAnonymous
August 16, 2012
Hi Sunil, Hope u r doing well. After capturing the deadlocks through profiler , this time its opposite way around, where select statement was trying hold Sch-S lock mode on tables but it was been blocked by Alter Index Statements which was already holding Sch-M lock mode. and Select statement was found as a victim and was cancelled. Though the index job went good this time, But my concern here is , if this continues , how do we stop this ,apart from changing the schedule of either the Alter index MP or Select tasks . Your help is much appreciated.Anonymous
November 26, 2012
check this article www.aboutsql.in/.../isolation-levels.htmlAnonymous
January 28, 2013
Good article. Repeatable read is default in Mysql afaik, so this isolation is not exactly - "not very common". :-)Anonymous
March 07, 2013
Are RCSI and SI the same except that automatic mapping is not possible in SI?Anonymous
September 06, 2013
in Read Uncommitted isolation you mentioned " Note, T1 still takes transaction duration locks for any data modified.". Can you please explain what you mean by transaction duration locks ?Anonymous
November 25, 2013
answering previous question: "data modified" means, all other dml statements besides select operationAnonymous
June 16, 2014
Very well explained with good examples. Thanks.Anonymous
June 26, 2014
I found great article on Isolation levels with good examples in the site www.besttechtools.com/.../sql-server-isolation-levels-by-example