MySQL的事务与事务隔离

共计 7763 个字符，预计需要花费 20 分钟才能阅读完成。

MySQL 中自从引入 InnoDB 引擎后，在 MySQL 中就支持事务，事务就是一组原子性的查询语句，也即将多个查询当作一个独立的工作单元，平时通过提交工作单元来完成在事务中的相应的查询或修改，在能支持事务的数据库中必须要满足 ACID 测试，即事务的四个特性：

A：Atomicity，原子性（都执行或者都不执行）

C：Consistency，一致性（从一个一致性状态转到另外一个一致性状态）

I：Isolaction，隔离性（一个事务的所有修改操作在提交前对其他事务时不可见的）

D: Durability，持久性（旦事务得到提交，其所做的修改会永久有效）

而在早期默认的引擎 MyISAM 是不支持事务的，所以如果是在 MyISAM 表中是不支持事物的，想要知道数据中具体支持哪些表引擎可以通过”SHOW ENGINES;”查看在所使用版本中 MySQL 所支持的所有引擎。在这里先创建一张表 transaction_tbl 用于测试：

CREATE TABLE transaction_tbl (id int(4)) ENGINE=InnoDB;  
INSERT INTO transaction_tbl VALUE (1); 
INSERT INTO transaction_tbl VALUE (2); 
INSERT INTO transaction_tbl VALUE (3); 
INSERT INTO transaction_tbl VALUE (4); 
INSERT INTO transaction_tbl VALUE (5);

在正常的使用过程中需要关闭自动提交的功能默认系统下是开启的

mysql> SHOW GLOBAL VARIABLES LIKE 'autocommit';                        
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| autocommit    | ON    | 
+---------------+-------+ 
1 row in set (0.00 sec)

在使用事务之前需要关闭，在平时使用事务之前需要先检查是否有开启 autocommit，当然如果对事务的依赖比较大建议可以永久关闭全局的自动提交，但是在平时使用的过程中只要在使用时关闭自动提交，而使用手动启动事务，这样在事务中需要回滚时才能根据相关的事务隔离级别得到想要的效果，可以在使用事务时关闭 autocommit，在所有的事务结束后再开启 autocommit

mysql> SET GLOBAL AUTOCOMMIT=off;# 当然这里也可以使用布尔值的 0 和 1 
Query OK, 0 rows affected (0.00 sec)

其中事务的控制语句也很简单，如下：

BEGIN; 或 START TRANSACTION; 
显式地开启一个事务 
  
COMMIT; 
提交事务即结束事务，并使已对数据库进行的所有修改为持久性的 
  
ROLLBACK; 
事务回滚，会结束用户的事务并撤销正在进行的所有未提交的修改 
  
SAVEPOINT identifier; 
允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT 
  
RELEASE SAVEPOINT identifier; 
删除一个事务的保存点，当没有指定的保存点时，执行该 SQL 语句会报异常 
  
ROLLBACK TO identifier; 
把事务回滚到标记点

而在事务中的隔离级别不同，则事物的安全性就不同，但是事物得安全性越高，并发性越低，当然需要根据实际情况选择，在 MySQL 中事务的隔离级别有四种，安全级分别由低至高：

READ UNCOMMITTEND：读未提交

READ COMMITTEND：读提交

REPEATABLE READ：可重读

SERIALIZABLE：可串行化

查看当前使用的默认的事务隔离级别：

mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; 
+---------------+-----------------+ 
| Variable_name | Value           | 
+---------------+-----------------+ 
| tx_isolation  | REPEATABLE-READ | 
+---------------+-----------------+ 
1 row in set (0.00 sec)

而在使用四个隔离级别中，所带来效果都是不相同的，此时测试需要开启 2 个 session 更为直观，在这里就用 A、B 来代表两个 session 中开启事务 A、B：

mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.01 sec)

一、READ UNCOMMITTEND：读未提交，顾名思义即所有的事务都可以读取到其他事务中未提交的内容，该隔离模式在平时一般都不使用，因为使用 READ UNCOMMITTEND 会带来脏读问题，下面就用 transaction_tbl 举一个简单例子说明下：

A、B 中：

mysql> SET tx_isolation='READ-UNCOMMITTED'; 
Query OK, 0 rows affected (0.00 sec) 
mysql> SHOW VARIABLES LIKE 'tx_isolation'; 
+---------------+------------------+ 
| Variable_name | Value            | 
+---------------+------------------+ 
| tx_isolation  | READ-UNCOMMITTED | 
+---------------+------------------+ 
1 row in set (0.00 sec)

事务 A 中做了操作，但不提交：

mysql> START TRANSACTION; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> UPDATE transaction_tbl SET id = '6' WHERE id='1'; 
Query OK, 1 row affected (0.02 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  6 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec)

但是此时事务 B 是可以看见事务 A 中数据

事务 B:

mysql> START TRANSACTION; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  6 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.01 sec)

此时事务 A 回滚：

mysql> ROLLBACK; 
Query OK, 0 rows affected (0.01 sec) 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec)

事务 B 中查出来的是事务 A 中未提交的数据：

mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec)

这样就是由 READ UNCOMMITTEND 所带来的脏读，一般数据库生产环境中都不用这种事务隔离级别。

二、READ COMMITTEND，读提交，同理根据名字可知事物的隔离级别会比读未提交高一个事务隔离级别更高，从而解决了脏读的问题，这也是大多数数据库中所用的默认事务隔离级别，但并不是 MySQL 的默认事务隔离级别，该事务隔离级别虽然解决了脏读问题，但是带来新的问题是不可重读, 如果此时恰好有 2 个事务对相同的一张表做操作时，在一个事务中执行相同的查询时会查出不同的结果：

A、B 中：

mysql> SET tx_isolation='READ-COMMITTED'; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SHOW VARIABLES LIKE 'tx_isolation';        
+---------------+----------------+ 
| Variable_name | Value          | 
+---------------+----------------+ 
| tx_isolation  | READ-COMMITTED | 
+---------------+----------------+ 
1 row in set (0.00 sec)

在事务 A 中：

mysql> BEGIN; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec)

此时事务 B 中也开启事务做了一个操作：

mysql> BEGIN; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> UPDATE transaction_tbl SET id = '6' WHERE id='1'; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  6 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.01 sec)

此时看下事务A中的查询：

mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec) 
# 但此时事务 B 中 COMMIT 提交后，事务 A 中 
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  6 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec)

这样就是由 READ COMMITTEND 所带来的不可重读问题，所以在一般数据库生产环境中也不建议采用这种事务隔离级别。

三、REPEATABLE READ，可重读，同理该事务隔离级别解决了不可重读的问题，在 REPEATABLE READ 中使用 MVCC（多版本并发控制）在每个事务启动时，InnoDB 会为每个启动的事务提供一个当下时刻的快照，为实现此功能，InnoDB 会为每个表提供两隐藏的字段，一个用于保存行的创建时间，一个用于保存行的失效时间，里面存储的系统版本号，MVCC 旨在 READ COMMITTEND 和 REPEATABLE READ 两个事务隔离中生效，但是在 REPEATABLE READ 同以上事务隔离级别一样，在解决了不可重读的问题同时也带来新的问题幻读，此时恰好有 2 个事务对相同的一张表做操作时，在一个事务中提交之前其中一个事务在另一事务提交前后查询的结果不一样：

A、B 中：

mysql> SET tx_isolation='REPEATABLE-READ';  
Query OK, 0 rows affected (0.01 sec) 
  
mysql> SHOW VARIABLES LIKE 'tx_isolation'; 
+---------------+-----------------+ 
| Variable_name | Value           | 
+---------------+-----------------+ 
| tx_isolation  | REPEATABLE-READ | 
+---------------+-----------------+ 
1 row in set (0.01 sec)

事务 A：

mysql> BEGIN; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  6 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.02 sec)

事务 B 中做相关操作并提交：

mysql> BEGIN; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  6 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec) 
  
mysql> UPDATE transaction_tbl SET id = '1' WHERE id='6'; 
Query OK, 1 row affected (0.00 sec) 
Rows matched: 1  Changed: 1  Warnings: 0 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.01 sec) 
  
mysql> COMMIT; 
Query OK, 0 rows affected (0.02 sec)

此时再来看下事务 A 中：

mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.02 sec) 
  
mysql> COMMIT; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  6 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec)

这样就是 REPEATABLE READ 带来的幻读问题，当然在实际生产中这么恰好的事比较少，所以一般都做为 MySQL 的默认事务隔离级别。

四、SERIALIZABLE，可串行化，强事务排序也是最高级别的事务隔离，所有的事务都有使用共享锁，这样就解决相应的幻读问题，但是因为共享锁的原因从而使写入的性能降低，从而降低了 MySQL 的性能:

A、B 中：

mysql> SET tx_isolation='SERIALIZABLE'; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SHOW VARIABLES LIKE 'tx_isolation'; 
+---------------+--------------+ 
| Variable_name | Value        | 
+---------------+--------------+ 
| tx_isolation  | SERIALIZABLE | 
+---------------+--------------+ 
1 row in set (0.00 sec)

在事务 A 中插入一条数据不提交：

mysql> BEGIN; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> INSERT INTO transaction_tbl VALUE ('7'); 
Query OK, 1 row affected (0.00 sec) 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
|  7 | 
+----+ 
6 rows in set (0.00 sec)

此时在事务 B 中，在事务 A 未提交前是无法写入提交的

mysql> SHOW GLOBAL VARIABLES LIKE 'tx_isolation'; 
+---------------+--------------+ 
| Variable_name | Value        | 
+---------------+--------------+ 
| tx_isolation  | SERIALIZABLE | 
+---------------+--------------+ 
1 row in set (0.00 sec) 
  
mysql> BEGIN; 
Query OK, 0 rows affected (0.00 sec) 
  
mysql> SELECT * FROM transaction_tbl; 
+----+ 
| id | 
+----+ 
|  1 | 
|  2 | 
|  3 | 
|  4 | 
|  5 | 
+----+ 
5 rows in set (0.00 sec) 
  
mysql> UPDATE transaction_tbl SET id = '6' WHERE id='1'; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

只有在事务 A 中 COMMIT 提交后才能在事务 B 中提交，但是在此需要注意的是在 MySQL 中都是默认都是使用 REPEATABLE READ 的事务隔离级别，而在平时利用事务时多用于存储过程中大量使用，而不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写，所以把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大，在平时使用需要根据实际情况而定。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-09/146850.htm