mysql 间隙锁 作用_关于Mysql 间隙锁的理解

１：如果索引是唯一的，则间隙锁不起作用。

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)

For example, if the id column has a unique index, the following statement uses only an index-record lock for the row having idvalue 100 and it does not matter whether other sessions insert rows in the preceding gap:

If id is not indexed or has a nonunique index, the statement does lock the preceding gap.

2:间隙锁即使冲突也可以共存。

It is also worth noting here that conflicting locks can be held on a gap by different transactions. For example, transaction A can hold a shared gap lock (gap S-lock) on a gap while transaction B holds an exclusive gap lock (gap X-lock) on the same gap. The reason conflicting gap locks are allowed is that if a record is purged from an index, the gap locks held on the record by different transactions must be merged.

Gap locks inInnoDBare“purely inhibitive”, which means they only stop other transactions from inserting to the gap. They do not prevent different transactions from taking gap locks on the same gap. Thus, a gap X-lock has the same effect as a gap S-lock.

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level toREAD COMMITTEDor enable theinnodb_locks_unsafe_for_binlogsystem variable. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.