文章目录
一 事务隔离机制/级别介绍
事务具有原子性、一致性、隔离性、持久性四大特性，而隔离性顾名思义指的就是事务彼此之间隔离开，多个事务在同时处理一个数据时彼此之间互相不影响，如如果隔离的不够好就有可能会产生脏读、不可重复度、幻读等读现象，为此，隔离性总共分为四种级别
由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读 、不可重复读 、幻读 这几类问题。
√: 可能出现 ×: 不会出现
| 特点 | 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|---|
| 特点 | 脏读 | 不可重复读 | 幻读 | |
| Read uncommitted（独立提交，未提交读） | 允许事务查看其他事务所进行的未提交更改 | √ | √ | √ |
| Read committed（提交读） | 允许其他事务查看已经提交的事务 | × | √ | √ |
| Repeatable read（可重复读，innodb引擎默认） | 确保每个事务的 SELECT 输出一致 InnoDB 的默认级别 #commit之后，其他窗口看不到数据，必须退出重新登录查看 | × | × | √ |
| Serializable（可序列化、串行化） | 将一个事务于其他事务完全隔离，即串行化 #当一个事务没有提交，查询也不行。例如：我改微信头像的时候你不能看我的信息，我看你朋友圈的时候你不能发朋友圈也不能看朋友圈 | × | × | × |
需要强调的是：我们确实可以采用提高事务的隔离级别的方式来解决脏读、不可重复读、幻读等问题，但与此同时，事务的隔离级别越高，并发能力也就越低。所以，还需要读者根据业务需要进行权衡。
二 未提交读(Read uncommitted)
未提交读(READ UNCOMMITTED)是最低的隔离级别。通过名字我们就可以知道，在这种事务隔离级别下，一个事务可以读到另外一个事务未提交的数据。
未提交读的数据库锁情况（实现原理）
事务在读数据的时候并未对数据加锁。 务在修改数据的时候只对数据增加行级共享锁。现象：
事务1读取某行记录时，事务2也能对这行记录进行读取、更新（因为事务一并未对数据增加任何锁） 当事务2对该记录进行更新时，事务1再次读取该记录，能读到事务2对该记录的修改版本（因为事务二只增加了共享读锁，事务一可以再增加共享读锁读取数据），即使该修改尚未被提交，若此时事务2回滚，那事务1读到的就脏数据了，这就引发了脏读现象 事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。（因为事务一对数据增加了共享读锁，事务二不能增加排他写锁进行数据的修改）**举例** 下面还是借用我在数据库的读现象浅析一文中举的例子来说明在未提交读的隔离级别中两个事务之间的隔离情况。 | 事务一 | 事务二 | | :--: | :--: | | | /* Query 1 */SELECT age FROM users WHERE id = 1;/* will read 20 */ | | | | /* Query 2 */UPDATE users SET age = 21 WHERE id = 1;/* No commit here */ | | /* Query 1 */SELECT age FROM users WHERE id = 1;/* will read 21 */ | | | | ROLLBACK;/* lock-based DIRTY READ */ |
事务一共查询了两次，在两次查询的过程中，事务二对数据进行了修改，并未提交（commit）。但是事务一的第二次查询查到了事务二的修改结果。在数据库的读现象浅析中我们介绍过，这种现象我们称之为脏读。
所以，未提交读会导致脏读
三 提交读(Read committed)
提交读(READ COMMITTED)也可以翻译成读已提交，通过名字也可以分析出，在一个事务修改数据过程中，如果事务还没提交，其他事务不能读该数据。
提交读的数据库锁情况
事务对当前被读取的数据加 行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。现象：
事务1在读取某行记录的整个过程中，事务2都可以对该行记录进行读取（因为事务一对该行记录增加行级共享锁的情况下，事务二同样可以对该数据增加共享锁来读数据。）。 事务1读取某行的一瞬间，事务2不能修改该行数据，但是，只要事务1读取完改行数据，事务2就可以对该行数据进行修改。（事务一在读取的一瞬间会对数据增加共享锁，任何其他事务都不能对该行数据增加排他锁。但是事务一只要读完该行数据，就会释放行级共享锁，一旦锁释放，事务二就可以对数据增加排他锁并修改数据） 事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。（事务一在更新数据的时候，会对该行数据增加排他锁，知道事务结束才会释放锁，所以，在事务二没有提交之前，事务一都能不对数据增加共享锁进行数据的读取。所以，提交读可以解决`脏读`的现象）**举例** | 事务一 | 事务二 | | :--: | :--: | | | /* Query 1 */SELECT * FROM users WHERE id = 1; | | | | /* Query 2 */UPDATE users SET age = 21 WHERE id = 1;COMMIT;/* in multiversion concurrencycontrol, or lock-based READ COMMITTED */ | | /* Query 1 */SELECT * FROM users WHERE id = 1;COMMIT;/*lock-based REPEATABLE READ */ | |
在提交读隔离级别中，在事务二提交之前，事务一不能读取数据。只有在事务二提交之后，事务一才能读数据。
但是从上面的例子中我们也看到，事务一两次读取的结果并不一致，所以提交读不能解决不可重复读的读现象。
简而言之，提交读这种隔离级别保证了读到的任何数据都是提交的数据，避免了脏读(dirty reads)。但是不保证事务重新读的时候能读到相同的数据，因为在每次数据读完之后其他事务可以修改刚才读到的数据。
四 可重复读(Repeatable reads)
可重复读(REPEATABLE READS),由于提交读隔离级别会产生不可重复读的读现象。所以，比提交读更高一个级别的隔离级别就可以解决不可重复读的问题。这种隔离级别就叫可重复读（这名字起的是不是很任性！！）
可重复读的数据库锁情况
事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加 行级共享锁，直到事务结束才释放； 事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加 行级排他锁，直到事务结束才释放。现象
事务1在读取某行记录的整个过程中，事务2都可以对该行记录进行读取（因为事务一对该行记录增加行级共享锁的情况下，事务二同样可以对该数据增加共享锁来读数据。）。 事务1在读取某行记录的整个过程中，事务2都不能修改该行数据（事务一在读取的整个过程会对数据增加共享锁，直到事务提交才会释放锁，所以整个过程中，任何其他事务都不能对该行数据增加排他锁。所以，可重复读能够解决`不可重复读`的读现象） 事务1更新某行记录时，事务2不能对这行记录做更新，直到事务1结束。（事务一在更新数据的时候，会对该行数据增加排他锁，知道事务结束才会释放锁，所以，在事务二没有提交之前，事务一都能不对数据增加共享锁进行数据的读取。所以，提交读可以解决`脏读`的现象）**举例** | 事务一 | 事务二 | | :--: | :--: | | | /* Query 1 */SELECT * FROM users WHERE id = 1;COMMIT; | | | | /* Query 2 */UPDATE users SET age = 21 WHERE id = 1;COMMIT;/* in multiversion concurrencycontrol, or lock-based READ COMMITTED */ |
在上面的例子中，只有在事务一提交之后，事务二才能更改该行数据。所以，只要在事务一从开始到结束的这段时间内，无论他读取该行数据多少次，结果都是一样的。
从上面的例子中我们可以得到结论：可重复读隔离级别可以解决不可重复读的读现象。但是可重复读这种隔离级别中，还有另外一种读现象他解决不了，那就是幻读。看下面的例子：
| 事务一 | 事务二 |
|---|---|
| /* Query 1 */SELECT * FROM usersWHERE age BETWEEN 10 AND 30; | |
| /* Query 2 */INSERT INTO users VALUES ( 3, ‘Bob’, 27 );COMMIT; | |
| /* Query 1 */SELECT * FROM usersWHERE age BETWEEN 10 AND 30; |
上面的两个事务执行情况及现象如下：
1.事务一的第一次查询条件是age BETWEEN 10 AND 30;如果这是有十条记录符合条件。这时，他会给符合条件的这十条记录增加行级共享锁。任何其他事务无法更改这十条记录。
2.事务二执行一条sql语句，语句的内容是向表中插入一条数据。因为此时没有任何事务对表增加表级锁，所以，该操作可以顺利执行。
3.事务一再次执行SELECT * FROM users WHERE age BETWEEN 10 AND 30;时，结果返回的记录变成了十一条，比刚刚增加了一条，增加的这条正是事务二刚刚插入的那条。
所以，事务一的两次范围查询结果并不相同。这也就是我们提到的幻读
五 可序列化(Serializable)
可序列化(Serializable)是最高的隔离级别，前面提到的所有的隔离级别都无法解决的幻读，在可序列化的隔离级别中可以解决。
我们说过，产生幻读的原因是事务一在进行范围查询的时候没有增加范围锁(range-locks：给SELECT 的查询中使用一个“WHERE”子句描述范围加锁），所以导致幻读。
可序列化的数据库锁情况
事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放； 事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放。现象
事务1正在读取A表中的记录时，则事务2也能读取A表，但不能对A表做更新、新增、删除，直到事务1结束。(因为事务一对表增加了表级共享锁，其他事务只能增加共享锁读取数据，不能进行其他任何操作） 事务1正在更新A表中的记录时，则事务2不能读取A表的任意记录，更不可能对A表做更新、新增、删除，直到事务1结束。（事务一对表增加了表级排他锁，其他事务不能对表增加共享锁或排他锁，也就无法进行任何操作）虽然可序列化解决了脏读、不可重复读、幻读等读现象。但是序列化事务会产生以下效果： 1.无法读取其它事务已修改但未提交的记录。 2.在当前事务完成之前，其它事务不能修改目前事务已读取的记录。 3.在当前事务完成之前，其它事务所插入的新记录，其索引键值不能在当前事务的任何语句所读取的索引键范围中。
四种事务隔离级别从隔离程度上越来越高，但同时在并发性上也就越来越低。之所以有这么几种隔离级别，就是为了方便开发人员在开发过程中根据业务需要选择最合适的隔离级别。
六 InnoDB存储引擎
前文我们提到过InnoDB支持行锁（锁定字段含有索引的情况下，否则走表锁），但锁定方式并非简单的锁定指定行上的索引，而是分为3种锁定算法：
1）记录锁（Record Locks）：锁定指定行的索引项
2）Gap Locks：锁定某一个范围内的索引，但不包括记录本身
3）间隙锁定（Next-Key Locks）：锁定一个范围内的索引，并且锁定记录本身 Next-Key Locks = Record Locks + Gap Locks
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record
而InnoDB 存储引擎默认隔离级别为可重复读（Repeatable Read），该隔离级别下，对于索引的查询采用 next-key locks。这样做避免了幻读现象的产生。
七 修改事务隔离级别
在 MySQL 中，可以通过show variables like '%tx_isolation%'或select @@tx_isolation;语句来查看当前事务隔离级别。
查看当前事务隔离级别的 SQL 语句和运行结果如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> show variables like ‘%tx_isolation%’;
±--------------±----------------+
| Variable_name | Value |
±--------------±----------------+
| tx_isolation | REPEATABLE-READ |
±--------------±----------------+
1 row in set, 1 warning (0.17 sec）
mysql> select @@tx_isolation;
±----------------+
| @@tx_isolation |
±----------------+
| REPEATABLE-READ |
±----------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> show variables like ‘%tx_isolation%’;
±--------------±----------------+
| Variable_name | Value |
±--------------±----------------+
| tx_isolation | REPEATABLE-READ |
±--------------±----------------+
1 row in set, 1 warning (0.17 sec）
mysql> select @@tx_isolation;
±----------------+
| @@tx_isolation |
±----------------+
| REPEATABLE-READ |
±----------------+
1 row in set, 1 warning (0.00 sec)
|
结果显示，目前 MySQL 的事务隔离级别是 REPEATABLE-READ。
另外，还可以使用下列语句分别查询全局和会话的事务隔离级别：
|
1
2
|
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
1
2
|
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
|
提示：在MySQL 8.0.3 中，tx_isolation 变量被 transaction_isolation 变量替换了。在 MySQL 8.0.3 版本中查询事务隔离级别，只要把上述查询语句中的 tx_isolation 变量替换成 transaction_isolation 变量即可。
修改事务隔离级别
MySQL 提供了 SET TRANSACTION 语句，该语句可以改变单个会话或全局的事务隔离级别。语法格式如下：
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}其中，SESSION 和 GLOBAL 关键字用来指定修改的事务隔离级别的范围：
- SESSION：表示修改的事务隔离级别将应用于当前 session（当前 cmd 窗口）内的所有事务；
- GLOBAL：表示修改的事务隔离级别将应用于所有 session（全局）中的所有事务，且当前已经存在的 session 不受影响；
- 如果省略 SESSION 和 GLOBAL，表示修改的事务隔离级别将应用于当前 session 内的下一个还未开始的事务。
任何用户都能改变会话的事务隔离级别，但是只有拥有 SUPER 权限的用户才能改变全局的事务隔离级别。
如果使用普通用户修改全局事务隔离级别，就会提示需要超级权限才能执行此操作的错误信息，SQL 语句和运行结果如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql -utestuser -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright ? 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql -utestuser -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright ? 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)
|
示例 1
使用 SET TRANSACTION 语句分别修改 session 和全局的事务隔离级别SQL 语句和运行结果如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> select @@session.tx_isolation;
±-----------------------+
| @@session.tx_isolation |
±-----------------------+
| SERIALIZABLE |
±-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
±----------------------+
| @@global.tx_isolation |
±----------------------+
| REPEATABLE-READ |
±----------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> select @@session.tx_isolation;
±-----------------------+
| @@session.tx_isolation |
±-----------------------+
| SERIALIZABLE |
±-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation;
±----------------------+
| @@global.tx_isolation |
±----------------------+
| REPEATABLE-READ |
±----------------------+
1 row in set, 1 warning (0.00 sec)
|
还可以使用 set tx_isolation 命令直接修改当前 session 的事务隔离级别，SQL 语句和运行结果如下：
|
1
2
3
4
5
6
7
8
9
10
|
mysql> set tx_isolation=‘READ-COMMITTED’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@session.tx_isolation;
±-----------------------+
| @@session.tx_isolation |
±-----------------------+
| READ-COMMITTED |
±-----------------------+
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
|
mysql> set tx_isolation=‘READ-COMMITTED’;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@session.tx_isolation;
±-----------------------+
| @@session.tx_isolation |
±-----------------------+
| READ-COMMITTED |
±-----------------------+
1 row in set, 1 warning (0.00 sec)
|
永久修改如下
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
transaction_isolation=read-uncommit