SQL Server Locks

• Locking is a mechanism used by SQL Server to protect its resources.
• For example, this is needed when we wants to prevent someone from modifying data when we are reading it, etc.

• Lock Manager:  Locks are managed internally by a part of the Database Engine called the lock manager.

• Lock Resources
• RID: Row Identifier.
• Key: Row Lock within an index. Used to protect range of keys.
• Page: 8KB Data page/Index page.
• Extent: Logicla Group of Eight pages.
• Table: Table with indexes.
• HOBT: Heep or B-Tree.
• Application: An Application specific resource.
• Metadata:
• Allocation Unit:
• DB: Database

• Lock Modes:
• Shared(S) : For operations not changing data, prevents other queries for doing modifications.
• Update(U) : For resources that can be updated.
• Exclusive(X) : For Insert, Update, Delete.
• Intent : For establishing lock hierarchy.
• Intent Share(IS)
• Intent Exclusive(IX)
• Shared With Intent Exclusive(SIX)
• Schema
• Schema Stability(Sch-S)
• Schema Modification(Sch-M)
• Bulk Update: Used for Bulk Copy and for "TABLOCK" hint.
• Key-Range: Prevents range of rows query using Serializable isolation level.

• Lock Compatibility Table.

Row Locks

• Shared
• Update
• Exclusive

• Basic lock hierarchy  is top down in SQL Server as shown below.


• Screen below shows lock hierarchy which is followed when updating data rows.
 

• Lock Granularity
• Row/Page
• Partition
• Table Level

• Lock Escalation

Lock escalation is triggered when lock escalation is not disabled on the table & when either of the following conditions exists: 

•  A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index. 
• A single Transact-SQL statement acquires at least 5,000 locks on a single partition of a partitioned table and the ALTER TABLE SET LOCK_ESCALATION option is set to AUTO. 
• The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds. 

• Escalation setting per table
• Alter Table <Table Name> SET (Lock_Escalation = Auto| Table|Disable)
•  Auto: Partition level escalation, if partitioned.
• Table: Always escalates to Table.
• Disabled: No Escalation unless necessary.

• Checking Current Setting
• Select Lock_Escalation_Desc From sys.Tables Where Name='<Table Name>'

• Dynamic Locking  

• The Microsoft SQL Server Database Engine uses a dynamic locking strategy to determine the most cost-effective locks. The Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query. For example, to reduce the overhead of locking, the optimizer may choose page-level locks in an index when performing an index scan. 

•  Monitoring Locks 
• SELECT *
  FROM SYS.DM_TRAN_LOCKS
  WHERE Resource_Type <> 'DATABASE'.
• sp_Lock

• Controlling Lock Granularity
• We can use queru hints for controlling Lock Granularity
• ROWLOCK: Tells query optimizer to use row level lock instead of Page/Table Level Lock.
• PAGLOCK
• TABLOCK
• UPDLOCK
• XLOCK

• Lock Timeout
• Specifies the number of milliseconds a statement waits for a lock to be released.
• By Default Lock Timeout is set to infinite.
• SELECT @@Lock_Timeout
• SET LOCK_TIMEOUT 1800

• NOLOCK Table Hint
• This table hint, also known as READUNCOMMITTED, is applicable to SELECT statements only. 
• NOLOCK indicates that no shared locks are issued against the table that would prohibit other transactions from modifying the data in the table. 
•  SELECT COUNT(*) FROM SalesHistory WITH(NOLOCK) 

•  READPAST Table Hint 
• This is a much less commonly used table hint than NOLOCK. 
• This hint specifies that the database engine not consider any locked rows or data pages when returning results. 
• SELECT COUNT(*) FROM SalesHistory WITH(READPAST)

• Blocking
• Blocking is a situation where one query waits for acquiring lock on resource which is already locked by another query with uncompatable lock.
• Screen below, shows an example.

• Detecting a blocking session
• sys.dm_exec_requests DMV 

• The sys.dm_exec_requests DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.
• • USE Master GO SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0; GO

• sys.dm_os_waiting_tasks DMV 
• The sys.dm_os_waiting_tasks DMV returns information about the tasks that are waiting on resources. 
• To view the data, users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance. 
• USE Master GO SELECT session_id, wait_duration_ms, wait_type, blocking_session_id FROM sys.dm_os_waiting_tasks WHERE blocking_session_id <> 0 
• GO 

• use sp_Pref_Stats09
• Programmatically query data by querying 
• master.dbo.sysprocesses
•  sys.dm_exec_connections
• sys.dm_exec_sessions
• sys.dm_exec_requests

• Using System Stored Procedures
• Blk Column in sp_Who
• BlkBy column sp_Who2
• Shows wether or not connections are being blocked.
• Using Activity Monitor fro SSMS
• Right Click on Server node in Object explorer and select "Activity Monitor".

•  Now, Expand Processes and in "Blocked By" Column will show session ID of Blocking Process.

• SQL Server Management Studio Reports 
•  The second option in SQL Server Management Studio to monitor blocking is with the standard reports.
• Right Click on the instance name
• Select Reports -> Standard Reports -> Activity - All Blocking Transactions. 
• Or, Top Transaction by Blocking.
• Users should have SQL Server System Administrator or VIEW SERVER STATE permissions on the instance.