mysql - 第10课 - 深入理解mysql事务隔离级别与锁机制（mysql各种锁，mysql事务隔离级别实现原理）

名猿陈大浏

已于 2023-10-05 17:05:18 修改

阅读量1.2k

点赞数

分类专栏： mysql 架构师课程文章标签： mysql 数据库 sql 索引

于 2020-08-05 17:36:55 首次发布

本文链接：https://blog.csdn.net/qq_33743572/article/details/107818803

版权

mysql 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

架构师课程

11 篇文章 0 订阅

订阅专栏

官网文档：MySQL :: MySQL 5.7 Reference Manual

导图（用于总结和复习）

一. 事务与事务的四大特性（ACID）

事务定义：事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合。

ACID：

原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

二. 并发事务带来的问题

多个事务并发执行的时候会出现以下问题：

1.脏读：事务1读取到事务2未提交的数据，事务2回滚了数据，则事务1读取到了无效数据。

2.不可重复读: 事务1读取了一条数据，然后事务2修改了这条数据并且提交了，然后事务1再读这条数据，数据前后不一致。跟脏读有点像，但是这里提交了数据。

不可重复读重点在于 update 和 delete。

3.幻读：事务1使用范围查询数据，事务2往表里添加了数据，事务1再次查询，前后查询结果不一致。跟不可重复读有点像，但是这里是插入数据。

幻读重点在于 insert。

“脏读”、“不可重复读”和“幻读”，都是数据库读一致性问题，由数据库提供一定的事务隔离机制来解决。

三. 四种事务隔离级别

不同的隔离级别是在数据可靠性和并发性之间的均衡取舍，隔离级别越高，对应的并发性能越差，数据越安全可靠。

隔离级别	脏读(Dirty Read)	不可重复读(NonRepeatable Read)	幻读(Phantom Read)
读未提交(Read uncommitted)	可能	可能	可能
读已提交(Read committed)	不可能	可能	可能
可重复读(Repeatable read)	不可能	不可能	可能
串行化(Serializable)	不可能	不可能	不可能

读未提交（Read uncommitted）：可以读取到其他事务未提交的数据。
某个数据在被写的时候一定会加上写锁，写锁会阻止其他事务给这个数据加读锁，但是对不加锁的读就会阻止无效了。
在 READ UNCOMMITTED 隔离级别下，读不会加任何锁，所以写锁的阻止作用无效，直到事务结束之后释放。
读已提交（Read committed）：可以读取到其他事务已提交的数据。
1).该隔离级别可以解决读未提交的问题。
2).在该隔离级别下读取数据时不加任何锁，而是使用MVCC(多版本并发控制机制，下节课介绍）获取当前数据的最新快照。
3).该隔离级别会有不可重复读和幻读问题。问题在于MVCC版本的生成时机是在每次select时。如果我们在事务A中执行多次select，在每次select之间有其他事务更新并提交了我们读取的数据，那就出现了不可重复度。
可重复读（Repeatable read）：
1).可重复度与读已提交不同的是MVCC版本的生成时机。即：一次事务中只在第一次select时生成版本，后续的查询都是在这个版本上进行，从而实现了可重复读。2).使用间隙锁（Gap Lock）。间隙锁可以解决大部分场景的幻读问题。但是有种幻读场景不能解决：B事务插入一条数据后，A事务可以更新这条数据，然后A事务就可以查看这条数据。
串行化（Serializable）：
该级别下，会自动将所有普通select转化为读锁（select ... lock in share mode）执行，即针对同一数据的所有读写都变成互斥的了，可靠性大大提高，并发性大大降低。

注意：在所有隔离级别下的写操作都会加写锁。

设置系统级隔离级别	设置会话级隔离级别
set global tx_isolation='read-uncommitted';	set tx_isolation='read-uncommitted';
set global tx_isolation='read-committed';	set tx_isolation='read-committed';
set global tx_isolation='repeatable-read';	set tx_isolation='repeatable-read';
set global tx_isolation='serializable';	set tx_isolation='serializable';
查看系统级隔离级别	查看会话级隔离级别
select @@global.tx_isolation;	select @@tx_isolation;

注意：修改隔离级别后需要重新连接客户端才会生效！

Mysql默认的事务隔离级别是可重复读（oracle是读已提交），用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别。

上面说到数据库使用mvcc和锁实现隔离性，接下来将详细介绍锁。下篇详细介绍MVCC。

四. 数据库的各种锁

mysql锁官网文档：MySQL :: MySQL 5.7 Reference Manual :: 14.7.1 InnoDB Locking

锁分类

从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁) ，也叫共享锁和排他锁。

读锁（共享锁，S锁(Shared)）：针对被锁住的数据，可以被所有事务加读锁，但是不能加写锁，直至读锁被释放。读操作在可重复度级别之前的都不加锁

写锁（排它锁，X锁(eXclusive)）：针对被锁住的数据，只可以被当前事务加读锁、写锁，其他事务读锁、写锁都不能加，直至写锁被释放。所有的写操作都会加写锁。

从对数据操作的粒度分，分为表锁、页锁（BDB引擎）、行锁（InnorDb引擎）【按算法包括：记录锁(Record Locks)、间隙锁(Gap Lock)、临键锁(Next-Key Locks)】。

怎么加锁

加读锁（共享锁）：select * from t_account where id=1 lock in share mode;
加写锁（排他锁）：select * from t_account where id=1 for update;
加表锁：lock table 表名称 read(write),表名称2 read(write); 删除表锁：unlock tables;
加行锁：select * from t_account where id=1 for update; 或者 update t_account set balance=1 where id=1; 注意：id必须是唯一索引列或主键列，否则会变成临键锁。
间隙锁(Gap Lock)：它会锁住两个索引之间的区域。比如select * from t_account where id>8 and id<18 for update，就会在id为 (3,20] 的索引区间上加Gap Lock。
临键锁(Next-Key Locks)：它是Record Lock + Gap Lock形成的一个闭区间锁。比如select * from t_account where id>=1 and id<=10 for update，就会在id为[1,10]的索引闭区间上加Next-Key Lock。

锁详解

接下来对这些锁进行详细解说和测试。

表锁

每次锁住整张表，粒度大，发生锁冲突概率高，并发低；开销小，加锁快；不会出现死锁。

手动增加表锁sql

lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

例：（注意引擎是InnoDB）

DROP TABLE IF EXISTS `t_mylock`;
CREATE TABLE `t_mylock` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_mylock` VALUES (1, 'a');
INSERT INTO `t_mylock` VALUES (2, 'b');
INSERT INTO `t_mylock` VALUES (3, 'c');
INSERT INTO `t_mylock` VALUES (4, 'd');

测试读锁：

给表加读锁后，当前session和其他session都可以读该表。

当前session插入或者更新锁定的表会报错，其他session插入或更新则会等待。

解锁后可以插入数据。

测试写锁：

上图得知：针对被锁住的数据，可以被当前的事务读和写。

图1：

图2：

图3：

图1、2、3得知：其他session 开启的事务读和写都会被阻塞，默认30秒后自动释放。

行锁

注意：仅InnoDB支持事务和行锁；MyIsam不支持事务，只支持表锁。

每次锁住一行数据，粒度小，发生冲突概率低；开销大，加锁慢；会出现死锁。mysiam不支持行锁，innorDB支持行锁。

行锁测试（注意引擎是InnoDB）：

修改 id=1 的一行数据，会自动给这行数据自动加上行锁和写锁。

这时候用其他的事务修改 id=1 这行数据会被阻塞。但是可以读这条数据，因为InnoDB的读操作使用mvcc（多版本并发控制）机制，不需要加锁。

这时候修改其他行的数据可以成功，说明只给 id =1 的一行数据加上了行锁。

间隙锁(Gap Lock)：

测试环境：mysql5.7，InnoDB，默认的隔离级别（RR）

在隔离级别为可重复读(Repeatable read)生效。间隙锁会封锁索引的间隙，禁止往索引区间内插入数据，在某些情况下可以解决幻读问题。

使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

产生间隙锁的条件（RR事务隔离级别下）：

使用普通索引加锁；
使用多列唯一索引加索；
使用主键索引或唯一索引锁定多行记录或锁定不存在的记录。

测试数据：

DROP TABLE IF EXISTS `t_mylock`;
CREATE TABLE `t_mylock` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_mylock` (`id`,`name`) VALUES (1,'a');
INSERT INTO `t_mylock` (`id`,`name`) VALUES (5,'e');
INSERT INTO `t_mylock` (`id`,`name`) VALUES (7,'g');
INSERT INTO `t_mylock` (`id`,`name`) VALUES (10,'j');

在session1下执行SELECT * FROM t_mylock WHERE id BETWEEN 5 and 7 FOR UPDATE; 则其他事务无法添加和修改 (1, 7] 之间的数据。

从id索引分析到 t_mylock 表隐藏间隙：

(-∞, 1]
(1, 5]
(5, 7]
(7, 10]
(10, +∞]

1.只使用记录锁不会产生间隙锁。

session1:

begin
SELECT * FROM t_mylock where id=5 for update;
-- commit;

sessoin2:

INSERT INTO `t_mylock` (`id`,`name`) VALUES (4,'a'); -- 成功
INSERT INTO `t_mylock` (`id`,`name`) VALUES (6,'a'); -- 成功

session1的查询只会锁住一行记录锁，不会产生间隙锁。

还原测试数据继续测试。

2.使用主键索引/唯一键索引范围查询产生间隙锁

session1：

begin;
SELECT * FROM t_mylock where id between 5 and 7 for update;
-- commit;

session2：

insert into t_mylock(id,name) values (3,'a');  -- 成功
insert into t_mylock(id,name) values (4,'a');  -- 成功
insert into t_mylock(id,name) values (5,'a');  -- 阻塞
insert into t_mylock(id,name) values (8,'a');  -- 阻塞
insert into t_mylock(id,name) values (10,'a'); -- 阻塞
insert into t_mylock(id,name) values (11,'a'); -- 成功

测试结果：(5, 7]和(7, 10]这两个区间被加了区间锁不能插入数据，其他区间都可以。给[5, 7]区间加锁的时候，会锁住(5, 7]和(7, 10]这两个区间。

还原测试数据继续测试。

3.测试锁住不存在的数据

session1：

begin;
SELECT * FROM t_mylock where id = 3 for update;
-- commit;

session2：

insert into t_mylock(id,name) values (2,'a');  -- 阻塞
insert into t_mylock(id,name) values (4,'a');  -- 阻塞
insert into t_mylock(id,name) values (6,'a');  -- 成功

测试结果：给一条不存在的数据加锁的时候会产生间隙锁。

4.测试普通索引的间隙锁

测试数据脚本：

DROP TABLE IF EXISTS `t_mylock`;
CREATE TABLE `t_mylock` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_mylock` (`id`,`name`) VALUES (3,'a');
INSERT INTO `t_mylock` (`id`,`name`) VALUES (5,'e');
INSERT INTO `t_mylock` (`id`,`name`) VALUES (8,'g');

session1：

begin;
select * FROM t_mylock where name='e' for update;
-- commit;

name索引隐藏间隙：

1.(-∞, a]

2.(a, e]

3.(e, g]

4.(g, +∞]

session2：

insert into t_mylock(id,name) values (2, 'a');  -- 成功
insert into t_mylock(id,name) values (4, 'a');  -- 阻塞
insert into t_mylock(id,name) values (5, 'b');  -- 阻塞
insert into t_mylock(id,name) values (5, 'e');  -- 阻塞
insert into t_mylock(id,name) values (6, 'g');  -- 阻塞
insert into t_mylock(id,name) values (7, 'g');  -- 阻塞
insert into t_mylock(id,name) values (9, 'g');  -- 成功
insert into t_mylock(id,name) values (10, 'h');  -- 成功

测试结果：区间 (a, e] 、 (e, g] 被锁住，a到g的数据不能插入。但是有个奇怪的现象， (2, 'a') 和 (9, 'g')却执行成功了，这是怎么回事呢。

下面用一张索引的叶子节点图示意：

未命名文件.jpg

InnorDb的行锁是针对索引加的，当name相等的时候，id会按照大小进行排序。锁住e的时候会根据数据形成(a,3)到(g,8)的区间。

结论：

1.在普通索引上只要加锁都会产生间隙锁，而主键索引或唯一索引只有锁住多行才会加索。

2.普通索引上间隙锁，是先根据普通索引排序，然后再根据主键索引排序，锁住原始数据中间的数据。

5.无索引行锁会升级为表锁

DROP TABLE IF EXISTS `t_mylock`;
CREATE TABLE `t_mylock` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_mylock` (`id`,`name`) VALUES (3,'a');
INSERT INTO `t_mylock` (`id`,`name`) VALUES (5,'e');
INSERT INTO `t_mylock` (`id`,`name`) VALUES (8,'g');

session1：

begin;
select * FROM t_mylock where name='e' for update;
-- commit;

session2:

insert into t_mylock(id,name) values (1, 'a');    -- 阻塞
insert into t_mylock(id,name) values (100, 'z');  -- 阻塞

结论：给非索引字段加行锁会升级为表锁。

临键锁(Next-key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like'innodb_row_lock%';

对各个状态量的说明如下：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits:系统启动后到现在总共等待的次数

查看被锁住的表的sql：

查询是否锁表
show OPEN TABLES where In_use > 0;
查看所有进程
MySQL:
 
show processlist;
mariabd:
 
show full processlist;
查询到相对应的进程===然后 kill id
 
杀掉指定mysql连接的进程号
kill $pid
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
查看innodb引擎的运行时信息
show engine innodb status\G;
查看造成死锁的sql语句，分析索引情况，然后优化sql语句；
 
查看服务器状态
show status like '%lock%';
查看超时时间：
show variables like '%timeout%';

总结：

MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时，因为有mvcc机制不会加锁。但是update、insert、delete操作会加写锁。
InnorDb的行锁（包括记录锁、间隙锁和临键锁）是针对索引加的，如果加锁字段没有索引会升级为表锁。
记录锁会锁住一行记录，被锁住后其他事务修改该数据会被阻塞。
间隙锁只在可重复读（Repeatable read）和串行化（Serializable）隔离级别下产生。
间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据。
间隙锁会在以下情况下产生：(1).使用普通索引加锁；(2).使用多列唯一索引加索；(3).使用主键索引或唯一索引锁定多行记录或锁定不存在的记录。

五. 死锁

测试数据脚本：

DROP TABLE IF EXISTS `t_mylock`;
CREATE TABLE `t_mylock` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_mylock` (`id`,`name`) VALUES (1,'a');
INSERT INTO `t_mylock` (`id`,`name`) VALUES (2,'b');

什么是死锁

死锁定义：两个事务互相持有对方需要的锁，互相等待对方释放，并且都不释放。

例：

session1：

begin;
select * from t_mylock where id=1 for update;
select sleep(10);
select * from t_mylock where id=2 for update;
commit;

session2：

begin;
select * from t_mylock where id=2 for update;
select sleep(10);
select * from t_mylock where id=1 for update;
commit;

结果：

session1:

session2:

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁。

查看近期死锁日志信息：show engine innodb status;

为什么会形成死锁

mysql并发控制有两种方式，一个是MVCC（多版本并发控制），用于读的时候。令一个是2PL（两阶段锁协议），用于写的时候。使用2PL加锁的时候就有可能产生死锁。

2pc分两个阶段：1.获取锁。2.释放锁。

2pc不会一次将所有需要使用的数据加锁，并且在加锁阶段没有顺序要求，所以这种并发控制方式会形成死锁。

mysql如何处理死锁

等待超时（innodb_lock_wait_timeout=50s）。
发起死锁检测，主动回滚一条事务，让其他事务继续执行（innodb_deadlock_detect=on）。

如何避免死锁

修改的时候将表顺序保持一致。
合理使用索引，缩小锁范围，最好使用主键索引或唯一索引检索数据。
尽可能减少检索范围，避免间隙锁。
将大事务拆分成小事务。

名猿陈大浏

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
2
评论
mysql - 第10课 - 深入理解mysql事务隔离级别与锁机制（mysql各种锁，mysql事务隔离级别实现原理）

官网文档：https://dev.mysql.com/doc/refman/5.7/en/导图（用于总结和复习）一. 事务与事务的四大特性（ACID）事务定义：事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作；这些操作作为一个整体一起向系统提交，要么都执行、要么都不执行；事务是一组不可再分割的操作集合。ACID：原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。一致性(Consistent) ：在事务开始和
复制链接

扫一扫