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
