{"id":400,"date":"2017-12-28T20:27:23","date_gmt":"2017-12-28T19:27:23","guid":{"rendered":"http:\/\/blog.pjsen.eu\/?p=400"},"modified":"2022-02-22T13:33:40","modified_gmt":"2022-02-22T12:33:40","slug":"basic-example-of-sql-server-transaction-deadlock-with-serializable-isolation-level","status":"publish","type":"post","link":"https:\/\/blog.pjsen.eu\/?p=400","title":{"rendered":"Basic example of SQL Server transaction deadlock with serializable isolation level"},"content":{"rendered":"<p>Today I am demonstrating a deadlock condition which I came across after I had accidentally increased isolation level to <em>serializable<\/em>. We can replay this condition with the simplest table possible:<\/p>\n<p><script src=\"https:\/\/gist.github.com\/przemsen\/97eaf5028e91b9111fae417055eb9c3e.js?file=blog-2017-12-28-sql1.sql\"><\/script><\/p>\n<p>Now let&#8217;s open SSMS (BTW, since version 2016 there is an installer decoupled from SQL Server available <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssms\/download-sql-server-management-studio-ssms\">here<\/a>) with two separate tabs. Please consider, that each tab has its own connection. Then execute the following statement to increase isolation level in both tabs:<\/p>\n<p><script src=\"https:\/\/gist.github.com\/przemsen\/97eaf5028e91b9111fae417055eb9c3e.js?file=blog-2017-12-28-sql2.sql\"><\/script><\/p>\n<p>The following code tries to resemble application level function which does a bunch of possibly time consuming things. These are emulated with <code>WAITFOR<\/code> instruction. But the point is that the transaction does both <code>SELECT<\/code> and <code>UPDATE<\/code> on the same table having those time consuming things in between.<\/p>\n<p><script src=\"https:\/\/gist.github.com\/przemsen\/97eaf5028e91b9111fae417055eb9c3e.js?file=blog-2017-12-28-sql3.sql\"><\/script><\/p>\n<p>Let&#8217;s put the code in both tabs and then execute one tab and the second tab. After waiting more than 10 seconds, which is the delay in code, we will observe an error message on the first tab:<\/p>\n<pre>\r\nMsg 1205, Level 13, State 56, Line 8\r\nTransaction (Process ID 54) was deadlocked on lock resources with another \r\nprocess and has been chosen as the deadlock victim. Rerun the transaction.\r\n<\/pre>\n<p>This situation occurred in a web application where concurrent execution of methods is pretty common. For application developer it is very easy to be tricked into thinking that having set <code>SERIALIZABLE<\/code> isolation level we magically make sequential execution of our SQL code. But this is wrong. By setting <code>SERIALIZABLE<\/code> level we <strong>do not<\/strong> automatically switch the behavior of the code wrapped with transaction to the behavior of <code>lock<\/code> statement known from C# (technically <code>lock<\/code> is a <em>monitor<\/em>).<\/p>\n<p>I would advise having a closer look at the instructions wrapped in transaction. In real application the execution flow is much more `polluted` with an ORM calls, but my simplified code from above just tries to model common scenario of reads followed by writes. What happens here is that SQL Server takes a reader lock on the table after executing the <code>SELECT<\/code>. When we execute the code again in another session we have one more reader lock taken on the table. Now when the first session passes <code>waitfor<\/code> and comes to <code>UPDATE<\/code> it needs to take a writer lock and waits (I am purposely using generic vocabulary instead of SQL Server specific one &mdash; these locks inside database engine all have their names). We observe the first tab waits more than 10 seconds. This is because when the first tab reaches its <code>UPDATE<\/code> it needs to take writer lock, but it is locked by the <code>SELECT<\/code> in the second tab. Conversely, the second&#8217;s tab <code>UPDATE<\/code> waits for the lock taken by the <code>SELECT<\/code> in the first tab. This is deadlock which fortunately is detected by the engine. <\/p>\n<p>The problem is caused by the lock taken witch <code>SELECT<\/code> instruction having <code>SERIALIZABLE<\/code> isolation level set. The lock is not taken in this place with <code>READ COMMITED<\/code> which is the default level.<\/p>\n<p>I am writing about this for the following reasons:<\/p>\n<ul>\n<li>\nThis is very simple scenario from the application point of view: to read some data, update the data, do some things and have all of this wrapped with a transaction.\n<\/li>\n<li>\nIt is very easy to make <strong>wrong<\/strong> assumption, that <code>SERIALIZABLE<\/code> level guarantees that our SQL code will be executed sequentially. But it only guarantees, that if the transactions execute, their observable effects will be as if they both executed sequentially i.e. one after another. But it is your job to make them actually execute not run into a deadlock.\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Today I am demonstrating a deadlock condition which I came across after I had accidentally increased isolation level to serializable. We can replay this condition with the simplest table possible: Now let&#8217;s open SSMS (BTW, since version 2016 there is an installer decoupled from SQL Server available here) with two separate tabs. Please consider, that<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-400","post","type-post","status-publish","format-standard","hentry","category-general-programming"],"_links":{"self":[{"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=\/wp\/v2\/posts\/400","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=400"}],"version-history":[{"count":0,"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=\/wp\/v2\/posts\/400\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=400"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=400"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.pjsen.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=400"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}