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 |
+-----------------------+-----------------+

如何设置事务的隔离级别

set transaction isolation level 隔离级别;
或者 
set transaction_isolation='隔离级别';

隔离级别写以下四种：
read uncommitted;
read committed;
repeatable read;
serializable;

GLOBAL关键字

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

set global transaction isolation level 隔离级

set global transaction_isolation='隔离级别'

SESSION关键字

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

set  session transaction isolation level 隔离级

set session transaction_isolation='隔离级别'

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

初始化数据

创建表
mysql> create table account(
    -> id int primary key auto_increment,
    -> name varchar(20),
    -> balance decimal(10,2)
    -> );
Query OK, 0 rows affected (0.02 sec)

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

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

脏读演示

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


开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


+100块 没有提交
mysql> update account set balance=balance+100 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


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

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

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

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

可重复读演示

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

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

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

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

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

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

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

发生了不可重复读

幻读演示

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

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

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

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

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

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

这个过程就是幻读