The long running select statement blocked the insert/update statement

Question

The long running select statement blocked the insert/update statement

Anonymous

The long running select statement blocked the insert/update statement execution

Scenario:

We have the multi-thread batch insertion logic to insert data to MSSQL database.
When the insertion logic was executed, some users execute a long running select statement which both scripts are accessing the 'orders' table.
After select statement executed with successful result (over 45 mins), the insert statement session will be proceed.
Result: The insertion logic over 50 mins to insert data (almost 90% waiting time)

Solution:

To set the transaction isolation level to Read uncommitted to prevent 90% waiting time
script: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

We would like to know the impact/action:

After executed "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;" (execution time, execution right, CPU/Memory usage and select statement result)
The rollback process

0 comments

Answer recommended by moderator

Erland Sommarskog 137.5K MVP Volunteer Moderator

Yes, you can prevent blocking from long-running queries by using any form of snapshot as I discussed in my post. READ UNCOMMITTED is not a solution, it's an accident waiting to happen. And error 601 is the least of your problems in that case, because then the query failed, rather than producing incorrect results. And, of course, as Marcin discusses, tuning the long-running query can never be a bad idea, although it may be difficult to get it run that fast that blocking is not a problem any more.

0 comments

2 additional answers

Your answer

Answer 1

DO NOT DO THIS! ABSOLUTELY NOT!

Most likely this will result in the SELECT statements occasionally returning incorrect results, which users may or may not notice. If they notice, you will get bug reports you cannot reproduce. If they do not notice, they may make incorrect business decisions based on the incorrect data.

When you use READ UNCOMMITTED, several bad things can happen:

You read uncommitted and inconsistent data.
You fail to read existing data, because it was moved while tables were being scanned.
You may read the same data twice, again due to data movement.
The query may fail with error 601.

The correct solution to this type of blocking problem is to apply some form of snapshot solution. There are two ways to go.

ALTER DATABASE db SET READ_COMMITTED_SNAPSHOT ON. With this setting, all reads will be from a snapshot which reflects how the database looked like when the query started. This setting is on by default in Azure SQL Database and is generally recommended. However, you will be reading stale data, and in some situations this can cause incorrect results, for instance with validation. This can be overridden with the READCOMMITEDLOCK hint, but you need to change the code.
ALTER DATABASE db SET ALLOW_SNAPSHOT_ISOLATION ON. With this setting, code that want to use the snapshot needs to opt in with SET TRANSACTION ISOLATION LEVEL SNAPSHOT. With this setting, queries sees the database as when then transaction started.

Both these settings activates the version store, which lives in tempdb (unless you have activated accelerated database recovery), which means that tempdb will need more space, and overall the load on tempdb will increase. It also adds an overhead to UPDATE and DELETE operations (not so much to INSERT). This overhead is usually acceptable.

READ UNCOMMITTED a.k.a. NOLOCK can be useful for diagnostic queries, but it rarely has any place in application code.

Anonymous

2026-05-12T03:28:55.0766667+00:00

Hello Erland Sommarskog, Can share more about the error 601details? When the select statement added a (nolock) hints, Can we prevent the long running query blocking issue?
Anonymous

2026-05-13T07:28:58.6+00:00

Hi @sam chung ,

Thanks, Erland, for the detailed clarification on NOLOCK behavior and snapshot isolation recommendations.

@sam chung , please review the guidance shared above and test the recommended approach in a non-production environment based on your workload requirements.

Thanks,
Akhil.
Anonymous

2026-05-26T05:06:42.1033333+00:00

Hi @Anonymous ,

Just following up to check whether you had a chance to review and test the recommendations shared by Erland regarding NOLOCK behavior and snapshot isolation.

Please let us know if the suggested approach helped in your scenario or if you are still observing any blocking/consistency issues after testing.

Thanks,
Akhil.

Answer 2

AFAIK, under the default READ COMMITTED isolation level, a long-running SELECT keeps shared locks while reading data. Your INSERT and UPDATE statements require exclusive locks, so they wait until the SELECT finishes.

When you execute:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

the session can read data without waiting for normal shared locks. This reduces blocking and allows concurrent inserts and updates to continue.

The impact is mainly on data consistency. READ UNCOMMITTED allows dirty reads. A query may read uncommitted data that is later rolled back. The query can also return inconsistent or incorrect results during concurrent modifications.

For example:

BEGIN TRAN;

UPDATE orders
SET amount = 1000
WHERE order_id = 1;

-- Another session using READ UNCOMMITTED reads amount = 1000

ROLLBACK;

The second session reads a value that never became permanent.

Execution time usually improves because sessions spend less time waiting on locks. Your inserts can continue while the long-running select is executing.

CPU and memory usage are normally not significantly increased by READ UNCOMMITTED. The isolation level itself is lightweight.

Rollback behavior does not change. Transactions still rollback normally. The only difference is that another session may already have read the uncommitted data before the rollback occurs.

No special execution rights should typically be required beyond normal query permissions.

A safer alternative would be enabling row versioning with:

ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON;

This should prevent reader/writer blocking without allowing dirty reads. Readers access versioned rows from tempdb instead of waiting for locks.

You should also investigate why the SELECT runs for 45 minutes. The root cause might be missing indexes, table scans, outdated statistics, inefficient joins, or poor execution plans.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin