MySQL 对插入问题的描述

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.Prior to inserting the row, a type of gap lock called an insertion intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

大体的意思是：insert 会对插入成功的行加上排它锁，这个排它锁是个记录锁，而非 next-key 锁（当然更不是 gap 锁了），不会阻止其他并发的事务往这条记录之前插入记录。在插入之前，会先在插入记录所在的间隙加上一个插入意向 gap 锁（简称 I锁），并发的事务可以对同一个 gap 加 I锁。如果 insert 的事务出现了duplicate-key error ，事务会对 duplicate index record 加共享锁。这个共享锁在并发的情况下是会产生死锁的，比如有两个并发的 insert 都对要对同一条记录加共享锁，而此时这条记录又被其他事务加上了排它锁，排它锁的事务提交或者回滚后，两个并发的 insert 操作是会发生死锁的。