0

The specification for the Repeatable-Read isolation level defines that a transaction with this IL will prevent other transactions from updating any rows that this transaction has read until this transaction has completed. Thus, repeatable reads are guaranteed.

Consider the following order of operations for two concurrent transactions T1 and T2, both using repeatable read IL:

  1. T1: Read row
  2. T2: Read row
  3. T1: Update row
  4. T2: Update row

I think that the update in step 3 would violate the specification for the isolation level, since T2 would read a different value if it read the row again. The converse can be said for the update in step 4.

So, what different options are available for RDBMSs in general resolve this conflict? More specifically, how is this handled in SQL Server 2017+? Will this result in a deadlock since neither transaction can complete its operations? Or would one transaction be rolled back? I've seen that Lost Updates are prevented in SQL Server. What does this mean for the resolution of this specific case?

I have perused the answers to these questions: Repeatable read and lock compatibility table Repeatable Read - am I understanding this right? repeatable read and second lost updates issue MySQL Repeatable Read isolation level and Lost Update phenomena

And although the last one asks a similar question but doesn't include any specific info about how RDBMSs which prevent lost updates for txs with this isolation level handle this case.

djaxxon
  • 1
  • 2
  • SO questions are seldom as good as the actual database documentation. The links point to quite different things too, some of which talk about MVCC, not Repeatable Read. *Without* MVCC, there's no lost update. Update means reading so both threads will start with a S(hared) lock. In *this* example there's a deadlock, because both transactions want to upgrade the S to an eXclusive lock. Typically though, this doesn't happen as most queries read, don't update en masse. – Panagiotis Kanavos Jan 29 '20 at 11:48
  • That's why one should avoid such scripts. There's no need for separate READ and UPDATE queries, use one `UPDATE .. SELECT`. This will run as one atomic operation, taking the appropriate locks in one step. – Panagiotis Kanavos Jan 29 '20 at 11:54
  • @PanagiotisKanavos Thanks for your comment and suggestion. I was only considering separate READ and UPDATE statements as I am constrained by using Entity Framework as an SQL Client. It seems like the deadlock itself is the mechanism by which the lost update is prevented here, and the problem is better off avoided by somehow using an atomic operation as you say. – djaxxon Jan 29 '20 at 13:47

0 Answers0