Transaction Scheduling •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. •If you use locking reads ( SELECT ... FOR UPDATE  or  SELECT ... FOR SHARE ), try using a lower isolation level such as  READ COMMITTED . •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 so that your queries scan fewer index records and 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 a  FOR UPDATE  or  FOR SHARE  clause 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. 15.7.6 Transaction Scheduling InnoDB  uses the Contention-Aware Transaction Scheduling (CATS) algorithm to prioritize transactions that are waiting for locks. When multiple transactions are waiting for a lock on the same object, the CATS algorithm determines which transaction receives the lock first. The CATS algorithm prioritizes waiting transactions by assigning a scheduling weight, which is computed based on the number of transactions that a transaction blocks. For example, if two transactions are waiting for a lock on the same object, the transaction that blocks the most transactions is assigned a greater scheduling weight. If weights are equal, priority is given to the longest waiting transaction. 3101 SET autocommit=0; LOCK TABLES t1 WRITE, t2 READ, ...; ... do something with tables t1 and t2 here ... COMMIT; UNLOCK TABLES;