June 20, 2008 at 12:49 am
I have a stored proc which is running within a serializable transaction. Within the stored proc there are a couple of updates and an insert and then I check the data for consistency. If the data has an issue (it should never have) I raise an error and the whole transaction fails which is what I want.
This all works fine but reading the documentation for SERIALIZABLE, the following is stated:
Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction [...] This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows.
Now because I'm updating data that I'm later checking is OK, the above seems to be saying I shouldn't be able to do that when using SERIALIZABLE transaction. Or is it just saying the same set of rows will be returned but with the updated data, for the SNAPSHOT isolation level, the documentation explicitly states that the transaction itself can read data modified in the transaction.
Am I not interpreting the above correctly? My data check is very important and I want to make sure it's going to do what I want.
June 20, 2008 at 8:59 am
What you're doing will work. The locks it's talking about are with regards to reads from the database, not the data you're updating.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 10:43 pm
OK, ta, the documentation just seemed a it ambiguous...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply