mysql - InnoDB中的锁

Answer 1

------- TRX HAS BEEN WAITING 28 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6 page no 4 n bits 80 index idx_a of table `test`.`t` trx id 637972 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 8000000b; asc     ;;
 1: len 6; hex 000000000414; asc       ;;

It’s very clear here. lock_mode
Because the previously executed sentence delete from t where a = 11; will add an exclusive lock in the (negative infinity,11] interval. Why is it an exclusive lock instead of a Record Lock? Because a here is not a unique index. , just an ordinary index, see http://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
for details

Answer 2

MySQL's default session isolation level is repeated read, which will generate more gap locks. If phantom reads are acceptable, you can consider downgrading to read commit level to reduce the probability of lock conflicts.