Datasheet

19
Chapter 1: Introducing T-SQL and Data Management Systems
“ Transaction ## was deadlocked on x resources with another process and has been chosen as the
deadlock victim. Rerun the transaction. ” To avoid the deadlock from ever occurring, SQL Server will
typically use update locks in place of shared locks. Only one process can obtain an update lock,
preventing the opposing process from escalating its lock. The bottom line is that if a read is executed for
the sole purpose of an update, SQL Server may issue an update lock instead of a shared lock to avoid a
potential deadlock. This can all be avoided through careful planning and implementation of SQL logic
that prevents the deadlock from ever occurring.
Exclusive Locks
SQL Server typically issues exclusive locks when a modification is executed. To change the value of a
field in a row, SQL Server grants exclusive access of that row to the calling process. This exclusive access
prevents a process from any concurrent transaction or connection from reading, updating, or deleting
the data being modified. Exclusive locks are not compatible with any other lock types.
Intent Locks
SQL Server issues intent locks to prevent a process from any concurrent transaction or connection from
placing a more exclusive lock on a resource that contains a locked resource from a separate process. For
example, if you execute a transaction that updates a single row in a table, SQL Server grants the
transaction an exclusive lock on the row, but also grants an intent lock on the table containing the row.
This prevents another process from placing an exclusive lock on the table.
Here is an analogy I often use to explain the intent lock behavior in SQL programming classes: You check
in to Room 404 at the SQL Hotel. You now have exclusive use of the fourth room on the fourth floor
(404). No other hotel patron will be allowed access to this room. In addition, no other patron will be
allowed to buy out every room in the hotel because you have already been given exclusive control to one
of the rooms. You have what amounts to an intent exclusive lock on the hotel and an exclusive lock on
Room 404. Intent locks are compatible with any less - exclusive lock, as illustrated in the following table
on lock compatibility.
Existing Granted Lock
Requested Lock Type I S S U I X X
Intent shared (IS) Yes Yes Yes Yes No
Shared (S) Yes Yes Yes No No
Update(U) Yes Yes No No No
Intent exclusive (IX) Y e s N o N o Y e s N o
Exclusive (X) N o N o N o N o N o
Schema Locks
There are two types of schema locks SQL Server will issue on a table: schema modification locks (Sch - M)
and schema stability locks (Sch - S). Schema modification locks prevent concurrent access to a table while
the table is undergoing modification — for example, a name change or a column addition. A schema
stability lock prevents the table from being modified while it is being accessed for data retrieval.
CH001.indd 19CH001.indd 19 3/26/10 11:35:42 AM3/26/10 11:35:42 AM