Lock in SQL Server

• A lock is a mechanism to ensure data consistency.
• A lock can be applied on row level, page level, table level, and database level.

In this article, I will explain about the SQL Locks

• Exclusive Lock.
• Read Uncommitted and Committed data.
• Deadlock and how to avoid deadlock.
• Shared Lock.
• Intent Lock.

Firstly I have created a table it has two columns ID and NAME.

TABLE

EXCLUSIVE LOCK:-

• An exclusive lock is a row-level lock.
• When the user updates data, Its transaction holds an exclusive lock on data that prevents other transactions from accessing the data.

In the query, User1 updates a record where id is 1 then after 20 seconds again updates the same record then rolling back the whole transaction.

At that time User2 tries to fetch the record but User2 have does not display any record. When exclusive lock happens users cannot select, update, and insert the value.

BEFORE 20 SECONDS

After 20 seconds complete the transaction and displays the data.

AFTER 20 SECONDS

READ UNCOMMITTED DATA:

• A query in the current transaction can read data modified within another transaction but not yet committed.
• In the Read Committed Data, it displays only committed data.

In the query, when User1 will execute any transaction User2 displays uncommitted data.

BEFORE 30 SECONDS

After 30 sec displays the committed data.

AFTER 30 SECONDS

DEADLOCK:

• A deadlock is a situation where two transactions wait for each other to give up their respective locks.

Transaction1 and 2 updates the table subsequently read/update data from Table_1.

Transaction1 holds locks that transaction2 needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.

DEADLOCK

AVOID DEADLOCK:

• Keep transaction time minimum:

In transaction for avoiding deadlock keep the WAITFOR DELAY time as a minimum.

• keep logical chronological order:

The transaction will chronological order. so the transaction 2 will go in the blocking mode blocking is different from deadlock.

BLOCKING MODE

• After 20 seconds result will display.

RESULT

• Use an update lock:

Update lock used before an exclusive lock it minimizes the deadlock. During the update lock, other transactions will be waiting for mode but display select statement.

BEFORE 10 SECONDS

The first 10 seconds display select value. but after 10 seconds it goes on exclusive lock after that display the result.

AFTER 10 SECONDS

SHARED LOCK:

• Shared lock applied on during the select query.
• During the shared lock, other transactions can not update and delete that record only select the record.
• An update lock is applied when we have a shared lock. And exclusive lock only we applied when an update lock.

INTENT LOCK:

• Intent lock is used by a transaction to inform another transaction about its intention to acquire a lock.

If you are a newbie to database learning — SQL Server recommended is the following must-watch video: -