Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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.

share|improve this answer
thanks for the solutions – Terry Mar 8 '13 at 5:21

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.

share|improve this answer
thanks for the solutions. – Terry Mar 8 '13 at 5:21

You should choose the correct transaction isolation level. Check the


isolation levels of MySQL.

share|improve this answer
This answer would be better if it explained the relationship between isolation levels and deadlock potential, and why you feel a change would help in the specific situation described in the question. – Paul White Mar 6 '13 at 15:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.