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

我们自己通过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;

这也说明了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回滚,修改的数据被还原,就产生了脏读现象。


[SERIALIZABLE]

这个级别的封锁就很好理解了,读加共享锁,写加排他锁,读写互斥。使用的悲观锁的理论,所有操作串行化执行,数据更加安全,但是并发能力非常差。如果你的业务并发的特别少或者没有并发,同时又要求数据及时可靠的话,可以使用这种模式。

我们重点探究的不是[READ UNCOMMITTED]和[SERIALIZABLE]级别,而是[READ COMMITTED]和[REPEATABLE READ]。如果您对此感兴趣可以看一下后两篇博客。

原文发布于微信公众号 - 撸码那些事(lumanxs)

原文发表时间:2018-05-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

关于查看dba_data_files的一个小问题(r7笔记第72天)

今天帮一个朋友看一个pl/sql的问题,他已经钻到一个死胡同里列,可能明眼人一看就知道哪里有问题,但是当局者迷,所以我抽空看了一下这个pl/sql块。 pl/s...

3825
来自专栏撸码那些事

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

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

1183
来自专栏黑白安全

秒杀杀软的badusb

我们看过很多黑客电影,上面的黑客利用一个U盘轻松入侵到对方计算机。觉得很酷,今天我们也来做一个吧。

1.2K4
来自专栏数据和云

案发现场:被注入的软件及 ORA-600 16703 灾难的恢复

最近帮助一个客户恢复数据库,遇到了如下这个问题。让我们再一次惊醒于数据安全,如果不做好防范,问题总是会来得猝不及防。

2554
来自专栏乐沙弥的世界

只读表空间的备份与恢复

--====================== --  只读表空间的备份与恢复 --====================== 一、只读表空间的特性...

922
来自专栏杨建荣的学习笔记

通过ORA错误反思sql语句规范(r4笔记第41天)

今天快下班的时候,有个开发的同事问我一个问题,说他在客户端执行一条sql语句,里面包含子查询,如果单独执行子查询,会报"invalid identifier"...

2513
来自专栏程序员同行者

MySQL事务学习-->隔离级别

1023
来自专栏MYSQL轻松学

MySQL中,一条语句是否会被binlog记录以及以什么样的模式记录

Binlog官方 In MySQL 5.6, whether a statement is to be logged and thelogging mode t...

3279
来自专栏linjinhe的专栏

隔离级别、SI 和 SSIACID隔离级别Snapshot IsolationSerializable Snapshot Isolation

1524
来自专栏蓝天

MySQL数据导入导出方法与工具mysqlimport

mysqlimport位于mysql/bin目录中,是mysql的一个载入(或者说导入)数据的一个非常有效的工具。这是一个命令行工具。有两个参数以及大量的选项可...

2143

扫码关注云+社区

领取腾讯云代金券