As you may know, SQL Server uses different kinds of locks on resources belonging to the requested data and objects, to manage and maintain data consistency and data concurrency. By default, SQL Server acquires a shared lock on the resources when a SELECT query is executed. The resources can be anything from a table, to a range of keys, or single row. So, when we execute a
SELECT query, the SQL Server tries to acquire a shared lock on the requested resources. However, if another transaction is updating the same data and has acquired the
UPDATE locks on the same resource, the
SELECT query that tries to acquire a
SHARED lock on the resources may have to wait until another transaction is completed, based on the transaction ...