Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
laailalalaa
Yak Posting Veteran
57 Posts |
 Posted - 2010-06-09 : 09:37:55
|
| hello,T1 runs under Repeatable Read and performs a SELECT * FROM a table. T2 tries to delete / update a row in the range of rows read by T2. this doesn't work (T2 can't get an exclusive lock on data that is shared locked by T1).however, some online tutorials / documentation don't specify that only inserted phantom rows are allowed under Repeatable Read (I even found examples stating that deleted phantom rows work fine under RR).could anyone please enlighten me about this?thanks |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-06-09 : 11:34:08
|
Maybe this snippet from books online will help:quote:
REPEATABLE READSpecifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.
|
 |
|
|
laailalalaa
Yak Posting Veteran
57 Posts |
Posted - 2010-06-09 : 12:52:33
|
| thanks, Lamprey, i had already read that. i was only wondering why some tutorials, even online lectures, say it's ok to have deleted phantom rows under RR. thought i was missing smth (maybe the isolation level under which T2 runs or.. i don't know).thanks for your reply though |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-06-09 : 13:14:42
|
| From what I read and understand you cannot have deleted phantom rows. Only inserted phantom rows (phantom reads). |
|
|
|
laailalalaa
Yak Posting Veteran
57 Posts |
Posted - 2010-06-09 : 13:30:32
|
| ok, thanks again |
|
|
|
|
|
|
repeatable read - deleted phantom rows