深入理解Mysql锁与事物隔离级别

深入理解Mysql锁与事物隔离级别

1. 锁定义
   锁是计算机协调多个进程或线程并发访问某一资源的机制。
   在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用之外，数据也是一种供需要用户共享的资源。如何保障数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
2. 锁分类

• 从性能上可以分为乐观锁（用版本对比来实现）和悲观锁。
• 从对数据库的操作类型来分，分为读锁和写锁（都属于悲观锁）。
  读锁（S锁，共享锁）：针对同一份数据，多个读操作线程可以同时进行而不会互相影响。
  写锁（X锁，排他锁）：当前写操作没有释放锁之前，它会阻断其它写锁和读锁。
  IS锁、IX锁：意向读锁、意向写锁，属于标级锁。S和X主要针对行级锁。在对表记录添加S\X锁之前，会先对表添加IS\IX锁。

S锁：事物A对记录添加了S锁，可以对记录进行读操作，不能做修改，其它事物可以对该记录加S锁，但是不能追加X锁，需要追加X锁，需要等待记录的S锁全部释放。

X锁：事物A对记录添加了X锁，可以对记录进行读和修改，其它事物不能对记录做读和修改操作。

• 从对数据的操作粒度上分，分为表锁和行锁。

2.1 表锁
每次操作都会锁住整张表。开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突概率最高，并发度最低。
2.1.1 基本操作

‐‐建表SQL
CREATE TABLE mylock(
id INT (11) NOT NULL AUTO_INCREMENT,
NAME VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
‐‐ 插入数据
INSERT mylock (id, NAME) VALUES (1, ‘a’);
INSERT mylock (id, NAME) VALUES (2, ‘b’);
INSERT mylock (id, NAME) VALUES (3, ‘c’);
INSERT mylock (id, NAME) VALUES (4, ‘d’);

• 手动增加表锁
  lock table 表名称 read(write),表名称2 read(write);
• 查看当前数据库哪些表加了锁
  show open tables;
• 删除表锁
  unlock tables;

2.1.2 分析（加读锁） mysql版本：8.0.11

查看加锁结果：

对A、B两个session连接执行查询操作：

执行修改操作：

结论：当前session和其它session都可以读该表。
当前session中插入或者更新锁定的表都会报错，其它session插入或者更新会被阻塞，等到A客户端执行释放锁命令之后才可以执行完成。
2.1.3 分析（加读锁）

当前session会话（执行加读锁命令的会话）对该表的增删改查都没有问题，其它session对该表的所有操作都会被阻塞。
2.1.4 结论

读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写操作都阻塞。。

2.2 行锁

每次操作锁住一行数据。加锁开销大，加锁慢，会出现死锁，锁粒度最小，发生锁冲突概率最低，并发读最高。InnoDB行锁是通过对索引数据页上的记录加锁实现的。

InnoDB与MYISAM的最大不同有两点：
- 支持事物
- 支持行锁

2.2.1 行锁支持事物
- 事物（Transaction）及其ACID属性
事物由一组SQL语句组成的逻辑处理单元，事物具有以下4个属性。
原子性（Atomicity）：事物是一个原子操作单元，包含的sql语句，对数据的操作，要么全部执行，要么全部不执行。
例如执行一条修改语句：会先在Buffer Pool 修改，然后刷新到磁盘，可能会出现下面两种情况

• 事物 提交成功，但是Buffer Pool的脏页（内存数据页和磁盘数据页上的内容不一致），数据库宕机，如何保证修改的数据生效？ RedoLog
• 事物还没提交，但是Buffer Pool的脏页刷盘了，如何保证不该存在的数据撤销？ UndoLog

每一个写事物，都会修改Buffer Pool，从而产生相应的Redo（物理日志）/Undo（逻辑日志）日志，先修改数据，在写日志。在Buffer Pool中的页被刷到磁盘之前，这些日志信息会先写入到日志文件中，如果Buffer Pool中的脏页没有刷新成功，此时数据库挂了，那数据库下次启动之后，可以通过redo 日志将数据恢复出来，保证脏页写的数据不会丢失。Undo日志保存了事物发生之前的数据的一个版本，可以用于事物回滚，同时可以提供MVCC多版本并发控制下的读。

一致性（Consistent）：在事物开始之前和事物结束之后，数据库的完整性限制未被破坏。包含两方面内容，分别是约束一致性和数据一致性。

• 约束一致性：创建表结构 时指定的外键、唯一性索引等约束

• 数据一致性：是一个综合性的规定，由原子性、隔离性、持久性共同保证的结果

一致性也可以理解为数据的完整性。数据的完整性是通过原子性、隔离性、持久性来保证的，而这三个特性又是通过Redo/Undo来保障的。逻辑上的一致性，包括唯一索引、外键约束、check 约束，这属于业务逻辑范畴。

ACID及它们之间的关系如下图所示，4个特性中3个与WAL有关，都需要通过Redo、Undo日志来保证。

隔离性（Isolation）：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。
InnoDB支持的隔离性从低到高分别为：读未提交、读已提交、可重复读、串行化。锁和MVCC技术就是用于保障隔离性的。

持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。Mysql的持久性也与WAL（Write-Ahead Logging，先写日志，在写磁盘）技术相关，redo log 在系统Crash重启之类的情况时，可以修复数据，从而保障事物的持久性。通过原子性可以保证逻辑上的持久性，通过存储引擎的数据刷盘可以保证物理上的持久性

一个提交动作的触发的操作：binlog落地、发送binlog、存储引擎提交、flush_logs、check_point、事物提交标记等。都是保障其数据完整性、持久性的手段。

2.2.2 并发事物会带来的问题

更新丢失（Lost Update）
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题――最后的更新覆盖了由其他事务所做的更新。
例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读（Dirty Read）
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理。此时第一个事物进行回滚，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
一句话：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读(Non-Repeatable Reads)
一个事务在读取某些数据后的某个时间，同一个事物内再次读取以前读过的数据，却发现其读出的数据（同一条数据）已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性。

幻读(Phantom Reads)
一个事务接相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读“。
一句话:事务A读取到了事务B体提交的新增数据，不符合隔离性。

2.2.3 解决以上所述问题
‘脏读’、‘不可重复读’、‘幻读’ 都是数据库一致性问题，必须由数据库提供一定的事物隔离机制来解决。

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

数据库的事物隔离级别越严格，并发副作用越小，但付出的代价也就越大，因为事物隔离实质上就是使事物在一定程度上’串 行化‘进行，这显然与并发是矛盾的。
同时，不同的应用对读一致性和事物隔离程度的要求是不同的，比如许多应用对’不可重复读’和’幻读’并不敏感，可能更关心数据并发访问能力。

查看当前数据库的事物隔离级别：select @@global.transaction_isolation,@@transaction_isolation;;
设置全局隔离级别： SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
设置会话隔离级别：SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. 行锁与隔离级别分析

CREATE TABLE `account` ( 
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT, `name` VARCHAR ( 255 ) DEFAULT NULL, 
	`balance` INT ( 11 ) DEFAULT NULL, PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `test`.`account` ( `name`, `balance` ) VALUES ( 'lilei', '450' );
INSERT INTO `test`.`account` ( `name`, `balance` ) VALUES ( 'hanmei', '16000' );
INSERT INTO `test`.`account` ( `name`, `balance` ) VALUES ( 'lucy', '2400' );

3.1 行锁演示 （innodb）
一个session开启事物不自动提交，另一个session更新 同一个记录会被阻塞，更新不同记录不会阻塞。

查看mysql session是否是自动提交：show variables like ‘autocommit’;(on 自动提交 off 不是自动提交)
两种方式设置自动提交事物和关闭自动提交事物（mysql默认自动提交事物）
第一种 ：set autocommit=off; set autocommit=on;
第二种 set autocommit=0; set autocommit=1;
如果执行SQL语句之前 执行了begin 命令，mysql会放弃自动提交事物，需要手动执行 commit 命令提交事物。

• 手动开启事物：

• 在事物内执行修改语句，同时在另一个session B对此表进行修改操作（不是操作的同一条数据）
  
  发现可以修改成功

• 在session B中 对同一条数据进行修改操作
  
  session B一直处于阻塞状态

• 等待session A 执行Commit 命令，session B语句执行成功

结论：当对数据操作使用到索引字段时 会使用行级锁。一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更 新不同记录不会阻塞

3.2 读未提交（read-uncommitted）

• 设置session A 的隔离模式为 read-uncommitted，查询表初始化值。
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  
• 在 session A 事物未提交之前，使用 session B 对其中一条数据做操作：
  

此时可以看到 session B已经修改成功并未提交事物。

• 虽然session B 还没有提交，但是session A 已经可以查询到B更新的数据。
  
• 此时若session B 的事物因为某种原因回滚，所有的操作都会被撤销，那么session 此时查到的数据就会是脏数据。
  
• 在客户端执行更新语句 update account set balance = balance - 50 where id = 1; id = 1的值没有变为350，而是400，可能现在会想，脏读并没有带来数据不一致性，session A 中计算结果是正确的。但是在代码中，可能会使用 session A中的 400 - 50 = 350 来计算，并不知道其它会话回滚了，就会出现脏读现象。解决这个问题可以采用 读已提交 的隔离级别。
  
  3.3 读已提交（read-committed）
• 设置session A隔离级别为 read-committed，查询表account初始化数据。
  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  
• 在session A 事物提交之前，通过session B更新数据：
  
  此时 session B事物还没有提交，session A不可以查询到已经更新的数据，解决了脏读问题。
• 提交session B的事物
• session B 事物提交之后，session A 执行查询，结果与上一步查询结果不一致，两次查询均在同一个事物内，说明出现了不可重复读问题。
  
  3.3 可重复读（REPEATABLE-READ）
• 设置session A隔离级别为 REPEATABLE-READ，查询表account初始化数据。
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  
• 在session A事物提交之前，通过session B修改数据 并提交
  
• session B事物提交之后，在session A中执行查询操作，发现与之前结果一致，没有出现不可重复读问题。
  
• 此时在session A执行update account set balance = balance - 50 where id = 1; 结果balance没有变为 350 -50 =300，id=1 的balance的值是用session B提交事物之后中的300 来算的，所以是250，数据一致性没有被破坏。这是因为在可重复读的隔离级别下使用了MVCC（Multi-Version Concurrency Control）多版本并发控制机制，解决了并发读的问题。

3.4 验证幻读

• 重新打开session B，插入一条新的数据并提交
  
• 在session A中查询表account所有记录，没有查出新增数据
  
  但是在session A中执行 update account set balance = balance -50 where id =7;能更新成功，再次查询能查询到session B新增数据，说明出现了幻读。

Mysql默认级别是 RR，有什么方法解决幻读问题？

间隙锁在某些 情况下可以解决幻读问题
在session A 中执行update account set name = ‘zhaoliu’ where id >5 and id < 20;

session B无法在这个范围内对所包含的间隙里插入或修改任何数据，会一直处于阻塞状态，直到锁超时。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则会由行锁升级为表锁。

session A在执行update account set balance = 1000 where name = ‘zhaoliu’; 语句时并没有使用到索引，session B执行任何修改操作都会被阻塞。

锁定某一行数据还可以使用 lock in share mode(共享锁) 和 for update （排它锁）。

例如select * from account where id =1 for update; 这样其它session 只能读取这行数据，修改会被阻塞，直到锁定这行的session提交。

3.5 死锁
在 RR隔离级别下：
session A执行： select * from account where id =1 for update;
session B 执行： select * from account where id =2 for update;
session A执行：select * from account where id =2 for update;
session B执行：select * from account where id =1 for update;

可以看到mysql自动检索到死锁并回滚产生死锁的那个事物，但是有些情况mysql无法自动检测死锁。
查看近期死锁日志信息：show engine innodb status \G;

3.6 行锁分析
通过检查InnoDB_row_lock 状态变量来分析系统上的行锁争夺情况：
show status like ‘innodb_row_lock%’;

Innodb_row_lock_current_waits：当前正在被锁定的session数量
Innodb_row_lock_time：系统启动到现在被锁定的总时间
Innodb_row_lock_time_avg：每次等待所花费的平均时间
Innodb_row_lock_time_max：系统启动到现在等待时间最长的一次花费时间
Innodb_row_lock_waits：系统启动到现在总共等待的次数

比较有参考价值的三个：
Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）
尤其是当等待次数很多，而且每次等待时间也很长的时间，就需要分析系统中为什么会有如此多的等待，具体问题具体分析。

3.7 优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无所引行锁升级为表锁
• 合理设计所引，尽量缩小锁的范围
• 尽可能减少检索条件范围，避免间隙锁
• 尽量控制事物大小，减少锁定资源和时间长度，设计事物加锁的sql尽量放在事物最后执行

4 MVCC

多版本控制MVCC，也就是Copy on Write 的思想。MVCC除了支持读和读并行，还支持读和写、写和读的并行，但为了保证数据一致性，写和写是无法并行的。

在事物1开始写操作的时候会copy一个记录的副本，其它事物操作会读取这个记录副本(快照读)，因此不会影响其它事物对此记录的读取，实现写和读的并行。

4.1 MVCC概念

MVCC(Multi Version Concurrency Controller）被称为多版本控制，是指在数据库中为了实现高并发的数据访问，对数据库进行多版本的管理。并通过事物的可见性来保证事物能看到自己应该看到的数据版本。多版本控制很巧妙的将稀缺资源的独占互斥转换为并发，大大提高了数据库的吞吐量以及读写性能。
多版本如何生成？每次事物修改操作之前，都会在Undo Log中记录修改之前的事物状态和事物id，该备份记录可以用于其它事物的读取，也可以进行必要时数据的回滚。

4.2 MVCC实现原理

MVCC最大的好处是 读读、读写不冲突。在读多写少场景下，极大提高了并发性能，但是现在只支持 在 RC、RR隔离级别下。

MVCC并发控制下，读操作可以分为两类：快照读（Snapshot Read）与当前读（Current Read）。

• 快照读：读取的是记录的快照版本（有可能是历史版本，每个session 都会生成对应的快照），不用加锁。（select 操作时会触发）
• 当前读：读取的是记录的最新版本，并且返回的是当前记录都会加锁，保证其它事物不会对此记录进行修改。（触发条件：select…for update 或lock in share mode，insert/update/delete）

如下面更新案例诠释MVCC在多版本控制的体现。

假设 F1～F6 是表中字段的名字，1～6 是其对应的数据。后面三个隐含字段分别对应该行的隐含ID、事务号和回滚指针，如下图所示。

更新流程：加入一条数据是刚 Inser的，DB_ROW_ID为1，其它两个字段为空。当事物1更改此条记录时，会进行如下操作

• 用排它锁锁定改行，记录到Redo log
• 把改行修改前的值复制到Undo log 用于事物回滚
• 修改当前行的值，填写事物编号，使回滚指针指向Undo log中修改前的值

下面事物2操作，过程与事物1相同，此时Undo log中会有两条记录，并且通过回滚指针连在一起，通过当前记录的回滚指针回溯到该行创建时的初始内容

MVCC已经实现了读读、读写、写读并发处理，如果想进一步解决写写冲突，可以采用以下方案：

• 乐观锁
  乐观锁是相对于悲观锁而言的，不是数据库提供的功能，需要开发者自己实现。在操作数据库时想法很乐观，不做任何特殊处理，而是在进行事物提交的时候再去判断是否有冲突。

乐观锁实现的关键点：冲突的检测。
悲观锁和乐观锁都可以解决事物写写的并发，区别再与对并发率要求高的选择乐观锁，对并发率相对要求低的可以选择悲观锁。

实现原理：
（1）使用版本字段（version）

（2）使用时间戳（Timestamp）

• 悲观锁

悲观锁，广义上来说 行锁、表锁、读、写、共享锁、排它锁，都属于悲观锁。
1）表级读锁：
加锁

lock table 表名称 read|write，表名称2

read|write;

查看表上加过的锁

show open tables；

释放表锁

unlock tables；

表级读锁：当前表追加read锁，当前连接可以进行读操作，但是当前连接增删改操作报错，其它连接增删改查被阻塞。

表级写锁：当前表追加write锁，当前连接可以进行增删改查操作，其它连接对该表所有操作都被阻塞（包括查询）

• 共享锁（行级锁-读锁）
  简称S锁。多个事物可以对同一数据共享一把锁，都能访问数据，其它连接只读不可修改。使用共享锁的方法 select … lock in share mode，只适用于查询语句。

总结：事物使用了共享锁（读锁），其它事物只可以读取，不可以修改，修改操作被阻塞。

• 排它锁（行级锁-写锁）
  简称X锁，排它锁就是不能与其它所共存，一个事物获取了一个数据行的排它锁，其它事物就不可以对该行记录做任何操作，包括获取该行的共享锁。
  使用排它锁的方法是在SQL末尾加上for update，InnoDB引擎默认会在 update、delete语句后面加上 for update。

总结：事物使用了排它锁（写锁），当前事物可以读取和修改，其它事物不可以做任何操作，也不可以获取记录锁（select … for update）。如果查询没有使用到所有，会锁住整个表的记录。

4.3 行锁原理

InnoDB行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

• Record Lock：锁定当前行记录（记录锁，RC、RR隔离级别下都支持）
• GAP Lock：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR隔离级别支持）
• Next-key Lock锁：记录所和间隙锁的组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR隔离级别下支持）

在RR隔离级别下，InnoDB对于记录的加锁都是先采用 Next-key Lock锁，但是当操作的数据含有唯一索引时，InnoDB会对Next-key Lock进行优化，降级为RecordLock，仅锁住索引本身数据而非范围数据。

1）select… from 语句：innoDB引擎采用MVCC机制实现了非阻塞读，所以对普通的select语句，InnoDB不加锁。

2）select… from lock in share mode 语句：追加了共享锁，InnoDB会使用Next-key Lock锁进行处理，如果发现的唯一索引，就对记录降级为Record Lock锁。

3）select… from for update语句：追加了排它锁，InnoDB会使用Next-key Lock锁进行处理，如果发现唯一索引，可以降级为RecordLock锁。

4）update… where 语句：InnoDB会使用Next-key Lock锁进行处理，如果发现唯一索引，可以降级为RecordLock锁。

5）delete… where语句：InnoDB会使用Next-key Lock锁进行处理，如果发现唯一索引，可以降级为RecordLock锁。

6） insert 语句：InnoDB会在将要插入的那一行设置一个排它的RecordLock锁。

• 查看表test1 表结构
  
  表test1 did字段为普通索引

• session A 开启事物，执行select * from test1 where did = 5 for update; 语句

• session B中执行insert into test1 values(4,‘444’,100); 语句

session B被阻塞。

sessionB中执行insert into test1 values(6,‘666’,100); 语句
session B同样被阻塞。

sessionB中分别执行insert into test1 values(2,‘222’,100);insert into test1 values(100,‘100’,100);语句

发现都可以执行成功，这是session A在执行
select * from test1 where did = 5 for update 语句时 InnoDB使用Next-key Lock锁，对应test1表中的数据，对did(3-5)、（5-11）之间的范围加了锁，又由于表test1没有唯一索引，所以session A在这两个范围内对数据进行操作时会被阻塞。

对 "update t1 set name=‘XX’ where id=10"语句分析InnoDB对不同索引的加锁行，RR隔离级别下。

• 主键加锁
  
  加锁行为：仅在id=10的主键索引记录上加X锁。

• 唯一键加锁
  
  加锁行为：先在唯一索引id上加X锁，然后再id=10 的主键索引上加X锁。

• 非唯一键加锁
  
  加锁行为：对满足id=10条件的记录和主键分别加X锁，然后在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)-(11,f)范围分别加Gap Lock。

• 无索引加锁
  
  加锁行为：表里所有的行和间隙都会加X锁。（（当没有索引时，会导致全表锁定，因为InnoDB引擎锁机制是基于索引实现的记录锁定）