User Tools

Site Tools


notes:sql:locking

Locking in SQL Server

Return information about active locks in SQL Server:

-- Example #1
EXEC sp_lock
 
-- Example #2
EXEC sp_lock @@spid

Result:

spid   dbid   ObjId       IndId  Type Resource         Mode     Status 
------ ------ ----------- ------ ---- ---------------- -------- ------ 
51     7      0           0      DB                    S        GRANT
55     7      0           0      DB                    S        GRANT
56     1      85575343    0      TAB                   IS       GRANT
12     1      5575058     0      TAB                   IX       GRANT
12     2      0           0      EXT  1:280            X        GRANT
12     2      0           0      PAG  1:528            IX       GRANT
12     2      981578535   0      RID  1:528:0          X        GRANT

Columns:

  • spid - Server process id
  • dbid - Database id
  • ObjId - Object id
  • IndId - The index identification number
  • Type - Type of resource that is currently locked:
    • DB - database
    • EXT - extent
    • TAB - table
    • KEY - key (used only for Serializable transactions)
    • PAG - page
    • RID - row identifier
  • Resource - Describes the resource that is being locked. Example: 1:528:0 - indicates that row number 0, on page number 528, on file 1 has a lock applied to it.
  • Mode - Type of lock:
    • S - shared
    • X - exclusive
    • I - intent
    • U - update
    • Sch - schema
  • Status - A status of the lock:
    • GRANT - obtained
    • WAIT - the lock is blocking on another process
    • CNVRT - the lock is in the process of being converted

More examples:

// Example #1
SELECT @@SPID // RESULT: 56
 
// Example #2
SET LOCK_TIMEOUT 500
notes/sql/locking.txt · Last modified: 2015/06/24 by admin