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?