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.


  • 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.

After 20 seconds complete the transaction and displays the 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.

After 30 sec displays the committed data.


  • 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.


  • 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.

  • After 20 seconds result will display.
  • 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.

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


  • 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 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: -



Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store