mysql - SQL INSERT 是否涉及任何读/写锁？

Question

这是一个非常简单的问题，但我真的无法在谷歌上找到任何有用的答案。

我一直在读到在 SELECT 上有一个 READ LOCK，在 UPDATE 语句上有一个 WRITE LOCK。但是，我想知道当我们插入表时是否有任何类型的锁？

假设该表使用 InnoDB 作为引擎。

Answer 1

插入时，InnoDB 会创建一个所谓的“间隙锁”。

The manual describes this quite well :

A type of gap lock called an insertion intention gap lock is set by INSERT operations prior to row insertion. 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. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting

除此之外，任何唯一索引都将针对提供的值进行锁定，以确保两个不同的事务不会插入相同的值(这与间隙锁略有不同，如果我'我没记错)。