SHOW ENGINE INNODB STATUS to
determine the cause of the latest deadlock. That can help
you to tune your application to avoid deadlocks.
Always be prepared to re-issue a transaction if it fails due
to deadlock. Deadlocks are not dangerous. Just try again.
Commit your transactions often. Small transactions are less
prone to collision.
If you are using locking reads (
SELECT ... FOR
... LOCK IN SHARE
MODE), try using a lower isolation level such as
Access your tables and rows in a fixed order. Then
transactions form well-defined queues and do not deadlock.
Add well-chosen indexes to your tables. Then your queries
need to scan fewer index records and consequently set fewer
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 allow a
SELECT to return data from an old
snapshot, do not add the clause
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
TABLES with transactional tables, such as
InnoDB tables, is to set
AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until after you commit the
transaction explicitly. For example, if you need to write to
t1 and read from table
t2, you can do this:
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
Table-level locks make your transactions queue nicely, and
deadlocks are avoided.
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
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
In applications that use the
command, MySQL does not set