How to avoid deadlock while updating 2 rows in MySQL 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 -&gt; update row B -&gt; commit

The other process may execute a transaction like:
update row B -&gt; update row A -&gt; 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?
I don't like the interview question because in addition to the (presumably) correct SELECT ... FOR UPDATE on all rows, I suspect the other answers they're looking for may be something theoretically accurate but not necessarily the best idea (especially in a high-concurrency environment), such as:


use table level locking to force serialization of the transactions
use a semaphore mechanism such as GET_LOCK() and RELEASE_LOCK() to serialize the transactions with named locks... admittedly, this is a feature in MySQL that I really do find very useful (such as for preventing recurring scheduled events that run longer than expected from firing up parallel instances, and serializing the execution of unrelated low-priority background/maintenance jobs to keep resource utilization by the maintenance jobs at a minimum) but that I do not normally use for serializing live transactions.


@Chris Travers has already given what may be more useful real-world answers than these, but from my reading of the question, standardizing the order (#2) and using stored procedures (#3) aren't permitted modifications, perhaps, by definition... so I offer the speculation, above.