03.mysql事务隔离

事务隔离

• 1.事务要么全部成功，要么全部失败。
• 2.事务在引擎层
• 3.事务不是所有引擎都支持
• 4.原子性（Atomicity），一致性（Consistency），隔离性（Isolation），持久性（Durability）ACID

---

1.隔离性和隔离级别

• 1.数据库多个事务同时执行，可能出现脏读（dirty read）、不可重复度（nonrepeatable read）、幻读（phantom read）,事务隔离为了解决问题

• 2.隔离越严，效率越低；sql标准事务隔离：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）、串行化（serializable）

• 3.(1)读未提交：事务还未提交，它做的变更会能别的事务看到
  (2)读提交：事务提交后，他做的变更会被其他事务看到
  (3)可重复读：一个事务执行过程看到的数据，和这个事务启动看到的数据一致。早可重复读级别下，未提交变更对对其他事务不可见
  (4)串行化：对于同一行数据，‘写’会加‘写锁’，‘读’会加‘读锁’。写锁和读锁冲突时，后面访问的事务必须等前一个事务执行完成才能继续执行

两个事务：

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mYuLAxcp-1640873614888)(https://note.youdao.com/yws/res/3/WEBRESOURCEd876433b02447f9ed81f64696d2e0d33)]

若读未提交：v1的值是2，事务B未提交被A看到，v2、v3为2
若读提交：v1是1，v2是2，事务B在提交后才能被A看到更新，v3是2
若可重复读，v1是1，事务在执行期间看到的数据必须一致，v2是1，v3是2
若串行化，事务B将1改2会被锁，A提交B才继续执行，所以v1，v2是1，v3是2

• 4.实现时，数据库创建视图，访问以视图为主。

• 5.（1）可重复读级别下：视图是事务启动时创建，事务存在期间都用此视图
  （2）读提交级别下：视图在sql开始执行创建
  （3）读未提交级别下：直接返回记录上最新值，没有视图概念
  （4）串行化级别下：加锁的方式避免并行访问

• 5.Oracle数据库默认隔离级别为‘读提交’，Oracle迁移Mysql，要设置隔离级别为读提交
  隔离级别配置方式：
  将启动参数transaction-isolation的值设置成READ-COMMITTED。可以用
  showvariables来查看当前的值

mysql> show variables like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

---

2.事务隔离的实现

• 1.mysql记录更新同时记录回滚操作，记录上最新值，通过回滚，得到前一个状态值[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xPCK3ZrU-1640873614889)(https://note.youdao.com/yws/res/6/WEBRESOURCE8e0a7eb508aeb5e7bc9c2fa1435d1486)]
  如图，一条记录存在多个版本，这是多版本并发控制（MVCC）

• 2.回滚回收机制：系统会判断，当没有事务需要用到产生的回滚日志（系统中没有比这个回滚日志更早的read-view）回滚日志被删除

• 3.长事务内系统会存在很老的事务视图，事务随时可能访问数据库任何数据，so，在事务提交前，库内可能用到的回滚记录必须保留，占用大量存储空间、 并且占用资源锁也可能拖垮整个库

3.事务的启动方式

• 1.显式启动事务语句，begin和start transaction，提交语句commit，回滚语句rollback

• 2.线程提交自动关：set autocommit=0，只执行一个select，事务启动且不自动提交。持续存在到主动执行提交或回滚语句或断开连接。

• 3.连接成功先set autocommit=0命令会导致下面查询都会在此事务中，若连接很长会导致长事务。
  建议总是使用set autocommit=1, 通过显式语句的方式来启动事务。

• 4.set autocommit=1情况下，用begin显式启动的事务，如果执行commit则提交事务；执行commit work and chain则提交事务冰自动启动下一个事务，省去begin开销，明确知道语句是否处于事务中

-查询information_schema库的innodb_trx这个表中查询持续时间超过60s的长事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

---

如何避免长事务对业务的影响

1.应用开发角度：
1）确认是否使用set autocommit=0
2）确认是否有不必要的只读业务
3）通过SETMAX_EXECUTION_TIME命令，控制每个语句执行的最长时间，避免单个语句执行时间过长

2.从数据库端：

1. 监控informasdation_
   schema.Innodb_
   trx表,设置长事务阀值，超过阀值报警/kill==
   2）Percona的pt-kill工具
   3）业务功能测试前输出所有的gengeral_log,f分析日志提前发现问题
   4）把innodb_undo_tablespaces设置成2（或更大的
   值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便