Why and How Locks Occur

1.      Popular symptoms
a.      Error 1222 – lock request time out period exceeded
b.      Error 1205 – the deadlock victim, re-run the transaction
c.      Timeout expired – transaction takes too long
d.      Applications run slowly, but SQL Server CPU, Disk IO OK
e.      Some queries work fine, but others do not
f.       After restarting SQL Server, it runs fine for a while, but problem comes back again.

2.      Key point: Lock and deadlock is primarily caused by design or application, not by SQL Server itself.

3.      Three major reasons of locks and deadlocks
a.      Locks too long
b.      Too many locks
c.      The level of resources locked is too high

4.      What can we do to reduce the locks?
a.      Choose appropriate Transaction Isolation Level
                                          i.     As the higher a transaction isolation level is, the longer the shared lock is held, you can lessen the locks on the shared lock by lowering the transaction isolation level, but
                                         ii.     You cannot lessen the locks on the exclusive lock by lowering the transaction isolation level.
b.      Use small transactions and return few data if possible
c.      Use optimized execution plans

5.      How to check the current locks in a database?
--Displays information about all locks currently held in an instance of the Database Engine.
--deprecated since SQL 2005, use sys.[dm_tran_locks] instead.
sp_lock

--displays information, including locks, about process ID 60.
sp_lock 60

/*
sys.[dm_tran_locks] - Returns information about currently active lock manager resources.
Each row represents a currently active request to the lock manager for a lock that has
been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: resource and request.
The resource group describes the resource on which the lock request is being made,
and the request group describes the lock request.
*/

SELECT [request_session_id],
[resource_type],
[resource_associated_entity_id],
 [request_type],
 [request_mode],
 [resource_description]
FROM sys.[dm_tran_locks]

-- To further find the locks on which tables and indexes
SELECT
 [request_session_id],
 [resource_type],
 [resource_associated_entity_id],
 [request_status],
 [request_mode],
 [resource_description],
 p.[object_id],
 OBJECT_NAME(p.[object_id]) AS objectname,
 p.*
 FROM sys.[dm_tran_locks] LEFT JOIN sys.[partitions] p ON sys.[dm_tran_locks].[resource_associated_entity_id]
 =p.[hobt_id]
 WHERE [resource_database_id]=DB_ID('AdventureWorks2012')
 ORDER BY [request_session_id],[resource_type],[resource_associated_entity_id] 

6.      How to monitor the lock request and lock release during query execution?
a.      Many locks automatically work in the process. When the query is done, many of the locks have been released. But in some cases, the locks may not be released, lock waiting will occur (and will be likely reflected in the current locks in the database)
b.      SQL Profiler – Lock:Acquired and Lock:Released. Big negative impacts on SQL Server performances.
c.      Typically sys.dm_trans_lock is sufficient, rarely need to use SQL Profiler to trace the lock in the execution process.

7.      How indexes and the transaction isolation level affect locks in SELECT, UPDATE, DELETE, and INSERT (just some scenarios, not exclusive)? See the post - http://sqlserverandme.blogspot.com/2014/09/sql-server-does-not-just-lock-returned.html