INSERT is blocking UPDATE

Viewed 305

I am trying to understand why uncommitted inserts for a table are blocking update operations. For this I created a sample database with "READ_COMMITED_SNAPSHOT = ON" and "ALLOW_SNAPSHOT_ISOLATION = ON".

Test case is like this.

  1. Preparation
    CREATE TABLE [dbo].[Table_1](
        [id] [int] NOT NULL,
        [group] [int] NOT NULL,
        [first name] [nchar](20) NOT NULL,
        [last name] [nchar](20) NULL
    ) ON [PRIMARY]
    GO
    
    CREATE CLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1] ([id] ASC)WITH ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, ON [PRIMARY]
    GO
    
    INSERT INTO Table_1 VALUES (1, 1, 'Joe', 'Joee'), (2, 1, 'Foo', 'Bar'), (3, 2, 'Hank', 'Hanko')
    GO
  1. In session 1 run an insert and do NOT commit
    begin TRAN;
    INSERT INTO Table_1 VALUES (4, 1, 'Hans', 'Hansen')
  1. In session 2 using "read committed snapshop" isolation level run an update
    begin tran;
    UPDATE Table_1 SET [last name]='no name' WHERE [group]=1

Result: Session 2 will be lock waiting for a key lock until session 1 is committing the transaction.

key lock

Rows versioned by Session 1 are not conflicting with Rows versioned by Session 2. After reading https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189050(v=sql.105) I assume only modified rows should be versioned and blocking other update/exclusive lock requests.

WORKAROUND: using isolation level "Snapshot" for Session 2

EDIT 1: I missed to mention that I also tried with a Non-Clustered Index for the "group" column.

CREATE NONCLUSTERED INDEX [group_index] ON [dbo].[Table_1] ( [group] ASC ) WITH ( ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]

The situation is unchanged: enter image description here

EDIT 2: Same scenario on oracle works as I expect it to work. The update does only process the rows that are visible for him since the rows from the insert are not committed yet. Identical Oracle scenario:

CREATE TABLE Table_1 (p_id int NOT NULL, p_group int NOT NULL, first_name char(20) NOT NULL, last_name char(20) NULL);
INSERT INTO Table_1 VALUES (1, 1, 'Joe', 'Joee');
INSERT INTO Table_1 VALUES (2, 1, 'Foo', 'Bar');
INSERT INTO Table_1 VALUES (3, 2, 'Hank', 'Hanko');

Transaction 1 - do not commit!

INSERT INTO Table_1 VALUES (4, 1, 'Hans', 'Hansen')

Transaction 2 (read committed)

UPDATE Table_1 SET last_name='No Name' WHERE p_group=1;

-> 2 rows updated.

0 Answers
Related