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:
1) Before the user commits, she can “undo” or rollback her changes to this version
2) 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
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
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
1) The feature can be enabled or disabled at the SQL Server individual database level
2) The pre change versions are written to the tempdb rather than the rollback segments as they are in Oracle
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.
Our source code for this test can be downloaded here.
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.
We create three threads:
1) one that deletes all rows from a test table, but doesn’t commit
2) one that tries to read all rows from our test table before the thread above commits
3) a helper thread that merely prints blocked sessions
We see in the output below that our select thread is blocked by our delete thread, until the delete thread commits.
c:\java>java sqlServerBlockedReader off
turned read_committed_snapshot off
starting select thread
starting delete thread
starting lock monitor thread
delete session spid = 51
deleted data at Sat Jul 28 20:12:21 EDT 2012, sleeping 30 seconds
select session spid = 52
locks session spid = 54
starting lock monitor loop at Sat Jul 28 20:12:24 EDT 2012
starting data select at Sat Jul 28 20:12:26 EDT 2012
spid 52 is blocked by spid 51
spid 52 is blocked by spid 51
spid 52 is blocked by spid 51
spid 52 is blocked by spid 51
spid 52 is blocked by spid 51
spid 52 is blocked by spid 51
spid 52 is blocked by spid 51
spid 52 is blocked by spid 51
completed delete sleep at Sat Jul 28 20:12:51 EDT 2012
finished data select at Sat Jul 28 20:12:51 EDT 2012
c:\java>
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.
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.
c:\java>java sqlServerBlockedReader on
turned read_committed_snapshot on
starting select thread
starting delete thread
starting lock monitor thread
delete session spid = 52
select session spid = 51
locks session spid = 54
deleted data at Sat Jul 28 20:13:07 EDT 2012, sleeping 30 seconds
starting lock monitor loop at Sat Jul 28 20:13:09 EDT 2012
starting data select at Sat Jul 28 20:13:11 EDT 2012
finished data select at Sat Jul 28 20:13:11 EDT 2012
completed delete sleep at Sat Jul 28 20:13:37 EDT 2012
c:\java>
Our next article will address the performance implications of this configuration, for both writes and reads.