top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Explain different types of Locks in SQL Server?

+1 vote
413 views
Explain different types of Locks in SQL Server?
posted Nov 22, 2014 by Balu

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+1 vote

There are 3 kinds of locks in SQL Server

i.) Shared locks - they are used for operations which do not allow any change or update of data. For e.g. SELECT.

ii.) Update locks - they are used when SQL Server wants to modify a page. The update page lock is then promoted to an exclusive page lock before actually making the changes.

iii.) Exclusive locks - they are used for the data modification operations. For e.g. UPDATE, INSERT, or DELETE.

answer Nov 22, 2014 by Manikandan J
0 votes

SQL server supports following locks

  • Shared lock
  • Update lock
  • Exclusive lock

Shared lock

Shared Lock allows simultaneous access of record by multiple Select statements.
Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading.
If update process is going on then read command will have to wait until updating process finishes.

Update locks

This lock is used with the resources to be updated.

Exclusive locks

This kind of lock is used with data modification operations like update, insert or delete.

answer Nov 22, 2014 by Vrije Mani Upadhyay
...