Deadlocks caused by non-primary key index updates

  1. The problem

    Database logs throw DeadLock

  2. MySql update procedure

    1. MySql Storage Engine

      Innodb: transaction support, update using row-level locking, high concurrency

      MyISAM: no transaction support, table lock during update, poor concurrency

      This is why Innodb deadlocks.

    2. Update Update process

      Row-level locking does not directly lock records, but locks indexes. If a SQL statement uses a primary key index, mysql will lock the primary key index. If a statement operates on a non-primary key index, mysql will first lock the non-primary key index and then lock the primary key index. On the other hand,

      If the operation uses the primary key index, the primary key index is locked, and then the other indexes are locked.

      If no index is used, a full table scan is performed and the table is locked.

      When you update a where condition that is not a primary key index, perform the following steps:

      1) Obtain row-level locks for non-primary key indexes;

      2) From the basic principle of database, when the condition is not primary key index, the backtable query will occur, and then obtain the row-level lock of primary key index;

      3) After the update, the transaction is committed.

    3. Update operations on non-primary key indexes are not atomic operations, and row-level locks on different indexes are required, which can cause deadlocks:

      If an UPDATE statement uses a primary key index and a non-primary key index, the primary key index is acquired first, and then the non-primary key index is acquired. If the update statement uses only non-primary key indexes, the lock is obtained by first obtaining the non-primary key index and then obtaining the primary key index, which occurs between steps 1 and 2).

  3. The solution

    Where condition plus primary key index

    First lock query to find out, according to the primary key update

    A quick update

  4. Understanding that row-level locking is not a direct lock record, but a lock index

    MySQL row locks are used to access different rows, but if the same key is used to access different rows, lock conflicts will occur