Locking and Blocking, Family Management.

Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server.

Lock granularity consists of DATABASE, TABLE, PAGE and ROW locks.  As soon as you connect to a SQL Server database, a shared lock is issued on the database level itself. This prevents another user from performing an action such as “DROP DATABASE” while there is a connection to it.

SQL Server will perform it’s lock granularity from top down. First, it will check if a table lock is necessary, then page then row. An Intent(Shared or Exclusive), dependent if the operation is read or write, is issued on the table and page and a shared lock on the row. If the amount of data necessary is more than a row and on a page. Once SQL Server needs more than 5,000 row locks, it will escalate(by default) to a table lock.

You can alter these defaults by either using query hints such as ROWLOCK, PAGLOCK or TABLOCK. You can also alter the lock escalation of each table by using the following:

ALTER	TABLE MyTable
SET	LOCK_ESCALATION = <AUTO, TABLE, DISABLE>

This is generally not recommended as it is best to allow the Database Engine to escalate locks accordingly. Lower level locks increase concurrency, but consume more memory.

Fun Scripts

So, we now know that locking and blocking are part of the natural occurrence of processing transactions.  When it comes to blocking the objective is to lessen the affects and hold the blocks as little time as possible.  Below, you will find two scripts.  The first will list was locking is occurring within your database.  The second will list your current processes and list the ones blocking first.  These may help in ascertaining what issues need to be addressed regarding blocking.

Current processes, showing blocking first

if	object_id('tempdb..#tmpdm_exec_sessions') is not null
	drop	table #tmpdm_exec_sessions

if	object_id('tempdb..#tmpdm_exec_requests') is not null
	drop	table #tmpdm_exec_requests

if	object_id('tempdb..#tmpdm_exec_connections') is not null
	drop	table #tmpdm_exec_connections

if	object_id('tempdb..#tmpsysprocesses') is not null
	drop	table #tmpsysprocesses

if	object_id('tempdb..#tmpdm_os_waiting_tasks') is not null
	drop	table #tmpdm_os_waiting_tasks

select	*
into	#tmpdm_exec_sessions
from	sys.dm_exec_sessions

select	*
into	#tmpdm_exec_requests
from	sys.dm_exec_requests

select	*
into	#tmpdm_exec_connections
from	sys.dm_exec_connections

select	*
into	#tmpsysprocesses
from	sys.sysprocesses

select	*
into	#tmpdm_os_waiting_tasks
from	sys.dm_os_waiting_tasks

select	distinct *
from	(
	SELECT	s.session_id AS spid			,
		s.[status]				,
		s.login_name AS loginName		,
		s.[host_name] AS hostName		,
		DB_NAME(s2.dbid) AS dbName		,
		r.command				,
		s.cpu_time AS cpuTime			,
		s.reads + s.writes AS diskIO		,
		s.last_request_end_time AS lastBatch	,
		s.[program_name] AS programName		,
		s.session_id				,
		r.request_id				,
		CASE	s.transaction_isolation_level
			WHEN 0 THEN 'Unspecified' 
			WHEN 1 THEN 'ReadUncommitted' 
			WHEN 2 THEN 'ReadCommitted' 
			WHEN 3 THEN 'Repeatable' 
			WHEN 4 THEN 'Serializable' 
			WHEN 5 THEN 'Snapshot' 
		END	AS transactionIsolationLevel	,
		OBJECT_NAME(t.objectid) AS objectName	,
		SUBSTRING	(t.text,r.statement_start_offset/2,
			(
			CASE
				WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),t.text)) * 2
				ELSE r.statement_end_offset
			END - r.statement_start_offset
			)/2
			) as IndividualQuery		,
		t.[text] AS ParentQuery			,
		w.blocking_session_id			,
		r.percent_complete			,
		r.estimated_completion_time		,
		r.status as request_status		,
		r.wait_type
	FROM	#tmpdm_exec_sessions AS s 
		LEFT JOIN #tmpdm_exec_requests AS r ON r.session_id = s.session_id 
		LEFT JOIN #tmpdm_exec_connections AS c ON c.session_id = s.session_id 
		CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t 
		inner join #tmpsysprocesses s2 on s2.spid = s.session_id
		LEFT join #tmpdm_os_waiting_tasks w on w.session_id = s.session_id
	WHERE	s.is_user_process = 1
	) a
where	a.[status] != 'sleeping'
order	by a.blocking_session_id desc

 Locks currently held within the database

set	nocount on
/*****************************************************************************************************
**
*****************************************************************************************************/
	declare	@dm_tran_locks table
		(
		SPID				int		,
		DBName				varchar(256)	,
		LockedResource			nvarchar(60)	,
		resource_associated_entity_id	bigint		,
		resource_database_id		int		,
		LockType			nvarchar(60)
		)

	declare	@sys_partitions table
		(
		hobt_id				bigint		,
		[object_id]			int
		)

	declare	@sys_objects table
		(
		object_id			int		,
		name				varchar(256)
		)

	declare	@sys_dm_exec_sessions table
		(
		session_id			smallint	,
		login_name			nvarchar(128)	,
		host_name			nvarchar(128)
		)

	declare	@sys_dm_exec_connections table
		(
		auth_scheme			nvarchar(40)	,
		session_id			smallint	,
		most_recent_sql_handle		varbinary(64)
		)
/*****************************************************************************************************
**
*****************************************************************************************************/
	insert	into @dm_tran_locks
		(
		SPID				,
		DBName				,
		LockedResource			,
		resource_associated_entity_id	,
		resource_database_id		,
		LockType
		)
	select	l.request_session_id		,
		db_name(l.resource_database_id)	,
		l.resource_type			,
		l.resource_associated_entity_id	,
		l.resource_database_id		,
		l.request_mode
	from	sys.dm_tran_locks l
	WHERE   l.resource_database_id = db_id()

	insert	into @sys_partitions
		(
		hobt_id				,
		[object_id]
		)
	select	p.hobt_id			,
		p.object_id
	from	sys.partitions p

	insert	into @sys_objects
		(
		object_id			,
		name
		)
	select	object_id, name
	from	sys.objects s

	insert	into @sys_dm_exec_sessions
		(
		session_id			,
		login_name			,
		host_name
		)
	select	c.session_id, c.login_name, c.host_name
	from	sys.dm_exec_sessions c

	insert	into @sys_dm_exec_connections
		(
		auth_scheme			,
		session_id			,
		most_recent_sql_handle
		)
	select	c.auth_scheme, c.session_id, c.most_recent_sql_handle
	from	sys.dm_exec_connections c
/*****************************************************************************************************
**
*****************************************************************************************************/

	SELECT  l.SPID					,
		l.DBName				,
		o.Name		AS LockedObjectName	,
		p.object_id	AS LockedObjectId	,
		l.LockedResource			,
		l.LockType				,
		ST.text		AS SqlStatementText	,        
		ES.login_name	AS LoginName		,
		ES.host_name	AS HostName		,
		CN.auth_scheme	as AuthenticationMethod
	FROM    @dm_tran_locks l
		inner join @sys_partitions p on p.hobt_id = l.resource_associated_entity_id
		inner join @sys_objects o on o.object_id = p.object_id
		inner join @sys_dm_exec_sessions es on es.session_id = l.SPID
		inner join @sys_dm_exec_connections cn on cn.session_id = es.session_id
		CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) st
/*****************************************************************************************************
**
*****************************************************************************************************/
	SELECT  l.SPID					,
		o.Name		AS LockedObjectName	,
		l.LockedResource			,
		l.LockType				,
		count(1)	as lock_count		
	FROM    @dm_tran_locks l
		inner join @sys_partitions p on p.hobt_id = l.resource_associated_entity_id
		inner join @sys_objects o on o.object_id = p.object_id
	group	by l.SPID, o.name, l.LockedResource, l.LockType

Coming up: Deadlocks – Who gets the toy?

References: