Phantoms and isolation

Question

Post reply

Phantoms and isolation

TomThomson

SSC Guru

Points: 104773
More actions
August 13, 2014 at 11:47 pm

#292223

Comments posted to this topic are about the item Phantoms and isolation
Tom
August 14, 2014 at 4:50 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply

Carlo Romagnano SSC-Insane Points: 22668 More actions · Answer 1

I don't like qotd too long.

But, BOL states that with SERIALIZABLE isolation level:

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

So, the option "Transaction A could suffer a phantom read" is incorrect.

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 2

Sorry Carlo, but transaction A can easily have phantom reads between the two SELECT statements.

The snapshot isolation uses the version store to prevent phantom reads (including dirty and non-repeatable reads), but at the same time readers don't block writers. Modifications (insert, update, delete) can therefore occur since no locks are held.

If we insert a row with a=10, which satisfies the WHERE clause, then this row will be read by the second SELECT statement. Voila, phantom read.

Similar if we delete a row or change the value of column a.

david.gugg SSCertifiable Points: 6096 More actions · Answer 3

1% have gotten it correct? Clearly too complex for QotD. It would have been helpful know the number of correct answers to choose.

Be still, and know that I am God - Psalm 46:10

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 4

Wonderful question, Tom (even though I got it wrong :(). It's good to know the difference between snapshot and serializable.

Am I the only one who noticed that the code was repeated? It was the same both times, but that made it harder, at first, to figure out what was going on: was it showing first code for transaction A, then code for transaction B, then code that was run later for transaction A, etc.?

I, for one, am glad it didn't specify the number of correct answers. If it had, then it would have been possible to figure out from that how many of the transactions completed successfully (0, 1, or 2).

Keep the complex questions coming!!!

jjtetzlo SSCrazy Points: 2511 More actions · Answer 5

Correct answers:1%(1)

I assume that's you, Tom? Wow... I hope I never have to troubleshoot a scenario like this. Tough, tough question. But very well laid out, great explanation.

- Jeff

TomThomson SSC Guru Points: 104773 More actions · Answer 6

stephen.long.1 (8/14/2014)
Wonderful question, Tom (even though I got it wrong :(). It's good to know the difference between snapshot and serializable.
Am I the only one who noticed that the code was repeated? It was the same both times, but that made it harder, at first, to figure out what was going on: was it showing first code for transaction A, then code for transaction B, then code that was run later for transaction A, etc.?
I, for one, am glad it didn't specify the number of correct answers. If it had, then it would have been possible to figure out from that how many of the transactions completed successfully (0, 1, or 2).
Keep the complex questions coming!!!

Thanks for teh encouragement.

I've no idea where the duplication of the script came from. Probably an error in cutting an pasting on my part, rather than anything the SQLCentral team have done. I'm sorry it happened.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 7

jjtetzlo (8/14/2014)
Correct answers:1%(1)
I assume that's you, Tom? Wow... I hope I never have to troubleshoot a scenario like this. Tough, tough question. But very well laid out, great explanation.

No, it wasn't me. I was tied up today until a short while ago and didn't look at SqlServerCentral until a few minutes ago - well after you posted that comment. I suspect that Nils Gustav was the original 1% who got it right before I stuck my answer in, as he gave the correct explanation fin response to Carlo's and Stewart's comments.

The 1% shocks me - I thought more people than that would know how transactions with mixed isolation levels work. I already knew through painful experience that quite a few people did not, but 99% not knowing is amazing. It's crept up to 3% (well, 1.5% really - 3 out of 198, including me) now so still pretty bad.

I think my next question will be a bit easier - it's intended as an adition to Hugo's recent series of questions, and should turn up next Tuesday or Wednesday.

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 8

david.gugg (8/14/2014)
1% have gotten it correct? Clearly too complex for QotD. It would have been helpful know the number of correct answers to choose.

Knowing the number would have made it easier; and I expected the SQLServerCentral team (or their question handling software) to add a statement of the numer of options that should be chosen, as this always used to be done for questions requiring more than 1 answer to be selected - but apparently this has changed.

But I don't think this is too complex for QotD: if ony 1 person in a hundred knows that transactions starting in non-snapshot isolation level can't switch to a different level and that each statement within a transaction takes takes locks or uses the database state at transaction start according to the transaction isolation level the transaction is currently operating at there is a serious lack of knowledge and that needs remedying; my view of QotD is that it is a fun way or remedying such lacks of knowledge.

But the 1% number did surprise me, and now I wonder if I should have made two separate questions out of this, each testing one of the two pieces of knowledge, instead of combining them.

Tom

Zagyg SSCrazy Points: 2091 More actions · Answer 9

I got this wrong which was rather disappointing as I thought of myself as fairly competent as regards isolation levels. Too much BI & not enough TSQL in the last few years I guess! The important thing is understanding what I had misunderstood (thanks for the post Nils, and the followups Tom).

Anyway, thinking I needed a refresher I came across this [/url]series of articles which explain the concepts Tom is dealing with here in a very clear and helpful way, hopefully it will be useful to others interested.

http://michaeljswart.com/2010/03/transaction-phenomena-part-1-dirty-reads/

(Edit: Parts 3 & 4 are the most relevant but the link points to the start of the series)

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 10

Thank you Tom, very interesting one.

(I used only READ UNCOMMITTED, and others I have just read them but never had any practical experience on them, so had really no idea what to choose based on reality, but learnt a lot. thank you.)

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

Revenant SSC-Forever Points: 42467 More actions · Answer 11

Missed on 3 points... Thanks for the question anyway, Tom!

Hany Helmy SSChampion Points: 13921 More actions · Answer 12

I am one onf the luckiest 20 % 🙂

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

Hany Helmy SSChampion Points: 13921 More actions · Answer 13

Raghavendra Mudugal (8/15/2014)
Thank you Tom, very interesting one.
(I used only READ UNCOMMITTED, and others I have just read them but never had any practical experience on them, so had really no idea what to choose based on reality, but learnt a lot. thank you.)

For me, I am using "READ COMMITTED" as my production databases can`t tolerate dirty reads, but had read a lot about Isolation levels so could got it right. Anyway it`s always good to share information & practical experiences.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant