When learning how Read Committed Snapshot Isolation works in SQL Server, it can be a little tricky to understand how writes behave. The basic way I remember this is “Readers don’t block writers, writers don’t block readers, but writers still block writers.”
But that’s not so easy to understand. Let’s take a look at a simple test showing what it means that “writers still block writers”.
Create our simple test schema
We’re doing a super simple test: we just need a database with READ_COMMITTED_SNAPSHOT enabled and a table with a single row.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE DATABASE RCSI_Updates GO ALTER DATABASE RCSI_Updates SET READ_COMMITTED_SNAPSHOT ON GO USE RCSI_Updates GO CREATE TABLE dbo.UpdateTest ( i int identity primary key, Color varchar(256) not null ); GO INSERT dbo.UpdateTest (Color) VALUES ('White'); GO |
Start one transaction that does an update (Session #1)
An update begins! To test this out, we’re doing an explicit BEGIN TRAN so that our update will hold locks. Run this in one session in SQL Server Management Studio:
|
1 2 3 4 5 6 |
BEGIN TRAN UPDATE dbo.UpdateTest SET Color='Red' WHERE Color='White'; /* Transaction left open on purpose*/ |
Another update begins! But it’s blocked. (Session #2)
Before that update transaction complete, another session comes in. Just open up another session window in SSMS and run this:
|
1 2 3 4 |
UPDATE dbo.UpdateTest SET Color='Black' WHERE Color='White'; GO |
This won’t complete right away, because it’s blocked by your first session.
Back in Session #1, run:
|
1 2 |
COMMIT GO |
When Session #1 commits, it finishes up and cleans up its lock. Session #2 is also able to finish, and in its output you’ll see:
[code] (0 row(s) affected)[/code]
Who “wins”?
Let’s check the rows in the table:
|
1 2 3 |
SELECT * FROM dbo.UpdateTest; GO |
Here’s how things happened:
- Session #1 locked the row and turned the marble red.
- Session #2 was also doing an update, and it didn’t get to the row until Session #1 released the lock.
- At that point it found zero rows to update, because the color had already changed.
If You Wrote the Update Differently, You Might Get a Different outcome…
Remember, writers do NOT block readers under read committed snapshot isolation. What if someone wrote this as a two part operation (instead of a single update statement)?
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE #marbles (i int) INSERT #marbles (i) SELECT i FROM dbo.UpdateTest WHERE Color='White'; UPDATE dbo.UpdateTest SET Color='Black' FROM dbo.UpdateTest u JOIN #marbles m on u.i=m.i; GO |
When we check how things turned out…
In this case, Session #2 wasn’t completely blocked! Things happened differently:
- Session #1 locks the row, that’s the same
- Session #2 is doing a SELECT statement, so it uses the version store and isn’t blocked. It’s able to populate the temp table.
- But Session #2 becomes blocked at the point it runs the UPDATE.
- When Session #1 completes, Session #2 is unblocked and updates using the data it’s cached in its temp table.
- Session #2 changes the color of the marble.
I’m not saying that it’s always better to do a single UPDATE or that it’s always better to use a temp table. You just have to know how they behave so you can pick the right code for your situation.
If you need to use a temporary table for performance reasons, but want your read to be blocked if any writer has a lock, you can achieve that by using locking hints. Which might cause a lot of blocking, just like anytime you use locking hints.
What About Snapshot Isolation?
In this post we’ve looked at Read Committed Snapshot Isolation. There’s another isolation level, SNAPSHOT, which behaves a bit differently. If you want to test that out, you can modify the code samples above to reproduce an “update conflict” with both of the sessions using SNAPSHOT isolation.
This May Sound Complicated, But It’s Not That Bad
When you talk about any isolation level in detail in SQL Server, things get complicated. I don’t think that Read Committed Snapshot Isolation is any more complicated than plain old Read Committed, it’s just that people don’t think too much about Read Committed, since it’s the default!
19 Comments. Leave new
You state that in READ COMMITTED, “Readers don’t block writers”. However, according to http://msdn.microsoft.com/en-us/library/ms173763.aspx, when in READ COMMITTED “the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation”. This sounds like readers do block writers. Am I misunderstanding something about this?
That’s true! This article is just about Read Committed Snapshot Isolation, RCSI, which changes the behavior of Read Committed.
The best single-line description I’ve found in msdn is: “If you set the READ_COMMITTED_SNAPSHOT database option to ON, the database engine uses row versioning and snapshot isolation as the default, instead of using locks to protect the data.”
That is somewhat buried in this page: http://msdn.microsoft.com/en-us/library/tcbchxcb
This post is specifically about how locks and data modification statements under RCSI.
Hope this helps!
Hi Kendra,
Good post, as always. But I see your samples a little bit messed up. I’l try to explain:
In your first scenario, you try to update in both session 1 and 2 the white marble. It is 100% clear after your explanation that session 2 has to wait for session 1, and then it won’t find a white marble, because it will be already red, so nothing else will happen, and the marble will stay red.
But in your second scenario, you insert into the temp table, and then JOIN with the UpdateTest table by column “i” and not by “Color”. This will make the second update to work always, reagardless of what the first session does, and, thus, to change the marble color to black. This won’t happen by the fact that you are using a temp table. If you would had made the join by color (u.color = m.color) the update wouldn’t had found the white marble, as it happened in the first scenario.
Or, otherwise, if you had written “WHERE i=1;” instead of “WHERE Color=’White’;” in your SESSION 2 for the first scenario, it would have updated the marble to black as well.
Maybe I am missing something. But I think that this subtle difference make both samples not comparable. Am I wrong?
Best regards and Merry Christmas,
Jaime
Hi Jaime,
You’re correct– the examples are logically different. The point I was trying to make with the code samples was simply that if you’re turning on RCSI for an existing codebase, outcomes may vary depending on which patterns you’re using.
Reading through your comment did make me realize that I haven’t made a core part of this totally clear in the article, which is:
1) Sample 1 will have the same outcome under RCSI and read committed
2) Sample 2 will NOT have the same outcome — under RCSI the marble will be black, and under Read Committed the marble will be Red (because they get blocked at different points).
I think you already understand that part, but a lot of folks don’t and are confused why it’s not always safe to just turn on RCSI.
Thank you very much for your thoughtful comment!
Hi Kendra,
The first example that you demonstrated has nothing to do with Read commited Snapshot Isolation levels. Thats the default behavior of Transaction. When you run the first small script in Transaction in any isolation level, it will hold locks on it and then you run the second script to update the same record, second script has to wait until first transaction is completed(committed or rolled back).
Only difference in read committed Snapshot isolation level is you can still read the committed version of data and while any transaction is updating the data and is not committed yet.
Thanks,
Taj
Example 1 is completely relevant to a discussion of Read Committed Snapshot Isolation for more than one reason.
Notice that the UPDATE statement has a where clause in it. Many people are not sure if “read” from the WHERE clause is executed using the version store under RCSI and if that could change the behavior. The example shows what happens.
The code in Example 1 will also not work the same way in all isolation levels. If you run it under Snapshot isolation, session 2 will fail with an update conflict. Other folks read about this and want to know if it could occur under RCSI.
The examples in this post were written to answer multiple questions from readers, and they weren’t dumb questions at all. They were very smart questions from people struggling to learn something complicated.
Something that I ran into while running SQL “processing” code with RCSI ON is that I can write to a table one time during this process with no problems. But when I attempt to write again I get a timeout. My testing consisted of updating someones account twice while this process was running (2 separate update/insert statements executed through Entity Framework 6.x). Does my first “account update” need to wait until the background process that touches the same table(s) completes before actually having its changes committed, hence locking those rows or do I have a different problem on my hands?
Oh, interesting!
The first thing I would do would be to do some tracing against a test environment to confirm exactly what’s going on. It sounds like it’s the following, but I’m not certain:
Write 1:
BEGIN TRAN
INSERT ….
Write 2, in a separate session:
INSERT [into the same table]
You’d need to dig into the SQL Server and analyze what’s happening to figure out if that’s what’s going on or not.
The description is not 100% clear for me, but if You don’t want two writes to the same row block each other, they have to be in the same transaction (and consequently the same connection). The first write will acquire lock on the row and keep it until the transaction commits or rollbacks. And there is also possibility that many row locks can escalate to table lock.
Yes, good points.
I find this discussion useful. In my case READ_COMMITTED_SNAPSHOT is set to ON. I understand its meaning and implications. After setting this on for first three days there were no SQLTime outs on SELEC. However recently I found SQL Time outs ( No deadlocks )
Is this related to temdb management ?
Shrikant
The first example is clear. 1 script to create the initial data and 2 others for the two sessions.
The second example is confusing. Only one script that creates the table and updates it. What are the scripts for the two sessions? And does only one of them leave a transaction open (as in the ifrst example)?
The second example is just a replacement for Session #2 in Example 1.
[…] possível queda de performance. Caso você queira saber mais sobre ele, sugiro a leitura do artigo Read Committed Snapshot Isolation: Writers Block Writers (RCSI), do grande mestre Brent […]
[…] em uma abordagem mais completa do que utilizar esse hints, que seria utilizar o modo de isolamento Read Committed Snapshot (RCSI), que permite utilizar o modo Read Commited sem travar as leituras quando ocorrem transações […]
How would RCSI react/handle in a case where attempting to insert new records into the database at the same time? Wont this cause PK violations in some cases where they wouldn’t have before (where records are in the same transaction) such as allocation?
No. RCSI is only about readers & writers coexisting, not writers.
How do I have long running selects blocking inserts with RCSI enabled??
Troubleshooting specific queries is beyond what I can do in blog post comments, unfortunately.