Consider this table and two concurrent queries:
create table BLARG (a int not null unique);
Connection #1:
set transaction isolation level snapshot;
begin transaction;
if exists (select 1 from BLARG with (serializable) where a=1) print 'DIE!';
Connection #2:
set transaction isolation level snapshot;
begin transaction;
insert into BLARG values (2); -- Blocked!
Why does inserting a=2 block a serializable read looking for a=1?
In snapshot isolation, is there another way to prevent insertion into a table?
...
In case I've overgeneralized or am not asking a precisely correct question, here's my (slightly less generalized but still generalized) scenario:
Tables [Parts] and [Completed] each have column "Txid"
For any Txid, inserts to [Parts] can be done until a [Completed] record is created, enforced by a [Parts] insert trigger along the lines of "if exists (select 1 from Completed where Txid=...) throw".
Inserting to [Completed] causes the [Parts] table to be summarized and results go somewhere else
All transactions begin in Snapshot isolation
If 2 transactions begin, then one inserts to [Completed] and one inserts to [Parts], the write skew would make the [Completed] trigger miss the insert to [Parts] which happened in the concurrent transaction.
But [Completed] is a very busy table and would rather not use the Serializable isolation if it's going to prevent ALL inserts, when only an insert with a matching Txid is problematic.
Is there a more optimum solution?
