Database Reference
In-Depth Information
You can change the code similar to that shown in Listing 24-2.
Listing 24-2.
Reducing blocking with a temporary table: Using a temporary table to refactor
create table #OrdersToBeCancelled
(
OrderId int not null primary key
)
insert into #OrdersToBeCancelled(OrderId)
select OrderId
from dbo.Orders
where
(PendingCancellation = 1) or
(Paid = 0 and OrderDate < @MinUnpaidDate) or
(Status = 'BackOrdered' and EstimatedStockDate > @StockDate)
update dbo.Orders
set
Cancelled = 1
where
OrderId in (select OrderId from #OrdersToBeCancelled)
In this case, a select statement that populates the temporary table acquires either shared (S) locks, or it does not
acquire row-level locks at all depending on the transaction isolation level. It helps with concurrency at the expense of
the additional overhead of creating and populating the temporary table. This method is more or less the last resort,
however. Creating the right indexes is the better option in most cases. It also only makes sense when you need to
update just a handful of rows. Otherwise, you will end up with execution plans that scan the entire
dbo.Orders
table
during update anyway, which is even less efficient than the original statement, due to the temporary table overhead.
When you need to read and write from/to the same table in the same transaction, it is better first to read the data
whenever possible. Avoid updating the row multiple times, especially using different indexes. Both of these patterns
would increase the chances of deadlocks.
Do not mix DDL and DML statements in one transaction. That also increases the chance of a deadlock. One very
common case where it is impossible to avoid doing so is a system with partitioned tables when some partition-related
information is stored in the user tables. For example, you can have an
Orders
table partitioned on a monthly basis
and an
OrderPartitionsInfo
table that stores the information about the min and max
OrderId
from every partition.
When we alter the
Orders
table partition function, we also need to update the
OrderPartitionsInfo
table in the same
transaction. In such cases, when we choose which statement should go first—DDL or DML—the goal is to eliminate
the possibility that other sessions are blocked while waiting for the lock on the
OrderPartitionsInfo
table and
holding intent locks on the
Orders
table at the same time.
Remember locking behavior when lock partitioning is involved. Especially when there is the code that alters the
schema online in busy OLTP systems. Make sure that your test servers have 16 or more logical CPUs, which enables
lock partitioning. We are talking about logical processors here, so even dual quad-core systems with hyperthreading
would do the trick.
Be careful with lock escalation, especially in OLTP systems. When you process a large batch of rows within the
same transaction, try to work with batches that are less than 5,000 rows. If that is impossible, monitor how often
lock escalation occurs, and consider disabling it on the table level, or switch to partition escalation if lock escalation
becomes a problem. Do not disable lock escalation, just in case that introduces concurrency issues, without first
analyzing the root-cause of the problem. Do not forget about additional memory requirements and error 1204 when
escalation is disabled, especially if lock partitioning is enabled. Keep in mind that smaller batches can be less efficient
than larger ones, and lock escalation can improve performance of the system due to the smaller number of locks that
a session needs to acquire and maintain.
