【眼见为实】自己动手实践理解数据库READ UNCOMMITED && SERIALIZABLE

阅读量：7231 次

发布时间：2019-06-29

本文共 3155 字，大约阅读时间需要 10 分钟。

我们自己通过Sql语句模拟场景来验证Mysql InnoDB引擎事务各级隔离级别对应封锁协议的工作机制。在开始实践之前我们需要做一些准备工作。

准备工作

①准备测试表和测试数据

需要建立一个测试数据表，建表语句：

create table users (    id int auto_increment not null primary key,    name char(10) not null,    state int not null);

然后插入一条测试数据：

insert into users values(1,'swj',0);

②关闭数据库事务自动提交

# 0为关闭 1为开启SET autocommit = 0;

设置完成后我们可以通过下列语句查看是否关闭了自动提交。

show variables like 'autocommit';

OFF关闭无误。

③设置InnoDB存储引擎隔离级别

首先我们可以使用下面的语句分别查看数据库的系统级隔离级别和会话级隔离级别。

select @@global.tx_isolation,@@tx_isolation;

mark

这也说明了MySql数据库默认使用的隔离级别是可重复读（REPEATABLE-READ）。

我们可以使用下面的语句设置隔离级别。隔离级别有READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE四种。

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

[READ UNCOMMITTED]

首先设置数据库隔离级别为读未提交（READ UNCOMMITTED）:

set global transaction isolation level READ UNCOMMITTED ;set session transaction isolation level READ UNCOMMITTED ;

[READ UNCOMMITTED]能解决的问题

我们来看一下为什么[READ UNCOMMITTED]能解决丢失更新的问题：

事务1：

START TRANSACTION;① UPDATE users SET state=state+1 WHERE id=1;② SELECT sleep(10);COMMIT;

事务2：

START TRANSACTION;① UPDATE users SET state=state+1 WHERE id=1;COMMIT;

事务1先于事务2执行。

事务1的执行信息：

[SQL 1]START TRANSACTION;受影响的行: 0时间: 0.000s[SQL 2]UPDATE users SET state=state+1 WHERE id=1;受影响的行: 1时间: 0.001s[SQL 3]SELECT sleep(10);受影响的行: 0时间: 10.000s[SQL 4]COMMIT;受影响的行: 0时间: 0.068s

事务2的执行信息：

[SQL 1]START TRANSACTION;受影响的行: 0时间: 0.000s[SQL 2]UPDATE users SET state=state+1 WHERE id=1;受影响的行: 1时间: 8.787s[SQL 3]COMMIT;受影响的行: 0时间: 0.098s

执行结果：

结论：读未提交[READ UNCOMMITTED]隔离级别可以解决丢失更新的问题。

分析：因为读未提交[READ UNCOMMITTED]隔离级别对应数据库的一级封锁协议。一级封锁协议在修改数据之前对其加X锁，直到事务结束释放X锁。读数据不加锁。因为事务1先执行修改，修改前申请持有X锁，事务结束释放X锁。持锁时间段为[SQL 2]开始前到[SQL 4]结束，持锁时间大约为10.069s。事务2也执行修改操作，修改前也申请持有X锁。因为事务1执行更新操作等待10秒才会提交释放锁，所以事务2申请持锁需要等待，直到事务1结束才能获取到锁的持有权进行修改。事务2的执行信息中的[SQL 2]时间为8.787s（因为手速原因存在误差，实际应该为10秒左右）就能说明这一点。这样对同一数据的修改会变成串行化的修改，所以不会出现因为并发只进行一次+1的情况，也就不会出现丢失修改的问题。

[READ UNCOMMITTED]不能解决的问题

读未提交，顾名思义，一个事务可以读到另一个事务没有提交的内容，如果另一个事务进行回滚就会产生脏读。

我们来模拟一下脏读：

事务1：

START TRANSACTION;① UPDATE users SET state=1 WHERE id=1;② SELECT sleep(5);ROLLBACK;

事务2：

START TRANSACTION;① SELECT * FROM users WHERE id=1;COMMIT;

事务1先于事务2执行。

事务1的执行信息：

[SQL 1]START TRANSACTION;受影响的行: 0时间: 0.000s[SQL 2]UPDATE users SET state=1 WHERE id=1;受影响的行: 1时间: 0.002s[SQL 3]SELECT sleep(5);受影响的行: 0时间: 5.000s[SQL 4]ROLLBACK;受影响的行: 0时间: 0.067s

事务2的执行信息：

[SQL 1]START TRANSACTION;受影响的行: 0时间: 0.001s[SQL 2]SELECT * FROM users WHERE id=1;受影响的行: 0时间: 0.001s[SQL 3]COMMIT;受影响的行: 0时间: 0.000s

事务2的执行结果:

事务1和事务2都执行结束时，再进行一次查询的结果:

结论：读未提交[READ UNCOMMITTED]隔离级别解决不了脏读的问题，更解决不了不可重复读的问题。

分析：因为读未提交[READ UNCOMMITTED]隔离级别对应数据库的一级封锁协议。一级封锁协议在修改数据之前对其加X锁，直到事务结束释放X锁。读数据不加锁。因为事务1先执行修改，修改前申请持有X锁，持锁时间段为[SQL 2]开始前到[SQL 4]结束，持锁时间大约为5.069s。事务2执行读操作，不需要申请持锁，而是直接去磁盘读取数据。读取出的数据是事务1修改后的，而此时事务1回滚，修改的数据被还原，就产生了脏读现象。