aspose file tools*
The moose likes JDBC and the fly likes Question about TRX_REPEATABLE_READ Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Question about TRX_REPEATABLE_READ" Watch "Question about TRX_REPEATABLE_READ" New topic
Author

Question about TRX_REPEATABLE_READ

SAFROLE YUTANI
Ranch Hand

Joined: Jul 06, 2001
Posts: 257
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
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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

Joined: Jul 06, 2001
Posts: 257
Thanks, and yes, Oracle only supports TRX_COMMITED and TRX_SERIALIZABLE. I think I understand now, but I might need another drink
SAF
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Question about TRX_REPEATABLE_READ