In this article, I’m explaining the locks in sql server and its types.
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 transaction integrity:
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
1. Shared Lock
2. Exclusive Lock
3. Update Lock
4. Intent Lock
5. Schema Lock
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 level.
Schema locks ensures that a table or an index is not dropped or its structure modified, when referenced by another session.