开始MySQL之路——MySQL 事务（详解分析）

MySQL 事务概述

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

• 事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

• 事务用来管理 insert 、update 、delete 语句。

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性或不可分割性）、Consistency（一致性）、Isolation（隔离性或独立性）、Durability（持久性）。

 

• 原子性：一组事务，要么成功；要么撤回，即事务在执行过程中出错会回滚到事务开始前的状态。
• 一致性 ： 一个事务不论是开始前还是结束后，数据库的完整性都没有被破坏。因此写入的数据必须完全符合所有预设规则（资料精确度、串联性以及后续数据库能够自发完成预定工作）。
• 隔离性：数据库允许多个事务并发的同时对其数据进行读写修改等操作，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离可分为：Read uncommitted（读未提交）、Read committed（读提交）、Repeatable read（可重复读）、Serializable（串行化）。
• 持久性：事务在处理结束后对数据做出的修改是永久的，无法丢失。

事务控制语句

1、显式的开始一个事务：

start transaction 或  begin

2、做保存点，一个事务中可以有多个保存点：

savepoint 保存点名称

3、提交事务，并使数据库中进行的所有修改成为永久性的：

commit 或 commit work

4、回滚结束用户的事务，并撤销正在进行的所有未提交的修改：

rollback 或 rollback work

5、删除一个事务的保存点，若没有指定保存点，执行该语句操作会抛错。

release savepoint 保存点名称

6、将事务滚回标记点：

rollback to 标记点

7、设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

set transaction

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

1、用 BEGIN、ROLLBACK、COMMIT来实现

• BEGIN 开始一个事务

• ROLLBACK 事务回滚

• COMMIT  事务确认

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

• SET AUTOCOMMIT=0   禁止自动提交

• SET AUTOCOMMIT=1 开启自动提交

事务测试：

mysql> use NHOOO;
Database changed
mysql> CREATE TABLE nhooo_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)mysql> select * from nhooo_transaction_test;
Empty set (0.01 sec)mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)mysql> insert into nhooo_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)mysql> insert into nhooo_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)mysql>  select * from nhooo_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)mysql>  insert into nhooo_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)mysql>   select * from nhooo_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)mysql>

事务的隔离级别

事务并发问题

1、脏读
含义：在事务过程中，A事务还未提交，B事务就读到了A事务未提交的数据。

    一个事务正在对一条记录做修、改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫作脏读。

解决方法：将数据库事务提升到提交读或以上的隔离级别。

2、不可重复读
含义：一次事务中，两次读操作中，读出来的数据内容不一致。

    A事务在本次事务中，对自己未操作过数据，进行多次读取，结果出现不一致或记录不存在的情况。一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫作 “不可重复读”。

解决方法：将数据库事务提升到可重复读或以上的隔离级别。

3、幻读
含义：一次事务中，两次读操作中，读到的数据行数不一致。读到了新增或者读不到删除的语句。

    A事务在本次事务中，对自己未操作过数据，进行多次读取，结果出现不一致或记录不存在的情况。一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

解决方法：将数据库事务提升到序列化(串行化)或以上的隔离级别。

注：幻读和不可重复读很像但有区别。幻读是事务1操作过程中，有新数据添加提交了。再读时会出现新数据。不可重复读是，事务1操作过程中，之前读过的数据被修改或删除了。

4、脏写
含义：多个事务同时对数据进行修改，其中一个事务的数据被另一个事务的操作覆盖，导致丢失修改。如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了 脏写。

解决：脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏 写的情况发生。

SQL中的四种隔离级别

　　上面介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题有轻重缓急之分，我们给这些问题按照严重性来排一下序：脏写 > 脏读 > 不可重复读 > 幻读

SQL标准中设立了4个隔离级别:

1. 读未提交(READ UNCOMMITTED)MySQL事务隔离其实是依赖锁来实现的，加锁自然会带来性能的损失。而读未提交隔离级别是不加锁的，所以它的性能是最好的，没有加锁、解锁带来的性能开销。但是有利就有弊，这基本上就是裸奔了，所以连脏读的问题都没法解决。
2. 读已提交(READ COMMITTED)既然读未提交没办法解决脏数据的问题，那么就有了读提交。读提交就是一个事务只能读到其他事务已经提交过的数据，也就是其他事务调用commit命令之后的数据。
3. 可重复读(REPEATABLE READ)可重复读是对比不可重复而言的，上面说不可重复读是指同一个事务不同时刻读到的数据可能不一致。而重复读是指事务不会读到其他事务对已有数据的修改，即使其他事务已提交，就是说，事务开始时读到的已有数据是什么，在事务提交前的任意时刻，这些数据的值都是一样的。但是，对于其他事务新插入的数据是可以读到的，这就引发了幻读问题。
4. 串行化(SERIALIZABLE)串行化是4种事务隔离级别中隔离效果最好的，解决了脏读、不可重复读、幻读的问题，但是性能最差，它将事务的执行变为顺序执行，与其他三个隔离级别相比，它相当于单线程，后一个事务的执行必须等待前一个事务结束。

MySQL默认的事务隔离级别是可重复读(REPEATABLE READ)

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题

MySQL支持的四种隔离级别及设置

　　不同的数据库厂商对SQL标准中规定的四种隔离级别支持不一样。比如Oracle就只支持READ COMMITTED (默认隔离级别)和SERIALIZABLE隔离级别。MySQL虽然支持4种隔离级别，但与SQL标准中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的，禁止幻读的原因在后面将详解。MySQL的默认隔离级别为REPEATABLE READ，我们也是可以手动修改一下事务的隔离级别。

查看隔离级别：

MySQL5.7版本及以前SHOW VARIABLES LIKE 'tx_isolation';+---------------+-----------------+| Variable_name | Value           |+---------------+-----------------+| tx_isolation  | REPEATABLE-READ |+---------------+-----------------+
MySQL5.7.20版本以后MySQL5.7.20只会引入transaction_isolation来替代tx_isolationSHOW VARIABLES LIKE 'transaction_isolation';+-----------------------+-----------------+| Variable_name         | Value           |+-----------------------+-----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+-----------------+
不同的MySQL版本中都可以指定的系统变量来查看当前的事务隔离级别SELECT @@GLOBAL.transaction_isolation;  -- 全局系统变量SELECT @@SESSION.transaction_isolation; -- 会话系统变量

设置事务的隔离级别：

第一种方式设置隔离级别：SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别;第二种方式设置隔离级别（设置系统变量方式）：SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别'
MySQL四种可选隔离级别：
1、READ-UNCOMMITTED 
2、READ-COMMITTED 
3、REPEATABLE-READ
4、SERIALIZABLE

补充：在设置事务隔离级别的语句中，在SET关键字后面可以放置GLOBAL、SESSION或者什么都不放，这样会对不同范围的事务产生不同的影响，具体如下：

Ⅰ：使用GLOBAL关键字(在全局范围产生影响):
        如：SET GLOBAL TRANSACTION_ISOLATION = 'SERIALIZABLE';
        ①：只对执行完该语句之后新产生的会话起作用；
        ②：当前已经存在的会话无效。

Ⅱ：使用SESSION关键字(在会话范围产生影响):
        如：SET SESSION TRANSACTION_ISOLATION = 'SERIALIZABLE';
        ①：对当前会话所有的后续事务有效;
        ②：该语句可以在已经开启的事务中执行，但不会影响当前正在执行的事务;
        ③：如果在事务之间执行，则对后续的事务有效。

Ⅲ：上述两个关键字都不使用(只对执行SET语句后的下一个事务产生影响):
        如：SET TRANSACTION_ISOLATION = 'SERIALIZABLE';
        ①：只对当前会话中下一个即将开启的事务有效；
        ②：下一个事务执行完后，后续事务将恢复到之前的隔离级别；
        ③：该语句不能在已经开启的事务中执行，否则报错。