SQL Server 2012 - How Does the Repeatable Read Isolation Work?
I feel like I should know this, but I can't find anything that specifically describes this, so this happens.
The documentation for SQL Server describes REPEATABLE READ as:
Indicates that operators 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 as long as the current transaction completes
This makes sense, but what actually happens when one of these situations occurs? If, for example, transaction A reads row 1 and then transaction B tries to update row 1, what happens? Transaction B is waiting for transaction A to complete, then try again? Or is this an exception?
source to share
REPEATABLE READ
takes S-locks on all rows that were read by query plan statements at the time of the transaction. The answer to your question follows from this:
- If reading starts first, S-locks the row and write should wait.
- If the write comes first, S-lock waits for the write to commit.
In Hekaton, it works differently because there are no locks.
source to share