Solve Blocking Problems

In any multi-user database, data integrity must be maintained by preventing more than one process from updating the same data at the same time. Relational databases all implement some kind of transaction isolation model to ensure data consistency. The better-known database vendors use two isolation models: optimistic and pessimistic.

The pessimistic isolation model assumes that many data conflicts will be encountered. It preserves data integrity by avoiding the possibility of conflicts. It does this by locking database objects while a process is changing them. This prevents an object from being changed by more than one process at a time. The pessimistic isolation mode is largely invisible to the application, although it can be tuned a bit using table-level locking in T-SQL code. There's nothing inherently wrong with the pessimistic isolation model, except perhaps the name.

The optimistic isolation model assumes that few conflicts will occur. It maintains data consistency by forcing conflicting transactions to roll back. It does this by giving processes versioned copies of the data they're requesting. If two or more processes update the same data at the same time, the database engine uses the version date stamps to decide which change will be accepted. The other processes will get a concurrency error when they try to commit their changes. The optimistic isolation model requires that the application gracefully trap and handle concurrency errors.

By default, SQL Server implements the pessimistic isolation model. However, in SQL Server 2005 and later, you can choose to use the optimistic isolation model, which Microsoft refers to as snapshot isolation. SQL Server offers two options when using snapshot isolation:

• Statement-level row versioning implementation of Read Committed isolation, which is often referred to as Read Committed Snapshot Isolation (RCSI)
• Transaction-level row versioning isolation level, which is often referred to as transaction-level snapshot isolation

You might be wondering which of the three models -- pessimistic isolation, RCSI, or transaction-level snapshot isolation -- is better. It's not a question of better or worse. It's a question of what will work best for your particular database and the applications that use it.

If many data modification statements occur in your database and the potential for data conflicts is high, the default pessimistic isolation model might work better for you. If there are many more reading processes than statements that modify data, snapshot isolation might work better for you. Keep in mind, however, that your application must be written to trap concurrency errors if you choose to use transaction-level snapshot isolation. If it wasn't written to do that, it might require significant effort to rewrite the code.

If your blocking problems come from conflicts between reading and writing processes (which is common in many databases), RCSI might work well for you. An important feature of RCSI is that it doesn't require any code changes to legacy applications that have been running under SQL Server's default pessimistic isolation model (except in rare cases in which the application was written to depend on a certain locking behavior). Let's take a closer look at RCSI by examining how it deals with a common problem, compared with the behavior of the default pessimistic isolation model.

The Problem with Dirty Reads

Conflicts between reading and writing processes can lead to a situation in which data is being changed at the same time it's being read. This is often called a dirty read.

Let's look at an example of what can happen if data is changed while being read. Suppose you have a table named Accounts, which contains information about a number of different bank accounts. Process A is involved in a transaction to deduct $100 from a savings account and then add it to a checking account. At the same time, process B is reading the balances in all accounts in the table and summing the total balance across all accounts. Without some mechanism to keep the data from being changed during the read operation, process B might read the table after the deduction from the savings account is made but before it's added to the checking account. As a result, the balance computed by process B will be off by $100.

The Default Behavior

The default pessimistic isolation mode in SQL Server prevents this problem by requiring a reading process to get a shared lock on the data, preventing any changes to the data while it's being read. When there's a shared lock on the data, other processes can read that data but no process can get the exclusive lock necessary to change it. Conversely, no process can get a shared lock to read data that's locked by a process changing the data. This is the source of many of the blocking problems that occur in the default pessimistic isolation mode. A long-running report or a big update statement can block many processes and slow database operations to a crawl.