Monthly Archives: August 2011

Locking in Microsoft SQL Server (Part 8 – Optimistic transaction isolation levels)

There was some time since we discussed locking and blocking in Microsoft SQL Server. One big area we did not cover is optimistic transaction isolation levels. So what exactly are those?

As you remember, standard “pessimistic” isolation levels – read uncommitted, read committed and repeatable read – all of them can have anomalies. Dirty and non-repeatable reads, ghost rows and duplicated reads. Higher isolation levels reduce anomalies in price of reduced concurrency. Most restrictive – Serializable isolation level guarantees no anomalies although with such level concurrency greatly suffers. Before SQL Server 2005 it was virtually impossible to have consistent reporting in the systems with heavy reading and writing activities.

Obviously in quite competitive market Microsoft had to do something to solve that problem. As the result, one of the features Microsoft introduced in SQL Server 2005 was new “optimistic” transaction isolation levels. With those isolation levels readers use row versions rather than locking. When you start the transaction and modify some rows, old version of the rows are copied to the version store in tempdb. Actual row holds 14 bytes pointer to the version store.

There are 2 different isolation levels – well, actually one and half. First one is called Read Committed Snapshot and not really the level but more or less the mode of Read Committed. This level implements statement level consistency – readers would not be blocked by writers but in transaction scope it could have same anomalies as regular read committed (with the exception of duplicated reads). Let’s take a look. If you don’t have test database, create it first. First, let’s enable read committed snapshot isolation level. It worth to mention that when you switch the mode, you should not have any users connected to the database.

Next, let’s create the table and populate it with some data

Now let’s start one session and update one of the rows. As you can see, this session starts in read committed isolation level

Let’s look at the locks we have in the system. As you can see, we have exclusive lock on the row. This is expected and should happen with any isolation level. Click to the image to open it in the different window

Now let’s start another session and try to select the same row.

And here is the big difference with regular read committed. Instead of being blocked as when we have pessimistic isolation level, it returns old version of the row with placeholder equal ‘a’. So far, so good. Now let’s run another update.

As you can see, with read committed snapshot we still have writers acquiring U and X locks – so those statements would be blocked as with regular pessimistic isolation levels. What does it really mean for us? If your system suffers because of the blocking between readers and writers, read committed snapshot helps in such situation – blocking would be reduced. This is especially good if you need to deal with 3rd party vendors databases – you can change the database option and (partially) solve the problem. Although read committed snapshot would not help if writers block each other. you’ll still have typical update and exclusive locks behavior.

One other thing I already mentioned – read committed snapshot does not provide you consistency on the transaction scope. Consistency is guaranteed only on the statement scope. Let’s check that:

Now let’s commit the session 1 (I don’t put screen shot here) and run select statement again. We’re on the same transaction context.

As you can see, select returns new (updated) row value. Again, no transaction level consistency in such case. If you need full consistency on transaction level, you need to use SNAPSHOT isolation level. With such isolation level SQL Server stores multiple “old” versions of the row – as long as there are transactions that can reference them. When session references the row, it reads the version that was valid on the moment when transaction started. It has “snapshot” of the data for the transaction.

Let’s take a look. First, you need to enable that option on database level. Again, you need to be on single user mode in order to do so.

Next, let’s run same update statement and keep transaction uncommitted.

Now let’s go to another session, start transaction in snapshot isolation level and see what happens.

First, let’s select of the row updated by another session that holds X lock. As you can see – same behavior with read committed snapshot – it returns old value.

Now let’s try to update another row with table scan. As you can see – no blocking due U/X lock incompatibility now. Both rows have been updated, 2 X locks held

Now let’s commit transaction in the first session and run select in the second session again. It still returns the data that you had at the time when transaction was just started. Even if that row has been modified.

And now let’s delete that row in the first session.

If you run same select in the second session – it still returns that row.

As you can see, this is consistency on the transaction level. SQL Server guarantees that data within the transaction would be exactly the same during transaction lifetime. Even if it looks similar to Serializable isolation level, there is the key difference. With Serializable level you “lock” the data on the moment you access it. With Snapshot, it “locked” on the moment when transaction started. And obviously, this is the Heaven for reporting – consistent data with no blocking.

Too good to be true. Yes, there are a few things you need to keep in mind from both DBA and Development prospective. And we will talk about them next time.

Source code is available for download

Part 9 – Optimistic transaction isolation levels – TANSTAAFL! 

Also take a look at Part 15 – When Transaction Starts. It gives some additional details about snapshot isolation level

Table of content

Locking in Microsoft SQL Server (Part 7 – Read Committed – duplicate readings)

One of very interesting phenomenon we have not discussed earlier when we covered lock types and isolation levels  is duplicate readings. As we already know, read committed isolation level protects us from reading of modified and uncommitted data but the scope of protection is “this row/right now”. So what happens if row updates during the time when query is executed.

Let’s take a look. First, as usual, let’s create the table and populate it with 100,000 rows:

As you can see, even if we don’t have any unique constraints, that table has unique ID and Value columns (equal to each other) with values from 1 to 100,000. We can even run SQL query and see that there is no duplications.

Now let’s start another session and lock one row:

As you can see, we don’t commit transaction, so exclusive (X) lock is in play. Now let’s start another session, create temporary table and simply copy data from our main table there. When we run this statement in read committed mode, it would be blocked because there it tries to acquire shared (S) lock on the row that held (X) lock from the previous session.

Now let’s come back to the previous session and update another row (value column this time) and commit transaction.

As you can see, first (X) lock has been released and our blocked session resumes and finishes copying the data. And now, let’s see what we have in this table –

Surprise! We have 1 extra row – the one the first session updated. This row appears twice – with old and new values in Values column. In order to understand what happened, let’s take a look at the execution plan for insert/select statement.

As you can see, plan uses index scan. Let’s think what happened – select read first 999 rows (with ID from 1 to 999) and was blocked by (X) lock from the different transaction. Next, that transaction updated Value column for the row with ID = 1. Because Value is the index key, that index row has been moved in the index. As result, when select resumes, it read this row (with ID = 1) second time during remaining part of the index scan. Fun?

With read uncommitted another factor could come in play – sometime SQL Server can decide to use allocation scan. There are a lot of factors that can lead to that decision – but if it happens and if you’re lucky enough to have page split during query execution, you can easily have rows to be read/processed more than once.

Fortunately neither of those cases happen quite often. I’d say I saw duplicate readings in production systems maybe 2-3 times during my career. But it could give you a lot of grey hairs – think what if #Temp table had primary key on ID and you would get primary key violation on the data that guaranteed to be unique? If you want to be protected from that you have to use optimistic isolation levels. I (promise another time) is going to blog about them next time 🙂

Source code is available for download

Part 8 – Optimistic transaction isolation levels

Table of content

SQL Saturday #79 (South Florida)

I cannot believe that year past since I started the blog. It happened right before SQL Saturday #40 event in Miramar, FL. Well, in 2 weeks this wonderful place is going to host SQL Saturday #79 event and I’m going to present 2 sessions there:

  1. Data Sharding in OLTP systems. I’m going to talk about various approaches that can help when you need to reduce SQL server load.  Even if this session announced in BI track, it does not have anything to do with BI – it would be “system architecture” type talk focusing on the sharding and vertical partitioning.
  2. Anti-patterns in Database Design and T-SQL code. Self-explanatory. This one should be fun – no Power Point slides -Management Studio and SQL Code only.

Stop by and say “Hello!” if you’re planning to attend!