Database Reference
In-Depth Information
Consider implementing logic that retries the operation in case of deadlocks. This is especially important with
statements that acquire full object locks on systems with lock partitioning enabled.
Of course, use the right transaction isolation levels. While a lot of people are trying to reduce blocking issues
by switching to READ UNCOMMITTED mode, either explicitly or with
NOLOCK
hints, this is rarely the right choice.
First, READ UNCOMMITTED does not help with blocking issues introduced by writers. They still use update (U)
locks during scans. Most importantly, however, by using a READ UNCOMMITTED transaction isolation level, we are
telling SQL Server that we do not care about data consistency at all, and it is not only about reading uncommitted
data. SQL Server can choose execution plans that use allocation order scans on large tables. This can lead to missing
rows and duplicated reads due to page splits, especially in the busy systems. There are some cases when READ
UNCOMMITTED can be used legitimately, but those cases are quite rare. Even then, we need to understand clearly all
of the side effects it could and would introduce.
A READ COMMITTED transaction isolation level is usually good enough for OLTP systems when queries are
optimized. Optimization is the key here—optimized queries acquire a small number of locks and do not introduce a
lot of blocking while keeping server overhead at a minimum.
REPEATABLE READ and SERIALIZABLE isolation levels are not good for OLTP due to excessive blocking,
and they should be avoided unless absolutely necessary.
Optimistic isolation levels are a mixed bag when discussing OLTP. A READ COMMITTED SNAPSHOT is an
option as long as you can live with extra tempdb load and additional index fragmentation. That isolation level can
be a great replacement for READ UNCOMMITTED; that is, writers do not block readers and at the same time it
provides statement-level consistency. On the other hand, using a SNAPSHOT isolation level is questionable. While
it provides transaction-level consistency and it eliminates blocking between writers, in OLTP systems where data is
highly volatile, it adds excessive tempdb load and introduces update conflicts that lead to the error 3960. While it is
still acceptable in some cases, you need to consider carefully all of the factors and test the system under load using
hardware that is as similar to the production environment as possible when making this decision.
For Data Warehouse-type systems, much depends on how you update the data and how critical data
consistency is during the update window. For static read-only data, any isolation level will work because
readers do not block other readers. You can even switch the database to read-only mode to reduce the locking
overhead. Otherwise, optimistic isolation levels may be your best choice. They provide either transaction- or
statement-level consistency for report queries, and they eliminate possible blocking with the writers involved.
Obviously, if statement-level consistency is enough, it is better to use a READ COMMITTED SNAPSHOT isolation
level and reduce the load on tempdb.
Last but not least, it is completely normal to use different transaction isolation levels in a system and even within
the same transaction. Different use cases require different approaches. Different problems force different solutions.
You just need to understand the locking behavior in each case and choose the one that provides the greatest benefit.
