mysql 设置重复_如何理解MySQL事务的隔离级别

如何理解MySQL事务的隔离级别

1. 读未提交(READ UNCOMMITTED)
2. 读已提交(READ COMMITTED)
3. 可重复读(REPEATABLE READ)
4. 可串行化(SERIALIZABLE)

• MySQL的默认事务的隔离级别是可重复读

-- 登录mysql的root账户，-p待输入密码，-h mysql服务器地址 -P 端口号(注意大写)➜ mysql -u root -p -h localhost -P 3307Enter password: ****Welcome to the MySQL monitor.  Commands end with ; or g.Your MySQL connection id is 284Server version: 5.7.28 MySQL Community Server (GPL)Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.mysql>-- 查看当前事务的隔离级别mysql> show variables like 'transaction_isolation';+-----------------------+-----------------+| Variable_name         | Value           |+-----------------------+-----------------+| transaction_isolation | REPEATABLE-READ |+-----------------------+-----------------+-- 或者如此查看事务的隔离级别mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ         |+-------------------------+-- 可以看到MySQL的默认事务隔离级别是可重复读

读未提交

会产生所有的问题(脏读、不可重复读、幻读)

读已提交

在此事务隔离级别下，事务B只能在事务A修改并且已提交后才能读取到事务A修改的数据。

避免了脏读

但还是会产生(不可重复读、幻读)

可重复读

在此事务隔离级别下，事务B只能在事务A修改数据并提交后，自己也提交事务后，才能读取到事务A修改的数据。

避免了脏读和不可重复读

但还是会产生幻读问题

可串行化

此种事务隔离级别别最高，不会发生任何以下问题(脏读、可重复读、幻读)，通过加锁实现

读锁和写锁，只在读读不阻塞，读写、写读、写写都会阻塞。

解读一下并发造成的问题(脏读、不可重复读、幻读)

数据库表t_user我的表字段

mysql> show columns from t_user;+-------+-------------+------+-----+---------+----------------+| Field | Type        | Null | Key | Default | Extra          |+-------+-------------+------+-----+---------+----------------+| id    | int(11)     | NO   | PRI | NULL    | auto_increment || name  | varchar(50) | NO   |     | NULL    |                |+-------+-------------+------+-----+---------+----------------+

注意：MySQL 默认开启事务自动提交模式，即除非显式的开启事务(BEGIN 或 START TRANSACTION)，否则每条 SOL 语句都会被当做一个单独的事务自动执行。

-- 可以看到mysql默认开启自动提交模式mysql> SHOW VARIABLES LIKE 'autocommit';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON    |+---------------+-------+-- 可以设置是否自动提交-- 值为 0 和值为 OFF：关闭事务自动提交。-- 值为 1 和值为 ON：开启事务自动提交。-- 如下可设置的4种值-- SET autocommit = 0|1|ON|OFF;

• 脏读

事务A 读取到了 事务B 修改但是未提交的脏数据

开始之前一定调整事务的隔离级别为读未提交，不然不可能产生脏读现象

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;Query OK, 0 rows affected (0.00 sec)mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| READ-UNCOMMITTED        |+-------------------------+

首先查看一下t_user表的数据

mysql> select * from t_user;+----+--------+| id | name   |+----+--------+|  1 | 达摩   |+----+--------+

然后进行如下测试

-- session1 事务A 开始mysql> begin;-- session2 事务B 开始mysql> begin;-- session2 事务B 修改name = 吕布  (注意这里并未提交)mysql> update t_user set name = '吕布' where id = 1;-- session1 事务A 读取id = 1的数据 (注意，读到了未提交的脏数据 name = 吕布)mysql> select * from t_user where id = 1;+----+--------+| id | name   |+----+--------+|  1 | 吕布   |+----+--------+-- session1 事务A 对读到的脏数据进行提交(事务A结束)mysql> commit;-- session2 事务B 对刚才的修改进行回滚(事务B结束)mysql> rollback;-- 最终数据库中的name并没有改变，但是事务A读到了脏数据，这就是脏读-- 此事务隔离级别(读未提交)也会造成不可重复读和幻读，这里不做演示，看下面的隔离级别中演示

• 不可重复读

事务A 读取表的名字为达摩(并未提交)，之后事务B修改了名字为曹操(修改完提交了)，此时事务A在未提交的情况下又读取了name却变成了曹操，这就是不可重复读

设置事务的隔离级别为读已提交，也可以为读未提交，因为读未提交也会产生不可重复读现象

-- 设置事务的隔离级别为读已提交mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;Query OK, 0 rows affected (0.00 sec)-- 查看修改结果，已经为读已提交mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| READ-COMMITTED          |+-------------------------+

首先查看一下t_user表的数据

mysql> select * from t_user;+----+--------+| id | name   |+----+--------+|  1 | 达摩   |+----+--------+

然后进行如下测试

-- session1 事务A开始事务mysql> begin;Query OK, 0 rows affected (0.00 sec)-- session1 事务A 读取表中数据(注意此时未提交事务)mysql> select * from t_user where id = 1;+----+--------+| id | name   |+----+--------+|  1 | 达摩   |+----+--------+-- session2 事务B直接修改并提交事务mysql> update t_user set name = '曹操' where id = 1;Query OK, 1 row affected (0.03 sec)-- session1 事务A 再次读取表中数据，发现同一个事务中得到了不一样的结果，这就是不可重复读mysql> select * from t_user where id = 1;+----+--------+| id | name   |+----+--------+|  1 | 曹操   |+----+--------+-- 只要是事务A一直不提交，其他事务修改提交之后，事务A再次查询都能查到最新修改的结果-- 此事务隔离级别也会造成幻读，但是避免了脏读-- 测试是否避免脏读，事务A此时未提交-- session2 事务B开始mysql> begin;Query OK, 0 rows affected (0.00 sec)-- session2 事务B修改name = 伽罗 (注意此时未提交)mysql> update t_user set name = '伽罗' where id = 1;Query OK, 1 row affected (0.00 sec)-- session1 事务A进行读取，看到并没有读取到脏数据mysql> select * from t_user where id = 1;+----+--------+| id | name   |+----+--------+|  1 | 曹操   |+----+--------+-- session2 事务B回滚mysql> rollback;Query OK, 0 rows affected (0.05 sec)-- session1 事务A提交mysql> commit;Query OK, 0 rows affected (0.00 sec)

• 知识点：为什么加了写锁，别的事务还可以进行读操作？
• 因为InnoDB有MVCC机制(多版本并发控制)，可以使用快照读，而不会被阻塞。

• 幻读

事务A读取表中数据为一条(这时候未提交)， 事务B向表中添加了一条记录(提交)，此时事务A再次查询表中数据，结果是两条，好像产生了幻觉，这就是幻读

设置事务的隔离级别为可重复读，也可以为读未提交，也可以为读已提交，因为这三种事务隔离级别都会产生幻读现象

-- 设置事务的隔离级别为可重复读mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;Query OK, 0 rows affected (0.00 sec)-- 查看修改结果，已经为可重复读mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| REPEATABLE-READ         |+-------------------------+

首先查看一下t_user表的数据

mysql> select * from t_user;+----+--------+| id | name   |+----+--------+|  1 | 曹操   |+----+--------+

之后进行的测试

-- session1 事务A开启mysql> begin;Query OK, 0 rows affected (0.00 sec)-- session1 事务A查询(注意未提交)mysql> select * from t_user;+----+--------+| id | name   |+----+--------+|  1 | 曹操   |+----+--------+-- session2 事务B添加数据(提交)mysql> insert into t_user(name) values('马可波罗');Query OK, 1 row affected (0.04 sec)-- session1 事务A再次查询(一个事务中两次查询多出了数据，幻读)mysql> select * from t_user;+----+--------------+| id | name         |+----+--------------+|  1 | 曹操         ||  2 | 马可波罗      |+----+--------------+-- 测试此事务隔离级别能避免不可重复读现象(此时事务A还是未提交)-- session2 事务B修改id= 1的数据名字为狄仁杰(提交)mysql> update t_user set  name = '狄仁杰' where id = 1;Query OK, 1 row affected (0.04 sec)-- 此时session1 事务A再次读取数据(可以看到跟上一次一样，证明避免了不可重复读现象)mysql> select * from t_user;+----+--------------+| id | name         |+----+--------------+|  1 | 曹操         ||  2 | 马可波罗      |+----+--------------+

以上讲的都是并发操作可能造成的读的问题

关注我，更多精彩文章第一时间推送给你