This is a interview problem:
There is a perl program that updates the database, and it could run in different processes.
One process may execute a transaction like:
update row A -> update row B -> commit
The other process may execute a transaction like:
update row B -> update row A -> commit
The rows need to be updated is selected in the program before the transaction.
I was asked how to avoid deadlocks without changing the transaction logic (I cannot commit after updating A and commit again after updating B).
They want me to propose at least 3 different methods. What I know is to use
select .. for update when selecting row A and B. Can anyone help to suggest some other methods?