Take the 2-minute tour ×
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.

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
add comment

3 Answers

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
add comment

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
add comment

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

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
add comment

Your Answer

 
discard

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.