14.2.11 How to Cope with Deadlocks

This section builds on the conceptual information about deadlocks
in Section 14.2.10, “Deadlock Detection and Rollback”. It explains how to
organize database operations to minimize deadlocks and the
subsequent error handling required in applications.

Deadlocks are a classic
problem in transactional databases, but they are not dangerous
unless they are so frequent that you cannot run certain
transactions at all. Normally, you must write your applications so
that they are always prepared to re-issue a transaction if it gets
rolled back because of a deadlock.

InnoDB uses automatic row-level locking. You
can get deadlocks even in the case of transactions that just
insert or delete a single row. That is because these operations
are not really “atomic”; they automatically set locks
on the (possibly several) index records of the row inserted or
deleted.

You can cope with deadlocks and reduce the likelihood of their
occurrence with the following techniques:

At any time, issue the
SHOW ENGINE
INNODB STATUS command to determine the cause of the
most recent deadlock. That can help you to tune your
application to avoid deadlocks.

If frequent deadlock warnings cause concern, collect more
extensive debugging information by enabling the the
innodb_print_all_deadlocks
configuration option. Information about each deadlock, not
just the latest one, is recorded in the MySQL
error log. Disable this
option when you are finished debugging.

Always be prepared to re-issue a transaction if it fails due
to deadlock. Deadlocks are not dangerous. Just try again.

Keep transactions small and short in duration to make them
less prone to collision.

Commit transactions immediately after making a set of related
changes to make them less prone to collision. In particular,
do not leave an interactive mysql session
open for a long time with an uncommitted transaction.

When modifying multiple tables within a transaction, or
different sets of rows in the same table, do those operations
in a consistent order each time. Then transactions form
well-defined queues and do not deadlock. For example, organize
database operations into functions within your application, or
call stored routines, rather than coding multiple similar
sequences of INSERT,
UPDATE, and DELETE
statements in different places.

Add well-chosen indexes to your tables. Then your queries need
to scan fewer index records and consequently set fewer locks.
Use EXPLAIN
SELECT to determine which indexes the MySQL server
regards as the most appropriate for your queries.

Use less locking. If you can afford to permit a
SELECT to return data from an
old snapshot, do not add the clause FOR
UPDATE or LOCK IN SHARE MODE to
it. Using the READ
COMMITTED isolation level is good here, because each
consistent read within the same transaction reads from its own
fresh snapshot.

If nothing else helps, serialize your transactions with
table-level locks. The correct way to use
LOCK TABLES with transactional
tables, such as InnoDB tables, is to begin
a transaction with SET autocommit = 0 (not
START
TRANSACTION) followed by LOCK
TABLES, and to not call
UNLOCK
TABLES until you commit the transaction explicitly.
For example, if you need to write to table
t1 and read from table
t2, you can do this:

Table-level locks prevent concurrent updates to the table,
avoiding deadlocks at the expense of less responsiveness for a
busy system.

Another way to serialize transactions is to create an
auxiliary “semaphore” table that contains just a
single row. Have each transaction update that row before
accessing other tables. In that way, all transactions happen
in a serial fashion. Note that the InnoDB
instant deadlock detection algorithm also works in this case,
because the serializing lock is a row-level lock. With MySQL
table-level locks, the timeout method must be used to resolve
deadlocks.

User Comments

Combine queries for Insert and Select always obeys Innodb locking rulesif one of the source table is based on Innodb engine.It is also possible that the INSERT activity applicable to TEMPORARYtable which is not InnoDB engine. It is also possible that in SELECTsection with INNODB, some other TEMPORARY Tables are used.Devang Modi