【数据库】隔离级别，锁基础，一致性锁定/非锁定 读

本文内容：

• 数据库中的隔离级别
• 锁的类型
• 幻读举例
• 一致性非锁定读
• 一致性锁定读

---

隔离级别相关语句

1.查看当前会话隔离级别

select @@tx_isolation;

2.查看系统当前隔离级别

select @@global.tx_isolation;

3.设置当前会话隔离级别

set session transaction isolation level repeatable read;

4.设置系统当前隔离级别

set global transaction isolation level repeatable read;

5.命令行，开始事务时

set autocommit=off 或者 start transaction

---

查询事务或锁的相关语句

查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

查询 正在执行的事务：
SELECT * FROM information_schema.INNODB_TRX;

---

锁的两个基本类型

共享锁【S锁】
又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排他锁【X锁】
又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。

---

各隔离级别详解（以下场景均指事务执行过程中）

Read Uncommitted（读取未提交内容）

• 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）

• 这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select（连续的两次）可能返回不同结果，即所谓的幻读 （Phantom Read）。

Repeatable Read（可重读）（MySQL默认事务隔离级别）

• 它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行（如果退出当前事务即失效）。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）

• 这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

可以在cmd中使用mysql指令，模拟并发事务的场景。

mysql -u root -p

---

一致性非锁定读（顾名思义，只针对select）

在事务隔离级别RC和RR下，InnoDB存储引擎引擎使用非锁定的一致性读。在这种默认的读取方式下，才能呈现上节中各种隔离级别产生的场景。

一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(multi versionning)的方式来读取当前执行时间数据库中行的数据，如果读取的行正在执行DELETE或UPDATE操作，这时读取操作不会因此等待行上锁的释放。相反的，InnoDB会去读取行的一个快照数据。

然而，对于快照数据的定义却不相同。在RC事务隔离级别下，对于快照数据，非一致性读总是被锁定行的最新一份快照数据。而在RR事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

上面展示了InnoDB存储引擎一致性的非锁定读。之所以称为非锁定读，因为不需要等待访问的行上X锁的释放

---

多版本控制

快照数据是指该行之前版本的数据，该实现是通过undo段来完成。而undo用来事务中的回滚数据，因此快照数据本身没有额外的开销。这里可以理解为既然undo日志包括了所有用来恢复历史版本数据的信息，那么我们只要将“不同版本”指针指向不同时间节点的undo日志即可。此外，读取快照数据不需要上锁，因为没有事务需要对历史数据进行修改操作。

---

一致性锁定读

如果一个事务希望在读取的时候就把记录锁住，不允许其他事务进行修改应该怎么做呢？那就是SELECT … FOR UPDATE，SELECT … FOR UPDATE显式地给一条记录加X锁，因此其他事务不能获取该记录的任何锁。我们也可以使用SELECT … LOCK IN SHARE MODE来给记录显式地加S锁，因此其他事务能够获取该记录的S锁而不能获取该记录的X锁，这两种语句都是有特定的应用场景的。

注意：for update和in share mode后缀，只能和select语句搭配使用

（X锁和任何锁都不兼容，S锁和S锁可以兼容）

---

一些场景（但不是传统意义上的幻读）

1.RC和RR模式下，当其中一个线程往数据库中添加了主键，但并没有commit时（※），另一线程若尝试添加同样的记录，则会提示超时。

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2.RR模式下会出现的幻读情况

t Session A                   Session B
|
| START TRANSACTION;          START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| empty set
|                             INSERT INTO t_bitfly
|                             VALUES (1, 'a');
|
| SELECT * FROM t_bitfly;
| empty set
|                             COMMIT;
|
| SELECT * FROM t_bitfly;
| empty set
|
| INSERT INTO t_bitfly VALUES (1, 'a');
| ERROR 1062 (23000):
| Duplicate entry '1' for key 1
v (shit, 刚刚明明告诉我没有这条记录的)

t Session A                  Session B
|
| START TRANSACTION;         START TRANSACTION;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|                            INSERT INTO t_bitfly
|                            VALUES (2, 'b');
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|                            COMMIT;
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | a     |
| +------+-------+
|
| UPDATE t_bitfly SET value='z';
| Rows matched: 2  Changed: 2  Warnings: 0
| (怎么多出来一行)
|
| SELECT * FROM t_bitfly;
| +------+-------+
| | id   | value |
| +------+-------+
| |    1 | z     |
| |    2 | z     |
| +------+-------+
|

一致性非锁定读读取的仍然是同一个快照版本，即旧的版本。
在同一个事务中，我们不是select，而用update，就会查到数据，因为此时没有使用一致性非锁定读，select对应的是旧快照的版本，而update修改的却是最新的版本

---

RR模式下避免幻读

InnoDB提供了next-key locks，但需要应用程序自己去加锁。

SELECT * FROM child WHERE id > 100 FOR UPDATE;