mysql08事务

 

1 什么是事务

　　事务就是保障一系列的操作统一执行，要嘛全部成功，要嘛全部失败。

　　事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体，如果单元中某条SQL语句一旦执行失败或产生错误，整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态；如果单元中的所有SQL语句均执行成功，则事物被顺利执行

 

2 mysql存储引擎

　　在mysql中的数据用各种不同的技术存储在文件（或内存）中。

　　通过show engines；来查看mysql支持的存储引擎。

　　在mysql中用的最多的存储引擎有：innodb，myisam ,memory 等。其中innodb支持事务，而myisam、memory等不支持事务

 

3 事务的属性ACID

1）原子性(Atomicity)

　　一连串的操作看做一个操作，要嘛全部成功，要嘛全部失败

 

2）一致性(Consistency)

　　事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态。

 

3）隔离性(isolation)

　　事务之间的操作互不影响，一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰

 

4）持久性(durabolity)

　　数据一旦提交永久保存

 

4 隔离级别

4.1 隔离级别相关问题

　　在一个事务中对数据进行多次查询，在这个过程中，如果其它事务对数据进行更新、新增、删除操作，那么多次查询的结果就会不同

 

1）脏读

　　对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段.之后, 若 T2 回滚, T1读取的内容就是临时且无效的

 

2）不可重复度

　　对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段.之后, T1再次读取同一个字段, 值就不同了

 

3）幻读

　　对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行

 

4.2 隔离级别

　　数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响, 避免各种并发问题。

　　一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱

　　Oracle 支持的 2 种事务隔离级别：READ COMMITED,SERIALIZABLE。 Oracle 默认的事务隔离级别为: READCOMMITED

　　Mysql 支持 4 种事务隔离级别. Mysql 默认的事务隔离级别为: REPEATABLE READ，也就相对于行锁。

　　每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别.

 

4.3 查看和获取隔离级别

1）查看当前的隔离级别

SELECT @@tx_isolation;

　　

 

2）设置当前 mySQL 连接的隔离级别

set SESSION  transaction isolation level read committed;  

　　只对当前登录mysql的用户有效

 

3）设置数据库系统的全局的隔离级别

set global transaction isolation level read committed;

　　重启才会有效，对所有用户有效

 

5 示例

1）提交

set autocommit = 0;  
start TRANSACTION;
DELETE FROM jcustomer WHERE CustomerNo = 10000002;
DELETE FROM jcustomer WHERE CustomerNo = 10000003;
COMMIT;

　　set autocommit标识关闭自动提交。因为mysql默认自动提交，执行一条操作语句就会自动提交，为了保证事务内多条语句全部执行成功后再一起提交，需要先关闭

　　start TRANSACTION;开启事务

　　COMMIT;提交事务

　　成功删除两条记录

 

2）回滚

set autocommit = 0;  
start TRANSACTION;
DELETE FROM jcustomer WHERE CustomerNo = 10000005;
DELETE FROM jcustomer WHERE CustomerNo = 10000006;
ROLLBACK

ROLLBACK回滚

没有删除，受影响行0

 

3）回滚到节点

set autocommit = 0;  
start TRANSACTION;
DELETE FROM jcustomer WHERE CustomerNo = 10000010;
DELETE FROM jcustomer WHERE CustomerNo = 10000011;
SAVEPOINT a;
DELETE FROM jcustomer WHERE CustomerNo = 10000012;
ROLLBACK TO a;

SAVEPOINT a;设置回滚节点

ROLLBACK TO a;回滚到节点a处

上面语句执行后，回滚到节点a处，10000010，10000011被删除，10000012没有被删除

6 MYSQL展示ACID特性示例

　　现在有一张表，jcustomer，主键为CustomerNo，有一个属性Name。

　　使用连接工具开启三个窗口

　　

 　　三个窗口的执行语句分别是

　　1号窗口

set autocommit = 0;  
start TRANSACTION;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
UPDATE  jcustomer set `Name` =  CONCAT(`Name`,'aaa') WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
COMMIT;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

　　2号窗口

set autocommit = 0;  
start TRANSACTION;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
UPDATE  jcustomer set `Name` =  CONCAT(`Name`,'bbb') WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
COMMIT;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

　　3号窗口

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

6.1 示例1

1）执行三号窗口

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

执行结果

 

 2）执行1号窗口前三条语句

set autocommit = 0;  
start TRANSACTION;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

3）执行2号窗口前三条语句

set autocommit = 0;  
start TRANSACTION;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

4）执行1号窗口第四条语句

UPDATE  jcustomer set `Name` =  CONCAT(`Name`,'aaa') WHERE CustomerNo = 10000010;

 

5）执行2号窗口第四条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 读取到的还是：小明

 没有脏读

 

6）执行1号窗口第五条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

读取到的：小明aaa

 

7）执行3号窗口的语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

读取到的是小明：没有脏读

 

8）执行1号窗口的第六条语句

COMMIT;

 

 9）执行1后窗口的第七条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

 10）执行3号窗口的语句

读取到小明aaa，1号窗口的事务成功提交了

 

11）执行2号窗口的第五条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 读取到的还是：小明

 没有不可重复度

 

 12）执行2号窗口的第六条语句

UPDATE  jcustomer set `Name` =  CONCAT(`Name`,'bbb') WHERE CustomerNo = 10000010;

 

13）执行3号窗口的语句

 

还是小明aaa

 

14）执行2号窗口的第七条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

15）执行2号窗口的第八条语句

COMMIT;

 

16）执行2号窗口的第九条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

6.2 示例2

1）执行3号窗口

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

执行结果

 

 2）执行1号窗口前三条语句

set autocommit = 0;  
start TRANSACTION;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

3）执行2号窗口前五条语句

set autocommit = 0;  
start TRANSACTION;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

4）执行1号窗口第四条语句

UPDATE  jcustomer set `Name` =  CONCAT(`Name`,'aaa') WHERE CustomerNo = 10000010;

 

5）执行2号窗口的第六条语句

UPDATE  jcustomer set `Name` =  CONCAT(`Name`,'bbb') WHERE CustomerNo = 10000010;

 修改操作阻塞了，这是数据库锁的作用

 

6）执行1号窗口第五条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

读取到的：小明aaa

 

7）执行3号窗口的语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 读取到的是小明：没有脏读

 

8）执行1号窗口的第六条语句

COMMIT;

同时窗口2，阻塞的修改语句执行了

 

 9）执行1后窗口的第七条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

 10）执行3号窗口的语句

 读取到小明aaa，1号窗口的事务成功提交了

 

11）执行2号窗口的第七条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

12）执行2号窗口的第八条语句

COMMIT;

 

13）执行2号窗口的第九条语句

SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

6.3 示例3

把窗口1的修改语句修改成下面的

UPDATE  jcustomer set `Name` =  'aaa' WHERE CustomerNo = 10000010;

把窗口2的修改语句修改成下面的

UPDATE  jcustomer set `Name` =  'bbb' WHERE CustomerNo = 10000010;

 

1）窗口1执行前五条语句

set autocommit = 0;  
start TRANSACTION;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
UPDATE  jcustomer set `Name` =  'aaa' WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

 

2）窗口2执行前六条语句

set autocommit = 0;  
start TRANSACTION;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;
UPDATE  jcustomer set `Name` =  'bbb' WHERE CustomerNo = 10000010;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

同样阻塞了

 

3）执行窗口1第六、七条语句

COMMIT;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;

同时窗口2 阻塞的修改语句执行了

 

 4）执行窗口2第八、九条语句

COMMIT;
SELECT * FROM jcustomer WHERE CustomerNo = 10000010;