Database Reference
In-Depth Information
Chapter 17
Lock Types
There are more than 20 different lock types in SQL Server. As the first step we will look at the major ones and
understand when and how SQL Server uses them.
When session inserts, updates, or deletes the row SQL Server acquires
exclusive (X) lock
on that row and holds it
until the end of the transaction. As you can guess by the name—
exclusive
means
exclusive
—only one session can hold
exclusive (X) lock on the resource at any given point of time. This would protect the object (row, for example) from
being modified by other sessions. I would like to repeat one of the key points here: exclusive (X) locks are held until
the end of the transaction regardless of the transaction isolation level. The longer a transaction we have, the longer
exclusive (X) locks would be held.
While row level locking improves consistency, held locks only on the row level would be bad from a performance
standpoint. Let's think about the situation when a session needs to have exclusive access to the table. For example,
when we want to alter a table's metadata, or perhaps create the new index. If we have row level locks only, this session
would scan the entire table and check if there are any held locks as well as doing something to prevent other sessions
from acquiring locks on the portion of the data that was already scanned, perhaps by acquiring and holding the row
locks by itself. As you can imagine, this would be an extremely inefficient process in terms of I/O and memory usage,
especially on large tables. To improve that situation SQL Server introduces the concept of
intent (I*)
locks. SQL Server
uses those lock types to indicate that there are locks held on the child objects. Let's take a look.
■
In all of the examples from Part 3 of the topic, I'm going to use table
Delivery.Orders
of the structure that
follows. This table has a clustered primary key on the
OrderId
column with no non-clustered indexes.
Note
create table Delivery.Orders
(
OrderId int not null identity(1,1),
OrderDate smalldatetime not null,
OrderNum varchar(20) not null,
Reference varchar(64) null,
CustomerId int not null,
PickupAddressId int not null,
DeliveryAddressId int not null,
ServiceId int not null,
RatePlanId int not null,
OrderStatusId int not null,
DriverId int null,
Pieces smallint not null,
Amount smallmoney not null,
