Schema Locks - Pro SQL Server Internals - page 443

Listing 23-1. Specifying lock priority

alter index PK_Customers on Delivery.Customers rebuild
with
(
online=on
(
wait_at_low_priority
(
max_duration=10 minutes,
abort_after_wait=blockers
)
)
)
go

alter table Delivery.Orders
switch partition 1 to Delivery.OrdersTmp
with
(
wait_at_low_priority
(
max_duration=60 minutes,
abort_after_wait=self
)
)
Go

As you can see, WAIT_AT_LOW_PRIORITY has two options. The MAX_DURATION setting specifies the lock wait time in
minutes. The ABORT_AFTER_WAIT setting defines the session behavior if a lock cannot be obtained within the specified
time limit. The possible values are:
NONE : The low-priority lock is converted to a regular lock. It behaves as a regular lock after
conversion blocking sessions, which want to acquire incompatible lock types while it is
waiting for the lock to be acquired. The session continues to wait until the lock is acquired.
SELF : The operation is aborted if a lock cannot be granted within the time specified by the
MAX_DURATION setting.
BLOCKERS : All sessions that held locks on the resource are aborted, and the session, which is
waiting for a low-priority lock, is able to acquire it.

■ omitting the WAIT_AT_LOW_PRIORITY option works the same way as specifying WAIT_AT_LOW_PRIORITY
(MAX_DURATION=0 MINUTES, ABORT_AFTER_WAIT=NONE) .

Note

Very active OLTP tables always have a large number of concurrent sessions accessing them. Therefore, there
is always the possibility that a session would not be able to acquire a low-priority lock even with a prolonged
MAX_DURATION specified. You may consider using the ABORT_AFTER_WAIT=BLOCKERS option, which will allow
the operation to complete, especially when client applications have proper exception handling and retry logic
implemented.