(mysql.info) innodb-deadlocks

Info Catalog

(mysql.info) innodb-deadlock-detection

(mysql.info) innodb-transaction-model

 
 14.2.10.11 How to Cope with Deadlocks
 .....................................
 
 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 `...
      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 set
      `AUTOCOMMIT = 0' and not to call `UNLOCK TABLES' until after 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
      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
      `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.
 
    * In applications that use the `LOCK TABLES' command, MySQL does not
      set `InnoDB' table locks if `AUTOCOMMIT=1'.
 

Info Catalog

(mysql.info) innodb-deadlock-detection

(mysql.info) innodb-transaction-model