dP2Jr0TG

1/*
2-- Initialization:
3CREATE DATABASE TestRCSI
4ALTER DATABASE TestRCSI SET ALLOW_SNAPSHOT_ISOLATION ON
5GO
6USE TestRCSI
7CREATE TABLE T (X CHAR(3) PRIMARY KEY)
8*/
9GO
10-- try running this script in two SSMS windows (after executing the initialization above in only one of those windows)
11USE TestRCSI
12GO
13SET TRANSACTION ISOLATION LEVEL SNAPSHOT
14SET XACT_ABORT ON
15SET NOCOUNT ON
16DECLARE @x CHAR(3)
17 
18start:
19SET @x=LEFT(CONVERT(CHAR(36),NEWID()),3)
20 
21BEGIN TRAN
22INSERT INTO T SELECT @x
23WHERE NOT EXISTS (SELECT * FROM T WITH (UPDLOCK) WHERE X=@x)
24-- in the above statement we also need a HOLDLOCK hint, else we will eventually get a violation of the PK
25COMMIT
26
27--IF @@ROWCOUNT=0 DELETE T WHERE x>'A'
28IF @@ROWCOUNT=0 BEGIN
29    BEGIN TRAN
30    IF EXISTS (SELECT * FROM T WITH (TABLOCKX,HOLDLOCK) WHERE X>'A') BEGIN
31        -- we need to check if there are matching rows before deleting, else we will get a deadlock
32        DELETE T WHERE X>'A'
33    END
34    COMMIT
35END
36 
37GOTO start
38
39GO
40/*
41-- Cleanup:
42USE master
43DROP DATABASE TestRCSI
44*/
45
46