How to avoid deadlock while updating 2 rows in MySQL

Question

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?

Answer 1

3 approaches:

1. Each application SELECTs FOR UPDATE all rows it will update before it starts its update run.

2. All applications standardize the order in which they hit the tables, minimizing the chances of deadlocks.

3. Use stored procedures to enforce a write order via an API. This way the update code is centrally managed and all applications hit the tables in the same order.

Note I have run into deadlocks in MySQL in statements like:

INSERT INTO foo (bar, baz) VALUES (1, 2), (2, 3), (3, 4), (4, 5), (5, 6)....

This seems to happen more frequently the more tuples are inserted at once, and I suspect it's a threading problem as it is never perfectly reproducible unless importing large data sets. The solution to that is not exactly what you are being asked but it's to simply insert one record per statement.

Answer 2

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.

Answer 3

You should choose the correct transaction isolation level. Check the

READ UNCOMMITTED,
READ COMMITTED,
REPEATABLE READ,
SERIALIZABLE

isolation levels of MySQL.
http://dev.mysql.com/doc/refman/5.6/en/dynindex-isolevel.html