测试环境：centos6.8_x86_64 mysql-5.6.28

主要关注的词：

Mysql|Innodb |transaction |ACID |read ucommited | read commited |repeatable read |serializable |脏读 |不可重复读|幻读

一． mysql事务的基本要素：

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

二． 事务并发导致的问题：

1. 脏读(Dirty Read)：所有事务都可以看到其他未提交事务的执行结果。
2. 不可重复读(Nonrepeatable Read)：一个事务只能看见已经提交事务所做的改变。
3. 幻读(Phantom Read)：它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

三． SQL定义的事务的4种隔离级别

这里以几个例子来说明mysql事务的4种隔离级别：

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

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 200 || 2 | tom | 300 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

session A上执行:

mysql> select @@tx_isolation;+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)

mysql> set tx_isolation = 'READ-UNCOMMITTED';

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update t6 set sal = sal + 50 where id = 1;

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 300 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

然后session B上执行:

mysql> set tx_isolation = 'READ-UNCOMMITTED';Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 | -----------A未提交事务，B可以查到修改的数据| 2 | tom | 300 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

然后session A上回滚操作：

mysql> rollback;Query OK, 0 rows affected (0.00 sec)

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 200 || 2 | tom | 300 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

再在session B上查看：

mysql> select * from t6;+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 200 || 2 | tom | 300 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

锁情况：

在session A上：mysql> start transaction ;Query OK, 0 rows affected (0.00 sec)

mysql> select * from t6 where id = 1 for update;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 |+----+----------+------+1 row in set (0.00 sec)

然后session B上：

mysql> start transaction;Query OK, 0 rows affected (0.00 sec)

mysql> select * from t6 where id = 1 for update;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

A上commit后，B上正常更新。可见上了行锁（注意理解innodb行锁实现的原理，否则可能还是会上表锁的）；

2 . Read Committed(读已提交)

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

Session A上执行：

mysql> select * from t6;+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 300 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

mysql> set tx_isolation = 'read-committed';

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update t6 set sal = sal + 60 where id = 2;

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 360 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)Session B上执行：mysql> set tx_isolation = 'read-committed';Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 300 | -----查询不到A事务对数据的修改，因为A事务没提交| 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

然后session A上执行：

mysql> commit;Query OK, 0 rows affected (0.00 sec)

再在session B上查看：

mysql> select * from t6;+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 360 | ----A事务提交后，B事务就能看到修改的记录| 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

3.Repeatable Read(可重读)

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

Session A上操作：

mysql> set tx_isolation = 'repeatable-read';Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;

+-----------------+| @@tx_isolation |+-----------------+| REPEATABLE-READ |+-----------------+1 row in set (0.00 sec)

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 360 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update t6 set sal = sal + 60 where id =2 ;

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 420 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

Session B 上执行：

mysql> start transaction;Query OK, 0 rows affected (0.00 sec)

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 360 | -----查询不到A事务对数据的修改，A事务没提交| 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

可以看到A事务没提交， B事务上查询不到A事务对数据的修改，那么A事务提交后，B事务是否能查询到呢？

session A 上操作：mysql> commit;Query OK, 0 rows affected (0.00 sec)

Session B上操作：

mysql> select * from t6;+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 360 | A事务提交了，B事务还是查不到修改的记录（幻读）| 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

那么B事务提交后，看是否能查看？

Session B 上执行：mysql> commit;Query OK, 0 rows affected (0.00 sec)

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 420 | B提交后，再查询，可以看到修改的记录| 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

4.Serializable(可串行化)

(1)这是最高的隔离级别(2)它通过强制事务顺序执行，从而解决幻读问题。当一个事务有更新操作时，其它事务只有等待这个事务提交后才能进行读、写操作。(3)在这个级别，可能导致大量的超时现象和锁竞争Session A 上执行：mysql> select @@tx_isolation;+----------------+| @@tx_isolation |+----------------+| SERIALIZABLE |+----------------+1 row in set (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> update t6 set sal=sal+60 where id = 2;

Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t6;

+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 480 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

Session B上执行：

mysql> set tx_isolation = 'serializable';Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;

+----------------+| @@tx_isolation |+----------------+| SERIALIZABLE |+----------------+1 row in set (0.00 sec)

mysql> select from t6;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update t6 set sal=sal+50 where id = 1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

可以看到读、写都等待锁。A事务commit提交后，B事务可以正常执行：

mysql> select from t6;+----+----------+------+| id | name | sal |+----+----------+------+| 1 | zhangsan | 250 || 2 | tom | 480 || 3 | jim | 400 || 4 | liu | 500 |+----+----------+------+4 rows in set (0.00 sec)

mysql> update t6 set sal=sal+50 where id = 1;

Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0