Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

SERIALIZABLE Transaction Isolation Level Is Not Behaving As I Expected Expand / Collapse
Posted Wednesday, February 20, 2013 4:23 PM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 23, 2015 7:53 AM
Points: 263, Visits: 1,127
Thanks for the advice, Jamie.

I've fallen back to my original READCOMMITTED transaction isolation level. It should work. I don't know why it appears not to.

As you suggested, I experimented with setting both the data archive/delete query and the test query to the SERIALIZATION transaction isolation level. This produced a deadlock on the test query which was not a surprise. I increased the interval between loop executions in the data archive/delete query and the test query executed without incident. Unfortunately, it was not a complete test because at this moment, there are no rows to be archived/deleted.

Your comments, this test, and some additional research I've done has made me realize that my fallacy was almost certainly that I had 2 different queries looking at the same data with different levels of transaction isolation. Phantom reads were definitely the symptom I experienced.

Thank you, all of you, for your responses.
Post #1422351
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse