{"id":128,"date":"2010-09-02T14:36:19","date_gmt":"2010-09-02T14:36:19","guid":{"rendered":"http:\/\/blog.joefield.co.uk\/?p=128"},"modified":"2010-09-02T16:11:35","modified_gmt":"2010-09-02T16:11:35","slug":"sql-server-isolation-levels","status":"publish","type":"post","link":"https:\/\/blog.joefield.co.uk\/?p=128","title":{"rendered":"SQL Server Isolation Levels"},"content":{"rendered":"<p>I keep deleting this then needing it again, so here it is!\u00a0 It needs an update to discuss snapshot isolation &#8216;though.<\/p>\n<p>Thanks to Andy Grout for working this stuff out with me\u2026<\/p>\n<p>All isolation levels affect the way in which you <strong>read<\/strong> data. They also affect the way others can update data because the type and duration of the locks differ.<\/p>\n<h4>Read Uncommitted<\/h4>\n<ul>\n<li>Takes out no locks when issuing SELECT statements<\/li>\n<li>So you can read other transactions\u2019 uncommitted data i.e. <strong><em>dirty reads<\/em><\/strong><\/li>\n<\/ul>\n<h4>Read Committed<\/h4>\n<ul>\n<li>Prevents <strong><em>dirty reads<\/em><\/strong><\/li>\n<li>Takes out a shared lock when issuing a SELECT statement (this prevents updates)<\/li>\n<li>If another transaction has an exclusive lock your SELECT will block until the other completes<\/li>\n<li>Releases the shared lock when the <strong>SELECT<\/strong> completes, not when the tranaction completes<\/li>\n<li>So the same SELECT later on in the same transaction might return different data i.e. <strong><em>non-repeatable reads<\/em><\/strong><\/li>\n<\/ul>\n<h4>Repeatable Read<\/h4>\n<ul>\n<li>Prevents <strong><em>dirty reads<\/em><\/strong> and <strong><em>non-repeatable reads<\/em><\/strong><\/li>\n<li>Takes out shared locks when issuing SELECT statements and doesn\u2019t release them until the transaction ends<\/li>\n<li>So prevents any data you read from changing via updates or deletes<\/li>\n<li>But doesn\u2019t prevent new rows from being inserted into other pages<\/li>\n<li>So your SELECT statement might still return more rows the second time you execute it i.e. <strong><em>phantom rows<\/em><\/strong><\/li>\n<\/ul>\n<h4>Serializable<\/h4>\n<ul>\n<li>Prevents <strong><em>dirty reads<\/em><\/strong>, <strong><em>non-repeatable reads<\/em><\/strong> and <strong><em>phantom rows<\/em><\/strong><\/li>\n<li>In SQL 2000 and SQL 2005 takes out key-range locks, a special type of shared lock which prevents inserts into a range of values<\/li>\n<li>Ensures a SELECT statement always returns the same results within a transaction<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>I keep deleting this then needing it again, so here it is!\u00a0 It needs an update to discuss snapshot isolation &#8216;though. Thanks to Andy Grout for working this stuff out with me\u2026 All isolation levels affect the way in which you read data. They also affect the way others can update data because the type [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/128"}],"collection":[{"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=128"}],"version-history":[{"count":3,"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/128\/revisions"}],"predecessor-version":[{"id":131,"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=\/wp\/v2\/posts\/128\/revisions\/131"}],"wp:attachment":[{"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=128"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=128"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.joefield.co.uk\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}