Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
Always acquire locks in the same order (this avoids deadlocks) and use with UR.
| Code: |
The select statement runs, and first has to be connected and in the context
of a database, so it get's a shared database lock:
S DB (shared database)
Then it wants to access a table, so it gets a shared lock on the table:
S Table (shared table)
Next, it signals its intention to get shared locks on stuff in the table by
placing an intent shared.
IS Table (table intent shared)
Now that it has these locks, it can begin to hunt for data. Assuming the
worst case of query that requires a few hundred bookmark lookups: it needs
to place some shared locks on the index it's using. Let's assume the index
requires 5 pages to hold all of it. It first places an
IS Index (index intent shared)
lock on the index that it will be getting shared locks on, and then a
S Index (shared index)
lock on the first page of the index. The index page is scanned, and matching
cluster keys are stored. It then releases the S lock on the first index
page, and places an S lock on the 2nd index page....
Meanwhile, back in the other process and UPDATE wants to run. The connection
has a S DB lock. Next it is dealing with a table, and places an S lock on
the table. Now that it will be updating rows in that table, it signals its
intention by placing an
IU Table (table intent update)
lock.
Next it needs to figure out which rows it will update, so it scans the table
and indexes, placing S locks as it reads the values, releasing the S locks
when it's done reading that stuff, and then placing an U (update) lock on
rows it wants to update. If it wants to eventually update a lot of rows, it
might escalate the row level U locks to page level U locks. Since U locks
are compatible with S locks, and S locks are compatible with U locks, there
is no lock contention.
Back in the select statement, it has been getting and releasing S (shared)
locks without incident.
When the UPDATE has finally marked all rows it will be updating with U
(update) locks, it then goes back and tries to convert all those U locks to
X (exclusive) locks. It indicates it's intention to place exclusive locks on
the table by trying to place an
IX Table (table intent exlusive)
But since our select has some S shared locks on the table, the UPDATE will
hang waiting for the select to finish. |
|
|
