Can an INSERT statement block SELECT ?

Sam 1,371 Reputation points
2023-02-05T12:05:08.78+00:00

Hi All,

Can an INSERT statement block SELECT statements? not sure why ? Can anyone help me understand in what scenarios it may happen?

Thanks,

Sam

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,811 questions
Accepted answer
  1. Erland Sommarskog 101.8K Reputation points MVP
    2023-02-05T13:19:38.6233333+00:00

    Adding to Dan's answer, you can get blocking even if there are good indexing in place. Say that there is an INSERT statement which inserts a row where City = 'Paris'. This statement is left uncommitted for whatever reason.

    At the same time, there is a SELECT query that wants to retrieve data where City = 'Paris', possibly in combination with other parameters. There is an index on the City column. This SELECT query will be blocked since there is an uncommitted row which the query would like to read.

    This assumes the default isolation level READ COMMITTED, and the default variation of the isolation level. If you use the isolation level READ UNCOMMITTED, there will not be any blocking, and in this case, the result will include the uncommitted row. Nor will there be any blocking if you have enabled READ_COMMITTED_SNAPSHOT for the database, since in this case the SELECT query will read from the snapshot, and the result will not include the uncommitted row. In both cases, the result could, depending on requirements, be considered to be incorrect.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dan Guzman 9,211 Reputation points
    2023-02-05T12:48:33.04+00:00

    An insert statement can block select queries in cases where the select query tries to read an uncommitted data. For example, a simple SELECT COUNT(*) FROM dbo.SomeTable; query will perform a full scan of a table/index and acquire/release shared locks during the scan (assuming pessimistic locking). If newly inserted but uncommitted rows are encountered (or uncommitted updates/deletes), the select query wait until the changes are committed. This includes newly inserted rows that happen to be at the end of the scan.

    Lack of query/index tuning is the most common scenario where readers block writers because more rows than needed are read. Useful indexes allow select queries to touch only the rows needed and avoid touching others, reducing the likelihood of long-term blocking.

    1 person found this answer helpful.
  2. Sam 1,371 Reputation points
    2023-02-07T07:21:35.46+00:00

    Thanks everyone for the explanation.But one thing bothering me.

    Considering the below example, I am in read committed isolation level and assuming that my table has 100 rows inside it.

    now, comes 2 spids. spid = 55 , started a trasanction and does 200 new INSERT's and the txn is still open. In the meanwhile, another spid = 56, tries to do a SELECT on the table. In this case, I would expect sql to return 100 rows which are committed and since other txn is still active. it doesnt show up the newly added 200 rows. in this case, does the SELECT gets blocked or passes through?