{"id":2393,"date":"2012-07-28T19:34:36","date_gmt":"2012-07-29T00:34:36","guid":{"rendered":"http:\/\/appcrawler.com\/wordpress\/?p=2393"},"modified":"2016-07-08T14:23:37","modified_gmt":"2016-07-08T19:23:37","slug":"sql-server-mvcc-with-read_committed_snapshot","status":"publish","type":"post","link":"http:\/\/appcrawler.com\/wordpress\/2012\/07\/28\/sql-server-mvcc-with-read_committed_snapshot\/","title":{"rendered":"SQL Server MVCC with read_committed_snapshot"},"content":{"rendered":"

Oracle originally built its flagship database product with multi-version consistency control. Just as it sounds, multiple versions of a row can exist, albeit only one can exist as the current row version at any given time. It accomplishes this by using rollback, or undo segments. From a simplistic perspective, when a row is changed, the previous version is written to the rollback segment chosen by Oracle. This provides two important benefits:<\/p>\n

1) Before the user commits, she can “undo” or rollback her changes to this version
\n2) If another user comes along and requests the row prior to our user committing her changes, he retrieves the previous version from the rollback segment into which it was written<\/p>\n

There are many other pieces to this design, but that suffices for this article. For more information, please see the Oracle Database Concepts guide available at tahiti.oracle.com<\/p>\n

Microsoft SQL Server long had a black eye with DBA’s, especially Oracle DBA’s, that it wasn’t a “real” database since it couldn’t provide the same functionality. In SQL Server 2005, Microsoft provided this functionality for the first time. There are two differences (at least) that I have found<\/p>\n

1) The feature can be enabled or disabled at the SQL Server individual database level
\n2) The pre change versions are written to the tempdb rather than the rollback segments as they are in Oracle<\/p>\n

This article will be the first of at least two that deal with this feature. In this test case, we show that after enabling it at the database level, writers no longer block readers. This was the bulk of the argument Oracle proponents made against SQL Server; a change made by User A would cause User B’s read of the same data to “hang” until User A committed her changes.<\/p>\n

Our source code for this test can be downloaded here.<\/a><\/p>\n

We first look at the out of the box configuration, in which writers block readers. We do so by ensuring our read_committed_snapshot is disabled at the database level. To make this change, no users can be logged into the database.<\/p>\n

We create three threads:<\/p>\n

1) one that deletes all rows from a test table, but doesn’t commit
\n2) one that tries to read all rows from our test table before the thread above commits
\n3) a helper thread that merely prints blocked sessions<\/p>\n

We see in the output below that our select thread is blocked by our delete thread, until the delete thread commits.<\/p>\n

\r\nc:\\java>java sqlServerBlockedReader off\r\nturned read_committed_snapshot off\r\nstarting select thread\r\nstarting delete thread\r\nstarting lock monitor thread\r\ndelete session spid = 51\r\ndeleted data at Sat Jul 28 20:12:21 EDT 2012, sleeping 30 seconds\r\nselect session spid = 52\r\nlocks session spid = 54\r\nstarting lock monitor loop at Sat Jul 28 20:12:24 EDT 2012\r\nstarting data select at Sat Jul 28 20:12:26 EDT 2012\r\nspid 52 is blocked by spid 51\r\nspid 52 is blocked by spid 51\r\nspid 52 is blocked by spid 51\r\nspid 52 is blocked by spid 51\r\nspid 52 is blocked by spid 51\r\nspid 52 is blocked by spid 51\r\nspid 52 is blocked by spid 51\r\nspid 52 is blocked by spid 51\r\ncompleted delete sleep at Sat Jul 28 20:12:51 EDT 2012\r\nfinished data select at Sat Jul 28 20:12:51 EDT 2012\r\n\r\nc:\\java>\r\n<\/pre>\n
In our second test, we enable read_committed_snapshot at the database level.  Once again, no user sessions can be connected, so we terminate any that may exist.<\/p>\n
Notice that in this test, our select completes immediately.  It can do this (with no code changes), because we have instructed our database to write the previous versions of all row changes to the tempdb.  We are also using the default read committed transaction isolation level, which would normally block as it did above.  However, based on the read_committed_snapshot setting, SQL Server is “smart” enough to read the previous version from tempdb.<\/p>\n
\r\nc:\\java>java sqlServerBlockedReader on\r\nturned read_committed_snapshot on\r\nstarting select thread\r\nstarting delete thread\r\nstarting lock monitor thread\r\ndelete session spid = 52\r\nselect session spid = 51\r\nlocks session spid = 54\r\ndeleted data at Sat Jul 28 20:13:07 EDT 2012, sleeping 30 seconds\r\nstarting lock monitor loop at Sat Jul 28 20:13:09 EDT 2012\r\nstarting data select at Sat Jul 28 20:13:11 EDT 2012\r\nfinished data select at Sat Jul 28 20:13:11 EDT 2012\r\ncompleted delete sleep at Sat Jul 28 20:13:37 EDT 2012\r\n\r\nc:\\java>\r\n<\/pre>\n
Our next article will address the performance implications of this configuration, for both writes and reads.<\/p>\n","protected":false},"excerpt":{"rendered":"
Oracle originally built its flagship database product with multi-version consistency control. Just as it sounds, multiple versions of a row can exist, albeit only one can exist as the current row version at any given time. It accomplishes this by…<\/p>\n
Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,34],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2393"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=2393"}],"version-history":[{"count":13,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2393\/revisions"}],"predecessor-version":[{"id":5522,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/2393\/revisions\/5522"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=2393"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=2393"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=2393"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}