• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Question about TRX_REPEATABLE_READ

 
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I've been reading up on transactions, among other things, in preparation for the SCAE certification, but I came across something that confused me. I was reading an article discussing transactions and when I came across the explanation on TRX_REPEATABLE_READ, I got a bit confused. Here's the snippet...

TRANSACTION_REPEATABLE_READ: This level ensures that reading the same data multiple times will receive the same value even if another transaction modifies the data.


The author mentions "even if another transaction modifies the data", but does this mean modify and commit, or just modify within the context of another transaction, essentially uncommitted. What would be the point of using this isolation level if other transactions can change the same data selected in the current transaction? I would think that, for example, when you withdraw money from your bank account, the ATM selects your current account balance to ensure your have sufficient funds, and this account balance cannot change during the span of the withdrawal, for obviously reasons.
I thought the behavior of TRX_REPEATBLE_READ was to allow the same transaction to re-read a row and obtain the same values for that row, which must mean that other transactions can read, but cannot commit changes to that same data until the current transaction either commits or rollbacks.
Thanks
 
author
Posts: 3252
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by SAFROLE YUTANI:
[...] The author mentions "even if another transaction modifies the data", but does this mean modify and commit, or just modify within the context of another transaction [...]

It means modify and commit. You're not seeing any uncommitted data at this isolation level.

I thought the behavior of TRX_REPEATBLE_READ was to allow the same transaction to re-read a row and obtain the same values for that row, which must mean that other transactions can read, but cannot commit changes to that same data until the current transaction either commits or rollbacks.

Not necessarily. A database can ensure repeatable read isolation by granting read locks which prevent modification until the reading transaction is committed. This is what you are suggesting here; I believe Microsoft SQL Server does things this way, for example. Alternatively, a database can ensure repeatable read isolation by effectively maintaining a "snapshot" of the data as it was when the reading transaction started. The underlying data in the database can be modified by other transactions. This is how Oracle works (if you ask for a serializable transaction isolation level; I don't think Oracle supports repeatable read). It is more complicated but allows for greater concurrency.
- Peter
[ May 19, 2003: Message edited by: Peter den Haan ]
 
SAFROLE YUTANI
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks, and yes, Oracle only supports TRX_COMMITED and TRX_SERIALIZABLE. I think I understand now, but I might need another drink
SAF
 
reply
    Bookmark Topic Watch Topic
  • New Topic