Database Reference
In-Depth Information
Chapter 24
Designing Transaction Strategies
Now it is time to put everything together. In this chapter, we will summarize, and perhaps repeat a few rules that can
help us reduce blocking and improve concurrency in a system. The most important rule we need to remember is
that most cases of concurrency and blocking can be fixed by query optimization. Even if we do not talk about system
performance in general, non-optimized queries scan more data than is needed. From a locking standpoint, this
means that those queries issue more lock requests, which increase the chances of collision and blocking with other
sessions. This is especially true for queries that change data. While we have some options to deal with shared (S)
locks from non-optimized readers, there is very little that we can do with blocking introduced by writers and update
(U) locks. SNAPSHOT transaction isolation level is the only option where writers do not block other writers, although
switching to that isolation level requires code redesign and introduces major overhead to the server.
We need to make transactions as short as possible. Exclusive (X) locks are not released until the end of the
transaction. The longer the transaction, the longer the locks are held. This again increases the chances of collision
and blocking. Avoid any long-term operations while a transaction is active. Never have any interactions with users
through the UI while a transaction is active. When dealing with external resources, be especially careful with external
access CLR and linked servers. For example, when a linked server is down, it can take a long time before a connection
timeout occurs. It is not a good idea to have exclusive (X) locks in place all that time.
Update the data as close to the end of the transaction as possible. This also reduces the time that exclusive (X)
locks are held. In some cases, it might make sense to use temporary tables as the preliminary staging place, insert data
there, and flush it from the temporary to the actual tables at the very end of the transaction. You need to remember
that this approach helps reduce blocking, but creating and populating temporary tables is expensive in terms of I/O,
so it introduces overhead and slows down execution, especially when a lot of data needs to be stored there.
One particular instance when this technique is very useful is an update statement that is impossible or
unpractical to optimize. If the statement scans a lot of rows, but updates just a handful of them, it would make sense
to refactor the code and collect the clustered index key values of the rows that need to be updated into a temporary
table. After that, you can run an update statement based on those collected key values. While you still need to scan the
data during select, you would not use update (U) locks there, which could dramatically help with the concurrency. For
example, if you have a statement like the one shown in Listing 24-1, there is a great chance that SQL Server would use
a clustered index scan on the Orders table. This scan leads to an update (U) lock acquired on every row of the table.
Listing 24-1.
Reducing blocking with temporary table: Original statement
update dbo.Orders
set
Cancelled = 1
where
(PendingCancellation = 1) or
(Paid = 0 and OrderDate < @MinUnpaidDate) or
(Status = 'BackOrdered' and EstimatedStockDate > @StockDate)
