none
Doubt in Lock escalation manager

    Question

  • I want to know, what type lock is been put on the resources if run below statement

    Select top 5000 from table1

    I tried to examine by sp_lock spid;

    I gave me type as TAB.

    And if run the Select top 1 from table1 it also say the type as TAB.
    In the MSDN TAB = Lock on an entire table, including all data and indexes.

    I want to know why there is Table lock is been on the resource,even if query returns 1 record?

    This is standalone test database, and only my session is active. There is no other transaction happening in the db.

    Correct me if I am wrong. Unless there are 5000 locks on the table, lock escalation manager, will not do the lock escalation to the table.
    In my case, it should acquire only one shared lock on the resource. I'm trying to understand why it returns type as tab.

    Sample Output:

    spid     dbid   ObjId          IndId Type Resource Mode Status
    67       10      0                 0      DB                      S GRANT
    67        1     1131151075  0      TAB                     IS GRANT

     

     


    vivekanandhan periasamy

    Friday, January 04, 2013 10:52 PM

Answers

  • You are looking at a mirage. The TAB lock is on the table spt_values in the master database, which sp_lock uses. You can tell that you are looking at a system object from the fact that dbid is 1 which is the master database. (And if you use select object_name on that number in master, you will get spt_values.)

    The locks from your queries are released as soon as the query completes. To see the locks after the fact, you need to do:

    BEGIN TRANSACTION

    SELECT TOP 1 * FROM tbl WITH (REPEATABLEREAD)

    -- COMMIT TRANSACTION

    Now the locks taken will remain until you run COMMIT TRANSACTION.

    I recommend that you run sp_lock in a separate query window.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by ETLiens Saturday, January 05, 2013 12:10 AM
    • Marked as answer by Iric WenModerator Monday, January 14, 2013 9:30 AM
    Friday, January 04, 2013 11:18 PM
  • When you run this script in the default READ_COMMITTED isolation level, locks are immediately released as rows are returned.  Run the script with SET TRANSACTION ISOLATION LEVEL REPEATABLE READ as the first statement so locks are held for the duration of the transaction.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Saturday, January 05, 2013 5:06 AM

All replies

  • You are looking at a mirage. The TAB lock is on the table spt_values in the master database, which sp_lock uses. You can tell that you are looking at a system object from the fact that dbid is 1 which is the master database. (And if you use select object_name on that number in master, you will get spt_values.)

    The locks from your queries are released as soon as the query completes. To see the locks after the fact, you need to do:

    BEGIN TRANSACTION

    SELECT TOP 1 * FROM tbl WITH (REPEATABLEREAD)

    -- COMMIT TRANSACTION

    Now the locks taken will remain until you run COMMIT TRANSACTION.

    I recommend that you run sp_lock in a separate query window.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by ETLiens Saturday, January 05, 2013 12:10 AM
    • Marked as answer by Iric WenModerator Monday, January 14, 2013 9:30 AM
    Friday, January 04, 2013 11:18 PM
  • Actually i'm try to test, under how many minimum row locks requires for lock escalation manager to escalate from row level lock to table lock.

    I'm trying to capture this information. Does any one know, how get this details practically?

    I tried running in the below command to capture the information

    begin transaction
    waitfor delay '00:00:05'
    select top 5001 *  from Sessions
    waitfor delay '00:00:20'
    commit transaction

    but it's too fast to capture. And it gives me the output like below

    spid dbid ObjId IndId Type Resource Mode Status
    67    10       0       0    DB                    S   GRANT

    As suggested I ran it in the different window. Now it's not giving me the dbid 1 in the output.


    vivekanandhan periasamy

    Saturday, January 05, 2013 1:42 AM
  • When you run this script in the default READ_COMMITTED isolation level, locks are immediately released as rows are returned.  Run the script with SET TRANSACTION ISOLATION LEVEL REPEATABLE READ as the first statement so locks are held for the duration of the transaction.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Saturday, January 05, 2013 5:06 AM