Lock and it types - SQL Server

In SQL Server, a "LOCK" is a mechanism used to ensure data consistency and prevent data corruption when multiple transactions access the same data concurrently. When a transaction needs to access a piece of data, it requests a lock on that data. If the data is already locked by another transaction, the requesting transaction may have to wait until the lock is released.

There are several types of locks in SQL Server, including:

Shared Lock: A shared lock allows multiple transactions to read the same data simultaneously, but it prevents any transaction from modifying the data until the lock is released. For example, when multiple transactions are reading from the same table, they acquire shared locks on the table to ensure that they don't interfere with each other.

Exclusive Lock: An exclusive lock is acquired when a transaction needs to modify data, and it prevents any other transaction from reading or modifying the same data until the lock is released. For example, when a transaction is updating a row in a table, it acquires an exclusive lock on that row to ensure that no other transaction can modify it at the same time.

Update Lock: An update lock is a special type of lock that is acquired when a transaction needs to modify data, but it still allows other transactions to read the same data. However, if another transaction tries to acquire an exclusive lock on the same data, it will have to wait until the update lock is released.

Intent Lock: An intent lock is a type of lock that is used to signal the intent of a transaction to acquire a higher-level lock. For example, when a transaction wants to acquire an exclusive lock on a row, it first acquires an intent exclusive lock on the table.

Schema Lock: A schema lock is a type of lock that is acquired when a transaction needs to modify the schema of a database object, such as a table or a stored procedure.

Here is an example of how locks work in SQL Server:

• Assume we have two transactions T1 and T2 that are accessing the same table.
• Transaction T1 starts and acquires a shared lock on the table.
• Transaction T2 starts and also tries to acquire a shared lock on the table, but it has to wait because the table is already locked by T1.
• Transaction T1 reads some data from the table.
• Transaction T2 tries to update a row in the table, so it requests an exclusive lock on that row.
• Transaction T2 has to wait until T1 releases its shared lock on the table.
• Transaction T1 finishes and releases its shared lock on the table.
• Transaction T2 acquires an exclusive lock on the row and updates it.
• Transaction T2 finishes and releases the exclusive lock on the row.

Get info about active SQL Server locks

SQL Server provides the Dynamics Management View (DMV) sys.dm_tran_locks that returns information about lock manager resources that are currently in use, which means that it will display all “live” locks acquired by transactions. 

The most important column used for the identification of the lock are resource_type, request_mode, and resource_description. If needed, more columns as additional resource for information info can be included during troubleshooting

Here is the example of the query -

SELECT resource_type, request_mode, resource_description

FROM sys.dm_tran_locks

Locks in SQL Server are used to control concurrent access to data by transactions. Different types of locks are used to ensure that transactions don't interfere with each other, and that data remains consistent.