Mysql – How to avoid deadlock while updating 2 rows in MySQL

deadlockMySQLperl

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?

Best Answer

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.

Related Question