Locks Concept in SQL Server:
A very important consideration to look while working on
Performance related issues in SQL Server.
What is a Lock in SQL Server: Preventing the transaction from other transaction is called a lock. There are several types of locks in SQL Server out of which few of them are explained here.
Types of Locks:
Shared Locks: As the name itself is explaining, locks are
shared between users. It means a table which is in shared lock can be read by
other user as well.
Update Locks: Update lock comes into picture when any UPDATE
operation takes place in SQL Server.
Exclusive Locks: Exclusive lock comes into picture when any
INSERT, UPDATE or Delete operation takes place in SQL Server.
How Much compatible is one lock with another locks:
As the above diagram says,
Shared Locks are compatible with other Shared locks and
Update locks.
Update Locks are compatible with only Shared locks.
Exclusive locks are not compatible with any other lock.
What is the difference between Update and Exclusive Lock:
Update lock will take place when Update operation is
performed and Exclusive locks will take place when Insert, Update or Delete
operation is performed.
Update lock / Exclusive Lock ????
Update operation will call for Update lock first which then
escalates the lock to Exclusive lock whereas operations like Insert and Delete
directly calls for Exclusive Lock.
What is an Intent Lock then?
Each lock will have its own Intent locks. Here is how we
call Shared Lock (IS) – Intent Shared Lock
Update Lock (IU) – Intent Update Lock, Exclusive Lock (IX) –
Intent Exclusive lock.
Depending upon the necessity SQL Server will allocate the
lock Key level, Row level, Page level, Table level or Database level. Any type of lock can be imposed on the above
levels.
How will SQL Server Proceed for locking:
SQL Server will proceed from top to bottom level. Database
level to Row level. If it want lock at row level, Intent lock will imposed on
Database level, then table level, then page level then finally to Row level.
After Coming to Row level Intent lock it will decide whether to go for
Shared/Update/Exclusive.
Does every user will have an Intent Lock on database:
Yes, Every user will have an intent lock on database, which
restricts other user to restore the database, alter any database related stuff.
How to identify Locks in database:
LCK_M_<locktype>
Eg: LCK_M_IS , LCK_M_IU, LCK_M_IX
Example:
Am trying to insert a row into my table “Room” and will keep
the transaction open as shown below.
Now am running the select statement in other window of SQL
Server. Querying the same table which has the insert just now as shown below:
Second session will be in suspended status until the lock
gets released by the first transaction.
Second transaction is trying to acquire shared lock by first
transaction already has an Exclusive lock on the table “Room”. As we know from
above that Exclusive locks are not compatible with any other locks, it will now
allow the second user to read the table.
Execute the below query to find the details about the
sessions in Locking:
SELECT er.session_id ,
host_name , program_name ,
login_name , er.reads
,er.writes ,er.cpu_time , wait_type , wait_time , wait_resource ,
blocking_session_id , st.text
FROM sys.dm_exec_sessions es
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE blocking_session_id > 0
UNION
SELECT es.session_id , host_name , program_name , login_name ,
es.reads , es.writes , es.cpu_time , wait_type , wait_time ,
wait_resource , blocking_session_id , st.text
FROM sys.dm_exec_sessions es
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
WHERE es.session_id IN ( SELECT blocking_session_id
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0 )
Hope the above information helps
Thanks.
