Update - Locks in SQL Server - Part 4
Click here to Subscribe to IT PORT Channel : / @itport28
Update (U) locks prevent a common form of deadlock. In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock.
If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs.
The second transaction attempts to acquire an exclusive (X) lock for its update. Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.
To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.
Isolation Level - • SQL Server Isolation Example
Script for Active_Locks Function
---------------------------------------------------
Create Function Active_locks () returns table return
select Top 10000000 case dtl.request_session_id
when -2 then 'orphaned distributed transaction'
when -3 then 'deferred recovery transaction'
else dtl.request_session_id end as spid,
db_name(dtl.resource_database_id) as databasename,
so.name as lockedobjectname, dtl.resource_type as lockedresource,
dtl.request_mode as locktype, es.login_name as loginname, es.host_name as hostname,
case tst.is_user_transaction when 0 then 'system transaction'
when 1 then 'user transaction' end as user_or_system_transaction,
at.name as transactionname, dtl.request_status
from sys.dm_tran_locks dtl join sys.partitions sp on sp.hobt_id = dtl.resource_associated_entity_id
join sys.objects so on so.object_id = sp.object_id
join sys.dm_exec_sessions es on es.session_id = dtl.request_session_id
join sys.dm_tran_session_transactions tst on es.session_id = tst.session_id
join sys.dm_tran_active_transactions at on tst.transaction_id = at.transaction_id
join sys.dm_exec_connections ec on ec.session_id = es.session_id
cross apply sys.dm_exec_sql_text(ec.most_recent_sql_handle) as st
where resource_database_id = db_id() order by dtl.request_session_id