'Duplicate keys in PK, in Read Committed Snapshot Isolation Level
Read Committed Snapshot Isolation Level,
Create Table Tmp(ID Int Primary Key);
In one session (query in the SSMS):
Begin Tran
Insert Into Tmp(ID) Select 1 Where Not Exists (Select * From Tmp Where ID=1);
The code runs successfully and waits for either Commit or RollBack. In another session the same code:
Begin Tran
Insert Into Tmp(ID) Select 1 Where Not Exists (Select * From Tmp Where ID=1);
The code runs and remains locked, waiting for the first session to release the table.
In the first session I run
Commit
It finishes successfully, but the second session fails despite the "Where Not Exists" predicate:
Violation of Primary Key Constraint ..
How can I avoid it and finish the second one successfully (without inserting any row), or to avoid in advance the second transaction?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
