How Serializable works with insert in SQL Server 2005

Question

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.

So what I have seen is that the Reconcile query has picked up data entries that are in the data table but not in the transactions table when entered with this query, which I thought wasn't possible due to the isolation level.

Answer 1

All transaction isolation levels refer exclusively to reads. There is no 'serializable' insert, just as there is no 'read committed' insert. Writes are never affected by the serialization level. Wrapping two inserts into a serialization level, any level, is a no-op since the inserts will behave identically under all isolation levels.

The second query, the INSERT ... SELECT ... on the other hand, by containing a read (the SELECT), is affected by isolation level. The SELECT part will behave according to the current isolation level (in this case, read committed).

Updated Writes in a transaction are visible outside the transaction only after the commit. If you have a sequence begin transaction; insert into A; insert into B; commit then a reader that is at least at read committed isolation will not see the insert into A before the insert into B. If your reconcile query sees the partial transaction (ie. sees the insert into A w/o a corresponding insert into B) then you have some possible explanations:

• the reconcile query is running at the wrong isolation level and does dirty reads
• the application did commit the two inserts separately
• a code defect in the application that results in inserting only into A

The most likely explanation is the last one.