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
Author
Message
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: Sunday, August 24, 2014 1:22 PM
Points: 259, Visits: 1,087
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