2

I have this query

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN

UPDATE c
SET c.Score = 2147483647 
FROM dbo.Comments AS c
WHERE c.Id BETWEEN 1 AND 5000;

Which have these stats

+--------------+---------------+---------------+-------------+
| request_mode | locked_object | resource_type | total_locks |
+--------------+---------------+---------------+-------------+
| RangeX-X     | Comments      | KEY           |        2429 |
| IX           | Comments      | OBJECT        |           1 |
| IX           | Comments      | PAGE          |          97 |
+--------------+---------------+---------------+-------------+

I wonder about the IX, which is an Intent Lock. What does that mean, and why does there exist one on the table it self? As I understand, it is not a true lock but more something SQL Server use (or is set by the transaction?) to indicate that a lock might occur.

Is the above right?

3

You are correct, it is a signal to the engine that you wish to put a lock on an object. To over-simplify it, imagine you want to obtain a book from the library, if somebody has the book checked out you cannot obtain it, so you put your name on a list of people who intend to check that book out. When everybody else is done with it, and it is your turn, then you are allowed to check out the book.

If you haven't done so already I highly recommend reading Transaction Locking and Row Versioning Guide on MSDN which provides some great detail on how it all works.

The below is a snippet from that page:

Intent Locks

The SQL Server Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level. Intent locks serve two purposes:

  • To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.

  • To improve the efficiency of the SQL Server Database Engine in detecting lock conflicts at the higher level of granularity.

For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. Intent locks improve performance because the SQL Server Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

  • 1
    Many thanks. Emphasis on "allowed to check out the book" because maybe I don't need it :) I like your example. Give me some hope that I am not totally lost. Thanks. – mslot Sep 28 at 17:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.