Isolation Levels, Concurrency and Database Locks analysis with Spring’s @Transactional

Today, I’m going to demonstrate the practical differences between the standard isolation levels, how they affect the lock types generated in the database, and how to write unit tests that can ensure the desired behavior in a specific @Transactional method. We’re also going to explore the influence of having a non-clustered index for one of the search columns.

We’re going to analyse 2 cases:

• Selecting an existing row and updating that same row later in the transaction, based on a determined condition
• Selecting a row, and inserting a new registry in case the row does not exist.

For both cases, we’re going to use the same table, which contain only 3 columns. An auto-generated id, an UUID, and a numeric status representing the state of this registry.

Database Setup

We’re going to make the tests utilizing SQL Server for the database. The creation of the table was done with the following DDL:

CREATE TABLE control_table (
 id int IDENTITY(1,1) NOT NULL,
 uuid varchar(36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 status int NOT NULL,
 CONSTRAINT PK__control___3213E83F4F29C19D PRIMARY KEY (id)
);

The table was initially populated with only 100 entries. Later, we’re going to INSERT more entries in the table to show the effect of having a indexed column in the locking mechanism.

DECLARE @i INT = 1;
WHILE @i <= 100
BEGIN
    INSERT INTO control_table (uuid, status)
    VALUES (NEWID(), (ABS(CHECKSUM(NEWID())) % 5) + 1);
    SET @i = @i + 1;
END

Analysis Queries

We’re going to use the following queries to check the system tables of SQL Server and see information about the generated locks and blocking sessions. The query below shows information about the current transaction locks. For simplicity, we’re going to refer to this table as the “locks table”.

SELECT 
 tl.resource_type, 
 tl.request_session_id,
 tl.resource_description, 
 tl.resource_associated_entity_id, 
 tl.request_mode, 
 tl.request_type, 
 tl.request_status,
 c.connection_id
FROM sys.dm_tran_locks tl
JOIN sys.dm_exec_sessions s ON tl.request_session_id = s.session_id
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id;

The next query shows information about the sessions, showing which session is blocking which. For simplicity, we’re going to refer to this table as the “sessions table.”

SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.last_wait_type,
    r.status,
    r.command,
    t.resource_type,
    t.resource_description,
    t.request_mode,
    t.request_status
FROM sys.dm_exec_requests r
LEFT JOIN sys.dm_tran_locks t
    ON r.session_id = t.request_session_id
WHERE r.blocking_session_id <> 0
ORDER BY r.session_id;veling session. If you're already amiliar with the terms, you can skip to the practical part

The next two sessions will serve as a knowledge-leveling session. If you’re already familiar with the terms, you can skip to the practical part.

Isolation Levels

• READ UNCOMMITTED: Dirty reads are allowed, meaning one transaction may see not-yet-committed changes made by other transactions.
• READ COMMITTED: Prevents dirty reads. The Database Engine keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the read operation is performed. This is SQL Server default level.
• REPEATABLE READ: The Database Engine keeps read and write locks that are acquired on selected data until the end of the transaction. This prevents other transactions from modifying selected data, avoiding non-repeatable reads.
• SERIALIZABLE: The Database Engine keeps read and write locks acquired on selected data until the end of the transaction. Range-locks are acquired when a SELECT operation uses a range WHERE clause to avoid phantom reads. This prevents other transactions from changing the result set, because there are locks on every key that would match the predicate of the query, avoiding phantom reads.

SQL Server Lock Types

We’re going to focus on lock types that are going to appear in this article.

• Shared (S): allow concurrent transactions to read a resource under pessimistic concurrency control. No other transactions can modify the data while shared locks exist on the resource. Shared locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to REPEATABLE READ or higher, or a locking hint is used to retain the shared locks for the duration of the transaction.
• Update (U): The Database Engine places update locks as it prepares to execute an update. U locks are compatible with S locks, but only one transaction can hold a U lock at a time on a given resource. This is key - many concurrent transactions can hold S locks, but only one transaction can hold a U lock on a resource. Update locks are eventually upgraded to exclusive locks to update a row.
• Exclusive (X): Exclusive locks prevent access to a resource by concurrent transactions. With an exclusive lock, no other transactions can modify the data protected by the lock; read operations can execute only under the READ UNCOMMITTED isolation level or the use of NOLOCK hint
• Intent (I): Acquired before a lock at the lower level and, therefore, signal intent to place locks at a lower level.
• Key-Range Locks (Range): Protect a range of rows implicitly included in a record set being read by a SQL statement while using the SERIALIZABLE transaction isolation level. Prevents phantom reads by protecting the ranges of keys between rows, and it also prevents phantom insertions or deletions.

In this section, we’re going to explore the effects of changing the isolation levels for the two given scenarios.

Scenario 1: selecting an existing row for UPDATE

We’re going to analyze the possible outcomes of having 2 threads calling the update() method concurrently. The method first select a registry based on it’s UUID, then updates the registry only if the incoming status is higher than the current status in the database.

Goal: our goal is to guarantee that we’re end up with status 3 in the database. If thread STATUS2 comes first, then the expected evolution of status would be 1 → 2 → 3. If thread STATUS3 comes first, then would be 1 → 3.

@Transactional(isolation = Isolation.READ_COMMITED)
public void update(StatusDomain statusDomain) {
    log.info("Thread {} entered update method", currentThread().getName());

    StatusDomain current = repository.findByUuid(statusDomain.getUuid());
    log.info("Thread {} selected current status {}", currentThread().getName(), current.getStatus());

    ThreadUtils.sleep(3000);

    if (statusDomain.getStatus() > current.getStatus()) {
        repository.update(statusDomain);
        log.info("Thread {} updated from current status {} to new status {}",
                currentThread().getName(), current.getStatus(), statusDomain.getStatus());
    }

    log.info("Thread {} exited update method", currentThread().getName());
}

Inside the update() method, there’s a sleep of 3 seconds after the SELECT and before the UPDATE statement, so both threads will be able to perform the SELECT before any of them perform the UPDATE. The logs will be useful to show the selected values and general order of events.

The integration test are going to have this format:

@SpringBootTest
class StatusServiceTest {

    @Autowired
    public StatusService service;

    @MockitoSpyBean
    public StatusRepository repository;

    private final Integer BEFORE_EACH_UPDATE = 1;

    private final StatusDomain status1 = new StatusDomain(1L, "DBFB79A1-5E78-4EA8-9FD1-2B2F4217DACC", 1);
    private final StatusDomain status2 = new StatusDomain(1L, "DBFB79A1-5E78-4EA8-9FD1-2B2F4217DACC", 2);
    private final StatusDomain status3 = new StatusDomain(1L, "DBFB79A1-5E78-4EA8-9FD1-2B2F4217DACC", 3);

    @BeforeEach
    void setUp() {
        repository.update(status1);
    }

    @Test
    void testUpdate() {
        assertDoesNotThrow(() -> {
            Thread t1 = new Thread(() ->                service.update(status2),   "STATUS2");
            Thread t2 = new Thread(() -> { sleep(1000); service.update(status3);}, "STATUS3");

            t1.start(); t2.start();
            t1.join(); t2.join();
        });

        Mockito.verify(repository, times(2)).findByUuid(anyString());
        Mockito.verify(repository, times(2 + BEFORE_EACH_UPDATE)).update(any());
    }
}

The registry with UUID "DBFB79A1–5E78–4EA8–9FD1–2B2F4217DACC” will be updated to status 1 before each test. Two threads will be started at the same time, but thread 2 will have a delay of 1 second. The only purpose of this delay is to ensure which thread enters the update method first.

The first thread will be called “STATUS2” and aims to update the registry to status 2, while the second thread will be called “STATUS3” and aims to update the registry to status 3.

The StatusRepository is annotated with @MockitoSpyBean so we can test the amount of times each method was called.

READ COMMITTED

The READ_COMMITTED isolation level prevents dirty reads, it will not allow the selection of uncommitted data. If a transaction modifies a registry, but didn’t commit yet, the other readers won’t be able to select that row until the transaction is committed or rolled back.

Executing this test with this isolation gives us the following output:

Execution of update method with READ_COMMITTED isolation level.

We can see that in that case, both the threads entered the method and selected the registry with status 1. From the point of view of each thread, both are valid updates, since 2 > 1 and 3 > 1. Both threads perform the update and exit the method successfully. In a real scenario, this means that if we get both updates for status 2 and 3 concurrently, there’s a chance that we’re going to end up with status 2 instead of 3, even with the constraint of not allowing to decrease the status, because both threads read 1 as the current status.

If we analyse the generated locks in the database throughout the method, the SELECT statements produces no locks at all. If we put a break point right after the UPDATE statement, but before exiting the method, we capture the following locks in the database:

Generated locks for updating a registry

This shows that when an UPDATE statement is executed, the updated resource gets an exclusive (X) lock. Then, after releasing the break point in the second thread, but still before committing first thread’s transaction, we get the following:

Generated locks for 2 transactions trying to update the same row

The second thread (session_id = 61) generates 3 locks, and the lock for the KEY resource has status “WAIT”, meaning this thread has to wait until the first one finishes the transaction. This is also visible in the sessions table:

Sessions table with second thread’s UPDATE command suspended by first thread.

This view clearly demonstrates that the second thread (session_id = 61) is blocked by the first thread (session_id = 52), and the blocked command in an UPDATE. After releasing the break point on the first thread, the update is committed, allowing the second thread to do the same. Both updates are executed without exceptions. This isolation is not sufficient to ensure that where going to end in status 3, because in a concurrency scenario, the update to status 2 might be executed after the update to status 3.

REPEATABLE READ

Now, let’s see what happens when we use the REPEATABLE_READ isolation level. As the name suggests, it prevents non-repeatable reads, meaning that the result set for the same SELECT statement must be consistent within the transaction.

Let’s see the output of executing the same method, with the REPEATABLE_READ isolation level:

Execution of update method with REPEATABLE_READ isolation level. Stack trace is omitted.

That one is interesting. We can see that both the threads can enter the method and both perform the SELECT statement. Then, thread “STATUS3” got an SQLServerException with this message: “Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

Let’s understand why this happens, taking a closer look at the generated locks after the SELECT statements run:

Generated locks for both the transactions SELECT statements in REPEATABLE_READ isolation.

Different from the READ_COMMITTED isolation, the REPEATABLE_READ isolation generates shared locks after executing SELECT statements. Then, after releasing the break point on the first thread (STATUS2), the update is not performed. The locks table show that the shared locks on the KEY resource are converted to update locks (U) or exclusive locks (X):

Generated locks after running the UPDATE statement, but before committing the transaction.

Note the “request_status” on the X lock being CONVERT. This means the session already has a lock in one mode (S), but it’s requesting to upgrade to another mode. If another session blocks the conversion (another transaction is holding S on the same resource), the request stays in CONVERT until it can succeed or times out.

At this moment, the sessions table shows the following:

Sessions table.

We can see that the first thread (session_id = 52) is blocked by the second sessions’s select (session_id = 61), suspending the UPDATE command, because the second session has a shared (S) lock on the same resource, preventing the first session to convert their Shared lock to exclusive (X).

This means that after both transactions acquired shared locks for their SELECT statements, those registries cannot be updated until the transaction which owns the locks is finished, because otherwise the REPEATABLE_READ isolation constraint wouldn’t be satisfied.

Since thread STATUS2 is waiting on resources, we can release the break point on thread STATUS3, which is also going to get blocked as well, for the same reason, causing a deadlock in the database resources. This deadlock is noticed by the Database Engine and one of the transactions is killed randomly, thus generating the exception.

If we keep querying the locks table during the execution of this test, we can capture the moment the deadlock occurs, right before the Database Engine kills one of the transactions:

Generated locks for 2 transactions trying to update the same registry

For a brief instant, we can see both sessions trying to get exclusive locks on the same key. The sessions table also clearly shows the deadlock situation:

Sessions table showing deadlock occurring.

We can see both sessions blocked by each other, with their corresponding UPDATE statements suspended. As we saw from the execution logs of the method, after one of the transactions is killed by the DB, the remaining transaction succeeds to update the registry.

In a real scenario, this means that if the API processes both updates for status 2 and 3 concurrently, a deadlock might occur, and only of one the updates will succeed, which might not be the correct one, so again, this isolation level is still not sufficient for our goal.

SERIALIZABLE

Now, the most restrictive isolation level, prevents all of the above and also phantom reads, which means that the result set for a SELECT statement cannot be altered. The execution of the update method with SERIALIZABLE isolation outputs the following:

Execution of update method with SERIALIZABLE isolation level. Stack trace is omitted.

Again, the deadlock occurs, which is resolved automatically by the Database Engine. The message is the same as before.

Taking a closer look at the locks generated after the SELECT statements, we see the following:

Generated locks for both transactions using SERIALIZABLE isolation level.

Hundreds of RangeS-S locks are generated. To facilitate, we’re going to analyze the locks using aggregation methods, to see how many locks of each type were generated for each session and resource type, without selecting the locked resource itself (column resource_description).

Generated locks for both transactions using SERIALIZABLE isolation level, aggregated.

In total, 101 locks of type RangeS-S were created for each session, basically generating a lock for each entry in the table (remember the table has 100 entries at this point).

Releasing the break point after the SELECT for thread STATUS2, and pausing after the UPDATE but before exiting the method, some of the locks of the first thread (session_id = 52) are promoted:

Generated locks for UPDATE and SELECT statements using SERIALIZABLE.

A new RangeS-U is generated for the UPDATE statement. Also, a new KEY resource appears with an X lock, with the same CONVERT status seen in the REPEATABLE_READ analysis. The difference here is that the whole table gets a RangeS-S shared lock, instead of just the selected row.

Can you guess why that happens? Remember, the RangeX-Y means a X lock on a range, and a Y lock on a resource. Since SERIALIZABLE needs to prevent phantom reads, and we haven’t created any index for the queried column (uuid), SQL Server can’t predict what range needs to be protected, so the strategy is to protect every possible insert point in the table.

Inspecting the sessions table, we see the same behavior as with REPEATABLE_READ, the UPDATE can’t be executed because first thread (session_id = 52) is blocked by second thread’s (session_id = 61) range locks.

Sessions table after first thread’s UPDATE, before committing.

After releasing the break point for the second thread, the deadlock occurs and is automatically resolved, resulting in the same output of the REPEATABLE_READ test. Even with the strictest isolation level, we still cannot guarantee that the status will end in 3, due to the non-deterministic choice for the session to be chosen as the deadlock victim. Later in the article, we’re going to see how to actually solve this problem.

Scenario 2: selecting a non-existing row before insertion

We’re going to study the possible outcomes of having 2 threads calling the save() method concurrently. The method first selects a registry based on it’s UUID, then inserts a new registry for that UUID if it doesn’t exists yet.

Goal: our goal is to guarantee that only one registry will be inserted for each UUID.

@Transactional(isolation = Isolation.READ_COMMITTED)
public void save(StatusDomain statusDomain) {
    log.info("Thread {} entered save method", currentThread().getName());

    StatusDomain current = repository.findByUuidWithHoldLock(statusDomain.getUuid());
    log.info("Thread {} selected registry {}", currentThread().getName(), current);

    ThreadUtils.sleep(3000);

    if (current == null) {
        Long id = repository.save(statusDomain);
        log.info("Thread {} saved statusDomain and generated id {}", currentThread().getName(), id);
    }

    log.info("Thread {} exited save method", currentThread().getName());
}

Again, the 3 seconds sleep ensures both threads will try to perform the SELECT statement before any of them does the insertion.

The integration test is pretty similar to the first scenario:

@SpringBootTest
class StatusServiceTest {

    @Autowired
    public StatusService service;

    @MockitoSpyBean
    public StatusRepository repository;

    private final StatusDomain status4 = new StatusDomain(null, "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX", 4);

    @BeforeEach
    void setUp() {
        repository.deleteByUuid(status4.getUuid());
    }

    @Test
    void testSave() {
        assertDoesNotThrow(() -> {
            Thread t1 = new Thread(() ->                service.save(status4),   "THREAD1");
            Thread t2 = new Thread(() -> { sleep(1000); service.save(status4);}, "THREAD2");

            t1.start(); t2.start();
            t1.join();  t2.join();
        });

        Mockito.verify(repository, times(2)).findByUuid(anyString());
        Mockito.verify(repository, times(2)).save(any());

        assertEquals(2, repository.findAllByUuid(status4.getUuid()).size());
    }
}

Before each test, the registry with UUID “XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX” will be deleted. The first thread will be called “THREAD1” and the second thread will be called “THREAD2”.

READ COMMITTED

The READ COMMITTED isolation outputs the following:

Execution of save method with READ COMMITTED isolation level.

Both threads execute the SELECT statement concurrently, and none of them the finds the UUID, so both of them inserts the registry, creating a duplicate registry in the database. Since the READ COMMITTED doesn’t create any locks after the SELECT statements, this is the expected behavior. Definitely this isolation level isn’t enough for the goal.

REPEATABLE READ

The execution with REPEATABLE_READ isolation outputs the following:

Execution of save method with REPEATABLE READ isolation level.

Both threads execute the SELECT statement concurrently, and none of them finds the UUID. But, this time, a few shared locks were generated:

Generated locks for SELECT in REPEATABLE_READ isolation level.

Note that differently than Scenario 1, the locks table for the SELECT statements shows Intent Shared (IS) locks only on OBJECT and PAGE resources, while in Scenario 1 we saw the same locks with the additional KEY shared lock (S). Because in this scenario the row was not found, there’s no KEY resource to lock.

After releasing the break point in both threads, the locks table shows the following:

Locks table after UPDATE statements in both transactions, before committing.

A new exclusive (X) lock is generated for each session, representing the newly inserted row. No blocking happens in the sessions table. Proceeding with the execution, both entries are successfully inserted and no exception gets thrown.

Comparing this scenario with the Scenario 1 SELECT + UPDATE, the first one got an SQLServerException, because the data read by one thread was being modified by another transaction, which is not allowed. But here, since the registry doesn’t exists, no exception gets thrown.

SERIALIZABLE

The execution with SERIALIZABLE isolation outputs the following:

Execution of save method with SERIALIZABLE isolation level.

This time, we got the same SQLServerException as before. Only one entry was inserted, ensuring no duplicate insertions, but as we can see from the logs above, both threads executed the SELECT statement and found nothing. Let’s see the locks table for this execution:

Generated locks for both transactions using SERIALIZABLE isolation level, aggregated.

Similarly to Scenario 1, a RangeS-S lock was generated for the whole table, for each thread. The rest of this scenario will evolve in the same way. After releasing the break point for thread 1 (session_id = 61), the INSERT command gets suspended, blocked by thread 2 (session_id = 62) shared locks.

Sessions table.

After releasing thread’s 2 break point, the deadlock occurs. After the Database Engine detects it and kills a session, the surviving session manages to complete the transaction, inserting the new row.

For a brief moment, the deadlock can be seen in the sessions table, having both INSERT commands suspended:

Deadlock during save method with SERIALIZABLE isolation.

So, this scenario evolves literally in the same way as Scenario 1, but since we’re inserting and not updating, the exception thrown happens to prevent the duplicate insertion. The SERIALIZABLE isolation manages to ensure that only one entry will be inserted, achieving our goal, but at the costs of generating lots of locks and also producing a SQLServerException. Let’s see how we can accomplish our goal more efficiently.

Pessimistic Locking

Until now, we saw the behavior of using different isolation levels in our transactional methods, but we kept the SELECT query the same. In all tests, the following lines happened in the same order:

2025-09-08 12:47:08.242 Thread THREAD1 entered save/update method
2025-09-08 12:47:08.278 Thread THREAD1 selected registry XXX
2025-09-08 12:47:09.246 Thread THREAD2 entered save/update method
2025-09-08 12:47:09.253 Thread THREAD2 selected registry XXX

This shows us that even with the most restrictive isolation level, we are never effectively blocking data from being read by another transaction after the first SELECT statement.

To make this possible, we could use a few Table Hints, which provide a mechanism for us to step in the way of the execution plan of the query, and create more specific locks for the selected rows. This is called pessimistic locking.

The following tests will be executed with the READ COMMITTED isolation level. Let’s get back to Scenario 1. To force thread 2 to wait until the first thread completes it’s transaction, we can modify the SELECT statement as follows:

SELECT * FROM control_table WITH(UPDLOCK) WHERE uuid = :uuid

Now, re-executing the update() method from Scenario 1, we get the following:

Execution of update method with READ COMMITTED isolation level and SELECT with UPDLOCK.

This time, we can see that after thread STATUS3 enters the update method, it cannot perform the SELECT. It has to wait until thread STATUS2 finishes the update and commits the transaction, to be able to perform the read.

The hint “UPDLOCK” marks the selected rows with an update (U) lock. This still allow other transaction to query the row for reading, but doesn’t allow to query the row for update. Since the second thread is also performing the same query, with the same hint in the same row, the second thread must wait until first thread’s transaction is completed to perform the selection. The locks table and sessions table can demonstrate this behavior:

Locks table after both SELECT statements with UPDLOCK.

Sessions table after both SELECT statements with UPDLOCK.

This way, we can use the less restrictive READ COMMITTED isolation level, while still ensuring one update at a time, generating less locks on the database then with the REPEATABLE READ/SERIALIZABLE isolation levels, and not throwing any exceptions. It’s also guaranteed that the final status will be 3.

For Scenario 2, however, the same approach will not work. Since the searched value does not exist, it’s impossible to lock it, right? Wrong.

It’s possible to use the following query for the SELECT statement:

SELECT * FROM control_table WITH(UPDLOCK, HOLDLOCK)
WHERE uuid = :uuid

The hint “HOLDLOCK” acts as a synonym for the SERIALIZABLE isolation level, but applies only to the table or view for which it’s specified, and only for the duration of the transaction defined by the statement that it’s used in.

Other transactions can’t insert new rows that would match the statement predicate in the current transaction until the current transaction completes. Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction.

Now, re-executing the save() method from Scenario 2, we get the following:

Execution of save method with READ COMMITTED isolation level and SELECT with UPDLOCK, HOLDLOCK.

This time, we can see that after THREAD2 enters the update method, it cannot perform the SELECT. It has to wait until THREAD1 finishes the transaction to be able to perform the read.

This way, we can ensure only one registry will be inserted without throwing any exceptions. But remember that the HOLDLOCK hint is the same as SERIALIZABLE isolation, which can be costly for the database performance, because it will still generate a considerable amount of locks.

The Database Engine has to be able to determine the range of keys which could possibly match the result of the select statement. So, for the UUID column, which is non-sequential and not indexed, this could mean the same as locking the entire table. Let’s see the locking behavior after the SELECT statements:

Locks table after first SELECT statement with UPDLOCK, HOLDLOCK hints.

As we can see, the first thread generated 101 RangeS-U locks, while the second thread is waiting to perform it’s read. That’s the difference from the previous approach, which generated 101 RangeS-S locks.

The sessions table also evidence the blocking:

Sessions table showing second thread being blocked by first.

The SELECT command of thread 2 (session_id = 62) is blocked by the first SELECT command of thread 1 (session_id = 61). After releasing the break point of the first thread, the INSERT statement is performed.

Locks table after the first INSERT statement.

Another lock was created of type RangeX-X, which is the new entry which was inserted but not committed yet. After releasing again the break point in the first thread, it succeeds to commit, and the second thread is able to perform the SELECT and verify that the registry is not null, therefore not inserting it again.

In conclusion, using both UPDLOCK and HOLDLOCK hints together, we accomplish our goal without having to manage the SQLServerException. Actually, using HOLDLOCK alone is the same as using SERIALIZABLE isolation. What prevents the deadlock is the UPDLOCK hint, as stated in the official documentation.

Indexed Columns and Locking Behavior

Until now, all the tests were made on a table with only 100 entries. Let’s make it 5,000,000. This takes a while, but it’s important to show the difference between having or not a non-clustered index.

Now, the test still works as expected, inserting only once. But, after the SELECT statement, the locks table shows the following:

Generated locks for SELECT with UPDLOCK, HOLDLOCK, without index, on 5M rows table, aggregated.

As we can see, 43,860 PAGE locks were generated in update (U) mode. Now, let’s query the space-usage for this particular table:

Selecting from sys.dm_db_partition_stats on OBJECT_ID(‘control_table’)

The column “in_row_data_pages” represents the amount of pages that contain normal row data. So, in other words, we blocked 100% of the table in U mode. Since it’s a U lock, readers can still read, but any INSERT or UPDATE statement would be blocked, because no other transaction can acquire an U or X lock inside that page.

The Range locks were substituted for PAGE locks because is cheaper for the Database Engine to manage thousands of PAGE locks than millions of range locks. When the table had only 100 rows, the whole table was locked as well, only with a different type of lock (RangeS-U). Without an index, the Database Engine can’t prevent phantom reads without locking the whole table because it doesn’t know where the new KEY would be.

Now, create a non-clustered index:

CREATE NONCLUSTERED INDEX IControlTable_Uuid ON control_table (uuid);

Executing the test again, the locks table shows the following:

Generated locks for SELECT with UPDLOCK, HOLDLOCK, with index on UUID, on 5M rows table.

Now there’s only a single RangeS-U lock for KEY, a single IU and IX locks for PAGE and OBJECT, respectively. Remember that the Range lock doesn’t mean a single lock, it represents the lock mode protecting the range between two consecutive index entries (RangeS) plus the row lock mode protecting the index entry (U). In our case, RangeS-U means that the range between index entries has Shared locks, and the KEY itself has a U lock. In summary, the index enables us to trade locking the whole table in U mode for a single RangeS-U lock, causing way less impact on performance.

Conclusion

Throughout this article, we have seen the influence that different isolation levels, table hints, and indexes have on the different types of locks generated in the database. We also saw how to write an integration test which is capable of asserting the expected behavior for a determined scenario, through the amount of times a method is called.

Errors caused by concurrency can be hard to spot, and sometimes can take a long time to occur, being noticeable only after the system scales enough. There’s lots of possible ways we can mix the different isolation levels, table hints, and is not hard to make code which is not thread-safe in a micro-services distributed system.

The use of table hints such as UPDLOCK can help prevent deadlocks and race conditions and maintain database integrity, while HOLDLOCK can be used when there’s the need to prevent phantom reads. If that’s the case, then a non-clustered index should be used to minimize performance issues.

SQL Server’s locking behavior isn’t determined only by isolation levels, indexes, and hints. It’s also shaped by system and workload factors that developers and DBAs should be aware of. Table size and row counts influence whether SQL Server locks at the row, page, or table level, and lock escalation might trigger when too many locks are acquired on a single object. Memory pressure can make escalation more frequent, while high concurrency and CPU load increase lock contention and deadlocks. Query optimizer choices, triggers, cascading constraints, and partitioning can also expand the scope of locks in ways that aren’t obvious from the query text. Pay attention not only to your queries and indexes, but also to data volume, system resources and database configuration, since all of these can change how locks behave under load.

If you want to try these tests by yourself (which I strongly recommend), the source code is in this Github repository.

References

[embed]Table Hints (Transact-SQL) - SQL Server Table hints override the default behavior of the query optimizer during the DML operation.learn.microsoft.com

[embed]Transaction locking and row versioning guide - SQL Server Transaction locking and row versioning guidelearn.microsoft.com