查询和设置事务隔离级别

查询和设置事务隔离级别

通过以下SQL语句可以查看全局MySQL事务隔离级别和当前会话的事务隔离级别：

SELECT @@global.tx_isolation, @@tx_isolation;

每个连接到MySQL的客户端可以使用以下SQL设置当前连接（客户端）的事务隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL 事务隔离级别;

其中事务隔离级别有四个值：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

脏读问题

脏读问题指一个事务读取到了另一个事务为提交保存的数据，之后此事务进行了回滚操作，导致第一个事务读取了一个不存在的脏数据。

通过以下SQL演示了脏读问题，使用了读未提交的事务隔离级别：

-- 创建一个城市表
DROP TABLE IF EXISTS city;
CREATE TABLE city (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(250) NOT NULL
);

-- 客户端 A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- 客户端 B
START TRANSACTION;
INSERT INTO city (name) VALUES ('南京');
-- 客户端 A
SELECT * FROM city;
-- 客户端 B
ROLLBACK;
-- 客户端 A
SELECT * FROM city;

不可重复读问题

不可重复读问题在同一个事务中，同一个查询在不同的时间得到了不同的结果。

通过以下SQL演示了在读已提交的事务隔离级别下可能存在的不可重复读的问题：

-- 客户端 A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM city WHERE id = 1;
-- 客户端 B
START TRANSACTION;
UPDATE city SET name = '南京' WHERE id = 1;
COMMIT;
-- 客户端 A
SELECT * FROM city WHERE id = 1;

幻读问题

幻读问题指同一个查询在不同时间得到了不同的结果，例如，一个SELECT被执行了两次，但第二次返回了第一次没有返回的一行，即“幻像”行。

通过以下SQL演示了在可重复读隔离级别下可能存在的幻读的问题：

-- 客户端 A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM city WHERE id < 5; -- 查询出1条数据
-- 客户端 B
START TRANSACTION;
INSERT INTO city (id, name) VALUES (2, '北京');
COMMIT;
-- 客户端 A
UPDATE city SET name = '京城' WHERE id = 2;
SELECT * FROM city WHERE id < 5; -- 查询出2条数据

总结

MySQL中有四种事务隔离级别：读未提交、读已提交、可重复读和序列化，其中可重复读是MySQL默认的事务隔离级别。脏读、不可重复读和幻读是不同隔离级别下可能出现的问题。