How Serializable works with insert in SQL Server 2005
- by Spence
G'day
I think I have a misunderstanding of serializable. I have two tables (data, transaction) which I insert information into in a serializable transaction (either they are both in, or both out, but not in limbo).
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
INSERT INTO dbo.data (ID, data) VALUES (@Id, data)
INSERT INTO dbo.transactions(ID, info) VALUES (@ID, @info)
COMMIT TRANSACTION
I have a reconcile query which checks the data table for entries where there is no transaction at read committed isolation level.
INSERT INTO reconciles (ReconcileID, DataID)
SELECT Reconcile = @ReconcileID, ID FROM Data
WHERE NOT EXISTS (SELECT 1 FROM TRANSACTIONS WHERE data.id = transactions.id)
Note that the ID is actually a composite (2 column) key, so I can't use a NOT IN operator
My understanding was that the second query would exclude any values written into data without their transaction as this insert was happening at serializable and the read was occurring at read committed. I have evidence that reconcile is picking up entries
