Click here to monitor SSC
SQLServerCentral is supported by Redgate
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

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:11 AM
Points: 305, Visits: 1,246
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