Is Optimistic Concurrency Right For You?

You might be asking, “is optimistic concurrency right for me?”  The answer, in the famous words of the Reverend Lovejoy:  “Short answer:  yes, with an if; long answer:  no, with a but.”  But that’s not a very satisfying answer, so let’s take a look into what optimistic concurrency is, how it differs from pessimistic concurrency, how to turn on optimistic concurrency in SQL server, what happens when you turn on optimistic concurrency, and what to look out for with optimistic concurrency.

What Are Optimistic and Pessimistic Concurrency?

Kendra Little has a fantastic article on optimistic concurrency in SQL Server.  I highly recommend reading her article to get an idea of the differences between optimistic and pessimistic concurrency.

Pessimistic concurrency is the default for SQL Server.  The idea behind pessimistic concurrency is that we want to protect people from seeing incomplete data and protect people from getting their data clobbered while they’re in the process of writing it.  That’s why we see the following behavior when query 1 starts a transaction and, while that transaction is ongoing, somebody else runs query 2.

Query 1: Query 2: Result:
Reader Reader Both queries read without blocking.
Reader Writer Reader blocks writer.  The writer waits until the reader is done before writing a row.
Writer Reader Writer blocks reader.  The reader waits until the writer is done before reading a row.
Writer Writer Writer blocks writer.  The second writer waits until the first writer is done before writing a row.

Except in the scenario in which we have two readers hitting the table, we see blocking.  In a production system, this blocking can cause performance problems, especially with a denormalized database design with interlocking and long-running updates.  The answer developers tend to give to this problem is to drop down to READ UNCOMMITTED mode for reads, typically using NOLOCK hints.  This changes the game a bit, so that writers no longer block readers, but the problem is that readers can pick up dirty data—data which is in the middle of being updated.  This can lead to inconsistent results and confused users who refresh the page and see something different even when nothing has changed.  This makes READ UNCOMMITTED isolation level a dangerous tool.

By contrast, optimistic concurrency gives us the following results:

Query 1 Query 2 Result
Reader Reader Both queries read without blocking.
Writer Reader Both queries run.  The reader reads the expected (old) version of data, before the writer began updating.
Reader Writer Both queries run.  The reader reads the expected (old) version of data, and the writer updates the row.
Writer Writer The first writer may block the second writer (as in read committed snapshot isolation) or it may force the second writer to roll back (as in In-Memory OLTP).

This calculus is significantly different.  When you turn on optimistic concurrency in the form of READ COMMITTED SNAPSHOT ISOLATION, writers no longer block readers and vice versa.  Instead, what happens is that the database engine keeps track of versions of rows, keeping old versions of rows until there are no transactions which could read them.  What that gives us is a consistent state of the world as of the beginning of a statement:  if I change row 5 while you’re querying rows, the version of row 5 you see will be the one which existed right as your query began.

READ COMMITTED SNAPSHOT ISOLATION

The most common form of optimistic concurrency in an OLTP system is READ COMMITTED SNAPSHOT ISOLATION.  This was introduced in SQL Server 2005, and it’s very easy to turn on:

ALTER DATABASE [DBName] SET READ_COMMITTED_SNAPSHOT ON;

Running this command will turn on READ COMMITTED SNAPSHOT ISOLATION.  That’s really all you have to do.  Your queries which normally run in READ COMMITTED will automatically switch over to RCSI, and you get the immediate benefits of optimistic concurrency.  If you had NOLOCK hints in your code before, you’ll need to remove them, but that’s a relatively simple change.

Why Not Use Optimistic Concurrency?

Now that I’ve shown you what it is and how to use it, let’s talk about cases in which optimistic concurrency—specifically, using READ COMMITTED SNAPSHOT ISOLATION—can be difficult.  The first reason you might not want to use RCSI is that it hits tempdb hard.  All of those versions of rows need to live somewhere, and they end up living in tempdb.  If your tempdb storage is slow, RCSI could reduce performance significantly.

In addition to storing data in tempdb, each row will use an extra 14 bytes to store versioning information.  14 bytes isn’t too much, but if you have a billion rows, you are talking about 14 GB of extra data.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s