What Are the Most Common Lock Modes in SQL Server?

Locking is a core component of any SQL database, since it allows processes to run concurrently without compromising data integrity through simultaneous use of the same resources.

To ensure optimal server performance, there are actually a number of different lock modes which can be applied to processes to determine the priority they hold in relation to their counterparts.

Here is a look at the most common lock modes around and the things they are designed to achieve in normal database operations.

Exclusive Locks

As the name suggests, if a transaction has an exclusive lock assigned to it, it will be able to reserve a given page or row for as long as is required, before relinquishing it so that lower-priority processes can gain access.

This type of lock results in SQL Server blocking in databases, another fundamental component of the way concurrency-based systems operate. Without blocking, two processes might attempt to modify the same resource simultaneously, leading to a higher likelihood of data corruption. Persistent SQL server blocking can be an issue, but irregular occurrences are in less need of immediate attention from a Database Administrator (DBA).

Shared Locks

Once again this is a clearly-named lock type, since it is intended to allow multiple transactions to share read-only access to a page or row, while preventing them from being able to modify it.

Of course, if only one process has placed a shared lock on a resource and it needs to complete a write operation, this is still possible, so long as there is not a second transaction with its own shared lock in place.

Update Locks

If you want a lock mode that offers a little more flexibility than a full-fledged exclusive lock, then an update lock might fit the bill.

The update lock will be applicable to records that have been endowed with a shared lock, but can turn into an exclusive lock at the point when it is required to make alterations to the target data. In this sense the interaction between update locks and shared locks is asymmetrical, with the latter not being able to supersede the former though not vice-versa.

Intent Locks

Preserving the hierarchy of operations is another cornerstone of smooth, consistent SQL database operation and an intent lock aims to make this achievable even if a large volume of transactions are being executed in quick succession.

In short, it lets processes indicate to one another the kind of lock they will seek to place on a resource, so that there are fewer conflicts further down the line.

There are actually three main types of intent locks, each of which corresponds to the aforementioned exclusive, shared and update locks, as you might expect.

Conversion Locks

This is getting into the slightly more complex yet no less significant realm of SQL server lock modes, in which a further intermingling of the top-level types covered so far is apparent.

"Shared with intent exclusive" locks, for example, let processes block out whole tables so that other transactions cannot apply alterations but can still read the data until such a time as the process with this lock adds its own changes.

There are also "shared with intent update" and "update with intent exclusive" lock modes that fall into this category, so it is definitely worth exploring the options in more detail when learning SQL if you are eager to get a handle on how locking works. This is not just good for improving server performance, but can also help with any troubleshooting you may need to do when blocking and even deadlocking occurs.