Transaction Concurrency Isolation - Why can I update a subset of another transactions records?

Question

I'm trying to understand a problem I have run into that I don't believe should be possible when dealing with transactions utilizing the read committed isolation level. I have a table that is being used as a queue. In one thread (connection 1) I insert multiple batches of 20 records into each table. Each batch of 20 records is performed inside a transaction. In a second thread (connection 2) I perform an update to change the status of the records that have been inserted into the queue, which also occurs inside a transaction. When running concurrently, it is my expectation that the number of rows affected by the update (connection 2) should be a multiple of 20, since connection 1 is inserting rows in the table inserts in batches of 20 rows within a transaction.

But my testing shows this is not always the case, and on occasion I'm able to update a subset of records from connection 1's batch. Should this be possible or am I missing something about transactions, concurrency, and isolation levels? Below is a set of test scripts I created to reproduce this issue in T-SQL.

This script inserts 20,000 records into the table in transaction batches of 20.

USE ReadTest
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

SET NOCOUNT ON

DECLARE @trans_id INTEGER
DECLARE @cmd_id INTEGER
DECLARE @text_str VARCHAR(4000)

SET @trans_id = 0
SET @text_str = 'Placeholder String Value'                

-- First empty the table
DELETE FROM TABLE_A

WHILE @trans_id < 1000 BEGIN
    SET @trans_id = @trans_id + 1
    SET @cmd_id = 0

    BEGIN TRANSACTION
--  Insert 20 records into the table per transaction
    WHILE @cmd_id < 20 BEGIN
        SET @cmd_id = @cmd_id + 1

        INSERT INTO TABLE_A ( transaction_id, command_id, [type], status, text_field ) 
            VALUES ( @trans_id, @cmd_id, 1, 1,  @text_str )
    END             
    COMMIT

END

PRINT 'DONE'

This script updates the records in the table, changing the status from 1 to 2 and then checks the rowcount from the update operation. When the rowcount is not a multiple of 20, and print statement indicates this and the number of rows affected.

USE ReadTest
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

SET NOCOUNT ON
DECLARE @loop_counter INTEGER
DECLARE @trans_id INTEGER
DECLARE @count INTEGER

SET @loop_counter = 0

WHILE @loop_counter < 100000 BEGIN

    SET @loop_counter = @loop_counter + 1
    BEGIN TRANSACTION
        UPDATE TABLE_A SET status = 2 
        WHERE status = 1
            and type = 1
        SET @count = @@ROWCOUNT
    COMMIT

    IF ( @count % 20 <> 0 ) BEGIN
--      Records in concurrent transaction inserting in batches of 20 records before commit.
        PRINT '*** Rowcount not a multiple of 20. Count = ' + CAST(@count AS VARCHAR) + ' ***'
    END

    IF @count > 0 BEGIN
--      Delete the records where the status was changed.
        DELETE TABLE_A WHERE status = 2
    END
END

PRINT 'DONE'

This script creates the test queue table in a new database called ReadTest.

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'ReadTest')
  BEGIN;
  DROP DATABASE ReadTest;
  END;
GO

CREATE DATABASE ReadTest;
GO

ALTER DATABASE ReadTest
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE ReadTest
SET READ_COMMITTED_SNAPSHOT OFF
GO

USE ReadTest
GO

CREATE TABLE [dbo].[TABLE_A](
    [ROWGUIDE] [uniqueidentifier] NOT NULL,
    [TRANSACTION_ID] [int] NOT NULL,
    [COMMAND_ID] [int] NOT NULL,
    [TYPE] [int] NOT NULL,
    [STATUS] [int] NOT NULL,
    [TEXT_FIELD] [varchar](4000) NULL
 CONSTRAINT [PK_TABLE_A] PRIMARY KEY NONCLUSTERED 
(
    [ROWGUIDE] ASC
) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[TABLE_A] ADD  DEFAULT (newsequentialid()) FOR [ROWGUIDE]
GO

Answer 1

You expectations are completely misplaced. You have never expressed in your query the requirement to 'dequeue' exactly 20 rows. The UPDATE can return 0, 19, 20, 21 or 1000 rows and all results are correct, as long as the status is 1 and type is 1. If you expect that the 'dequeue' occurs in the order of the 'enqueue' (which is somehow eluded to in your question, but never explicitly stated) then your 'dequeue' operation must contain an ORDER BY clause. Had you add such an explicitly stated requirement then your expectation that 'dequeue' always return an entire batch of 'enqueue' rows (ie. multiple of 20 rows) would be one step closer to being a reasonable expectation. As things stand right now, is, as I said, completely misplaced.

For a lengthier discussion see Using Tables as Queues.

I shouldn't be concerned that while one transaction is committing a batch of 20 inserted records, another concurrent transaction is only able to update a subset of those records and not all 20?

Basically the question boils down to If I SELECT while I INSERT, how many inserted rows will I see?. You only have a right to be concerned if the isolation level is declared as SERIALIZABLE. None of the other isolation levels make any prediction about how many rows inserted while the UPDATE was running will be visible. Only SERIALIZABLE states that the outcome has to be the same as running the two statements one after another (ie. serialized, hence the name). While the technical details of how the UPDATE 'sees' only part of the INSERT batch are easy to understand once you consider physical order and the lack of ORDER BY clause, the explanation is irrelevant. The fundamental issue is that the expectation is non-warranted. Even if the 'issue' is 'fixed' by adding a proper ORDER BY and the correct clustered index key (the article linked above explains the details), the expectation is still non-warranted. It will still be perfectly legal for the UPDATE to 'see' 1, 19 or 21 rows, although it will be unlikely to happen.

I guess I've always understood READ COMMITTED to only read committed data, and that a transaction commit is an atomic operation, making all the changes that occurred in the transaction available at once.

That is correct. What is incorrect is to expect that a concurrent SELECT (or update) to see the entire change, irrelevant of where it happens to be in the execution. Open an SSMS query and run the following:

use tempdb;
go

create table test (a int not null primary key, b int);
go

insert into test (a, b) values (5,0)
go

begin transaction
insert into test (a, b) values (10,0)

Now open a new SSMS query and run the following:

update test 
    set b=1
    output inserted.*
    where b=0

This will block behind the uncommitted INSERT. Now go back to first query and run the following:

insert into test (a, b) values (1,0)
commit

When this commits, the second SSMS query will finish, and it will return two rows, not three. QED. This is READ COMMITTED. What you expect is SERIALIZABLE execution (in which case the example above will deadlock).

Answer 2

It could happen like this:

1. The writer/inserter writes 20 rows (does not commit)
2. The reader/updater reads one row (which is not committed - it discards it)
3. The writer/inserter commits
4. The reader/updater reads 19 rows which are now committed thus visible

I believe that only an isolation level of serializable (or snapshot isolation which is more concurrent) fixes this.