Mysql一事务

一、事务

1.1 什么是事务？

• transaction 这个单词除了事务，还有交易、业务的意思。可以将具有处理完整业务逻辑的一组称认为是事务

  简单来说：它是它是一组sql语句，要没一起成功，要么一起失败

• Mysql 中常用的存储引擎中，默认的 INNODB 支持事务，而MYISAM 不支持事务

1.2 事务的特性

我们常说的事务具有ACID的特性

• A：
  • Atomicity(原子性)
  • 一个事务中的那组SQL语句，要么全部执行成功，要么一起失败。如果其中有一条SQL语句执行发生错误，就会回滚。回到最初的状态。不会出现，有的SQL执行成功，有的SQL执行失败。
  • 一荣俱荣、一损俱损
• C：
  • Consistency(一致性)
  • 执行的中间状态不可见，只会存在执行前的状态和执行后的状态。也就是说看见的数据是执行前的数据或者执行后的数据，保证了数据的一致性
• I：
  • Isolation(隔离性)
  • 并发事务时，当多个事务对相同的数据进行修改时，隔离性保证每个事务隔离不会影响其他事务，防止事务的交叉执行对数据产生预期之外的结果
• D:
  • Durablity(持久性)
  • 最终执行的结果将数据写入磁盘，保证数据不丢失

1.3 执行事务过程

1.3.1 自动提交问题

• MYISAM 下的提交时自动开启的
• INNODB 下默认也是自动开启的，但是开启事务的话，自动提交不会默认提交
• 查看当前自动提交是否开启：SHOW VARIABLES LIKE ‘autocommit’;
• 设置自动提交开启/关闭：
  • ON：set autocommit = 1;
  • OFF：set autocommit = 0;

1.3.2 创建表和测试数据

CREATE TABLE account (
    account_number VARCHAR(10) PRIMARY KEY,
    balance  INT
);
INSERT INTO account VALUES ('A', 600), ('B', 400);

1.3.3 例子:A向B转账100

-- 开启事务
BEGIN; 或 START TRANSACTION;

-- 1. 查询账户A的余额是否大于100
select balance FROM account WHERE account_number = 'A';

-- 2. 如果大于100则执行下面的两条SQL
UPDATE account SET balance = balance - 100 WHERE account_number = 'A';
UPDATE account SET balance = balance + 100 WHERE account_number = 'B';

-- 3. trt catch {} 包裹后台逻辑代码，除了异常就回滚，没出异常就提交
COMMIT;
ROLLBACK;

二、使用保存点

2.1 使用情形

假如我有三个用户A、B、C，A分别向B和C各子装入100，可能A向B转账成功，但是A向C转账失败，我现在不想要因为向其中某一人转账失败，就所有的转账都回滚
… ….
使用 savepoint pointname 来设置一个保存点；
使用 rollback to **pointname 来会滚到这个保存点；**

2.2 使用案例

-- A 分别向 B 和 C 各自转账 100, 先查询出三人都是500元
mysql> select * from account
+----------------+---------+
| account_number | balance |
+----------------+---------+
| A              |     500 |
| B              |     500 |
| C              |     500 |
+----------------+---------+

-- 开启事务
mysql> begin;

-- A向B转账100
update account set balance = balance - 100 where account_number = 'A';
update account set balance = balance + 100 where account_number = 'B';

-- 设置一个保存点
SAVEPOINT transfer_toB;

-- A向C转账100
update account set balance = balance - 100 where account_number = 'A';
update account set balance = balance + 100 where account_number = 'C';

-- 查询此时的情况,我此时没有提交
mysql> select * from account
+----------------+---------+
| account_number | balance |
+----------------+---------+
| A              |     300 |
| B              |     600 |
| C              |     600 |
+----------------+---------+

-- 此时我如果发生了异常，情况A向C转账发生了异常

-- 回到保存点
ROLLBACK to transfer_toB;
commit;

-- 再去查看
mysql> select * from account
+----------------+---------+
| account_number | balance |
+----------------+---------+
| A              |     300 |
| B              |     600 |
| C              |     600 |
+----------------+---------+

三、隔离级别

• 当多个事务同时发生时，事务的隔离级别就确定了事务间对相同资源的读取和写入问题
• Mysql 中有四种隔离级别
  • 读未提交
  • 读已提交
  • 可重复读
  • 序列化

3.1 读未提交(read uncommit)

时间线	事务A	事务B
1	设置当前会话的事务隔离级别为 读未提交	设置当前会话的事务隔离级别为 读未提交
	set transaction_isolation = ‘read-uncommitted’; select @@transaction_isolation;	set transaction_isolation = ‘read-uncommitted’; select @@transaction_isolation;
2	开启事务A，并查询当前account表的数据	开启事务B，并查询当前account表的数据
	begin; select * from account;	begin; select * from account;
3	修改账户A的金额并进行查询
	update account set balance = 100 where account_number = ‘A’; select * from account;
4		此时事务A并没有提交数据，我们在事务B中查询account表的情况看看
		select * from account
读未提交： - 我们从结果中可以看到，事务B可以读取到事务A中未提交的数据。 - 这引发了一个问题，那就是脏读。试想一下如果事务A因为某些原因回滚，如果事务B在事务A修改后回滚前读

  读取了数据，那么读取的这条数据就是错误的数据<br />- 这是事务隔离级别中最低一级的隔离级别，我们一般几乎是不会遇到<br /> |  |  |

| |

3.2 读已提交(read commit)

时间线	事务A	事务B
1	设置当前会话的事务隔离级别为 读已提交	设置当前会话的事务隔离级别为 读已提交
	set transaction_isolation = ‘read-committed’; select @@transaction_isolation;	set transaction_isolation = ‘read-committed’; select @@transaction_isolation;
2	开启事务A，并查询当前account表的数据	开启事务B，并查询当前account表的数据
	begin; select * from account;	begin; select * from account;
3	修改账户A的金额并进行查询
	update account set balance = 200 where account_number = ‘A’; select * from account;
4		此时事务A并没有提交数据，我们在事务B中查询account表的情况看看
		select * from account
可以看到这里 事务B读取到的还是事务A的未提交之前的记录。
5	事务A提交
	commit;
6		此时，再去事务B中查看account表
		select * from account
读已提交： - 出现一个问题，那就是如果最后事务A成功提交。那么事务B两次读的结果不一样。 - 所以读已提交也会造成问题，那就是一个事务前后读取到的数据不一致问题。 - 造成了不可重复读的问题 - 这种隔离级别已经是大多数数据库的隔离级别（Oracle的隔离级别），但不是mysql数据库的隔离级别。

3.3 可重复读(Repeatable Read)

  insert、update和delete会更新版本号，是当前读(当前版本）。<br />- 另外mysql8开始，默认的可重复读下解决了幻读的问题，此时如果在A中插入或删除数据，会报错<br />
 lock wait timeout .... 进行锁等待 |  |  |

3.4 序列化/串行话()

 非完全的串行化就算不是表级的共享锁，至少也要把选定的所有行的数据锁起来<br />- 它提高了最高级别的隔离，就像它的另一个名字串行化，占用资源的事务A给资源加了一把共享锁<br />
 其他事务其它事务只有等这个事务完成才能继续操作......<br />- 读写相互会相互互斥，这样可以更好的解决数据一致性的问题，但是同样会大大的降低数据库的实际吞吐性能。所以该隔离级别因为损耗太大，一般很少在开发中使用。<br /> |  |  |

3.5 总结

Mysql 8 中事务隔离级别的问题

        <br />    

事务并发引起的问题

• 脏读：事务A读取了事务B未提交的数据，事务B回滚，事务A则读取的数据是脏数据
• 不可重复读：事务A在多次读取同一批数据时，其它事务对数据进行了修改，导致事务A读取的数据不一致
  • 解决办法，锁住读取的数据
• 幻读：事务A在读取数据时，其他事务增加或者修改了数据
  • 解决办法，锁表
  • mysql，已经解决了幻读的问题

Mysql INNODB中的 可重复读

• 一个事务通过一条语句只能看到相同的数据，即使在另外一个事务中对数据进行了修改。
• 假设，开启两个事务A、B。B事务的提交前和提交后A事务读取的是一样的数据，这个数据是A未做修改的数据。只有一个例外就是：事务可以读取自己在同一事务中更改的数据。
• 事务A开始第一次读取数据。会创建读取视图并保持打开状态，知道事务A结束。为了在结束前都提供相同的结果集，INNODB 使用了 MVVC（多版本并发控制）和重做日志。假设事务A读取了几行的数据，另外一个事务B，恰好删除了这几行的数据。但是事务A并没有关闭，还是处于打开的状态，只要它还没提交，它还是可以看到这几行数据。这是为啥？因为已经删除的数据保留在UNDO日志空间中来继续履行事务A，但是只要事务A提交，也就是事务A完成，这些被标记的数据就会从重做日志中删除，这就是MVVC的体现。

事务隔离级别的意义

事务的隔离级别的意义就是：

• 在高并发的情况下，保持数据的安全，防止脏读、幻读、可重复读等预期之外的结果
• 但是随着隔离级别的提高，系统的开销也会越来越大，并发性也会越来越差
• 事务的四个基本特性 ACID
  • 原子性、一致性、主要由日志来保证
  • 隔离性就需要锁了

四、锁

4.1 锁的简单介绍

• 外部锁：MySQL为客户端会话可以 显示的锁表和解锁
  • 锁表：LOCK TABLES table_name [READ | WRITE]
    • READ：共享锁，不允许写入数据，可以读数据
    • WRITE：排它锁，除所释放外，其他会话读写都不允许
  • 解锁表：UNLOCK TABLES
  • 冻结所有表的写入：FLUSH TABLES WITH READ LOCK;
• 内部锁：自身对资源的管理，管理会话对表数据的竞争
  • 行级别的锁：
    • 只有被访问的行会被锁定，允许多个会话同时进行访问，并发效果好。只有INNODB支持行级 别的锁。
  • 页级别的锁：
    • BDB存储引擎支持的锁，锁住一页数据大概是16KB，不过这种存储引擎几乎现在不会用到了，一般我们只会选择MYISAM和INNODB。
  • 表级别的锁：
    • 对于MYISAM、MERGE、MEMORY 存储引擎使用的锁

4.2 锁队列

除了共享锁之外，没有任何两个锁可以一起加在同一个表上。如果有一个表加了一个共享锁，此时一个排他锁要进入，那么它将会被留在队列中，直到共享锁被释放。
InnoDB从表中读取/写入数据时会获取元数据锁，如果此时有一个事务请求 WRITE LOCK，则这个事务会被保留在队列，直到第一个事务完成。依次类推… …

锁队列测试

-- 事务1 开启查询account表数据
begin;
select * from account;

-- 查询的结果：
account_number balance
A                                500
B                                500    

-- 注意我没有提交,事务1还是开启的状态

-- 事务2 开启,锁表使用排他锁
begin;
LOCK TABLE account WRITE;

-- 执行的结果：
[SQL]LOCK TABLE account WRITE;

-- 事务3 开启,查数据
begin;
select * from account;

-- 执行的结果：
[SQL]select * from account;

-- 这里可以看到事务3什么也查询不到，它一直是阻塞的状态。
-- 事务3在等待事务2的完成
-- 事务2在等待事务1的完成

-- 我们再来一个会话，来查看现在的状态
SHOW PROCESSLIST;

-- 执行结果
id        User                            Host                            DB        COmmand        Time  State                                                            Info                                            
4            event_scheduler        localhost                                Daemon        917        Waiting on empty queue    
8            root                            localhost:54688        cook    Sleep            821        
9            root                            localhost:54694        cook    Query            731        Waiting for table metadata lock        LOCK TABLE account WRITE
10        root                            localhost:54700        cook    Sleep            715        
11        root                            localhost:54705        cook    Query            656        Waiting for table metadata lock        select * from account
12        root                            localhost:54793        cook    Sleep            46        
13        root                            localhost:54800        cook    Query            0            starting                                                    SHOW PROCESSLIST

-- 可以直接从state和Info看到事务2和事务3都在等待事务1

-- 现在我提交事务1, 然后事务3他还是看不到，因为它在等待事务2
commit;

-- 继续提交事务2 然而还是不行？？？？？？
commit;

-- 因为
Waiting for table metadata lock

场景一:长事物运行,阻塞DDL,继而阻塞所有同表的后续操作

场景二:未提交事物,阻塞DDL,继而阻塞所有同表的后续操作

场景三：

通过show processlist看不到TableA上有任何操作.
在information_schema.innodb_trx中也没有任何进行中的事务。
这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），
这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。
从performance_schema.events_statements_current 表中可以查到失败的语句。

官方手册上对此的说明如下：
If the server acquires metadata locks for a statement that is syntactically 
valid but fails during execution, it does not release the locks early. 
Lock release is still deferred to the end of the transaction 
because the failed statement is written to the binary log and 
the locks protect log consistency.

也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。
because the failed statement is written to the binary log and 
the locks protect log consistency 
但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。

处理方法：通过performance_schema.events_statements_current找到其sid, 
kill 掉该session. 
也可以 kill 掉DDL所在的session.

-- 事务的长时间运行和阻塞都会造成很严重的问题。

-- 另外为了保证备份的一致性，所有的备份方法都会使用 
FLUSH TABLES WITH READ LOCK READ;
-- 如果此时表中存在长时间运行的事务 .......

共享锁测试**

-- 事务1加上一把共享锁
begin;
select * from account where account_number = 'A' lock in share mode ;

-- 事务2 
begin;
select * from account where account_number = 'A';

-- 结果：事务2中是可以查询出这条记录的

排他锁测试**

-- 事务1加上一把共享锁
begin;
select * from account where account_number = 'A' lock in share mode ;

-- 事务2 
begin;
select * from account where account_number = 'A';

-- 结果：事务2中是可以查询出这条记录的
-- 因为：快照读并不需要加锁