3

I was wondering what are the advantage/inconvenient for these two optimistic lock solutions :

  1. Use a "version" field and detect changes during updates (i.e. using hibernate @Version annotation)

  2. Use the Snapshot isolation level on transactions

If I'm correct these 2 solutions have the same behavior : an error will be thrown if the row has been updated during the transaction

Thanks

Quentin
  • 3,150
  • 4
  • 24
  • 34
  • I have added an answer. If there are specific aspects you'd like more information on let me know. – usr Feb 17 '15 at 13:10

1 Answers1

4

These two do not have the same behavior. With hibernate optimistic concurrency it is OK if the row to be written was modified in the meantime and modified back. For example a +1 followed by a -1. It is also OK that columns have been modified that hibernate does not check.

Snapshot isolation checks all columns and does not compare data. Any write, even a null write such as x = x counts.

Snapshot isolation also guarantees you a point-in-time snapshot for reads. You did not say what isolation level you intend to use for (1). I assume it's not SNAPSHOT. For that reason (2) is likely to offer you guarantees that hibernate cannot offer on its own.

Hibernate optimistic concurrency is suitable for detached entities. Snapshot isolation cannot do that because it requires a transaction to encompass all operations you make.

Note, that both solutions are not serializable since they verify writes but not reads.

usr
  • 168,620
  • 35
  • 240
  • 369
  • any idea about the performance impact of a snapshot isolation vs version field + Repeatable reads ? From your answer I'd say that the best way to go is with a version field as long as we don't care about phantom reads. Are you OK with that ? – Quentin Feb 18 '15 at 13:45
  • 1
    Repeatable read can cause lots of locks to accumulate. It is very near SERIALIZABLE regarding locking, blocking, overhead and concurrency reduction. Be careful.; I cannot recommend a concurrency strategy without knowing your workload. You obviously need to pick a strategy that satisfies your safety needs. I cannot know those needs.; Overhead: There are overheads for both strategies. They are in very different places. SI in general is an amazing tool. The main benefit I see is that it is automated and very easy to predict. If you *can* use it I would. It is better than RR in almost all regards. – usr Feb 19 '15 at 18:33
  • I can better answer questions that are more concrete. The vagueness of my answer is due to the broadness of the question. – usr Feb 19 '15 at 18:35