mysql记录锁（record lock）,间隙锁（gap lock）,Next-key锁（Next-key lock）亲测

行锁

记录锁（record lock）

这是一个索引记录锁，它是建立在索引记录上的锁（主键和唯一索引都算），很多时候，锁定一条数据，由于无索引，往往会导致整个表被锁住，建立合适的索引可以防止扫描整个表。
如：开两个会话，两个事务，并且都不commit，该表有主键，两个会话修改同一条数据，第一个会话update执行后，第二个会话的update是无法执行成功的，会进入等待状态，但是如果update别的数据行就可以成功。
再例如：开两个会话，两个事务，并且都不commit，并且该表无主键无索引，那么第二个会话不管改什么都会进入等待状态。因为无索引的话，整个表的数据都被第一个会话锁定了。

有主键和唯一索引

举例：
CREATE TABLE t_url_mapping (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘id’ ,
url varchar(5000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT ‘原url’ ,
expire_in bigint(255) NULL DEFAULT NULL COMMENT ‘过期时间点（毫秒）’ ,
create_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’ ,
PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin
COMMENT=‘短链接映射表’
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
;
创建一个有主键的表。
插入两条数据
INSERT INTO world.t_url_mapping (id, url, expire_in, create_time) VALUES (1, ‘www.baidu.com’, NULL, NULL);
INSERT INTO world.t_url_mapping (id, url, expire_in, create_time) VALUES (2, ‘www.95303.com’, NULL, NULL);

开启会话1：

1：先执行开启事务
START TRANSACTION;
2：更新主键是1的url为baidu
update t_url_mapping set url=‘baidu’ where id=1;
不提交

开启会话2：

1：先执行开启事务
START TRANSACTION;
2：更新主键是1的url为baidu
update t_url_mapping set url=‘95303’ where id=2;
不提交

不会进行等待

开启会话3

1：先执行开启事务
START TRANSACTION;
2：更新主键是1的url为baidu
update t_url_mapping set url=‘baidu1’ where id=1;
不提交，一直卡住。

无主键和唯一索引

创建一个无主键和唯一索引的表
CREATE TABLE t_url_mapping (
id bigint(20) NULL DEFAULT NULL COMMENT ‘id’ ,
url varchar(5000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT ‘原url’ ,
expire_in bigint(255) NULL DEFAULT NULL COMMENT ‘过期时间点（毫秒）’ ,
create_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’
)
插入两条数据
INSERT INTO world.t_url_mapping (id, url, expire_in, create_time) VALUES (1, ‘www.baidu.com’, NULL, NULL);
INSERT INTO world.t_url_mapping (id, url, expire_in, create_time) VALUES (2, ‘www.95303.com’, NULL, NULL);

开启回话1：

1：先执行开启事务
START TRANSACTION;
2：更新id是1的url为baidu
update t_url_mapping set url=‘baidu’ where id=1;
不提交（因为没有索引，所以此时为表级锁）

开启回话2：

1：先执行开启事务
START TRANSACTION;
2：更新id是2的url为95303
update t_url_mapping set url=‘95303’ where id=2;
不提交，一直卡住。

间隙锁（gap lock）

MySQL默认事务隔离级别是可重复读，这个隔离级别为了避免幻读现象，引入了这个间隙锁，对索引项之间的间隙上锁。

示例：

创建一个含有主键的表，如下：
CREATE TABLE t_url_mapping (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘id’ ,
url varchar(5000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT ‘原url’ ,
expire_in bigint(255) NULL DEFAULT NULL COMMENT ‘过期时间点（毫秒）’ ,
create_time datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’ ,
PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_bin
COMMENT=‘短链接映射表’
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT;
插入两条数据：
INSERT INTO world.t_url_mapping (id, url, expire_in, create_time) VALUES (1, ‘www.baidu.com’, NULL, NULL);
INSERT INTO world.t_url_mapping (id, url, expire_in, create_time) VALUES (2, ‘www.95303.com’, NULL, NULL);

开启会话1：

1：先执行开启事务
START TRANSACTION;
2：执行如下语句
SELECT * FROM t_url_mapping WHERE id>3 LOCK IN SHARE MODE;

不提交

开启会话2：

1：先执行开启事务
START TRANSACTION;
2：执行如下插入语句
INSERT INTO world.t_url_mapping (id, url, expire_in, create_time) VALUES (11, ‘www.sina.com’, NULL, NULL);
不提交，一直卡住。

Next-key锁（Next-key lock）

Next-key锁实际上是Record锁和gap锁的组合。Next-key锁是在下一个索引记录本身和索引之前的gap加上S锁或是X锁(如果是读就加上S锁，如果是写就加X锁)。
默认情况下，InnoDB的事务隔离级别为RR，系统参数innodb_locks_unsafe_for_binlog的值为false。InnoDB使用next-key锁对索引进行扫描和搜索，这样就读取不到幻象行，避免了幻读的发生。

Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
幻读是指在同一事务下，连续执行两次同样的SQL语句，第二次的SQL语句可能会返回之前不存在的行。
幻读 : 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。例如，一个编辑人员更改作者提交的文档，但当生产部门将其更改内容合并到该文档的主复本时，发现作者已将未编辑的新材料添加到该文档中。如果在编辑人员和生产部门完成对原始文档的处理之前，任何人都不能将新材料添加到文档中，则可以避免该问题。

示例：

创建student表
CREATE TABLE student (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
age bigint(20) NOT NULL COMMENT ‘年龄’,
name varchar(100) NULL DEFAULT NULL COMMENT ‘姓名’,
PRIMARY KEY (id),
KEY age (age) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
插入4条数据

INSERT INTO world.student (id, age, name) VALUES (1, 10, ‘Lee’);
INSERT INTO world.student (id, age, name) VALUES (3, 24, ‘Soraka’);
INSERT INTO world.student (id, age, name) VALUES (5, 32, ‘Zed’);
INSERT INTO world.student (id, age, name) VALUES (7, 45, ‘Talon’);

该表中age列潜在的临键锁有： (-∞, 10], (10, 24], (24, 32], (32, 45], (45, +∞],

开启会话1：

1：先执行开启事务
START TRANSACTION;
2：执行如下语句
– 根据非唯一索引列 UPDATE 某条记录
UPDATE student SET name = ‘Vladimir’ WHERE age = 24;
– 或根据非唯一索引列 锁住某条记录
SELECT * FROM student WHERE age = 24 FOR UPDATE;

开启会话2：

1：先执行开启事务
START TRANSACTION;
2：执行如下插入语句
INSERT INTO student VALUES(100, 26, ‘Ezreal’);
不提交，一直卡住。

解释：

很明显，会话1在对age为 24 的列进行 UPDATE 操作的同时，也获取了(24, 32]这个区间内的临键锁。

开启会话3：

1：先执行开启事务
START TRANSACTION;
2：执行如下插入语句
INSERT INTO student VALUES(100, 30, ‘Ezreal’);
不提交，一直卡住。

解释：

同会话2解释一样。
那最终我们就可以得知，在根据非唯一索引对记录行进行
UPDATE（update table set …） 、
FOR UPDATE （select * from table where … for update）、
LOCK IN SHARE MODE（select * from table where … LOCK IN SHARE MODE）操作时，InnoDB 会获取该记录行的临键锁，并同时获取该记录行下一个区间的间隙锁。
即事务 A在执行了上述的 SQL 后，最终被锁住的记录区间为(10, 32)。

开启会话4：

1：先执行开启事务
START TRANSACTION;
2：执行如下插入语句
INSERT INTO student VALUES(100, 35, ‘Ezreal’);
不会被卡住。

解释：

同会话3解释。

最后

以上就是魔幻鸡为你收集整理的mysql记录锁（record lock）,间隙锁（gap lock）,Next-key锁（Next-key lock）亲测的全部内容，希望文章能够帮你解决mysql记录锁（record lock）,间隙锁（gap lock）,Next-key锁（Next-key lock）亲测所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。