Mysql事务篇

思维导图

一、事务的特性

1. 原子性：一组事务内的多个操作，要么全部执行成功，要么全部执行失败。由undo log日志来实现。
2. 一致性：一组事务内数据的变化是一致的，比如A向B转账300元，A账户减少300的同时B账户一定是增加300。由其他三个特性共同支撑。
3. 隔离性：一组事务内执行的操作，不能受到其他事务的干扰否则会导致（脏读、脏写、幻读、不可重复读）问题。由各种锁以及MVCC版本机制来实现。
4. 持久性：一组事务内的操作执行完以后一定是要保存到磁盘的，不能事务完成以后数据没有被持久化。由redo log日志来实现。

二、并发事务

2.1 并发事务带来的问题

多个事务同时对一条数据进行操作时可能会出现脏写、脏读、幻读、不可重复读问题：

脏写：事务内更新一条记录后被其他事务更新覆盖。

脏读：事务内读到了其他事务没有提交的数据。

不可重复读：与幻读类似，但是特指修改的数据，事务内两次相同的sql执行结果不一致，读取到了其他事务提交的修改数据。

幻读：特指新增或删除，事务内两次相同sql执行的结果不一致，读取到了其他事务提交的新增或删除数据。

2.2 事务的隔离级别

读未提交、读已提交、可重复读、串行化

mysql默认的隔离级别是可重复读

查看mysql事务隔离级别：

show variables like 'tx_isolation';
或
show variables like 'transaction_isolation';

设置mysql事务隔离级别：

set tx_isolation = 'REPEATABLE-READ'
或
set transaction_isolation = 'REPEATABLE-READ'

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不会	可能	可能
可重复读	不会	不会	可能
串行化	不会	不会	不会

隔离级别的实现方式：

隔离级别	实现方式
读未提交	MVCC版本控制 undo log 链
读已提交	MVCC版本控制 undo log 链
可重复读	MVCC版本控制 undo log 链
串行化	锁【读锁（lock in share mode）和写锁（for update）】

2.3 案例分析

示例表：

CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
INSERT INTO `account` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO `account` (`name`, `balance`) VALUES ('hanmei', '16000');
INSERT INTO `account` (`name`, `balance`) VALUES ('lucy', '2400');

2.3.1 读未提交

原始数据

打开一个客户端A：

1. 当前事务隔离级别设置为读未提交（read-uncommitted）。
2. 开启事务（BEGIN）。
3. 第一次查询ID为1的余额（balance）。
4. 第二次查询ID为1的余额（balance）。
5. 提交事务。

注意：先执行到第三步查看结果

打开一个客户端B：

1. 开启事务。
2. 将ID为1的余额减去50。
3. 不提交事务

打开客户端A：

执行第二次查询：

客户端A在事务中读取到了别的事务没有提交的数据。一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据：

2.3.2 读已提交

原始数据

打开一个客户端A：

1. 当前事务隔离级别设置为读未提交（read-committed）。
2. 开启事务（BEGIN）。
3. 第一次查询ID为1的余额（balance）。
4. 第二次查询ID为1的余额（balance）。
5. 提交事务。

注意：先执行到第三步查看结果

打开一个客户端B：

1. 开启事务。
2. 将ID为1的余额减去50。
3. 不提交事务

打开客户端A：

执行第二次查询：

此时并未读取到客户端B没有提交的数据，有效避免了读未提交带来的脏读问题。

2.3.3 可重复读

这个隔离级别在同一个事务中，第一次查询会从数据库中获取最新的数据，并为当前数据库生成一份快照版本，在事务内之后的查询操作都是从这份快照版本中获取数据，这个时候不管其他事务怎么修改数据，当前事务中的查询结果不会受到影响，如果当前事务对某一条数据进行了更新（实际是先读取最新的值再修改快照版本中这条数据的值），那么再次查询这条数据就是修改后的值。

快照读：读取当前快照版本数据，只针对SELECT语句。

当前读：读取数据库中的数据，针对INSERT、UPDATE、DELETE操作语句。

2.3.3.1 不可重复读演示

原始数据

（1）打开客户端A将隔离级别设置为读已提交，并执行第一次查询ID为1的余额。

（2）打开客户端B将ID为1的余额减去50并提交事务。

（3）打开客户端A再次执行查询sql，查看ID为1的余额变化

（4）客户端A两次查询结果对比如下，发现两次查询的结果不对，此时就出现了不可重复读的问题

2.3.3.2 可重复读设置

原始数据

（1）打开客户端A，将事务隔离级别设置为可重复读（ repeatable-read ），并执行第一次查询ID为1的余额

（2）打开客户端B将ID为1的余额减去50并提交事务。

（3）打开客户端A再次执行查询sql，查看ID为1的余额变化

（4）客户端A两次查询结果对比如下，两次查询的结果一致，这个隔离级别有效的解决了不可重复读的问题。

2.3.4 串行化

2.3.4.1 幻读的演示

如果当前事务隔离级别设置的为可重复读的级别，当前事务没有发生“当前读“的操作时是不会发生幻读的现象。

如果当前事务隔离级别设置的为可重复读的级别，当前事务发生了“当前读”的操作时会出现幻读的现象。

原始数据：

（1）打开客户端A，设置隔离级别为可重复读，查询一次数据，不提交当前事务，当前没有ID为4的数据。

（2）在表中插入ID为4的数据

（3）在客户端A中再次查询，不提交当前事务，执行结果：

（4）在客户端A中发生一次“当前读”的操作，然后再次去查询表数据会发现出现了ID为4的数据，这是因为UPDATE语句是当前读，会从数据库中读取数据重新设置到当前快照中，由此出现了幻读的现象。

2.3.4.2 串行化的设置

必须等当前事务执行完以后，资源才能被操作。如果当前事务查询的是全表，则会对整张表加锁；如果当前事务查询的是某条记录，则会对当前记录加上行锁。

原始数据：

（1）打开客户端A，并设置当前事务隔离级别，串行化（ serializable ），查询数据，不提交事务

（2）此时`account`这张数据表不会被做任何操作。

---

三、锁

控制资源访问安全的一种操作手段。

锁的分类可以统一为两大类：

3.1 乐观锁

乐观锁就是通过条件来控制，比如增加版本号字段（version），如果要修改某条数据又不想加写锁时我们可以这样：

select verson,blance from exa where id = 1;
set blance = 20000;
loading 5 min -- 中间执行代码一段时间
update exa set blance = blance,version=version+1 where id =1 and version ={version};

在代码运行的期间，为保证这条数据没有被其他客户端修改，我们增加一个版本号条件。这样可以避免在查询的时候去添加写锁，从而保证再更新之前的这段时间内其他客户端可以去对资源进行访问，提高了资源的并发能力。

乐观锁适合读多写少的场景，一方面是因为再此期间不会对行数据加锁，提高了并发读的能力，另一方面如果写多的场景就会发生多次update更新不成功的情况。

3.2 悲观锁

3.2.1 读锁

读锁又被称为共享锁，必须在事务中使用，否则会失效，事务结束后会释放写锁

特点：读读共享，读写互斥。

如何添加读锁：

select * from exq lock in share mode;

3.2.2 写锁

写锁又被称为排他锁，必须在事务中使用，否则会失效，事务结束后会释放写锁

特点：读写互斥，写写互斥。

如何添加写锁：

select * from exq for update;

3.2.3 锁粒度

3.2.3.1 行锁

特点：粒度小，并发高，会有死锁的问题出现。

innoDB中的行锁实际是对索引字段加的锁（会在索引对应的索引项上做出标记）。如果写的sql没有使用到索引，mysql 会自动升级为表锁（RR级别下会升级为表锁，RC级别下不会升级为表锁）。

例：对ID为1的数据添加行锁

-- 读锁
select * from exq where id = 1 lock in share mode;
-- 写锁
select * from exq where id = 1 for update;

为什么RR级别会升级为表锁：

RR这种级别为了保证可重复读，在扫描过的索引记录时为了防止被修改，这里对所有已经扫描过的数据进行了添加锁的操作。

3.2.3.2 表锁

特点：粒度大，并发低，不会出现死锁。一般只在数据迁移中使用。

-- 读锁
lock table 表名称 read,表名称2 read;
-- 写锁
lock table 表名称 write,表名称2 write;
-- 删除表锁
unlock tables;
--查看表锁情况
show open tables;

3.2.3.3 页锁

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

3.2.3.4 意向锁

这种锁是mysql自动加的，如果一个表中存在读锁或者写锁，mysql会在这个表上做上标记，便于添加表锁时的速度。因为添加表锁（写锁）时不能有任何的行锁出现，这时就要逐行去遍历，如果表上有标记就免去遍历的步骤。

3.3 乐观锁和悲观锁区别

乐观锁：在数据处理的过程中，持有“乐观态度”，认为很少和其他会话产生冲突。

悲观锁：在数据处理的过程总，持有“悲观态度”，认为很大概览会和其他会话产生冲突。

3.3.1 示例

悲观锁：认为在查询后到修改数据之间的时间数据会被其他客户端修改。

begin;
select * from exq where id =1 for update; 
update exq set blance = 100 where id = 1;
commit;

乐观锁：认为在查询后到修改数据之间的时间数据不会会被其他客户端修改。

select * from exq where id =1; 
update exq set blance = 100 where id = 1 and version = {version};

3.4 锁等待分析

通过检查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: 系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：
Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;  
-- 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;  

-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id

-- 查看锁等待详细信息
show engine innodb status;

死锁问题分析

set tx_isolation='repeatable-read';
Session_1执行：select * from account where id=1 for update;
Session_2执行：select * from account where id=2 for update;
Session_1执行：select * from account where id=2 for update;
Session_2执行：select * from account where id=1 for update;
查看近期死锁日志信息：show engine innodb status; 

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。

---

四、MVCC

MVCC是多版本并发控制的实现，如果没有MVCC，多个事务访问同一个资源时就需要串行执行，也就是完全依靠锁机制来实现资源安全。mysql在读已提交和可重复读这种隔离级别下均实现了MVCC机制。

MVCC是由undo日志链和readView一致性视图来实现的。

4.1 undo log链

在事务中，每对数据进行操作在undo log链中都会有一条相应的记录。在日志链中会有三个隐藏的标志（事务ID，回滚记录ID，提交标志）。

事务会对操作的行记录来生成日志链，相同行记录可能会被多个事务共同访问，由此共同组成了undo log日志链。

4.2 readView机制

一致性视图（readView）可以理解为再查询时去访问undo日志链中哪个版本的数据，是在事务中执行第一次查询时生成的。一致性视图（readView）是由如下部分组成的：

1. 在生成readView时所有未提交的事务ID数组（事务ID最小的称为MIN_ID）。
2. 在生成readView时最大的事务ID（称为MAX_ID）。

例如：现在有：100（未提交）、101、102（已提交）、103（未提交）四个事务，我们在101事务中生成一致性视图（readView），此时的readView是：[100,103] 103。

事务中的任何查询都要通过undo log版本链中最新的数据依次逐行去比对，比对规则如下：

1. 如果undo log版本的事务ID落在绿色部分(trx_id < min_id)，则可见，返回该数据集。
2. 如果undo log版本的事务ID落在红色部分(trx_id > max_id)，则不可见，继续向上一个版本记录比对。
3. 如果undo log版本的事务ID落在黄色部分(min_id <= trx_id <= max_id)，则分两种情况：

1. 1. 事务ID在未提交事务数组中，则不可见，继续向上一个版本记录比对。如果undo log版本的事务ID为当前的事务ID，则可见（特殊，发生了当前读）。
   2. 事务ID不在未提交事务数组中，则可见，返回该数据集。

读已提交和可重复读两种隔离级别下readView是不同的。

4.2.1 读已提交

每次查询的readView都是会重新生成一份，然后再通过可见性算法比对规则来获取数据。

4.2.2 可重复读

每次查询所使用的readView都是同一份，然后再通过可见性算法比对规则来获取数据。

---

问题

1. insert、update、delete语句为什么会被默认加写锁？

答：数据修改操作都会添加写锁，select操作可以通过 for update来添加写锁。mysql为了保证行数据的准确性才去加的锁，试想一下如果这些修改操作不加锁，两个客户端同时添加相同主键数据就会发生主键重复的情况。

1. 可重复读隔离级别如何配合间隙锁来解决幻读问题？
2. 如何查看指定表的表锁（以及是读锁还是写锁），如何删除指定表的表锁？