การแยกธุรกรรมพร้อมกัน - เหตุใดฉันจึงสามารถอัปเดตชุดย่อยของบันทึกธุรกรรมอื่นได้

ฉันกำลังพยายามทำความเข้าใจปัญหาที่ฉันพบซึ่งฉันไม่เชื่อว่าควรจะเป็นไปได้เมื่อต้องจัดการกับธุรกรรมโดยใช้ระดับการแยกการอ่านที่คอมมิต ฉันมีโต๊ะที่ใช้เป็นคิว ในหนึ่งเธรด (การเชื่อมต่อ 1) ฉันแทรกชุดข้อมูล 20 รายการหลายชุดลงในแต่ละตาราง แต่ละชุดข้อมูล 20 รายการจะดำเนินการภายในธุรกรรม ในเธรดที่สอง (การเชื่อมต่อ 2) ฉันทำการอัพเดตเพื่อเปลี่ยนสถานะของเรคคอร์ดที่ถูกแทรกลงในคิว ซึ่งเกิดขึ้นภายในธุรกรรมด้วย เมื่อทำงานพร้อมกัน ฉันคาดหวังว่าจำนวนแถวที่ได้รับผลกระทบจากการอัปเดต (การเชื่อมต่อ 2) ควรเป็นผลคูณของ 20 เนื่องจากการเชื่อมต่อ 1 กำลังแทรกแถวในตารางที่แทรกเป็นชุด 20 แถวภายในธุรกรรม

แต่การทดสอบของฉันแสดงให้เห็นว่าไม่ได้เป็นเช่นนั้นเสมอไป และในบางครั้ง ฉันสามารถอัปเดตชุดย่อยของบันทึกจากชุดของการเชื่อมต่อ 1 ได้ สิ่งนี้ควรเป็นไปได้หรือฉันพลาดบางอย่างเกี่ยวกับธุรกรรม การทำงานพร้อมกัน และระดับการแยกออกจากกัน ด้านล่างนี้คือชุดสคริปต์ทดสอบที่ฉันสร้างขึ้นเพื่อจำลองปัญหานี้ใน T-SQL

สคริปต์นี้จะแทรก 20,000 บันทึกลงในตารางในชุดธุรกรรม 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'

สคริปต์นี้อัปเดตเรกคอร์ดในตาราง เปลี่ยนสถานะจาก 1 เป็น 2 จากนั้นตรวจสอบจำนวนแถวจากการดำเนินการอัปเดต เมื่อจำนวนแถวไม่เป็นจำนวนทวีคูณของ 20 และคำสั่งการพิมพ์จะระบุสิ่งนี้และจำนวนแถวที่ได้รับผลกระทบ

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'

สคริปต์นี้สร้างตารางคิวการทดสอบในฐานข้อมูลใหม่ที่เรียกว่า 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