Effect of READ_COMMITTED_SNAPSHOT for Delete Statement w/ Subquery

Question

Good day,

We recently encountered a race condition issue in SQL Server and we would like to ask if the behaviour is expected when using READ_COMMITTED_SNAPSHOT. Please see the details below.

SQL Server Options:

1. Transaction isolation level = READ COMMITTED
2. READ_COMMITTED_SNAPSHOT = ON
3. ALLOW_SNAPSHOT_ISOLATION = OFF

Transaction A:

1. insert into TABLE_A
2. insert into TABLE_B
3. commit

Transaction B:

1. delete from TABLE_B where COLUMN not in (select COLUMN from TABLE_A);
2. commit

Findings:

1. Even when using transactions, newly inserted records in TABLE_B by Transaction A got deleted by Transaction B. Seems the "select" and "delete" statements have different reference snapshots.
2. When disabling READ_COMMITTED_SNAPSHOT, we couldn't encounter this issue.

Questions:

1. Is it expected that SQL Server does not maintain "statement-level read consistency" for SQL statements with subquery?
2. If expected, what's your suggested approach in handling this?

Thank you!

Regards,
Ed

Answer 1

I was able to reproduce the issue (after some smaller modification to the scripts. The first script lacks BEGIN TRANSACTION, the other has a COMMIT too many).

This is my analysis of what is happening. In my test the plan for the DELETE was a MERGE JOIN of CI scans on the two tables. TABLE_A is accessed from the version store the normal way. For TABLE_B on the other hand, SQL Server wants an UPDATE lock. An UPDATE lock is a read lock which can only be held by one process. This sort of lock is taken when SQL Server is about to update the resource. If the update actually happens, the U lock is converted to an X lock.

If the INSERT transaction is in progress, this means that the scan over table A can still be carried out, since it runs over the snapshot. But the scan over table B is blocked by the transaction. The net result is that the result of the two scans are inconsistent.

I know of other anomalies that can occur with RSCI. Say for instance that a the procedure to deregister a product checks that there are no open orders for the product, and the procedure to add an order checks that all products are active. If they execute simultaneously, the business rules can be validated, because both procedures are reading data that is in fact stale. This is one I have not seen before or thought about.

There are at least two ways to skin the cat in your case. One is to use true snapshot isolation for the delete operation. When I tested this, no rows were deleted. The other solution is to use the hint READCOMMITTEDLOCK:

    delete
     from TABLE_B
    where SOME_PK not in (select SOME_PK
                            from TABLE_A WITH (READCOMMITTEDLOCK));

Here you are telling SQL Server that you don't want to use the snapshot in this case.

Answer 2

There is something hiding here which does not exhibit in your outline. Would it be possible for you to create a repro that we can play with?

Answer 3

Thank you for the prompt responses, @Sean Gallardy - MSFT , @Erland Sommarskog !

From Sean:
[...] I'm not sure the initial reason for using RCSI so it's hard to say if anything else would work within whatever other goals you have that aren't listed might be.

We wanted to mimic the default behaviour of Oracle RDBMS for Microsoft SQL Server.

From Erland
[...] Would it be possible for you to create a repro that we can play with?

We used the scripts below for replicating the issue.

/*  
 * Transaction isolation level = READ COMMITTED  
 * READ_COMMITTED_SNAPSHOT = ON  
 * ALLOW_SNAPSHOT_ISOLATION = OFF  
 */  
  
/*  
 * Connection 1  
 */  
declare  
  @cnt numeric(10),  
  @somePk nvarchar(36),  
  @i numeric(10);  
begin  
set nocount off  
  set @i = 0;  
  while (@i < 100)  
    begin  
	  set @i = @i + 1;  
	  set @somePk = cast (@i as varchar(10));  
      
	  insert into TABLE_A(SOME_PK) values(@somePk);  
        
	  waitfor delay '00:00:00.300';  
  
	  insert into TABLE_B(SOME_PK) values (@somePk);  
  
	  commit;  
    end;  
end;  
  
/*  
 * Connection 2  
 */  
declare  
  @i numeric(10),  
  @cnt numeric(10) = 0,  
  @cntN numeric(10);  
begin  
  set nocount off  
  
  set @i = 0;  
  while (@i < 500)  
    begin  
      set @i = @i + 1;  
	    
	  waitfor delay '00:00:00.100';  
	    
      delete  
        from TABLE_B  
       where SOME_PK not in (select SOME_PK  
                               from TABLE_A);  
  
	  select @cnt = @@ROWCOUNT  
  
	  select @cntN = count(1)  
        from TABLE_B;  
  
      print concat('Count deleted:', @cnt, ' tbc:', @cntN);  
	    
	  -- Problem: Unexpected TABLE_B records got deleted.  
	  -- Suspicion: Connection 1 committed in between the "delete" and "select".   
	  --            Then, SQL Server used a newer snapshot for the delete which resulted to deleting unexpected TABLE_B records.  
	  -- Test: We used this script and it printed 12 deleted records out of 100.  
        
	 commit  
    end;  
end;  

Answer 4

Is it expected that SQL Server does not maintain "statement-level read consistency" for SQL statements with subquery?

The statement level read consistency is maintained. Once you insert into Table B from your other transaction, that statement is complete and thus can be seen by other sessions in RCSI. The statement level is fine, you want transaction level, which would mean not using RCSI. If you check out the Docs, you'll se this is stated:

That is, the SQL Server Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement.

Thus, so long as the data was there at the start of the delete statement, it will be seen. The delete... select.. is a single query, it does not make two different row version start times.

If expected, what's your suggested approach in handling this?

It seems you don't want anything to see or touch the changes made in Transaction A, you could either use the default read committed where you'll have blocking as the natural course of action for the delete (depending on where and when), or snapshot isolation which would give you transaction level row versioning but bring in potential write conflicts. I'm not sure the initial reason for using RCSI so it's hard to say if anything else would work within whatever other goals you have that aren't listed might be.