Tags

, , ,


Well to begin with, I must stress the importance to understand the different Lock Modes in SQL Server not only for DBAs but also for Developers.

UPDATE Locks can be considered as hybrid of SHARED (S) and EXCLUSIVE  (X) locks. UPDATE (U)  indicates that the data is read for modification hence more than one UPDATE lock is not allowed on the data simultaneously in order to maintain the data integrity, but concurrent (S) lock on the data is allowed.  UPDATE  (U) lock is associated with UPDATE Statement.

UPDATE statements involves two intermediate steps:

  1. Read the data to be modified
  2. Modify the data.

By not acquiring an EXCLUSIVE (X) lock in the very first step, an UPDATE statement allows other transactions to read the data using SELECT statement since (U) and (S) are compatible with each other. This increase database concurrency.

UPDATE locks are really a deadlock avoidance process. If a SQL Server process begins a search operation with the intention of eventually modifying data, it acquires UPDATE locks until it finds the data to modify. UPDATE locks are compatible with SHARED locks, but are not compatible with EXCLUSIVE locks or other UPDATE locks. So if two processes were searching for the same data resource, the first one to reach it would acquire an UPDATE lock, and then the second process could not get any lock and would wait for the first process to be done. Since the first process was not blocked, it could convert its UPDATE lock to an EXCLUSIVE lock, make the data modification, and finish its transaction and release its locks. Then the second process could make its change.

Lets see a Demo:

— Creating a table on which we will see UPDATE lock Demo:

USE tempdb

IF (SELECT OBJECT_ID(‘dbo.t1’)
) IS NOT NULL
DROP TABLE dbo.t1
GO
CREATE TABLE dbo.t1
(c1 INT
,c2 INT
,c3 DATETIME)

— Inserting Dummy Records in the table create above

INSERT INTO dbo.t1
VALUES (11, 12, GETDATE())
INSERT INTO dbo.t1
VALUES (21, 22, GETDATE())

— Now close all other connection and open a new connection

BEGIN TRAN T1
UPDATE t1
SET c3=GETDATE()

— Open second connection in SSMS and paste the below query

BEGIN TRAN T2
UPDATE t1
SET c3=GETDATE()

— Go to the first connection and paste the below query and execute it:

SELECT request_session_id AS session_id, DB_NAME(resource_database_id) AS [database],
request_mode , resource_type ,
resource_associated_entity_id,
resource_description, request_status
FROM sys.dm_tran_locks;

You will see output like this:

Note the U lock with the status of WAIT for a KEY, with the same resource description as a KEY lock that the first connection has  been GRANTED. Now COMMIT or ROLLBACK the first connection,  and you should see the second connection will get the X lock on the KEY it is waiting for, plus the X lock on the other KEY.

Cheers,

Subhro Saha

Advertisements