MySQL事务隔离级别_mysql 查看事务隔离级别

一、前置知识

1、为什么要隔离级别？

并发事务会出现更新丢失、脏读、不可重复读，幻读。

• 更新丢失：当两个或多个事务更新同一行记录，会产生更新丢失现象。回滚覆盖，一个事务回滚操作，把其他事务已提交的数据给覆盖了；提交覆盖，一个事务提交操作，把其他事务已提交的数据给覆盖了
• 脏读：一个事务读取到了另一个事务修改但未提交的数据
• 不可重复读：一个事务读到了另一个事务已经提交的update数据，导致多次查询结果不一致
• 幻读：在同一个事务中，前后两次查询同一个范围的数据时，发现有新的数据插入，导致结果集不一致的情况。

通过隔离级别可以解决。

1、隔离级别种类

在MySQL中，可以使用SET TRANSACTION ISOLATION LEVEL语句设置事务的隔离级别。隔离级别用于控制并发事务之间的相互影响程度。

MySQL支持以下四种事务隔离级别：

1. READ UNCOMMITTED（未提交读）：允许事务读取其他未提交的事务所做的修改。但是，会出现脏读、不可重复读和幻读等问题。

2. READ COMMITTED（提交读）：只允许事务读取已经提交的事务所做的修改。在同一个事务内，对同一行数据的查询可能返回不同的结果。

3. REPEATABLE READ（可重复读）：事务执行期间，保证多次读取同一数据结果一致。但是，可能出现幻读问题。

4. SERIALIZABLE（可串行化）：最高级别的隔离级别，完全串行化事务执行。确保每个事务对数据库的读写操作是相互独立的。

2、查看/设置隔离级别

查看事务隔离级别：

-- 查看当前会话的事务隔离级别  
SELECT @@transaction_isolation;
SELECT @@tx_isolation;
-- 查看全局的事务隔离级别  
SELECT @@GLOBAL.tx_isolation;
SELECT @@GLOBAL.transaction_isolation;
1
2
3
4
5
6

可以使用以下命令来设置事务的隔离级别：

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

具体情况如下，详细可以查看官方文档：https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

例如，我们要设置当前会话的隔离级别为可重复读：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1

3、手动控制事务

开启事务：BEGIN; 和 START TRANSACTION; 在功能上是等价的。它们都会开始一个新的事务。
提交事务：COMMIT;
回滚事务：ROLLBACK;

例如：

BEGIN;
// 相关操作
COMMIT;
1
2
3

4、事务的锁信息查看

具体可以查看官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-transactions.html

其中关键就三张表，如下：

Three InnoDB INFORMATION_SCHEMA tables enable you to monitor transactions and diagnose potential locking problems:

INNODB_TRX: Provides information about every transaction currently executing inside InnoDB, including the transaction state (for example, whether it is running or waiting for a lock), when the transaction started, and the particular SQL statement the transaction is executing.

提供了当前在InnoDB内部执行的所有事务的信息

INNODB_LOCKS: Each transaction in InnoDB that is waiting for another transaction to release a lock (INNODB_TRX.TRX_STATE is LOCK WAIT) is blocked by exactly one blocking lock request. That blocking lock request is for a row or table lock held by another transaction in an incompatible mode. A lock that blocks a transaction is always held in a mode incompatible with the mode of requested lock (read vs. write, shared vs. exclusive). The blocked transaction cannot proceed until the other transaction commits or rolls back, thereby releasing the requested lock. For every blocked transaction, INNODB_LOCKS contains one row that describes each lock the transaction has requested, and for which it is waiting. INNODB_LOCKS also contains one row for each lock that is blocking another transaction, whatever the state of the transaction that holds the lock (INNODB_TRX.TRX_STATE is RUNNING, LOCK WAIT, ROLLING BACK or COMMITTING).

提供了有关InnoDB事务已请求但尚未获取的每个锁的信息，以及事务持有的阻止另一个事务的每个锁的信息。
通过查看这个表，你可以了解哪些事务正在等待锁，哪些事务持有锁，以及这些锁是如何影响数据库并发的。

INNODB_LOCK_WAITS: This table indicates which transactions are waiting for a given lock, or for which lock a given transaction is waiting. This table contains one or more rows for each blocked transaction, indicating the lock it has requested and any locks that are blocking that request. The REQUESTED_LOCK_ID value refers to the lock requested by a transaction, and the BLOCKING_LOCK_ID value refers to the lock (held by another transaction) that prevents the first transaction from proceeding. For any given blocked transaction, all rows in INNODB_LOCK_WAITS have the same value for REQUESTED_LOCK_ID and different values for BLOCKING_LOCK_ID.

包含了每个被阻止的InnoDB事务的信息，可以快速识别出哪些事务因为锁等待而被阻塞.

例子：查看哪些事务正在等待，哪些事务正在阻止它们

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

二、实战

表结构如下：

CREATE TABLE `isolation_level_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6
7

初始数据如下：

INSERT INTO `test`.`isolation_level_test` (`id`, `name`, `age`, `create_time`) VALUES (1, 'forlan1', 12, '2024-03-07 13:45:11');
1

1、READ UNCOMMITTED

会话1（为了验证在会话2中有啥效果）

BEGIN;
SELECT name,age,create_time FROM isolation_level_test;
INSERT INTO `isolation_level_test` (`name`, `age`) VALUES ('forlan2', 13);
SELECT name,age,create_time FROM isolation_level_test;
COMMIT;
1
2
3
4
5

查询结果如下：

会话2

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT name,age,create_time FROM isolation_level_test;
-- 等会话1插入数据后，再查询一次
SELECT name,age,create_time FROM isolation_level_test;
COMMIT;
1
2
3
4
5
6

查询结果如下：

可以看到，会话2读到会话1还没提交的数据，出现了脏读

2、READ COMMITTED

会话1（为了验证在会话2中有啥效果）

BEGIN;
SELECT name,age,create_time FROM isolation_level_test;
INSERT INTO `isolation_level_test` (`name`, `age`) VALUES ('forlan2', 13);
SELECT name,age,create_time FROM isolation_level_test;
COMMIT;
1
2
3
4
5

会话2

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT name,age,create_time FROM isolation_level_test;
-- 等会话1插入数据，提交事务后，再查询一次
SELECT name,age,create_time FROM isolation_level_test;
COMMIT;
1
2
3
4
5
6

查询结果如下：

可以看到，这种会话，不会出现脏读，但出现了不可重复读

3、REPEATABLE READ

会话1（为了验证在会话2中有啥效果）

BEGIN;
SELECT name,age,create_time FROM isolation_level_test;
INSERT INTO `isolation_level_test` (`name`, `age`) VALUES ('forlan2', 13);
SELECT name,age,create_time FROM isolation_level_test;
COMMIT;
1
2
3
4
5

会话2

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT name,age,create_time FROM isolation_level_test WHERE age>10;
-- 等会话1插入数据后，再查询一次
SELECT name,age,create_time FROM isolation_level_test WHERE age>10;
-- 等会话1插入数据，提交事务后，再查询一次
SELECT name,age,create_time FROM isolation_level_test WHERE age>10;
-- 修改数据，让后续查询变成当前读
UPDATE isolation_level_test SET name='forlan' WHERE age>10;
SELECT name,age,create_time FROM isolation_level_test WHERE age>10;
COMMIT;
1
2
3
4
5
6
7
8
9
10
11

可以看到，在当前读的情况下，会出现幻读，这就很奇怪了对吧，因为MySQL宣称是解决了幻读问题，其中"快照读"依靠MVCC控制，"当前读"通过Next-key Lock解决。

总的来说，RR下还是会出现幻读的，在当前读的情况下，所以如果为了避免这种，需要我们在当前事务加上S锁或X锁，比如，在会话1插入数据前，改我们的查询语句为：
SELECT name,age,create_time FROM isolation_level_test WHERE age>10 lock in share mode;
SELECT name,age,create_time FROM isolation_level_test WHERE age>10 for update;

4、SERIALIZABLE

这个不用验证了，所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰了，一般不用，性能特别低。

三、总结

Oracle 默认使用READ COMMITTED（读已提交）隔离级别
MySQL默认使用REPEATABLE（可重复读）隔离级别
总的来说，选择什么隔离级别，取决于你要并发性能还是数据一致性，两者之间的一个权衡。