Select query with read uncommitted is causing the blocks

Question

There is a long stored procedure of select queries. The transaction is set to read uncommitted. The blocking report viewed in monitoring tool shows that this stored procedure is causing the block.
Can select queries with read uncommitted block other processes?

Answer 1

Can select queries with read uncommitted block other processes?

Select queries in Read uncommitted isolation level will not take shared locks BUT takes Sch-S schema stability lock which is not compatible with any query which is trying to take schema level locks like Index rebuild, alter table. Such queries will be blocked by the select query you are running. Please see lock compatibility matrix. Its really a poor choice to use read uncommitted isolation level. Please read below to understand the problems you can get while using this isolation level.

the-read-uncommitted-isolation-level

Answer 2

Hi @Surendra Adhikari ,

READ UNCOMMITTED transactions will not take any database locks, but will still have to read databases pages to read the actual data. If other transactions are writing these pages at the same time, there might be some blocking between the two. Internally in the engine, we do not allow any transactions to read a page while a write is being in progress (we use latches to guarantee this). If a lot of transactions are writing while your big queries are going on, the big read might still become blocked.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet