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:
Use 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
UPDATE
or SELECT ...
LOCK IN SHARE MODE
), try using a lower isolation
level such as READ
COMMITTED
.
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
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 allow 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:
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
Table-level locks make your transactions queue nicely and avoid deadlocks.
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
Add your own comment.