MySQL笔记（十）MySQL事务 transaction

一、为什么要有事务？

用一个很经典的例子：

事务广泛的运用于订单系统、银行系统等多种场景。如果有以下一个场景：
A用户和B用户是银行的储户。现在A要给B转账500元。那么需要做以下几件事：

1. 检查A的账户余额>500元；
2. A账户扣除500元；
3. B账户增加500元；

正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。那如果A账户扣了钱之后，系统出故障了呢？
A白白损失了500，而B也没有收到本该属于他的500。以上的案例中，隐藏着一个前提条件：
A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此。

事务作用：

一个事务中的所有操作作为一个单元，要么完全地执行，要么完全地不执行。

事务是为了确保数据的完整性和一致性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

MySQL 事务注意点：
   1、在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
   2、事务用来管理 insert,update,delete 语句，不能回退 CREATE 或 DROP 操作

---

二、事务的基本要素（ACID）

（1）A(atomicity) 原子性。一个事务的执行被视为一个不可分割的最小单元。事务里面的操作，要么全部成功执行，要么全部失败回滚，不可以只执行其中的一部分。

（2）C(consistency) 一致性。一个事务的执行不应该破坏数据库的完整性约束。如果上述例子中第2个操作执行后系统崩溃，
保证A和B的金钱总计是不会变的。

（3）I(isolation) 隔离性。通常来说，事务之间的行为不应该互相影响。然而实际情况中，事务相互影响的程度受到隔离级别的影响。
（4）D(durability) 持久性。事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。

三、事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

MySQL默认的事务隔离级别为repeatable-read

---

四、事务的并发问题

1、脏读：事务A读取了事务B更新的数据（没提交的，commit后就不能rollback了），然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读的和幻读理解：

不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

---

五、事务控制语句

开启一个事务；
BEGIN 或 START TRANSACTION;

COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
COMMIT 也可以使用 COMMIT WORK ，不过二者是等价的。

回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
ROLLBACK 也可以使用 ROLLBACK WORK ，不过二者是等价的。

SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
SAVEPOINT identifier; 

删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
RELEASE SAVEPOINT identifier;

把事务回滚到标记点；
ROLLBACK TO identifier;

用来设置事务的隔离级别
SET TRANSACTION

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT 来实现

    BEGIN 开始一个事务
    ROLLBACK 事务回滚
    COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

    SET AUTOCOMMIT=0 禁止自动提交
    SET AUTOCOMMIT=1 开启自动提交

---

六、查看修改MySQL的存储引擎

看你的mysql现在已提供什么存储引擎:
show engines;
 
看你的mysql当前默认的存储引擎:
show variables like '%storage_engine%';
 
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
show create table tb_dept;
 
如何查看Mysql服务器上的版本
select version();

修改表引擎方法
alter table tb_dept engine=innodb;

mysql事物，rollback不起作用，怎么回事？
这时候就要查看你数据库、表的引擎是什么

---

七、MySQL事务隔离级别测试

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

MySQL默认的事务隔离级别为repeatable-read

所用的表和数据 MySQL笔记（二）基础的增删改查

事务隔离级别测试test:

注意；测试时每一个事物都需要开启一个客户端

事务隔离级别测试test:
注意；测试时每一个事物都需要开启一个客户端

（1）第一级别：读未提交（read-uncommitted） 
1、所有事务都可以看到其他未提交事务的执行结果
2、本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少
3、该级别引发的问题是——脏读(Dirty Read)：读取到了未提交的数据

1、首先，修改隔离级别
set tx_isolation='read-uncommitted';
select @@tx_isolation;

2、客户端A开启事物1 
START TRANSACTION;
SELECT *FROM tb_dept;

3、客户端B开启事物2
在这事务中更新数据，且未提交
BEGIN;
UPDATE tb_dept SET loc='十堰2' WHERE deptno=7200;
SELECT * FROM tb_dept;

4、客户端A查看数据
SELECT *FROM tb_dept;
重点：事物2能查看事物1未提交改变的脏数据，这就是脏读

5、客户端B回滚rollback
ROLLBACK;
SELECT * FROM tb_dept;

6、客户端A事物1查询
SELECT *FROM tb_dept;

（2）第二级别：读提交（read-committed）
1、这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）
2、它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变
3、这种隔离级别出现的问题是不可重复读(Nonrepeatable Read)：
不可重复读意味着我们在同一个事务中执行完全相同的select语句时可能看到不一样的结果。

导致这种情况的原因可能有：
(1)有一个交叉的事务有新的commit，导致了数据的改变;
(2)一个数据库被多个实例操作时,同一事务的其他实例在该实例处理其间可能会有新的commit。

1、首先，修改隔离级别
SET tx_isolation='read-committed';
SELECT @@tx_isolation;

2、客户端A开启事物1 
START TRANSACTION;
SELECT *FROM tb_dept;

3、客户端B开启事物2
在这事务中更新数据，且未提交
BEGIN;
UPDATE tb_dept SET loc='十堰2' WHERE deptno=7200;
SELECT * FROM tb_dept;

4、客户端A事物1查询
SELECT *FROM tb_dept;
注意：此时查看的是未提交的数据，原来表数据

5、客户端B提交事物commit;
commit后就不能rollback了 
COMMIT;

6、客户端A事物1查看数据
SELECT *FROM tb_dept;
重点：此时查看的是事物2提交的数据，和第四步相同的select语句，结果却不一样

（3）第三级别：可重复读（repeatable-read）     
1、这是MySQL的默认事务隔离级别
2、它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行
3、此级别可能出现的问题——幻读(Phantom Read)：
当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行
4、InnoDB和Falcon存储引擎通过多版本并发控制(MVCC，Multiversion Concurrency Control)机制解决了该问题

1、首先，修改隔离级别
SET tx_isolation='repeatable-read';
SELECT @@tx_isolation;

2、客户端A开启事物1 
START TRANSACTION;
SELECT *FROM tb_dept;

3、客户端B开启事物2
在这事务中更新数据，并且提交了
BEGIN;
UPDATE tb_dept SET loc='十堰2' WHERE deptno=7200;
SELECT * FROM tb_dept;
COMMIT;

4、客户端A事物1查询
SELECT *FROM tb_dept;
重点：这时候即使事务2已经提交了,但事物2还是看不到数据的变化。

只有事物1提交了，才能看到数据的变化
COMMIT;
SELECT *FROM tb_dept;

（4）第四级别：串行化（serializable）      
1、这是最高的隔离级别
2、它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。
3、在这个级别，可能导致大量的超时现象和锁竞争

1、首先，修改隔离级别
SET tx_isolation='serializable';
SELECT @@tx_isolation;

2、客户端A开启事物1 
START TRANSACTION;

3、客户端B开启事物2
BEGIN;
UPDATE tb_dept SET loc='十堰2' WHERE deptno=7200;
重点：在事物1没有commit之前，这个交叉事务是不能更改数据的