In this article, I’m explaining the locks in sql server and
Locking helps in providing concurrency within the database.
Without locking, SQL server would not be able to prevent multiple users from
updating the data at the same time.
Locks helps in preventing following situations that would let to compromise
An update can get lost when a transaction overwrite the
changes from another transaction.
This problem is also known as Dirty Read. This problem
occurs when a record is read while someone is still making changes to it and
yet not finished with it.
An inconsistent analysis occurs when a transaction reads the
same row more than once and in between these two readings another transaction
modifies that row.
This problem occurs when the transaction are not isolated
from one another.
Types of Locks
Shared lock is also known as Read Lock. SQL server uses the
shared locks for operation that neither change nor update data. No transaction
can modify the data in a resource while a shared lock is being held on that
resource by any other transaction.
Exclusive locks or write locks are used for data
modification statements such as INSERT, UPDATE or DELETE. Only one transaction
can acquire an exclusive lock.
Update lock signals that a transaction intends to modify a
resource. An update lock must be upgraded to an exclusive lock before the
transaction actually makes the modification. Only one transaction at a time can
hold an update lock on a particular resource.
SQL server uses the intent lock internally to minimize
locking conflicts. It helps in establishing a locking hierarchy so that other
transactions cannot acquire locks at a more inclusive level. This means that a
transaction has an exclusive row level lock on a particular record then another
transaction will be prevented from acquiring an exclusive lock at the table
Schema locks ensures that a table or an index is not dropped
or its structure modified, when referenced by another session.