Database Reference
In-Depth Information
ModTime datetime2(0) not null
constraint DEF_Orders_ModTime
default getDate(),
PlaceHolder char(100) not null
constraint DEF_Orders_Placeholder
default 'Placeholder',
constraint PK_Orders
primary key clustered(OrderId)
)
The example in Figure
17-1
shows the locks session held after updating one row in the table. There is the
exclusive (X) lock on the row (key) itself and two intent exclusive (IX) locks on the page and on the object (table).
Those intent exclusive (IX) locks just indicate that there is the child object (row) with exclusive lock held. There is also
the shared (S) lock on the database level. We will cover shared (S) locks later in this chapter.
Figure 17-1.
Exclusive (X) and intent exclusive (IX) locks
Resource description indicates the resources on which those locks are acquired. For the page it indicates its
physical location (page 944 in the database file 1) and for the row (key) it indicates the hash value of the index key.
Now, if another session needs to obtain exclusive access to the table (object), it could check if there are any intent
locks on the object held by the other sessions rather than scanning the table and checking if there are any row level
locks held.
Another very important lock type is
update (U)
lock. SQL Server acquires those locks when searching for the
rows that need to be updated. After update (U) lock is acquired, SQL Server reads the row and checks if it needs to be
updated by evaluating row data against query predicates. In such cases, update (U) lock is converted to exclusive (X)
lock and row is modified. Otherwise update (U) lock is released. Let's see how it works by taking a look at Figure
17-2
.
