MySQL~数据并发问题（脏写、脏读、不可重复读、幻读）与SQL隔离级别演示

文章目录

数据并发问题

脏写（Dirty Write）

两个事务A、B，事务B修改了数据，还未提交时，被A再次修改，就是脏写.

假设有张表student，事务B将某个学生姓名修改为李四，还没提交时，事务A又把这个学生姓名改为了张三，然后事务A提交，事务B回滚，本来应该是李四却被改成了张三，这就是脏写。

脏读(Dirty Read)

两个事务A、B,事务A读取了被事务B修改但还没有提交的数据，之后事务B回滚，那么事务A读取的数据是临时的无效的，这就是脏读

考试时你在试卷上写了答案，你同桌一脸兴奋的拿去抄了，抄完就交卷了。结果你发现你答案是错的，拿回来改了，那他的试卷上写的答案就是无效的。这个过程就是脏读。

发生过程如下:
还是假设学生表，事务B修改某个学生姓名张三，事务A读取了这个修改后的数据，提交了，结果事务B回滚，那么A读到的这个数据无效，并不存在，这就是脏读

不可重复读（Non-Repeatable Read）

两个事务A，B，事务A读取了一个值，然后事务B把这个值给改了，然后A又读取，发现两次读取的值不相同。这个过程就是不可重复读。

幻读（Phantom）

两个事务A、B，A从表中读取了几个数据，然后B又插入了几个行，A再次读取时，发现多了几个数据，像幻觉一样。这就是幻读。

注意：幻读是多读取了一些数据。如果A读取了数据后，B删除了一些数据，然后A再次读取时，发现数据变少了，这个算不算幻读？不算，幻读是读取后另一个事务又插入了一些数据，多的这些数据叫 幻影记录，之前读取到的数据再次读取时发现变少了是不可重复读。

四个问题严重性从高到低：

脏写、脏读、不可重复读、幻读

MySQL中的隔离级别

为了解决这些并发问题，SQL将事务进行隔离，并设置了不同的级别，隔离级别越低，并发问题越多，隔离级别越高，并发问题越少。

• 读未提交（read uncommitted）:所有事务都可以看到其他未提交事务的执行结果
• 读已提交（read committed）:事务只能看见已提交事务的改变
• 可重复读（repeatable read）：事务A读取数据后，事务B进行修改并提交，A再次读取时，读到的还是原来的内容。这个级别是MySQL的默认隔离级别
• 串行化（serializable）：当一个事务对表进行操作时，禁止其他事务对该表执行操作，可避免所有并发问题，是最高的事物隔离级别，但是性能非常低，一般不用

四个级别对数据并发问题的解决：

脏写的问题非常严重，不论是哪种隔离级别，都不允许脏写的问题发生

查看默认的隔离级别：

mysql>show variables like'transaction_isolation';+-----------------------+-----------------+| Variable_name         |Value|+-----------------------+-----------------+| transaction_isolation |REPEATABLE-READ|+-----------------------+-----------------+

如何设置事务的隔离级别

settransactionisolationlevel 隔离级别;
或者 
set transaction_isolation='隔离级别';

隔离级别写以下四种：
readuncommitted;readcommitted;repeatableread;serializable;

GLOBAL关键字

使用global关键字后，设置的隔离级别对当前会话中的事务无效，对之后的会话起作用

setglobaltransactionisolationlevel 隔离级

setglobal transaction_isolation='隔离级别'

SESSION关键字

使用session关键字设置的隔离级别，对当前会话的后续事务有效。可以在已经开启的事务中间执行，但不会影响正在执行的事务，对之后的事务有效

setsessiontransactionisolationlevel 隔离级

setsession transaction_isolation='隔离级别'

在不同隔离级别下并发问题的演示

初始化数据

创建表
mysql>createtable account(-> id intprimarykeyauto_increment,-> name varchar(20),-> balance decimal(10,2)->);
Query OK,0rows affected (0.02 sec)

插入数据
mysql>insertinto account values(1,'张三','100'),(2,'李四','0');
Query OK,2rows affected (0.02 sec)
Records: 2  Duplicates: 0Warnings: 0

查看隔离级别
mysql>select @@transaction_isolation;+-------------------------+| @@transaction_isolation|+-------------------------+|REPEATABLE-READ|+-------------------------+1rowinset(0.01 sec)

脏读演示

设置会话隔离级别
mysql>setsession transaction_isolation='read-uncommitted';
Query OK,0rows affected (0.00 sec)

开启事务
mysql>begin;
Query OK,0rows affected (0.00 sec)+100块 没有提交
mysql>update account set balance=balance+100where id=1;
Query OK,1row affected (0.00 sec)Rowsmatched: 1  Changed: 1Warnings: 0

此时在另一个事务中读取，结果读到了200块：
mysql>select*from account;+----+------+---------+| id | name | balance |+----+------+---------+|1| 张三 |200.00||2| 李四 |0.00|+----+------+---------+2rowsinset(0.00 sec)
这个过程就是脏读

事务回滚
mysql>rollback;
Query OK,0rows affected (0.00 sec)

数据没有发生改变
mysql>select*from account;+----+------+---------+| id | name | balance |+----+------+---------+|1| 张三 |100.00||2| 李四 |0.00|+----+------+---------+2rowsinset(0.00 sec)

说明另一个事务读取的是无效数据

可重复读演示

mysql>setsession transaction_isolation='read-committed';
Query OK,0rows affected (0.00 sec)

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

mysql>update account set balance=balance-50where id=1;
Query OK,1row affected (0.00 sec)Rowsmatched: 1  Changed: 1Warnings: 0

mysql>select*from account;+----+------+---------+| id | name | balance |+----+------+---------+|1| 张三 |50.00||2| 李四 |0.00|+----+------+---------+2rowsinset(0.00 sec)

此时在另一个事务中查看仍然是100块，说明 读已提交隔离级别 避免了脏读
mysql>select*from account;+----+------+---------+| id | name | balance |+----+------+---------+|1| 张三 |100.00||2| 李四 |0.00|+----+------+---------+2rowsinset(0.00 sec)

我们提交当前事务
mysql>commit;
Query OK,0rows affected (0.01 sec)

在另一个为提交的事物中进行查看发现变成了50块
mysql>select*from account;+----+------+---------+| id | name | balance |+----+------+---------+|1| 张三 |50.00||2| 李四 |0.00|+----+------+---------+2rowsinset(0.00 sec)

发生了不可重复读

幻读演示

在事务2中查看 0条数据
mysql>selectcount(*)from account where id=3;+----------+|count(*)|+----------+|0|+----------+1rowinset(0.00 sec)

在事务1中插入数据并提交事务1
mysql>begin;
Query OK,0rows affected (0.00 sec)

mysql>insertinto account(id,name,balance)values(3,'王五',100);
Query OK,1row affected (0.01 sec)

mysql>select*from account;+----+------+---------+| id | name | balance |+----+------+---------+|1| 张三 |50.00||2| 李四 |0.00||3| 王五 |100.00|+----+------+---------+3rowsinset(0.00 sec)

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

在未提交的事物2中查询，发现从0变成了1
mysql>selectcount(*)from account where id=3;+----------+|count(*)|+----------+|1|+----------+1rowinset(0.00 sec)

这个过程就是幻读