修改mysql事物级别

Mysql 数据库事物及隔离级别

• 事务的基本要素（ACID）
• 事务的隔离级别问题
• 事务的隔离级别

• READ UNCOMMITTED 读未提交
• READ COMMITED 读已提交
• REPEATABLE READ 可重复读
• SERIALIZABLE 可串行化

• 数据准备
• READ UNCOMMITTED
• READ COMMITED
• REPEATABLE READ
• SERIALIZABLE

事务的基本要素（ACID）

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

事务的隔离级别问题

• 脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
• 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
• 幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

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

事务的隔离级别

READ UNCOMMITTED 读未提交

在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称作脏读。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他的级别好太多，但确缺乏其他级别的很多好处，除非真的有必要的理由，在实际应用中一般很少使用。

READ COMMITED 读已提交

大多数数据库系统的默认隔离级别是 READ COMMITED（但mysql不是）。READ COMMITED满足前面提到的隔离性简单定义：一个事务开始时，只能看见已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读，因为两次执行同样的查询，可能会得到不一样的结果。

REPEATABLE READ 可重复读

MySQL的默认事务隔离级别。
REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另一个幻读的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。InnoDB存储引擎通过多版本并发控制（MVCC）解决了幻读的问题。

SERIALIZABLE 可串行化

SERIALIZABLE 是最高的隔离级别。它通过强事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在曲度的每一行数据都加上锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据一致性而且可以接受没有并发的情况下，才考虑使用该级别。

数据准备

CREATE TABLE `s_test` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '状态。0-删除，1-可用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into s_test(`name`) values ('a'),('b'),('c'),('d');

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

READ UNCOMMITTED

分别开启2个数据库连接终端，模拟2个事物操作。
终端A 设置事物隔离级别Read UnCommited 然后读取测试表中的数据

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.01 sec)

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

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.02 sec)

终端B设置事物隔离级别Read UnCommited 然后修改测试表中的数据，并不提交事物

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update s_test set name = 'x' where id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时再去终端A中读取数据库中的数据

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:46:50 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

可以发现事物A已经可以读到事物B未提交的修改，在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。

此时，我们将终端B的事物回滚

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

再到终端A查询数据库

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

发现数据已经回滚，则事物A中间那次读到的数据就是脏读。

READ COMMITED

同样，我们终端A 设置事物隔离级别Read Commited 然后读取测试表中的数据

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

然后在终端B设置事物隔离级别Read Commited 然后修改测试表中的数据，并不提交事物

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update s_test set name = 'x' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时再次到终端A查询数据库记录

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | a    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

此时我们会发现，我们终端B未提交的修改，我们并没有读到，即不会产生脏读。

此时，我们到终端B将刚做的修改提交

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

再次回到终端A查询数据库数据

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

此时我们发现，我们已经读到了终端B事物提交的事物，但是这也产生了一个新的问题，即事物A的2次提交读到的不同的数据。即产生了重复读的问题。

REPEATABLE READ

同样，我们终端A 设置事物隔离级别Repeatable Read然后读取测试表中的数据

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

然后 在终端B 设置事物隔离级别Repeatable Read然后然后修改测试表中的数据，并提交事物

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

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

mysql> update s_test set name = 'y' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

此时，再到终端A查看数据

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

发现事物B一提交的修改不回影响事物A的重复读取，即不回产生重复读的问题。

此时，再到终端B中插入一条记录

mysql> insert into s_test(`name`) values ('e');
Query OK, 1 row affected (0.02 sec)

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  5 | e    | 2019-05-08 11:13:54 | 2019-05-08 11:13:54 |      1 |
+----+------+---------------------+---------------------+--------+
5 rows in set (0.00 sec)

然后再回到终端A，插入一条看起来不存在的记录

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | x    | 2019-05-07 18:42:37 | 2019-05-08 10:59:54 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
+----+------+---------------------+---------------------+--------+
4 rows in set (0.00 sec)

mysql> insert into s_test(id,name) values(5,'e');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

即产生了幻读，读不出来已经存在的数据。

SERIALIZABLE

同样，我们在终端A中 设置事物隔离级别Serializable然后读取测试表中的数据

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from s_test;
+----+------+---------------------+---------------------+--------+
| id | name | create_time         | update_time         | status |
+----+------+---------------------+---------------------+--------+
|  1 | y    | 2019-05-07 18:42:37 | 2019-05-08 11:08:51 |      1 |
|  2 | b    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  3 | c    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  4 | d    | 2019-05-07 18:42:37 | 2019-05-07 18:42:37 |      1 |
|  5 | e    | 2019-05-08 11:13:54 | 2019-05-08 11:13:54 |      1 |
+----+------+---------------------+---------------------+--------+
5 rows in set (0.00 sec)

然后在终端B中设置事物隔离级别Serializable然后尝试修改测试表中的数据

mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into s_test(`name`) values ('f');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

然后会发现，终端B事务被挂住了，然后过了一段时间，提示了错误 (1205, u’Lock wait timeout exceeded; try restarting transaction’) ，说等待锁超时。

在串行化隔离级别中，事物会在读取的每一行数据都加上锁，也就是说，上面A事务在读取时，已经加了锁，此时B事务在插入操作时，得等待锁的放开，时间一长，A锁未放开，B就报错了。