Types of database locks

I recommend reading this article to understand the issues introduced by concurrency in databases, if you haven’t already.

So the tradeoffs between concurrent and sequential access are -

• Either we keep sequential access to the database. Sacrifice scalability in favor of keeping things simple.

• Or we allow concurrent access and face the issues that come with it.

Well, the issues introduced by concurrent access are resolved with the help of database locks. Database locks prevent access to one or more pieces of data within the database based on fulfillment of certain conditions. They make it look as if the transactions are executing sequentially and hence maintaining data integrity.

There can be different types of locks. These can be implemented differently by different databases. In this article, we are going to take a look at the types of locks.

1. Shared Locks (S Locks)

• Use case - This lock is applied on a resource when the transaction wants to read the resource but not modify it.

• It prevents any other transactions from modifying the resource. However, it allows other transactions to read the resource. Multiple transactions can obtain S Locks on the same resource at the same time.

2. Exclusive Locks (X Locks)

• Use case - This lock is applied on a resource when the transaction wants to read and modify the resource e.g. as in (INSERT, UPDATE and DELETE statements).

• Only 1 transaction can obtain an X Lock on a resource at a time. No other S or X Locks can be obtained on the same resource at the same time. This means that all other transactions are prevented from reading/modifying the same resource when another transaction has an X Lock on the resource.

3. Intent Locks

• Use case - This lock is used to tell the SQL server that a lock has been obtained at a lower level in the hierarchy. This lock can be an S, X or both S and X locks. This is used to speed up the SQL server.

• e.g. Let’s say that a transaction has obtained an S Lock on a row. In this case, it will obtain an Intent Shared Lock on the parent nodes in the hierarchy (page/table/database). The next time another transaction wants to acquire an X Lock on all the rows in the page, the SQL server can reject the request since a row on the page has an S Lock on it. For this, the server doesn’t need to check every row. It knows this since there is an Intent Shared Lock on the page.

• There can be different types of Intent Locks depending upon which lock has been obtained at the lower level in the hierarchy such as Intent Shared Locks (IS), Intent Exclusive Locks (IX), Intent Update Locks (IU) and Shared Intent Exclusive Locks (SIX). In SIX Lock, a transaction has an S Lock on a resource with an X Lock on lower levels of it.

• Here is a really good explanation about the intent locks - https://sqlundercover.com/2019/07/25/intent-locks-in-sql-server/

• Intent Locks are applied on their own by the database.

4. Update Locks (U Locks)

• Use case - These are applied when the transaction reads a resource and can possibly modify it later. In such cases, a U Lock is obtained to prevent deadlocks that may occur while upgrading an S Lock to an X Lock.

• How it works - Only 1 U Lock can be obtained on a resource at a time. X Locks cannot be obtained on the same resource while a U Lock is active. However, S Locks can be obtained on the same resource. Once the read is complete and the transaction wants to modify the resource, the U Lock is upgraded into an X Lock.

• Prevention of Deadlocks - U Locks aims to minimize the possibility of the occurrence of a Deadlock. In the absence of U Locks, Deadlock can occur when multiple transactions, that have an S Lock on a resource, decide to update the resource by obtaining an X Lock. They all end up waiting for the other transactions to terminate so they can obtain an X Lock leading to a Deadlock situation.

• Problem - How is the upgrade of U to X occur if multiple S Locks are also active? There are different lock management techniques that might be used. e.g. checkout this answer. In SQL Server, the locks are granted in a relaxed FIFO fashion. So, once a U Lock decided to upgrade to an X Lock, it is added to the queue. It will eventually be upgraded when all the earlier requests have been fulfilled.

5. Schema Locks (Sch Locks)

• Use case - Sch Locks are used to lock the schema of an object so that no other transaction can modify the schema of the object.

• Types - There are 2 types of Sch Locks - Schema Modification Locks (Sch-M) and Schema Stability Locks (Sch-S).

• Sch-M Locks - This is acquired when a DDL statement modifies the schema of an object. Then a Sch-M lock prevents other transactions from accessing the data of the locked object. An example of Sch-M lock is index rebuild. It is a table modification operation so a Sch-M lock is obtained and it is released only after the index rebuild is completed.

• Sch-S Locks - This is acquired when a schema dependent query is executed. It is compatible with all locks except Sch-M Locks. Basically, it prevents any modification to the schema as long as it is active. It is acquired by every DML and SELECT query to ensure that the queries are executed without errors. Errors like dirty reads can be absorbed but a change in schema while a SELECT query is being run won’t be absorbed.

• The term “object” in Schema Locks is used to refer to resources such as tables.

6. Bulk Update Locks (BU Locks)

• Use case - These are obtained on a table when we want to perform a bulk load operation.

• While a BU Lock is active, other processes will not be able to access the table. However, other bulk load processes can be performed in parallel depending upon the database configuration. An example of the case of parallel bulk loading is while loading the data in partitioned tables.

• It speeds up the process since individual locks on rows are not needed. Instead, a BU Lock can be obtained at the table level.

• Here are guidelines for bulk importing in SQL Server. An interesting read on the topic - https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms177445(v=sql.105)

7. Key-Range Locks

• Use case - Key-Range Locks are used while executing ranged queries (range in WHERE clause) to prevent Phantom reads.

Note - These locks can be used to explicitly control the access to data. However, they can be bypassed if needed (possibly leading to one or more concurrency issues). e.g. a S lock cannot be applied on an object that has an X lock on it. However, we can still access the object by a simple SELECT statement.