The four standard SQL Server isolation levels that SQL Server provides are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. (I leave aside the row-versioning levels for another day). SQL Server implements these isolation levels using locks.
In fact I was listening to Kimberly Tripp’s MCM Preparation Video “Snapshot Isolation“. She gives a recap of isolation levels and describes the locks taken during an index scan using READ COMMITTED. She says:
“(For readers) READ COMMITTED uses shared locks … the shared locks are only held for the life of the resource being read. You can almost think of shared locks kind of trickling through the table but not being kept.”
She invites us to visualize this scenario and so I did, literally. Enjoy!
Locks taken by SQL Server for the standard isolation levels
Locks taken by SQL Server for the standard isolation levelsOther reading
- Kalen Delaney’s book on Isolation SQL Server Concurrency – Locking, Blocking and Versioning
- Kimberly Tripp’s MCM preparation video Snapshot Isolation
- My own series on transaction isolation levels.
- A resource page on Isolation Levels compiled by Kendra Little.
It’s simplistic, but it captures in my head what is going on inside SQL Server.
Comment by Michael J. Swart — June 13, 2012 @ 12:09 pm
Maybe I should have drawn the faces with expressions varying from optimistic to pessimistic
Comment by Michael J. Swart — June 13, 2012 @ 2:31 pm
[…] Visualizing Transaction Isolations For SQL Server – A picture speaks a thousand words, well maybe not that many, depends on the picture I guess. SQL eduction through visual creativity with Michael J. Swart (Blog|Twitter). Did you catch his brilliant guest post – The Last Starfighter here this week! […]
Pingback by Something for the Weekend - SQL Server Links 15/06/12 — June 15, 2012 @ 5:01 am
That’s beautiful! Nice job. One very minor tweak: nolock/uncommitted does still need a schema stability lock. You can’t change the table while selects with nolock are running. This can produce an ugly scenario:
9:00AM – SPID 100 starts a SELECT WITH NOLOCK scanning a big table.
9:01AM – SPID 101, the DBA, tries to drop an index, and he’s blocked by SPID 100. His query waits.
9:02AM – SPID 102 tries to start a SELECT WITH NOLOCK. They’re blocked by SPID 101.
If it wasn’t for SPID 101, the 102 select would speed right through, but he’s being blocked by SPID 101, who’s being blocked by SPID 100. That’s why it’s helpful to know that WITH NOLOCK doesn’t really mean no locks. I wouldn’t change the well-done illustration, but I just wanted to get this comment into the post to help readers who might think the NOLOCK guy isn’t taking any locks at all.
Comment by Brent Ozar — June 15, 2012 @ 7:18 am
Hey Brent,
Thanks for the comment (I think Kendra likes to explain how she discovered that tip through experience).
I actually had considered adding that to the diagram (maybe as a special kind of lock hanging out somewhere). In the end K.I.S.S. won out. That and the C# to render and animate was trickier than I guessed.
As a further help to the readers, I’ve updated the post.
Comment by Michael J. Swart — June 15, 2012 @ 7:54 am
Yeah, the way I learned it was when a client called in a panic asking how they could drop indexes on an insanely busy production system. Their DBA had gotten into the habit of adding several indexes to speed up peak monthly reports, and then dropping them later. He’d forgotten to drop them before the heavy insert/update/delete activity started. Whoops…
Comment by Brent Ozar — June 15, 2012 @ 8:22 am
Great post. Pictures do speak a thousand words.
Comment by Jun Reyes — June 19, 2012 @ 9:25 pm
[…] Visualizing Transaction Isolations For SQL Server […]
Pingback by Link Resource # 59 : Jun 06 – Jun 22 « Dactylonomy of Web Resource — June 21, 2012 @ 1:20 am
[…] You can see that the update command is holding an X lock on one row and waiting for another X lock. The SELECT statement is also holding a shared (S) lock and waiting for another shared lock. But why the SELECT statement is holding a shared lock under READ COMMITTED isolation level? As stated in a previous post, SELECT statements should only hold the shared lock for the duration of processing the row. For a visual representation of the cocept of lock duration under different isolation levels, see Micheal J Swart’s (b |t) post “Visualizing Transaction Isolations For SQL Server” […]
Pingback by DEADLOCK on SELECT due to UNORDERED PREFETCH « sqlindian – blog Uncommitted — July 13, 2012 @ 8:55 pm
There are few scenarios where Shared locks will be held until the end of the statement in READ COMMITTED isolation level. Craig Freedman blogged about there here. (http://blogs.msdn.com/b/craigfr/archive/2007/06/07/read-committed-and-bookmark-lookup.aspx)
Sometimes this behavior can lead to unexepected deadlock. I’ve blogged about it here (http://sqlindian.com/2012/07/13/deadlock-on-select-due-to-unordered-prefetch/)
Comment by Roji Thomas — July 16, 2012 @ 9:03 pm