{"id":2774,"date":"2015-05-05T19:21:38","date_gmt":"2015-05-06T02:21:38","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/kimberly\/?p=2774"},"modified":"2015-05-06T09:04:45","modified_gmt":"2015-05-06T16:04:45","slug":"locking-isolation-and-read-consistency","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/locking-isolation-and-read-consistency\/","title":{"rendered":"Locking, isolation, and read consistency"},"content":{"rendered":"<p>Today I\u00a0ran\u00a0into\u00a0a\u00a0really interesting locking \/ blocking problem that I want to discuss&#8230; originally, a related discussion\u00a0came up last week in our IEPTO1 course (<a href=\"https:\/\/www.sqlskills.com\/sql-server-training\/iepto1\/\" target=\"_blank\">Immersion Event on Performance Tuning &#8211; Part 1<\/a>) but today I ran into a similar problem that brought me back to the original discussion and I was able to reproduce the original scenario with\u00a0a really interesting combination of things (some of which I did not expect and that led me down a super interesting path). However,\u00a0before I can get to the weirdness (and try to make sense of it), I need to make sure the foundation is set well&#8230; In IEPTO1,\u00a0I discuss locking types and how long locks are held for the different isolation levels &#8211; spending a great deal of time talking about the default mode of locking (read committed &#8211; with locking) vs. all other options:<\/p>\n<ul>\n<li><strong>Read uncommitted<\/strong> ( same as using\u00a0NOLOCK ) and a lower isolation level than the default. This allows dirty reads.<\/li>\n<li><strong>Read committed using locking<\/strong> ( this is the DEFAULT when <span style=\"color: #000000;\">read_committed_snapshot<\/span> has NOT\u00a0been turned on )<\/li>\n<li><strong>Read committed using version<\/strong> ( this is the DEFAULT when <strong><span style=\"color: #000080;\">read_committed_snapshot<\/span><\/strong> has been turned on )<\/li>\n<li><strong>Repeatable reads<\/strong> ( which also uses locking &#8211; even when one or both flavors of versioning has been enabled )<\/li>\n<li><strong>Serializable<\/strong> ( which also uses locking [ same as using HOLDLOCK ] &#8211; even when one or both flavors of versioning has been enabled )<\/li>\n<li><strong>Snapshot Isolation<\/strong> ( which is ALLOWED if <span style=\"color: #000080;\"><strong>allow_snapshot_isolation<\/strong><\/span> has been turned on and the client requests it, but won&#8217;t be used by anyone unless <span style=\"color: #000080;\"><strong>SET TRANSACTION ISOLATION LEVEL SNAPSHOT<\/strong><\/span> has been requested. Here you&#8217;ll have all the overhead of versioning without anyone using it <em>unless<\/em> you make code changes for snapshot isolation.)<\/li>\n<\/ul>\n<p><strong>Essentially, your database can be configured for\u00a0one of FOUR possible states:<\/strong><\/p>\n<ol>\n<li>No options set (connections default to <strong>read committed using locking<\/strong>)<\/li>\n<li>ONLY <strong><span style=\"color: #000080;\">read_committed_snapshot<\/span><\/strong> set ( connections default to <strong>read committed using versioning<\/strong>; no other code changes needed for read committed statements ). This provides statement-level read consistency. Every read will reconcile to the point in time when the STATEMENT started.<\/li>\n<li>ONLY <strong><span style=\"color: #000080;\">allow_snapshot_isolation<\/span><\/strong> set (\u00a0connections DEFAULT to read committed using LOCKING &#8211; because of the absence of <span style=\"color: #000080;\">read_committed_snapshot\u00a0<\/span>). For those that request snapshot isolation,\u00a0this provides transaction-level read consistency. Every read will reconcile to the point in time when the TRANSACTION\u00a0started.<\/li>\n<li>BOTH\u00a0<strong><span style=\"color: #000080;\">read_committed_snapshot<\/span><\/strong>\u00a0and\u00a0<strong><span style=\"color: #000080;\">allow_snapshot_isolation<\/span><\/strong>\u00a0set. Without snapshot transaction isolation level requested, statements will reconcile to the point in time that the statement started and when snapshot isolation is requested, then all statements will reconcile to the point in time that the transaction began.<\/li>\n<\/ol>\n<p>To be honest, I&#8217;ve been wanting to get that &#8220;simple&#8221; view of the locking world written down for a while. Even the combination of options ( that a database can really be configured into one of four possible states ) is not very well known. Often content\u00a0about versioning just states\u00a0that you need to turn both options on\u00a0and they don&#8217;t describe that they&#8217;re really distinct environments.<\/p>\n<h2><strong>Statement-level Read Consistency<\/strong><\/h2>\n<p>What I love about <strong><span style=\"color: #000080;\">read_committed_snapshot<\/span><\/strong> is that you get statement-level read consistency&#8230; what this means is that you can get a definable point in time to which your statement reconciles &#8211; that point, the time when the statement started. So, if you ask for a count of rows, you get THE count of rows that were present when your statement started. The best part about it is that this count is not only accurate to the point in time that hte statement started, it also does so without preventing transactions \/ locks on the object where you&#8217;re counting rows. Without a lot of detail here &#8211; it does this by copying the transactionally consistent VERSION of the row into the version store and leaving behind a pointer to allow readers to use without being blocked AND without blocking other writers. The marketing tagline makes it sound perfect ( readers don&#8217;t block writers and writers don&#8217;t block readers ). All of this happens AUTOMATICALLY and without code changes for all statements running with read committed isolation. If a statement has a hard-coded lock hint ( like NOLOCK or HOLDLOCK, etc. ) then their statement-level hints will override this and use locking.<\/p>\n<p><strong>NOTE:<\/strong> Windows Azure SQL Database defaults to read committed using VERSIONING for new databases.<\/p>\n<h2><strong>Transaction-level Read Consistency<\/strong><\/h2>\n<p>What I love about <span style=\"color: #000080;\"><strong>allow_snapshot_isolation<\/strong><\/span> is that you don&#8217;t have to use it, unless you really want version-based TRANSACTIONS. And, oddly, many of you won&#8217;t want this for the majority of your complex updates \/ OLTP transactions ( where you&#8217;ll also have to deal with version-based reads and update conflicts ). If your writers are in read committed using locking OR read committed using versioning then an update will use locks and not version-based reads so conflicts are prevented. The place to use transaction-level read consistency is for\u00a0&#8220;transactions&#8221; that will handle multiple reads across volatile data.\u00a0Imagine that you want to have 3 reports \/ queries reconcile to the same point in time ( and you want to do this real-time reporting in your OLTP environment ). What you could do is this:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nSET TRANSACTION ISOLATION LEVEL SNAPSHOT;\r\nGO\r\nBEGIN TRANSACTION\r\nSELECT... --query1\r\nSELECT... --query2\r\nSELECT... --query3\r\nCOMMIT TRANSACTION\r\nGO<\/pre>\n<p>So, your use of snapshot isolation should be a bit\u00a0more limited IMO. And, largely limited to controlled reporting. Yes, you can do this with modifications\u00a0\/ transactions&#8230; but if you have transaction-level read consistency and then you have multiple readers \/ writers to the same data then you&#8217;ll have to make sure you have good error handling ( you should be using TRY \/ CATCH ) so that you can deal with the conflict\u00a0detected by SQL Server and resolved with the following error:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nMsg 3960, Level 16, State 2, Line 6\r\nSnapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.member' directly or indirectly in database 'Credit' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update\/delete statement.<\/pre>\n<h2>In Summary<\/h2>\n<p>So, to bring it all together. All LOCKING discussions really need to start with how the session and the database are\u00a0handling isolation. Different behaviors occur in\u00a0the\u00a0different configurations\u00a0and some locks are held for different amounts of time depending on your isolation level.<\/p>\n<p>And, that&#8217;s a good start for now. I wanted to get a few of these basics out there first as\u00a0I have a couple of\u00a0problems I want to describe (and resolve) with long running transactions and blocking. And, some special cases with schema locks as well as some special code I&#8217;ve written to reduce some of the horrible problems you can run into with long blocking chains (and my code works across earlier versions of SQL Server and for MORE statements than just partition switching and index rebuilds&#8230; but, I&#8217;m getting ahead of myself on this one). Stay tuned,\u00a0I&#8217;m going to get to those in tomorrow&#8217;s post and I&#8217;ll compare them against the low-priority lock wait feature in SQL Server 2014.<\/p>\n<p>Also, if you&#8217;re more interested in learning more about versioning &#8211; check out the whitepaper I wrote (for SQL 2005) and which was updated for name changes\u00a0and a few other things\u00a0by Neal Graves ( thanks Neal! ):\u00a0<a href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ms345124(v=sql.90).aspx\" target=\"_blank\">SQL Server 2005 Row Versioning-based Transaction Isolation<\/a><\/p>\n<p>Thanks for reading!<br \/>\nk<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I\u00a0ran\u00a0into\u00a0a\u00a0really interesting locking \/ blocking problem that I want to discuss&#8230; originally, a related discussion\u00a0came up last week in our IEPTO1 course (Immersion Event on Performance Tuning &#8211; Part 1) but today I ran into a similar problem that brought me back to the original discussion and I was able to reproduce the original [&hellip;]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[89,88,43,90],"tags":[],"class_list":["post-2774","post","type-post","status-publish","format-standard","hentry","category-blocking","category-locking","category-manageability","category-troubleshooting"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2774","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=2774"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2774\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=2774"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=2774"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=2774"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}