0

Example: i have a table A (id, version and a field) and a table B (id, version and a field).

i need to make a transaction that edit a record A, and then a record B.

  • begin transaction
  • update tableA set field='aaa', version=version+1 where id=1 and version=savedversion -if recordupdated=0 then rollback
  • update tableB set field='bbb', version=version+1 where id=1 and version=savedversion -if recordupdated=0 then rollback
  • commit

but if i have another thread that need to update the table in reverse order (in a complex environment, there is the possibility that a developer doesn't follow the policies), or needs to update table A (not same record as first transaction), then table B (same record as first transaction), then A (same record as first transaction), can occur a deadlock?

what is the right way to make a transaction in a optimistic lock? could the solution be using only stored procedures?

Ciccio Pasticcio
  • 117
  • 7
  • "Optimistic lock" is a bad name; there really isn't any lock to cause a deadlock. See also: [Optimistic concurrency control](https://en.wikipedia.org/wiki/Optimistic_concurrency_control), [Could there be a deadlock when using optimistic locking?](https://stackoverflow.com/questions/38946812/could-there-be-a-deadlock-when-using-optimistic-locking) – Perette Apr 01 '19 at 13:43
  • I red this thread, but it tells only "if you want to avoid deadlocks, you have to respect the order of updates". – Ciccio Pasticcio Apr 01 '19 at 14:10
  • 1
    I rescind my comment that there is no lock. According to [these](http://kb.magicsoftware.com/articles/bl_Reference/Locking---SQL-Considerations-xpa) [articles](https://docs.oracle.com/cd/B19306_01/server.102/b14220/consist.htm), the DBMS performs implicit locking, which means there is risk of a deadly embrace if tables are updated (and thus locks established) in mismatched orders. – Perette Apr 01 '19 at 18:44

0 Answers0