Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    120

    Unanswered: lock acquisition order

    Hi all,
    I need to discover the actual order in which locks are acquired on a table during a query.
    This with a goal of analyzing the lock order of queries against the same table to prevent deadlocks.

    I'm using SQL Server 2008 R2.
    From Management Studio I execute:

    begin transaction
    <my query>
    exec sp_lock
    rollback transaction

    In the output I see interesting information about which locks are acquired, but:
    - are this locks ordered by the time they're acquired? That is, can I be sure that lock at row n is acquired before lock at row n+1?
    - if not, how can I get this information?

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Oh, you are looking at quite the rat hole here. The storage engine will take out locks on rows, pages, extents, or even the entire segment (table, index, whatever), depending on what the query plan is having done to the data. Consider this example:
    Code:
    update users
    set uLastName = 'Smith'
    where uLastName = 'Jones'
    Simple, right?

    But, if there is no index on the uLastName column of the users, then an intent update lock is taken out on the table followed fairly rapidly by an exclusive lock on the table. The entire table is locked, because the query plan can't estimate a priori how many rows are going to be updated.

    If there is an index on the uLastName column, and that index is used to select the records to be updated, you will get IX locks then X locks on the table AND the index (probably the table first, but I would not put money on that).

    Now, if there is an index on uLastName, but it is not used in the query (either the table is too small, or the value is not selective enough), then the IX and X locks are issued on the table first, then the index (think of the process working its way up from the table scan to the index to be updated).

    Sifting through all of these cases for the various combinations of update/insert/delete statements, indexes, and index use gets very complex very quickly.


    So, with all of that said, are you experiencing deadlocks now?

  3. #3
    Join Date
    Mar 2012
    Posts
    120
    Well.. I'm not experiencing deadlocks.

    I have a bunch of queries that insert/select/update from a table from different modules and I need to keep track of the lock acquisition sequence for each query to be reasonably sure I won't get a deadlock in production.

    So I'm searching for the smartest method to get the lock acquisition sequence for this table and its indexes for each of the queries...

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Not only is this a rat hole, it is a mutable rat hole... It will probably use different locks in a different order based on the "cache heat" level. A "cold" cache will lead to more locks that are more restrictive more quickly than a "warm" cache will.

    This means that the results will vary from one run of your query to another, and that you can get different results on different servers.

    I think that I'll watch this dance from the sidelines, thanks!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Your best bet at this stage to prevent deadlocks is to keep all of your updates as fast and small as possible. Don't start a transaction, then lookup a pile of values, email the user for confirmation, and only when the user has answered in triplicate do you commit the transaction.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •