READ COMMITTED sucks!

Are you still using READ COMMITTED transaction isolation level? Default transaction isolation level on all SQL Server versions (2000-2014) has serious inconsistency problems, by design. Not many people are familiar how and why is it happening. I am writing this because one fellow MCM just had that problem, scratching his head how is it possible. Few days ago I met one experienced Oracle DBA, a friend (yes, I do have Oracle friends! :)), who was asking me “Does SQL Server still have consistency problems?” making me scratch my head on what does he meant by that. I will show you a demo code here to reproduce it and a solution to the problem.

How READ COMMITTED works?

Under default READ COMMITTED isolation level, shared locks on the rows/pages are released as soon as the row is read. Consequence are two anomalies at a single-statement level, that some call “inconsistent analysis”:

- reading the same row multiple times

- legitimate rows are omitted, although they are present all the time there

That are serious, statement-level inconsistencies. E.g. single simple SELECT can read the same row twice and bring you duplicates in the result or it will skip some rows. The worst thing is that you will often not notice at all that data is inconsistent.

If you are running a SELECT and the other transaction updates the row you already read in a way that it moves that row in front of your reading point, you will read that row twice. If the other transaction updates the row you are yet to be read in a way that it moves that row before of your reading point, you will not read that row at all. Such data anomalies are difficult to reproduce because the timing is crucial.

Let’s reproduce it!

Here is the script. You have to be fast with separate session because timing is crucial:

Basically, it creates a database and a 500 000 rows table. Then copies full table to a #temp table, while another session does updates. We would expect that copy of the table has the same number of rows as original table (500 000), since UPDATE command does not change the number of rows – all of the time there is exactly 500 000 rows in a table.
But – surprise!
In first example we copied 123 rows more (500 123), showing multiple reads of the same row.
In second example we copied 123 rows less (499 877), showing that some rows are skipped during copy.

The solution

The solution of this complex problem is really simple, and is available since SQL 2005. It is called READ COMMITTED SNAPSHOT. You can enable it at database level with a single ALTER DATABASE command. But it also has consequences, like tempdb growing and changed rows become wider by 14-byte pointer, possibly causing page splits if your fillfactor is 100%. So you probably want to test that before you enable it on existing systems. New databases should be safe to use it. On SQL 2000 your only option is to introduce more locks: REPEATABLE READ or SERIALIZABLE isolation level.

Posted in Uncategorized
3 comments on “READ COMMITTED sucks!
  1. Just to remind our Oracle friends:
    Returning a snapshot of old data that is in the midst of being modified, is again not necessarily the best option and telling “the truth”.
    It might just as well be, that there is a serious mistake, like a wrong product price, which someone detected and just updated. But still, the enduser will get the old and wrong 1$ price for the new MAC – whereas under Read Committed this read would wait until the correction is done.
    So it always depends. And having a choice and knowing about it, that’s what leads to “returning the right information” (and not only consistent)

    • Vedran Vedran says:

      Andreas, thank you for your valuable comment. Indeed, with SQL server, if the other transaction is modifying a row, we can choose will we wait for the other transaction to finish and read the fresh value (standard “READ COMMITTED” behaviour) or we will not wait and read the value as it was before the other transaction began changing the row (READ COMMITTED SNAPSHOT). Even if database is in RC SNAPSHOT mode we can use standard RC behavior per statement with a hint “READCOMMITTEDLOCK”.

  2. Jaime says:

    Hi,

    I deeply recommend reading this great article by Kendra Little:
    http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

    Regarding what Andreas says, there is also a great article by Craig Freedman that explains how you can end up having different results from the same set of operations, depending on the transaction isolation level you choose: http://blogs.msdn.com/b/craigfr/archive/2007/05/16/serializable-vs-snapshot-isolation-level.aspx

    Regards

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

M.Sc. Vedran Kesegić

M.Sc. Vedran Kesegić

A Random Thought

You don't have a backup until you tried to restore it! Validate your backups. At least, use RESTORE VERIFYONLY immediately after a backup, and use CHECKSUM option when doing a backup.