innodb next-key lock解析_MySQL
这里补充一些:
(1)InnoDB默认加锁方式是next-key locking
(2)在聚集索引中,如果主键有唯一性约束(unique,auto increment),next-key locking 会自动降级为record locking。
(3)由于事务的隔离性和一致性要求,会对所有扫描到的record加锁。比如:update ... where/delete .. where/select ...from...lock in share mode/ select .. from .. for update这都是next-key lock。
(4)注意优化器的选择。包括聚集索引和辅助索引,有时会用全表扫描替代索引扫描,这时整张表(聚集索引表)都会被加锁。
record lock:记录锁,也就是仅仅锁着单独的一行
gap lock:区间锁,仅仅锁住一个区间(注意这里的区间都是开区间,也就是不包括边界值,至于为什么这么定义?innodb官方定义的)next-key lock:record lock+gap lock,所以next-key lock也就半开半闭区间,且是下界开,上界闭。(为什么这么定义?innodb官方定义的)
下面来举个手册上的例子看什么是next-key lock。假如一个索引的行有10,11,13,20
那么可能的next-key lock的包括:
(无穷小, 10]
(10,11]
(11,13]
(13,20]
(20, 无穷大) (这里无穷大为什么不是闭合?你数学不到家~~)
好了现在通过举例子说明:
表test
mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`a` int(11) NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from test;
+----+
| a |
+----+
| 11 |
| 12 |
| 13 |
| 14 |
+----+
4 rows in set (0.00 sec)
开始实验:
(一)
session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test where a=11;
Query OK, 1 row affected (0.00 sec)
session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(15);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(9);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(16);
Query OK, 1 row affected (0.01 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
ok,上面的情况是预期的,因为a上有索引,那么当然就只要锁定一行,所以其他行的插入不会被阻塞。
那么接下来的情况就有意思了
(二)
session 1(跟上一个session 1相同):
delete from test where a=22;
Query OK, 0 rows affected (0.01 sec)
session 2:
mysql> insert into test values (201);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (19);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (18);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (16);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (9);
Query OK, 1 row affected (0.00 sec)
从上面的结果来看,在a=11后面所有的行,也就是区间(11,无穷大)都被锁定了。先不解释原因,再来看一种情况:
(三)
session 1:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
+----+
| a |
+----+
| 7 |
| 9 |
| 10 |
| 12 |
| 13 |
| 14 |
| 15 |
| 22 |
| 23 |
| 24 |
| 25 |
+----+
11 rows in set (0.00 sec)
mysql> delete from test where a=21;
Query OK, 0 rows affected (0.00 sec)
session 2:
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values (20);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (26);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values (21);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (16);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values (6);
Query OK, 1 row affected (0.01 sec)
从这里可以看出,现在被锁住的区间就只有[16,21)了。
有了前面对三种类型的加锁解释,现在可以来解释为什么会这样了,在innodb表中 delete from where ..针对扫描到的索引记录加next-key锁(具体的什么语句加什么锁可以查看手册,另外需要说明一下,行锁加锁对象永远是索引记录,因为innodb中表即索引) 。
在(一)中,实际上加的next-key lock就是(11,11] 因此也只有a=11这一条记录被锁住,其他所有插入都没有关系。
在(二)中,因为a=22这条记录不存在,而且22比表里所有的记录值都大,所以在innodb看来锁住的区间就是(14, 无穷大)。所以在插入14以后的值都提示被锁住,而14之前的则可以。
在(三)种,a=21也是不存在,但是在表里面21前后都有记录,因此这里next-key lock的区间也就是(15,21],因此不在这个区间内的都可以插入。
那么为什么next-key lock都是下界开区间,上界闭区间呢?这个倒不重要,管它呢,但是有一点我个人却觉得比较怪,比如说
delete test where a > 11 #------- 1
它的next-key lock是(11, 无穷大)
delete test where a
它的next-key lock是(无穷小, 10]
这样给人的感觉就很怪,因为在手册上对next-key lock的定义:
Next-key lock: This is a combination of a record lock on the index record and a gap lock on the gapbefore the index record.
而在1那种情况下,如果按照手册上的解释,记录锁和它之前的gap那么就会有些牵强。[今天再次看了一遍官方手册,是之前自己的理解不到位,这个before是对的,因为innodb在加锁时是所有扫描过程中遇到的记录都会被加锁,那么对于1那种情况,实际上是从12开始扫描,但是因为要保证a>11的都被delete掉,因此得一直扫描下去那自然最大值就是无穷大,因为这个next-key lock就是无穷大这条记录(这是假设的一条记录,表示一个边界)加上它之前的gap lock (11, 无穷大),所以在任何时候next-lock都是record lock加上这个record之前的一个gap lock]
但是只要我们自己能理解就行了:记录锁---锁单条记录;区间锁---锁一个开区间;next-key 锁---前面两者的结合,而不要管什么before。
另外next-key lock虽然在很多时候是锁一个区间,但要明白一个区间也可能只有一个元素,因此在称delete from tb where key=x 这种情况下加next-key锁也是完全正确的。
另外还提两点:
1.如果我们的SQL语句里面没有利用到索引,那么加锁对象将是所有行(但不是加表锁),所以建索引是很重要的
2.next-key lock是为防止幻读的发生,而只有repeatable-read以及以上隔离级别才能防止幻读,所以在read-committed隔离级别下面没有next-key lock这一说法。

热AI工具

Undresser.AI Undress
人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool
免费脱衣服图片

Clothoff.io
AI脱衣机

AI Hentai Generator
免费生成ai无尽的。

热门文章

热工具

记事本++7.3.1
好用且免费的代码编辑器

SublimeText3汉化版
中文版,非常好用

禅工作室 13.0.1
功能强大的PHP集成开发环境

Dreamweaver CS6
视觉化网页开发工具

SublimeText3 Mac版
神级代码编辑软件(SublimeText3)

InnoDB是一个将表中的数据存储到磁盘上的存储引擎,所以即使关机后重启我们的数据还是存在的。而真正处理数据的过程是发生在内存中的,所以需要把磁盘中的数据加载到内存中,如果是处理写入或修改请求的话,还需要把内存中的内容刷新到磁盘上。而我们知道读写磁盘的速度非常慢,和内存读写差了几个数量级,所以当我们想从表中获取某些记录时,InnoDB存储引擎需要一条一条的把记录从磁盘上读出来么?InnoDB采取的方式是:将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小一般为16

InnoDB是MySQL的数据库引擎之一,现为MySQL的默认存储引擎,为MySQL AB发布binary的标准之一;InnoDB采用双轨制授权,一个是GPL授权,另一个是专有软件授权。InnoDB是事务型数据库的首选引擎,支持事务安全表(ACID);InnoDB支持行级锁,行级锁可以最大程度的支持并发,行级锁是由存储引擎层实现的。

1.Mysql的事务隔离级别这四种隔离级别,当存在多个事务并发冲突的时候,可能会出现脏读,不可重复读,幻读的一些问题,而innoDB在可重复读隔离级别模式下解决了幻读的一个问题,2.什么是幻读幻读是指在同一个事务中,前后两次查询相同范围的时候得到的结果不一致如图,第一个事务里面,我们执行一个范围查询,这个时候满足条件的数据只有一条,而在第二个事务里面,它插入一行数据并且进行了提交,接着第一个事务再去查询的时候,得到的结果比第一次查询的结果多出来一条数据,注意第一个事务的第一次和第二次查询,都在同

一、回退重新装mysql为避免再从其他地方导入这个数据的麻烦,先对当前库的数据库文件做了个备份(/var/lib/mysql/位置)。接下来将Perconaserver5.7包进行了卸载,重新安装原先老的5.1.71的包,启动mysql服务,提示Unknown/unsupportedtabletype:innodb,无法正常启动。11050912:04:27InnoDB:Initializingbufferpool,size=384.0M11050912:04:27InnoDB:Complete

MySQL储存引擎选型对比:InnoDB、MyISAM与Memory性能指标评估引言:在MySQL数据库中,储存引擎的选择对于系统性能和数据完整性起着至关重要的作用。MySQL提供了多种储存引擎,其中最常用的引擎包括InnoDB、MyISAM和Memory。本文将就这三种储存引擎进行性能指标评估,并通过代码示例进行比较。一、InnoDB引擎InnoDB是My

MySQL是一款广泛使用的数据库管理系统,不同的存储引擎对数据库性能有不同的影响。MyISAM和InnoDB是MySQL中最常用的两种存储引擎,它们的特点各有不同,使用不当可能会影响数据库的性能。本文将介绍如何使用这两种存储引擎来优化MySQL性能。一、MyISAM存储引擎MyISAM是MySQL最常用的存储引擎,它的优点是速度快,存储占用空间小。MyISA

提高MySQL存储引擎读取性能的技巧和策略:MyISAM与InnoDB对比分析引言:MySQL是最常用的开源关系型数据库管理系统之一,主要用于存储和管理大量结构化数据。在应用中,对于数据库的读取性能往往是非常重要的,因为读取操作是大部分应用的主要操作类型。本文将重点讨论如何提高MySQL存储引擎的读取性能,重点对比分析MyISAM和InnoDB这两个常用的存

支持GIS数据的MySQL存储引擎:InnoDB中的空间索引优化摘要:在现代的数据库应用中,地理信息系统(GIS)数据扮演着越来越重要的角色。GIS数据处理是复杂和动态的,传统的关系型数据库并不擅长处理这种类型的数据。然而,MySQL提供了一种存储引擎,即InnoDB,可以优化GIS数据的处理。本文将介绍如何在InnoDB存储引擎上使用空间索引来优化GIS数
