What is the practical use of TRANSACTION_REPEATABLE_READ?
Joined: Dec 27, 2000
I know that it ensures, that reading the same field twice should always result in the same value being read except when the transaction itself has changed the value.
But what I don't get is why would one want to read the same data twice, vs. reading it once and storing it in some memory location for future use? wouldn't that be faster?
Also, isn't it emplemented through row-level locking? whereas READ_SERIALIZABLE has table locks right? so does this mean that all Isolation levels really provide is different granularity locking?
Joined: Aug 24, 2005
TRANSACTION_SERIALIZABLE does not allow other users to insert rows that would affect the current transaction's results. TRANSACTION_REPEATABLE_READ does allow those inserts.
For example, this might be useful in a banking application. If one user is updating the status on an account, other users shouldn't be able to insert transactions until the update is complete because the fee structure might be changing. [It's a contrived example, I know.]
so does this mean that all Isolation levels really provide is different granularity locking?
It's a bit more than granularity. The isolation level sets the locking behavior -- what gets locked and what locks you will honor.