MySQL index and dead lock

MySQL 

Analyze：

step 1. Thread(test1) use "select for update" lock common age index in range (10,30]

step 2. Thread(test2) use "select for update" lock common age index in range (40,60]

step 3. Thread(test2) try to insert a row which age-index = 25, but the range has been locked by Thread(test1), so wait

step 4. Thread(test1) try to insert a row which age-index = 55, but the range has been locked by Thread(test2), so wait

step 5. mysql find dead lock, rollback Thread(test2), then Thread(test1) can insert and commit.

Usually, most dead locks happen when using non-unique-index and using gap lock and next-key lock, which default enable in REPEATABLE-READ.

When execute DML(like "select for update", "insert", "update", "delete"):

  if hit unique-lock or primary lock

      mysql will use record/row lock.

  if hit non-unique-lock and transaction_isolation=REPEATABLE-READ

      mysql will use gap and next-key lock.

  if doesn't hit any index

      mysql will use  table lock.

Solution for deadlock

1. make transaction short

2. for those DML which will add lock, add lock in same order

3. replace "select ... for update" to "select" if possible, read by MVCC, it won't add lock

4. use READ-COMMITTED if possible  

5. open dead-lock checking, which will auto rollback some transaction when find deadlock