数据库 事务

MySql 笔记[事务隔离]

ACID:atomicity(原子性),consistency(一致性),isolation(隔离性),durability(持久性)

1.隔离性与隔离级别

多个事务同时执行时，可能出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)的问题，为了解决这些问题 ，有了***隔离级别***的概念。

1. 脏读：事务A读到事务B未提交的数据。下面表格，A2读取的c=2 就属于脏读，因为B2事务还未提交

2. 不可重复读：事件A第一次查询得到一行记录row1,事处B提交更改后，事件A再次查询row1得到不同的结果。A1、A2、A3查询到的结果可能不同，属于不可复复读的错误。

3. 幻读：事件A第一次查询得到row1 ，但其他事件提交修改后，事务A第二次查询得到两条记录row1和row2

   事务A	事务B
   A1) 启动事务，查询得到 row1 其中列c=1	B1) 启动事务
   	查询得到row1 其中c=1
   	B2) 设置c=2
   A2) 查询得到row1,其中c=?
   	B3) 提交事务
   A3) 查询得到row1,其中c=?
   提交事务

   幻读：其中C2出现了幻读

   事务C	事务D
   C1） 事务开启，select * from T where v=1;查询结为0行	D1）开启事务
   	D2）insert into t (v) values (1);
   	D4）事务提交
   C2）select * from T where v=1;查询结为1行 相比C1的查询结果，多了一条，出现了幻读
   C3）事务提交

   解决如上问题，就引入了隔离级别，但隔离与效率是互斥的，隔离级别越高，效率越低，所以需要综合考虑，找到一个平衡点

   隔离级别：

   1. 读未提交：一个事务还未提交时，他做的变更可被其他事务看到，如表1中，A2 查到c=2 可以看到事务B的改动，这是事务B还未提交。

   2. 读提交：一个事务必须提交后，所做的变更才可以被其他事务看到 ，如A2中 查询c=1 但在A3 中查询得到的c=2;因为A3是在事务B提交后，查询的。

   3. 可重复读：一个事务执行过程中看到的数据，总是和事务启动时看到的数据是一致的，如 A1 A2 和A3 查询到的都是c=1。

   4. 串行化：对于同一行的记录：“写”会加“写锁”，“读”会加“读锁”，当出现读写锁冲突时，后访问的事务，必须等到前一个事务执行完成，才能继续执行。（不会出现幻读）

      读、写锁，什么时间释放？事务执行完释放，加个读锁后，其他事务可以读，但不可写。

   实现：

   数据库会创建一个视图，访问的时候以视图的逻辑结查为准。

   “可重复读”隔离级别：这个视图是在事务启动时创建的，整个事务存在期间都用这个视图；

   “读提交”隔离级别：视图是在每个SQL语句执行时创建的；

   “读未提交”隔离级别：没有视图概念，直接返回记录上的最新值。

   “串行化”隔离级别：直接使用加锁的方式来避免并行访问：

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

   可使用 show variable like 'transaction_isolation’命令来查看当前的隔离级别

   视图：是从一个或多个表导出的虚拟的表，其内容由查询定义。具有普通表的结构，但不实现数据存储

2.事务的启动方式：

1. 显示启动事务语句：begin或 start transaction。配套使用的提交语句是commit,回滚语句是rollback。
2. set autocommit = 0,这个命令会将这个线程的自动提交关掉。意味着如果执行一个select语句，这个事务就启动了，但并不会自动提交。这个事务持续存在直到你主动执行commit或rollback语句，或者断天连接。

误用长事务：

客户端连接框架在连接建立成功后会先执行一个set autocommit = 0 的命令，导致接下来的查询都在事务中，如果是长连接就导致意外长事务，会造成大量的回滚日志无法被删除。

--查询当前数据库中正执行的超过60s的长事务命令：
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

3.避免使用长事务

• 从应用开发端：
  1. 确认是否使用了set autocommit = 0（一般不要使用）；
  2. 确认是否有不必要的只读事务；平衡业务需要，看是否需要把select语名放在事务中
  3. 业务连接数据库时，通过SET MAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行的时间太长。
• 从数据库端
  1. 监控information_schema.Innodb_trx表，设置长事务阈值 ，超时报警或kill
  2. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为，提前发现问题。

三人行，必有吾师，欢迎加入星球，一起讨论技术点滴

三人行，必有吾师，欢迎加入星球，一起讨论技术点滴