0

If a row is modified by a transaction that uses the SNAPSHOT isolation level, any concurrent update (by another transaction) to the same row causes the SNAPSHOT transaction to fail with a conflict.

Is there a way to have the same behavior happen with rows that have only been read (not necessarily modified)?

As an example, I am looking to have TX1 fail with conflict if the following sequence happens:

  1. TX1 starts with SNAPSHOT isolation level
  2. TX1 reads row 1
  3. TX2 modifies row 1 and commits
  4. TX1 commits, and fail with a conflict as row 1 has been modified after it has been read
Flavien
  • 7,497
  • 10
  • 45
  • 52
  • That will anyways happens as a result of snapshot isolation level. What exactly you are asking here? – Rahul Dec 12 '15 at 22:38
  • That is not what I am seeing. The transaction only fails is TX1 modifies row 1. If it only reads it, no conflict is raised. – Flavien Dec 12 '15 at 22:39
  • Ahh, I didn't see that; you are just reading not doing any modification. Why you want that behavior BTW? – Rahul Dec 12 '15 at 22:42
  • I am making an update that depends on the value I read, but these are two separate rows. If the value read has changed, the update is no longer valid, should fail and be reattempted. – Flavien Dec 12 '15 at 22:44
  • You could start a txn and use isolation level of "repeatable read" – Bohemian Dec 13 '15 at 02:39

0 Answers0