Can an INSERT statement block SELECT statements?

• vsamantha35

  SSChampion

  Points: 11551

  More actions

  February 14, 2023 at 7:48 pm

  #4149706

  Thom A wrote:

  Just to prove Grant's statement a little more, here's a quick demo.

  

  So you can see that the first SELECT, which wants rows on or after the current date, is not blocked, due to there being an INDEX on SomeDate (which INCLUDEs all the columns the query needs), so the data engine is able to get all the rows without accessing the blocked data.

  For the second INSERT, however, we're now inserting a row after the current date, and when I run the SELECT again, it now can't complete, due to the INSERT blocking the SELECT. As soon as that INSERT is COMMITed, however, the SELECT is able to be completed.

  An INSERT can block a SELECT, but if they do depends on both what data is being INSERTed, what data is being SELECTed, and what INDEXes the data engine has access to. There's not as simple as a "yes it (always) will" or "No it won't (ever) be".

  SQL from the animated gif:

  --First Connection's batches
  CREATE TABLE dbo.SomeTable (ID int IDENTITY(1,1) CONSTRAINT PK_SomeTable PRIMARY KEY,
                              SomeString varchar(10),
                              SomeDate date);
  GO
  
  INSERT INTO dbo.SomeTable (SomeString, SomeDate)
  VALUES('abcd',GETDATE()),
        ('efgh',DATEADD(DAY,-2,GETDATE()));
  
  
  GO
  --Use a transaction to help simulate "simultaneous" statements
  BEGIN TRANSACTION;
  
  INSERT INTO dbo.SomeTable (SomeString, SomeDate)
  VALUES('ijkl',DATEADD(DAY,-1,GETDATE()));
  
  COMMIT;
  GO
  
  --Use a transaction to help simulate "simultaneous" statements
  BEGIN TRANSACTION;
  INSERT INTO dbo.SomeTable (SomeString, SomeDate)
  VALUES('mnop',DATEADD(DAY,+1,GETDATE()));
  
  COMMIT;
  
  GO
  DROP TABLE dbo.SomeTable;
  GO
  
  ---------
  --Second connection's batch
  
  SELECT *
  FROM dbo.SomeTable
  WHERE SomeDate >= CONVERT(date,GETDATE());
  
  

  This is really nice. Very cool stuff. Thanks a lot for taking time Thom.