第31讲：MySQL事务的并发问题以及事务的隔离级别

文章目录

1.事务的并发问题

事务并发使用的过程中，会面临以下三类问题，无论是什么问题都可以通过隔离级别来解决。

1.1.事务并发之脏读

脏读指的是：一个事务读到另一个事务还没有提交的数据。

如下图所示：事务A中先执行了一个select语句，然后又执行了一个update语句，此时id=1的数据已经被修改过了，但是还没有提交到数据库中，表的数据显示的还是旧数据，事务B中有一个select语句，这个select刚好就要查询id=1的数据，这时就会读取到事务A还没有提交的但是已经修改过的数据，这就是事务并发使用中的脏读现象。

事务虽然没有提交，但是在终端中已经执行了对应的SQL语句，在逻辑上已经更改了数据，此时查询等语句读到的就是已经修改过的数据，提交数据后表中、磁盘都会跟着发生变化。

1.2.事务并发之不可重复读

不可重复读指的是：一个事务中有多个select查询语句，第一次执行select查询获取的数据和后面执行select查询获取的数据不一致，两次查询出的数据不同，这种现象称之为不可重复读。

如下图所示：事务A中有多条select查询语句，第一次执行select语句获取了对应的数据，在执行后面SQL语句的操作时，事务B针对事务A查询的数据进行了修改，并且提交到了事务，当事务A在后面执行同样的select语句时，两次获取的数据就不一样了，这就引发了事务并发中的不可重复读。

1.3.事务并发之幻读

幻读指的是：是一个事务按照条件查询数据时，并没有查询到对应的数据，这时就会在数据库中写入这条数据，但是这个时候另外一个事务恰巧在数据库中写入了这条数据，原本要写入数据的事务在执行SQL时就会写入失败，这事事务又去查询该条数据，发现并不存在的，从而导致始终无法写入，这就是幻读的现象。

如下图所示，事务A中有三条SQL语句，一条SQL根据条件查询一条数据，发现没有这条数据，接着就会向数据库中写入这条数据，此时恰巧事务B要向数据库中写入了这条数据，从而导致了事务A写入数据失败，这时事务A又去查询该数据，发现数据会在数据表中依旧没有，就会产生一种类似幻觉的现象，这就是事务并发的幻读现象。

2.事务的隔离级别

隔离级别脏读不可重复读幻读Read uncommitted不能解决不能解决不能解决Read committed可以解决不能解决不能解决Repeatable Read（默认）可以解决可以解决不能解决Serializable可以解决可以解决可以解决
不同的隔离级别可以解决某类并发事务产生的问题，事务隔离的级别越高，数据就越安全，但是性能会降低，事务隔离的级别越低，数据安全性就会很低，但是性能会很强。

查看当前数据库事务隔离级别的命令：

SELECT @@TRANSACTION_ISOLATION;

@@表示引用一种变量

设置事务隔离级别的命令：

SET[SESSION|GLOBAL]TRANSACTIONISOLATIONLEVEL { READUNCOMMITTED|READCOMMITTED|REPEATABLEREAD|SERIALIZABLE }

SESSION：当前客户端会话中生效
GLOBAL：针对全局所有的客户端会话生效

3.模拟事务并发问题的产生以及如何避免

一切并发事务问题的模拟都需要同时启动多个事务。

3.1.事务并发问题脏读的模拟以及避免

脏读指的是：一个事务读到另一个事务还没有提交的数据。

脏读并发问题的模拟流程：

• 首先开启两个事务A、B
• 事务A查询yexxb表中的数据
• 事务B修改yexxb表中id=1的数据，但是不提交事务
• 事务A再次查询yexxb表中的数据，发现查询到的数据是事务B未提交事务的数据。

当读到其他事务没有提交的数据时，就表示已经产生脏读的现象了。

3.1.1.模拟事务并发脏读的问题

1）设置事务的隔离级别为read uncommitted。

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

2）开启两个事务会话，模拟脏读事务并发现象。

1.事务A开启一个事务
mysql>starttransaction;2.事务B开启一个事务
mysql>starttransaction;3.事务A查询yexxb的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |2000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.00 sec)4.事务B修改yexxb中id=1的数据，但是不提交事务
mysql>update yexxb set ye = ye+1000where id ='1';
Query OK,1row affected (0.00 sec)Rowsmatched: 1  Changed: 1Warnings: 05.事务A再次查询yexxb中的数据，发现查询的数据是事务B修改后但未提交事务的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |3000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.00 sec)

此时已经成功模拟出了脏读的现象，事务A有多次查询yexxb的操作，但是在事务A处理过程中，事务B也对yexxb的数据进行了修改，此时，事务A就会读到事务B对yexxb的处理操作，即使事务B没有提交事务。

3.1.2.解决事务并发脏读的问题

处理事务并发脏读的问题有三种隔离级别可以解决，分别是read committed、repeatable read、serializable等。

由于我们目前只想解决脏读的问题，因此我们将事务的隔离级别设置成read committed级别，再次观察同样的操作是否还会出现脏读的现象。

1）设置事务的隔离级别为read committed。

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

mysql>select @@transaction_isolation;+-------------------------+| @@transaction_isolation|+-------------------------+|READ-COMMITTED|+-------------------------+1rowinset(0.00 sec)

2）开启两个事务会话，模拟脏读事务并发现象。

1.事务A开启一个事务
mysql>starttransaction;2.事务B开启一个事务
mysql>starttransaction;3.事务A查询yexxb的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |3000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.00 sec)4.事务B修改yexxb中id=1的数据，但是不提交事务
mysql>update yexxb set ye = ye+1000where id ='1';
Query OK,1row affected (0.00 sec)Rowsmatched: 1  Changed: 1Warnings: 05.事务A再次查询yexxb中的数据，发现查询的数据依旧是最开始的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |3000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.00 sec)6.事务B提交事务
mysql>commit;7.事务A再次查询时，才会看到新的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |4000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.01 sec)

此时已经成功避免了脏读的问题，即使并发事务的情况下，其他的事务修改了表中的数据，只要不提交，当前事务依旧能读到旧数据。

3.2.事务并发问题不可重复读的模拟以及避免

不可重复读指的是：一个事务中有多个select查询语句，第一次执行select查询获取的数据和后面执行select查询获取的数据不一致，两次查询出的数据不同，这种现象称之为不可重复读。

不可重复读并发问题的模拟流程：

• 首先开启两个事务A、B
• 事务A查询yexxb表中的数据
• 事务B修改yexxb表中id=1的数据，修改完后立即提交事务
• 事务A再次查询yexxb表中的数据，发现查询到的数据是事务B提交后的数据。

不可重复读和脏读的模拟场景类似，但是不可重复读是基于了另外事务提交之后的数据，此时我们不希望读取到其他事务提交的数据，我们仍然希望读到在事务一开始查询的数据，也就是希望能够重复读。

3.2.1.模拟事务并发不可重复读的问题

1）当前的事务隔离级别为READ-COMMITTED，在这个隔离级别下可以解决脏读的问题，但是不可解决不可重复读和幻读的问题，因此处于这个隔离级别下就可以模拟出不可重复读的场景。

mysql>select @@transaction_isolation;+-------------------------+| @@transaction_isolation|+-------------------------+|READ-COMMITTED|+-------------------------+1rowinset(0.00 sec)

2）开启两个事务会话，模拟脏读事务并发现象。

1.事务A开启一个事务
mysql>starttransaction;2.事务B开启一个事务
mysql>starttransaction;3.事务A查询yexxb的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |4000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.00 sec)4.事务B修改yexxb中id=1的数据，立即提交事务
mysql>update yexxb set ye = ye+1000where id ='1';
Query OK,1row affected (0.00 sec)Rowsmatched: 1  Changed: 1Warnings: 0

mysql>commit;5.事务A再次查询yexxb中的数据，发现查询的数据是事务B修改后已提交事务的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |5000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.00 sec)

此时已经模拟出了不可重复读的现象，事务A第一次执行查询语句时，拿到了yexxb表的数据，在此时，事务B对yexxb的数据进行了修改，在不提交的情况下事务A依旧可以读取重复数据，但是这时事务B也进行了提交操作，那么事务A就无法再读到之前查询出来的数据了，导致两次查询的结果都不相同，这就是不可重复读。

3.2.2.解决事务并发不可重复读的问题

处理事务并发不可重复读的问题有两种隔离级别可以解决，分别是repeatable read、serializable等。

由于我们目前只想解决不可重复读的问题，基于性能问题，我们将事务的隔离级别设置成repeatable read级别，再次观察同样的操作是否还会出现不可重复读的现象。

repeatable read隔离级别也是MySQL中默认的隔离级别。

1）设置事务的隔离级别为repeatable read。

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

mysql>select @@transaction_isolation;+-------------------------++-------------------------+| @@transaction_isolation|+-------------------------+|REPEATABLE-READ|+-------------------------+1rowinset(0.00 sec)

2）开启两个事务会话，模拟脏读事务并发现象。

1.事务A开启一个事务
mysql>starttransaction;2.事务B开启一个事务
mysql>starttransaction;3.事务A查询yexxb的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |4000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.00 sec)4.事务B修改yexxb中id=1的数据，立即提交事务
mysql>update yexxb set ye = ye+1000where id ='1';
Query OK,1row affected (0.00 sec)Rowsmatched: 1  Changed: 1Warnings: 0

mysql>commit;5.事务A再次查询yexxb中的数据，发现查询的数据是事务B修改后已提交事务的数据
mysql>select*from yexxb;+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|1| 小明   |5000.00||2| 小红   |2000.00|+----+--------+---------+2rowsinset(0.00 sec)

此时已经成功解决了不可重复读的问题，即使并发事务的情况下，其他的事务修改了表中的数据，即使是提交了数据，当前事务依旧能读到旧数据。

3.3.事务并发问题幻读的模拟以及避免

幻读指的是：是一个事务按照条件查询数据时，并没有查询到对应的数据，这时就会在数据库中写入这条数据，但是这个时候另 外一个事务恰巧在数据库中写入了整条数据，原本要写入数据的事务，此时就发现这行数据还是不存在的，但始终无法写入，这就是幻读的现象。

不可重复读并发问题的模拟流程：

• 首先开启两个事务A、B
• 事务A查询yexxb表中xm=小江的数据，发现查询不到
• 此时事务B想yexxb表中插入了xm=小江的数据
• 事务A这时插入xm=小江的数据，发现插入失败，数据已经存在
• 事务A再次查询xm=小江的数据，发现数据是不存在的

事务A第一次查询xm=小江的数据不存在，这时想往表中写入xm=小江的数据，但是此时事务B已经在写入xm=小江的数据了，就会导致事务A无法写入，事务A再次查询xm=小江的数据时，发现数据还是不存在的，这就是幻读。

3.2.1.模拟事务并发幻读的问题

1）当前的事务隔离级别为REPEATABLE-READ，在这个隔离级别下可以解决脏读、不可重复读的问题，但是不可解决幻读的问题，因此处于这个隔离级别下就可以模拟出幻读的场景。

mysql>select @@transaction_isolation;+-------------------------+| @@transaction_isolation|+-------------------------+|REPEATABLE-READ|+-------------------------+1rowinset(0.00 sec)

2）开启两个事务会话，模拟脏读事务并发现象。

1.事务A开启一个事务
mysql>starttransaction;2.事务B开启一个事务
mysql>starttransaction;3.事务A查询xm=小江的数据，发现没有任何数据
mysql>select*from yexxb where xm ='小江';
Empty set(0.00 sec)4.事务B此时新增了xm=小江的数据，并且立即提交事务
mysql>insertinto yexxb values('3','小江','3000');
Query OK,1row affected (0.00 sec)

mysql>commit;
Query OK,0rows affected (0.22 sec)5.事务A没有查询到xm=小江的数据，此时就要插入xm=小江的数据，但是执行新增SQL时，就报错了，提示id=3的数据已经存在了
mysql>insertinto yexxb values('3','小江','3000');
ERROR 1062(23000): Duplicate entry '3'forkey'yexxb.PRIMARY'6.再次查询xm=小江的数据，发现依旧是没有数据的
mysql>select*from yexxb where xm ='小江';
Empty set(0.00 sec)

此时已经模拟出了幻读的现象，事务A第一次查询xm=小江的数据时，发现并没有查询到内容，此时事务A就想在表中新增xm=小江的数据了，但是就在这个时候，事务B在表中新增了xm=小江的数据，并且已经提交了数据，比事务A快了一步，事务A在插入xm=小江的数据时，就发现报错了提示数据已经存在了，事务A就该疑问了，我查询的时候并没有这个数据呀，再次查询确实没有这个数据，再次执行写入依旧无法写入，这就是幻读的现象。

3.2.2.解决事务并发幻读的问题

处理事务并发幻读的问题有一种隔离级别可以解决，隔离级别为serializable。

想要解决幻读的问题，只能将隔离级别设置为serializable，但是serializable级别对于数据库的性能是很有影响的。

1）设置事务的隔离级别为serializable。

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

mysql>select @@transaction_isolation;+-------------------------+| @@transaction_isolation|+-------------------------+|SERIALIZABLE|+-------------------------+1rowinset(0.00 sec)

2）开启两个事务会话，模拟脏读事务并发现象。

1.事务A开启一个事务
mysql>starttransaction;2.事务B开启一个事务
mysql>starttransaction;3.事务A查询xm=小张的数据，发现没有任何数据
mysql>select*from yexxb where xm ='小张';
Empty set(0.00 sec)4.事务B此时去新增xm=小张的数据，发现无法写入，一直处于等待中
mysql>insertinto yexxb values('4','小张','3000');5.事务A这时新增xm=小张的数据，数据写入成功
mysql>insertinto yexxb values('4','小张','3000');
Query OK,1row affected (0.00 sec)6.事务A提交事务
mysql>commit;
Query OK,0rows affected (0.03 sec)7.当事务A对表的操作完成后，事务B才输出了报错信息，提示数据已经存在了，此时也就说明了一个结论，当设置了serializable级别时，事务并发过程中，如果有一个事务正在操作这张表的数据，其他事务需要处于等待状态。
ERROR 1062(23000): Duplicate entry '4'forkey'yexxb.PRIMARY'7.事务A再次查询xm=小张的数据
mysql>select*from yexxb where xm ='小张';+----+--------+---------+| id | xm     | ye      |+----+--------+---------+|4| 小张   |3000.00|+----+--------+---------+1rowinset(0.01 sec)

此时已经成功解决了幻读的问题，即使在并发事务的情况下，其他的事务也要修改了表中的数据就会处于等待状态，无法修改，当前事务处理完成后，其他的事务才能对表进行操作。