SQL Server isolation behavior during count(*) in read commited mode

Question

I am looking for an explanation for the following isolation behavior.

In the SSMS I have a script running that inserts 50000 records within one transaction into a pre emptied table. The whole thing in a loop 100 times. In a separate connection I have permanently executed a select count(*) on the same table. As a result I got in most cases the expected result of 50000. Unfortunately, some queries returned values like 48005, which I can't explain, because all connections were in read committed mode. READ_COMMITTED_SNAPSHOT is not enabled, because actually read committed should isolate sufficiently.

This test was performed on a SQL Server 2019. However, the behavior was the same on a SQL Server 2017.

Kind Regards
Oliver.

Answer 1

The behavior is correct but not intuitive. Only isolation levels SERIALIZABLE, SNAPSHOT, or READ_COMMITTED with the READ_COMMITTED_SNAPSHOT ON database option guarantee the count will match the number of rows inserted by the concurrent transaction after the transaction is committed.

READ COMMITTED and REPEATABLE READ count only committed rows but the devil is in the details. During a scan, a row will be counted only if it was inserted and committed before the scan point is reached. What happens here is the SELECT query reads empty pages during the IAM scan (counting zero rows in the then empty pages) until it gets blocked by an exclusive lock on an uncommitted inserted row. The INSERT session later fills those already read pages with rows and commits, releasing locks and unblocking the SELECT query. The SELECT query proceeds to count committed rows in the remaining pages. However, the rows in the already read (previously empty) pages are not counted.

Here's a modified version of your script can reproduce the issue in a single iteration (at least on my machine) by using a larger transaction.

--session 1
USE tempdb;
DROP DATABASE IF EXISTS Repro;
CREATE DATABASE Repro;
ALTER DATABASE Repro SET RECOVERY SIMPLE;
GO
USE Repro;
CREATE TABLE dbo.T1 (
id int
);
GO

SET NOCOUNT ON;  
DECLARE @x AS int = 0;
BEGIN TRAN;
WHILE @x < 1000000
BEGIN  
INSERT INTO dbo.T1 (id) VALUES(@x);
SET @x += 1;
END;
COMMIT;
GO

--session 2
USE Repro;
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE @RowCount int, @FirstRowReadId int, @LastRowReadId int;
SELECT 
  @RowCount = COUNT(*)
, @FirstRowReadId = MIN(id)
, @LastRowReadId = MAX(id)
FROM dbo.T1;
IF @RowCount NOT IN (0,1000000)
BEGIN
 RAISERROR('Unexpected row count %d', 16, 1, @RowCount);
END;
SELECT
  COUNT(*) AS RowCountInserted
, @RowCount AS RowCountRead
, MIN(id) AS FirstInsertId
, @FirstRowReadId AS FirstRowReadId
, MAX(id) AS LastInsertId
, @LastRowReadId AS LastRowReadId
FROM dbo.T1;
USE tempdb;
GO

Answer 2

Hi @Oliver Trendelkamp ,

Welcome to Microsoft Q&A!

You could try to set XACT_ABORT ON before the transaction.When SET XACT_ABORT is ON and T-SQL statement raises a run-time error, SQL Server automatically rolls back the current transaction.

Running in SSMS:

Create table T1 (id int)  
 go  
      
 truncate table T1  
 declare @x as int  
 set @x=0  

 SET NOCOUNT ON  
 SET XACT_ABORT ON  

 begin transaction  
 WHILE (@x<50000 )  
 BEGIN    
     insert into T1 select @x  
     set @x=@x+1  
 END    
 commit transaction  
 go 100  
      
 drop table T1  
 go  

I tested above updated one and I could always get the result of count(*) as 50000 in the sqlcmd.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.