Exclusive (X) Lock in SQL Server

An Exclusive Lock (X Lock) is acquired by SQL Server whenever data is modified through INSERT, UPDATE, DELETE, or MERGE operations. An Exclusive Lock prevents all other transactions from reading or modifying the locked data using normal locking-based isolation levels. Only one transaction can own an Exclusive Lock on a resource at a time.

Shared Lock (S) = Read Lock

Exclusive Lock (X) = Write Lock

Imagine we are editing a Microsoft Word document. While editing we are changing the document

Others cannot Modify it and may not even be able to read the latest version depending on the system. This is exactly how an Exclusive Lock works.

Whenever data changes SQL Server acquires Exclusive Lock (X). below cases it acquires Exclusive Lock.

INSERT INTO Employee  VALUES (1,'Bagesh',50000);  UPDATE Employee SET Salary = 60000 WHERE EmployeeID = 1;  DELETE FROM Employee WHERE EmployeeID = 1;

When we are running the below query

BEGIN TRAN;

UPDATE Employee SET Salary = 70000

WHERE EmployeeID = 1;

Internally it performs the below steps

Step 1: Find Row

Step 2: Acquire X Lock

Step 3: Modify Data

Step 4: Write Log Record

Step 5: Hold X Lock

Step 6: COMMIT/ROLLBACK

Step 7: Release X Lock 

Below query is used to check the lock session

SELECT     request_session_id,     resource_type,     request_mode,     request_status FROM sys.dm_tran_locks WHERE request_mode = 'X';

Here we are updating a record

Now see the lock details

Session 84 owns an Exclusive Lock

Lock Hierarchy

X Locks can exist on:

Resource	Meaning
RID	Heap Row
KEY	Index Row
PAGE	Data Page
OBJECT	Table
DATABASE	Entire Database

Exclusive Lock vs Shared Lock

Feature	Shared (S)	Exclusive (X)
Purpose	Read	Write
Multiple Sessions Allowed	Yes	No
Blocks Readers	No	Yes
Blocks Writers	Yes	Yes
Used By	SELECT	INSERT/UPDATE/DELETE
Compatible with S	Yes	No
Compatible with X	No	No