Clever Geek Handbook

How can I get to disable snapshot isolation 3960

History

I have a SPROC using Snapshot Isolation to do multiple inserts via MERGE. This SPROC is called under very high load and often in parallel, which is why it sometimes throws error 3960, which indicates that the snapshot is being rolled back due to change conflicts. This is expected due to the high concurrency.

Problem

I ran a "retry" queue to do this work again later, but I am having a hard time reproducing the error to verify that my checks are accurate.

Question

How can I reproduce the snapshot error (3960 in particular) to test if my retry logic is working?

Already tried

  • RAISEERROR doesn't work because it doesn't allow me to create existing user-defined errors.
  • I tried re-inserting the same record, but this does not throw the same failure since these are not two different transactions "racing" others
+3


source to share


2 answers


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



Or handle user error as 3960 ...

+1


source


Why not just do this:

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

Replacing {sev} and {state} with the actual values ​​you see when a production error occurs? Strike>



(No, as Martin pointed out, it doesn't work.)

If this is not the case, I would suggest trying to execute your test query multiple times at the same time. I did it myself to simulate other concurrency errors. This should be doable as long as the test request isn't too fast (at least a couple of seconds).

0


source






More articles:


All Articles