InnoDB has several types of record-level locks:
Record lock: This is a lock on an index record.
Gap lock: This is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gap before the index record.
Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking. See Section 13.2.10.1, “Clustered and Secondary Indexes”.
By default, InnoDB operates in REPEATABLE READ transaction isolation level and with the innodb_locks_unsafe_for_binlog system variable disabled. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows (see Section 13.2.8.5, “Avoiding the Phantom Problem Using Next-Key Locking”).
Next-key locking combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before that index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record
(也就是说,next-key是一个索引记录锁和加在gap上的gap锁的组合)
.
If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order
(如果一个session
在R记录的索引上
获得一个共享或互斥锁,那么另一个session不能以索引顺序立即在R前面的间隙上加一条索引记录)
.
Suppose that an index contains the values 10, 11, 13, and 20. The possible next-key locks for this index cover the following intervals, where ( or ) denote exclusion of the interval endpoint and [ or ] denote inclusion of the endpoint:
(假设一个索引记录包含10,11,13,20。那么对这个索引来说可能的next-key locks包含以下几个区间,小括号表示该区间排除该点,中括号表示该区间包括该点。 )
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
For the last interval, the next-key lock locks the gap above the largest value in the index and the “supremum” pseudo-record having a value higher than any value actually in the index. The supremum is not a real index record, so, in effect, this next-key lock locks only the gap following the largest index value.
The preceding example shows that a gap might span a single index value, multiple index values, or even be empty.
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 id value 100 and it does not matter whether other sessions insert rows in the preceding gap:
(如果一个语句用了唯一索引那么间隙锁是不需要的(当然对几列组成的唯一索引是不适用的)例如,如果 id这一列有一个唯一索引,那么下面的查询只用 index-record lock 锁住id为100的行,而是否有另一个session要往该行前的间隙插入一条新纪录是没有关系的,是可以的)
SELECT * FROM child WHERE id = 100;
If id is not indexed or has a nonunique index, the statement does lock the preceding gap.
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.
(一种叫做insertion gap lock 的锁是用做插入操作前 插入行(这句怎么翻译
)????这种锁信号以这种方式插入:当很多事务同时插入到同一个索引的间隙中时,只要这些插入不是要插入到同一位置就不用互相等待。假设有值为4和7的索引纪录,当几个事务要在这之间插入5和6时分别用insertion gap lock锁住4和7之间的间隙,取得插入的行的排斥锁,但是不会互相阻塞,因为这些插入行之间是没有冲突的。)
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system 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.
There is also another effect of using the READ COMMITTED isolation level or enabling innodb_locks_unsafe_for_binlog: Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.
英文部分摘自:http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html
分享到:
相关推荐
mysql5.6官方文档:14.8 InnoDB File-Format Management
同时使用一种 -- next-key locking 的锁策略来避免幻读现象的产生,还提供了插入缓冲(insert buffer) 二次写(double write) 自适应哈希索引,预读(read ahead)等高性能和高可用的功能。对于表中的数据innodb...
MySQL InnoDB Java阅读器 innodb-java-reader是一个Java实现,用于直接访问MySQL InnoDB存储引擎文件。 使用库或命令行工具,它提供了只读功能,例如检查页面,通过主键,辅助键查找记录以及通过LSN或填充率生成页面...
InnoDB事务-锁-MVCC.pdf
mysql-shell-8.0.18-windows-x86-64bit.zip MySQL Shell is an advanced command-line client and code ... MySQL Shell includes the AdminAPI that enables you to work with InnoDB cluster, see InnoDB Cluster.
第11课、InnoDB-0223.avi 第12课、InnoDB2-0228.avi 第13课、InnoDB3-0302.avi 第14课、InnoDB4-0307.mp4 第15课、MySQL监控-0314.avi 第16课、MySQL监控2-0316.avi 第17课、MYSQL监控3-0321.avi 第18课、MySQL压测-...
2020年1月13日,MySQL官方发布了今年的第一个8.0版本-8.0.19,自2018年4月19日发布第一个稳定版本8.0.11到如今已经历了近两年共9个小版本的迭代,产品稳定性持续增强。极具吸引力的是官方在8.0版本上对MGR bug 的...
Discover the newest high-availability features in MySQL Set up and use InnoDB Cluster as an HA solution Migrate your existing servers to MySQL 8 Employ best practices for using InnoDB Cluster ...
MySQL Innodb 索引原理详解
[mysqld] # 设置3306端口 ...default-storage-engine=INNODB # 默认使用“mysql_native_password”插件认证 #mysql_native_password default_authentication_plugin=mysql_native_password [mysql] # 设置mysql客
MySQL分析 innodb
mysql 5.6 新特性 innodb
MySQL技术内幕InnoDB存储引擎-读书笔记.pdf
MySQL内核 INNODB存储引擎-卷1-高清-完整目录-2014年5月
MySQL技术内存-InnoDB存储引擎,
MySQL 核心引擎InnoDB-事务锁多版本分析,进阶资深DBA可以看看
8.MySQL存储引擎--MyISAM与InnoDB区别1
InnoDB 查询优化实现分析 -- MySQL
mysql innodb类型数据库表 根据ibd文件获取表 space id,用于恢复innodb类型数据表数据