Story

I have a SPROC using Snapshot Isolation to perform several inserts via MERGE. This SPROC is called with very high load and often in parallel so it occasionally throws an Error 3960- which indicates the snapshot rolled back because of change conflicts. This is expected because of the high concurrency.

Problem

I've implemented a "retry" queue to perform this work again later on, but I am having difficulty reproducing the error to verify my checks are accurate.

Question

How can I reproduce a snapshot failure (3960, specifically) to verify my retry logic is working?

Already Tried

• RAISEERROR doesn't work because it doesn't allow me to raise existing errors, only user defined ones
• I've tried re-inserted the same record, but this doesn't throw the same failure since it's not two different transactions "racing" another

• sql
• sql-server
• sql-server-2008
• tsql
• snapshot

Open two connections, start a snapshot transaction on both, on connection 1 update a record, on the connection 2 update the same record (in background because it will block), then on connection 1 commit

Or treat a user error as a 3960 ...

Why not just do this:

RAISERROR(3960, {sev}, {state})

Replacing {sev} and {state} with the actual values that you see when the error occurs in production?

(Nope, as Martin pointed out, that doesn't work.)

If not that then I would suggest trying to run your test query multiple times simultaneously. I have done this myself to simulate other concurrency errors. It should be doable as long as the test query is not too fast (a couple of seconds at least).