Update after insert is locked on primary

[Schorsch358]

Hi all,

we encounter a locking situation that we don't expect.

I you start a transaction and insert a record in session 1, start a transaction and insert another record in session 2, then update the just inserted record in session 1, it waits for commit or rollback of session 2.

How to reproduce:

Preparation

CREATE TABLE [dbo].[LOCKTEST](
       [DSID] [int] NOT NULL,
       [ID1] [int] NOT NULL
CONSTRAINT [PK_LOCKTEST] PRIMARY KEY CLUSTERED 
(
       [DSID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LOCKTEST] SET (LOCK_ESCALATION = DISABLE)
GO

Session 1

BEGIN TRANSACTION
INSERT INTO LOCKTEST (DSID, ID1) VALUES (1,2)
go

Session 2

begin transaction
INSERT INTO LOCKTEST (DSID, ID1) VALUES (2,1)
Go

Session 1

update LOCKTEST set ID1 = 3
where id1=2 
go

After this, Session 1 (here ID 66) waits for a lock on PK_LOCKTEST:

lock wait

Can someone explain what is this related to?

Regards

Georg

PS:

If we change the update to

Update LOCKTEST set ID1 = 3
Where dsid = 1
Go

then there is no lock wait. But this is by design not possible within our application.