Next-Key Locks

Next-Key Locks

一个next-key lock 是 一个record lock 在index record 和 一个区间锁 在一个区间在index record之前

InnoDB 执行 row-level locking  以这样一种方式当它搜索或者扫描 一个表的索引,


它设置共享或者排它锁在index records.

因此, row-level locks 实际上是  index-record locks. 

一个next-key lock 在一个index record 也影响区间在那个index record 之前。


也就是说,一个next-key lock  是一个Index-record 加上一个区间锁在index record 之前的区间。


如果一个会话有一个共享或者排它锁在记录R上在一个索引上,


另外的会话不能插入一个新的index record 在这个区间 

假设一个Index 包含值10,11,13,20.

可能的next-key locks 对于这个index包含了下面的时间间隔,

一个圆括号表示排除间隔端点

一个方括号表示包含间隔端点

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)


在最后的区间, next-key lock locks 的区间在最大值的上界在index和上界的伪记录

有一个值高于任何值在index里。


上界限不是一个真正的index record.所以,实际上,


next-key lock  locks只有区间在最大索引值后面的区间

默认情况下, InnoDB 工作在REPEATABLE READ 事务隔离级别下 

 innodb_locks_unsafe_for_binlog 系统变量被禁用,


在那种情况下,可以使用next-key locks来搜索和索引扫描,来防止幻读行










Session 1:

mysql> explain select * from t1 where id BETWEEN 5 and 7 for update;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | t1    | range | t1_idx1       | t1_idx1 | 5       | NULL |    3 | Using index condition |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> select * from t1 where id BETWEEN 5 and 7 for update;
+-----+------+------+
| sn  | id   | info |
+-----+------+------+
| 239 |    5 | a5   |
| 240 |    6 | a6   |
| 241 |    7 | a7   |
+-----+------+------+
3 rows in set (0.00 sec)



Session 2:
mysql>  update t1 set id=300 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql>  update t1 set id=500 where id=5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  update t1 set id=600 where id=6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  update t1 set id=600 where id=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql>  update t1 set id=600 where id=7;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  update t1 set id=800 where id=8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  update t1 set id=800 where id=9;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)


会锁住 5,6,7,8 4条记录




继续测试:

Session 1:
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | t1_idx1  |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> select * from t1 where id=7 for update;
+-----+------+------+
| sn  | id   | info |
+-----+------+------+
| 241 |    7 | a7   |
+-----+------+------+
1 row in set (0.00 sec)


Session 2:
mysql> update t1 set id=800 where id=8;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)



去掉Id列的索引继续测试:


Session 1:

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          0 | t1_idx1  |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> alter table t1 drop index t1_idx1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> select * from t1 where id=7 for update;
+-----+------+------+
| sn  | id   | info |
+-----+------+------+
| 241 |    7 | a7   |
+-----+------+------+
1 row in set (0.00 sec)


Session 2:


Database changed
mysql> update t1 set id=800 where id=8; --HANG




/************************************************
mysql> explain select * from t1 where id=7 for update;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   11 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)



mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1    |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| t1    |          1 | t1_idx1  |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain select * from t1 where id=7 for update;
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t1    | ref  | t1_idx1       | t1_idx1 | 5       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)




下面来举个手册上的例子看什么是next-key lock。假如一个索引的行有10,11,13,20 
那么可能的next-key lock的包括: 
(无穷小, 10] 
(10,11] 
(11,13] 
(13,20] 
(20, 无穷大) (这里无穷大为什么不是闭合?你数学不到家~~) 



举例测试:

mysql> desc t100;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| sn    | int(11) | NO   | PRI | NULL    | auto_increment |
| id    | int(11) | YES  |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> show create table t100\G;
*************************** 1. row ***************************
       Table: t100
Create Table: CREATE TABLE `t100` (
  `sn` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified



Session 1:
mysql> mysql> show index from t100;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t100  |          0 | PRIMARY  |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> select * from t100;
+----+------+
| sn | id   |
+----+------+
|  1 |    7 |
|  2 |    9 |
|  3 |   10 |
|  4 |   12 |
|  5 |   13 |
|  6 |   14 |
|  7 |   15 |
|  8 |   22 |
|  9 |   23 |
| 10 |   24 |
| 11 |   25 |
+----+------+
11 rows in set (0.00 sec)

mysql> update t100 set id=100 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0



id列上没有索引,导致:

Session 2:


mysql> insert into t100(id) values(100);


t100表所有记录锁住




/***************

mysql> show index from t100;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t100  |          0 | PRIMARY   |            1 | sn          | A         |          11 |     NULL | NULL   |      | BTREE      |         |               |
| t100  |          1 | t1oo_idx1 |            1 | id          | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

mysql> select * from t100;
+----+------+
| sn | id   |
+----+------+
|  1 |    7 |
|  2 |    9 |
|  3 |   10 |
|  4 |   12 |
|  5 |   13 |
|  6 |   14 |
|  7 |   15 |
|  8 |   22 |
|  9 |   23 |
| 10 |   24 |
| 11 |   25 |
+----+------+
11 rows in set (0.00 sec)

mysql> delete from t100 where id=21;
Query OK, 0 rows affected (0.00 sec)




Session 2:

mysql>  insert into t100(id) values (15);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (16);
 ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (17);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> mysql>  insert into t100(id) values (18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (19);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  insert into t100(id) values (21);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


锁15-21 之间

行锁加锁对象永远是索引记录,因为innodb中表即索引
在(三)种,a=21也是不存在,但是在表里面21前后都有记录,因此这里next-key lock的区间也就是(15,21],因此不在这个区间内的都可以插入。 


记录锁---锁单条记录;区间锁---锁一个开区间;next-key 锁---前面两者的结合

转载于:https://www.cnblogs.com/zhaoyangjian724/p/6199869.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据锁是MySQL中的一种锁机制,用于保护数据库中的数据一致性和并发控制。根据引用和的内容,MySQL的锁机制分为全局锁、表级锁和行锁三类。 全局锁是对整个数据库实例进行锁定,可以用于备份、恢复等操作,但在并发访问方面的性能较差。 表级锁是对整个表进行锁定,可以保证同一时刻只有一个事务可以对该表进行修改,但会限制并发性。 行锁是针对数据表中行记录的锁,用于控制并发访问时的数据一致性。根据引用的内容,行锁的加锁基本单位是next-key lock,但在某些情况下会退化为Record Lock或Gap Lock。 因此,数据锁在MySQL中起到了保护数据一致性和控制并发访问的重要作用。根据具体的需求和场景,可以选择适合的锁机制来实现数据的安全访问。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* [就这一次,带你彻底搞清 MySQL行级锁的加锁规则](https://blog.csdn.net/weixin_50205273/article/details/127818382)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *2* *3* [MYSQL锁的探索](https://blog.csdn.net/sanylove/article/details/126872249)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值