4. MySQL事务隔离级别

一、事务 Transaction

• 针对写操作

1、启动事务

> start transaction;

2、提交事务

> commit;

3、回滚事务

> rollback;

二、MySQL事务隔离级别

1、事务并发产生的问题

• 脏读、可重复读、幻读

2、四种隔离级别

• read uncommited 读未提交
• read commited 读已提交
• repeatable read 可重复读
• serializable 串行化

三、read uncommited 读未提交

1、说明

事务A会读取到事务B未提交的数据，产生脏读

2、调整事务隔离级别

MariaDB [test]> set global transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

3、测试

3.1 启动事务A，查看表info中的数据

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | demon  |
+----+--------+
2 rows in set (0.00 sec)

3.2 在另一个终端，启动事务B，对info表中的数据进行修改，但不提交

MariaDB [(none)]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update info set name="AA" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> 

3.3 在事务A中查看数据

MariaDB [test]> select * from info;
+----+-------+
| id | name  |
+----+-------+
|  1 | AA    |
|  2 | demon |
+----+-------+
2 rows in set (0.00 sec)

发现读取到的是事务B未提交的数据，即为脏读

3.4 事务B回滚，在事务A中再次查看数据

MariaDB [test]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | demon  |
+----+--------+
2 rows in set (0.00 sec)

四、read commited 读已提交

1、说明

事务A不会读取到事务B未提交的数据，只有事务B提交后事务A才会查看到修改后的数据

2、调整事务隔离级别

MariaDB [test]> set global transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

3、测试

3.1 启动事务A，查看表info中的数据

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | demon  |
+----+--------+
2 rows in set (0.00 sec)

3.2 在另一个终端，启动事务B，对info表中的数据进行修改，但不提交

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update info set name="BB" where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

3.3 在事务A中查看数据

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | demon  |
+----+--------+
2 rows in set (0.00 sec)

发现查看到的是事务B修改前的数据

3.4 在事务B中进行提交，再次在事务A中查看数据

MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | BB     |
+----+--------+
2 rows in set (0.00 sec)
可以查看到事务B提交的数据，但是，事务A两次读取到的数据不一致，存在不可重复读的问题

五、repeatable read 可重复读 [默认]

1、说明

事务A启动后，在未提交前，查看数据时不会受到其他事务修改的影响

2、调整事务隔离级别

MariaDB [test]> set global transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

3、测试

3.1 启动事务A，查看表info中的数据

MariaDB [(none)]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> use test;

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | BB     |
+----+--------+
2 rows in set (0.00 sec)

3.2 启动事务B，修改info表中的数据，但不提交

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update info set name="AA" where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> select * from info;
+----+------+
| id | name |
+----+------+
|  1 | AA   |
|  2 | BB   |
+----+------+
2 rows in set (0.00 sec)

3.3 在事务A中查看数据

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | BB     |
+----+--------+
2 rows in set (0.00 sec)

3.4 事务B提交，再在事务A中查看数据

MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | BB     |
+----+--------+
2 rows in set (0.00 sec)

即使事务B已经提交，但在事务A中查看到的还是之前未修改的数据，称为可重复读； 起始事务A中每次读的数据是一致的，不会受其他事务的影响

3.5 启动事务C，向表info中插入一条数据

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into info(name) values("leah");
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)

3.6 再次在事务A中查看数据

MariaDB [test]> select * from info;
+----+--------+
| id | name   |
+----+--------+
|  1 | martin |
|  2 | BB     |
+----+--------+
2 rows in set (0.00 sec)
 
 发现一样查看不到事务C新写入的数据，称为幻读，即查看不到最新的数据 

3.7 事务A提交，再次查看数据

MariaDB [test]> select * from info;
+----+------+
| id | name |
+----+------+
|  1 | AA   |
|  2 | BB   |
|  3 | leah |
+----+------+
3 rows in set (0.00 sec)

查看到最新的数据 !!!! 

六、serializable 串行化

1、说明

事务A启动后，会锁定整个表，后续其他事务B必须等待事务A结束后，才可正常执行

2、调整事务隔离级别

MariaDB [test]> set global transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| SERIALIZABLE   |
+----------------+
1 row in set (0.00 sec)

3、测试

3.1 启动事务A，查看表info中的数据

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> select * from info;
+----+------+
| id | name |
+----+------+
|  1 | AA   |
|  2 | BB   |
|  3 | leah |
+----+------+
3 rows in set (0.00 sec)

3.2 启动事务B，对表info中的数据进行修改

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> update info set name="martin" where id=1;

发现事务B中的操作会进入等待状态，因为事务A锁了整个表

3.3 事务A提交

MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)

事务A提交后，事务B中的操作才会开始执行