Tag Mysql

与世界分享我刚编的mysql http隧道工具-hersql原理与使用

Category Mysql
Tag Mysql
Tag Go
Posted on
View

原文地址:https://blog.fanscore.cn/a/53/

1. 前言

本文是与世界分享我刚编的转发ntunnel_mysql.php的工具的后续,之前的实现有些拉胯,这次重构了下。需求背景是为了在本地macbook上通过开源的mysql可视化客户端(dbeaver、Sequel Ace等)访问我司测试环境的mysql,整个测试环境的如图所示:

image.png

那么就有以下几种方式:

  • 客户端直连mysql #Pass# 测试环境mysql只提供了内网ip,只允许测试环境上的机器连接,因此不可行
  • 通过ssh隧道连接 #Pass# 测试环境机器倒是可以ssh上去,但是只能通过堡垒机接入,且堡垒机不允许ssh隧道,因此不可行
  • navicat http隧道连接 #Pass# 测试环境有机器提供了公网ip开放了http服务,因此技术上是可行的,但navicat非开源免费软件,我司禁止使用,因此不可行
  • 测试环境选一台机器建立mysql代理转发请求 #Pass# 测试环境机器只开放了80端口,且已被nginx占用,因此不可行
  • 内网穿透 这个想法很好,下次不要再想了

image.png

既然上面的方式都不行,那怎么办呢?因此我产生了一个大胆的想法

2. 一个大胆的想法

大概架构如下 image.png

首先,在本地pc上启动一个sidecar进程,该进程监听3306端口,实现mysql协议,将自己伪装为一个mysql server。本地pc上的mysql客户端连接到sidecar,发送请求数据包给sidecar,从sidecar读取响应包。

然后在测试环境某台机器上启动transport进程,该进程启动http服务,由nginx代理转发请求,相当于监听在80端口,然后连接到测试环境的mysql server。

sidecar会将来自客户端的请求包通过http请求转发给transporttransport将请求包转发到测试环境对应的mysql server,然后读取mysql的响应数据包,然后将响应数据包返回给sidecarsidecar再将响应包返回给mysql客户端。

遵循上述的基本原理,我将其实现出来: https://github.com/Orlion/hersql。但是在描述hersql的实现细节之前我们有必要了解下mysql协议

3. mysql协议

mysql客户端与服务端交互过程主要分为两个阶段:握手阶段与命令阶段。交互流程如下: image.png

在最新版本中,握手过程比上面要复杂,会多几次交互

3.1 握手阶段

在握手阶段,3次握手建立tcp连接后服务端会首先发送一个握手初始化包,包含了 * 协议版本号:指示所使用的协议版本。 * 服务器版本:指示MySQL服务器版本的字符串。 * 连接ID:在当前连接中唯一标识客户端的整数。 * 随机数据:包含一个随机字符串,用于后续的身份验证。 * 服务器支持的特性标志:指示服务器支持的客户端功能的位掩码。 * 字符集:指示服务器使用的默认字符集。 * 默认的身份验证插件名(低版本没有该数据)

随后客户端会发送一个登录认证包,包含了:

  • 协议版本号:指示所使用的协议版本。
  • 用户名:用于身份验证的用户名。
  • 加密密码:客户端使用服务端返回的随机数对密码进行加密
  • 数据库名称:连接后要使用的数据库名称。
  • 客户端标志:客户端支持的功能的位掩码。
  • 最大数据包大小:客户端希望接收的最大数据包大小。
  • 字符集:客户端希望使用的字符集。
  • 插件名称:客户端希望使用的身份验证插件的名称。

服务端收到客户端发来的登录认证包验证通过后会发送一个OK包,告知客户端连接成功,可以转入命令交互阶段

在mysql 8.0默认的身份验证插件为caching_sha2_password,低版本为mysql_native_password,两者的验证交互流程有所不同个,caching_sha2_password在缓存未命中的情况下还会多几次交互。另外如果服务端与客户端的验证插件不同的话,也是会多几次交互。

3.2 命令阶段

在命令阶段,客户端会发送命令请求包到服务端。数据包的第一个字节标识了当前请求的类型,常见的命令有:

  • COM_QUERY命令,执行SQL查询语句。
  • COM_INIT_DB命令,连接到指定的数据库。
  • COM_QUIT命令,关闭MySQL连接。
  • COM_FIELD_LIST命令,列出指定表的字段列表。
  • COM_PING命令,向MySQL服务器发送PING请求。
  • COM_STMT_系列预处理语句命令

请求响应的模式是客户端会发一个请求包,服务端会回复n(n>=0)个响应包

最后客户端断开连接时会主动发送一个COM_QUIT命令包通知服务端断开连接

4. hersql数据流转过程

在了解mysql协议之后我们就可以来看下hersql的数据流转过程了。

image.png

transport连接mysql server时必须要知道目标数据库的地址与端口号(mysql client连接的是sidecar),所以hersql要求mysql client需要在数据库名中携带目标数据库的地址与端口号。

transport发给mysql server的登录请求包中需要包含用mysql server发来的随机数加密之后的密码,但是mysql client给到sidecar的登录请求包中的密码是用sidecar给的随机数加密的,因此无法直接拿来使用,所以hersql要求mysql client需要在数据库名中携带密码原文,transport会用mysql server给的随机数进行加密, 这也是hersql的局限。

5. hersql使用

上面介绍了一堆原理性的东西,那么如何使用呢?

5.1 在一台能够请求目标mysql server的机器上部署hersql transport

首先你需要下载下来hersql的源码:https://github.com/Orlion/hersql,还需要安装下golang,这些都完成后你就可以启动hersql transport了。但是先别着急,我先解释下transport的配置文件tranport.example.yaml:

server:
  # transport http服务监听的地址
  addr: :8080

log:
  # 标准输出的日志的日志级别
  stdout_level: debug
  # 文件日志的日志级别
  level: error
  # 文件日志的文件地址
  filename: ./storage/transport.log
  # 日志文件的最大大小(以MB为单位), 默认为 100MB。日志文件超过此大小会创建个新文件继续写入
  maxsize: 100
  # maxage 是根据文件名中编码的时间戳保留旧日志文件的最大天数。 
  maxage: 168
  # maxbackups 是要保留的旧日志文件的最大数量。默认是保留所有旧日志文件。
  maxbackups: 3
  # 是否应使用 gzip 压缩旋转的日志文件。默认是不执行压缩。
  compress: false

你可以根据你的需求修改配置,然后就可以启动transport

$ go run cmd/transport/main.go -conf=transport.example.yaml

一般情况下都是会先编译为可执行文件,由systemd之类的工具托管transport进程,保证transport存活。这里简单期间直接用go run起来

5.2 在你本地机器部署启动hersql sidecar

同样的,你需要下载下来hersql的源码:https://github.com/Orlion/hersql,提前安装好golang。修改下sidecar的配置文件sidecar.example.yaml:

server:
  # sidecar 监听的地址,之后mysql client会连接这个地址
  addr: 127.0.0.1:3306
  # transport http server的地址
  transport_addr: http://x.x.x.x:xxxx
log:
  # 与transport配置相同

就可以启动sidecar

$ go run cmd/sidecar/main.go -conf=sidecar.example.yaml

同样的,一般情况下也都是会先编译为可执行文件,mac上是launchctl之类的工具托管sidecar进程,保证sidecar存活。这里简单期间直接用go run起来

5.3 客户端连接

上面的步骤都执行完成后,就可以打开mysql客户端使用了。数据库地址和端口号需要填写sidecar配置文件中的addr地址,sidercar不会校验用户名和密码,因此用户名密码可以随意填写

重点来了: 数据库名必须要填写,且必须要按照以下格式填写

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

举个例子:

root:123456@tcp(10.10.123.123:3306)/BlogDB

如图所示: image.png

5.4 举个例子

目标mysql服务器

  • 地址:10.10.123.123:3306
  • 数据库:BlogDB
  • 用户名:root
  • 密码:123456

可以直连目标mysql服务器的机器

  • 地址:10.10.123.100
  • 开放端口:8080

那么transport可以配置为

server:
  addr: :8080

sidecar可以配置为

server:
  addr: 127.0.0.1:3306
  transport_addr: http://10.10.123.100:8080

客户端连接配置

  • 服务器地址:127.0.0.1
  • 端口: 3306
  • 数据库名root:123456@tcp(10.10.123.123:3306)/BlogDB

5.5 局限

hersql目前只支持mysql_native_password的认证方式,mysql8默认的认证方式是caching_sha2_password,所以如果要通过hersql连接mysql8需要注意登录用户的认证方式是否是mysql_native_password,如果是caching_sha2_password那暂时是无法使用的。

6. 参考资料

如果hersql对你有帮助欢迎点个star

...

阅读全文 »

事务隔离级别实现原理

Category Mysql
Tag Mysql
Posted on
View

1. 前言

数据库隔离级别以及Mysql实操 一文中,我描述了为了解决并发事务间的冲突,实现事务的隔离性,SQL标椎定义了四种隔离级别,今天就通过这篇文章来看下SQL标准中每种隔离级别的实现原理以及InnoDB引擎又是如何实现的。

2. 标准SQL事务隔离级别实现原理

解决并发问题最直觉的方法就是加锁了,而标准SQL事务隔离级别的实现就是依赖于锁的。

隔离级别 实现
未提交读 事务对当前读取到的数据不加锁;事务在更新的瞬间对其加行级共享锁(读锁),直到事务结束才释放。 更新时加共享锁,会阻塞其他事务的更新,但是不会阻塞读。 由于在更新时没有加排他锁(写锁)并且其他事务读的时候也没有尝试加锁,导致其他事务是可以读到修改的,即脏读。
提交读 事务对当前读到的数据加行级共享锁,一旦读完该行就释放锁;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。 由于更新时加了排他锁,所以当前事务提交前,其他事务是读不到修改的,这就解决了脏读。 由于读完数据后就释放了锁,所以之后另外一个事务还能修改该行,修改后再读到就是修改之后的数据,这就造成一个事务内读取两次读到的数据是不同的了,即不可重复读。
可重复读 事务开始读取时,对其加行级共享锁,事务结束后才释放;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。 由于直到事务结束后才释放读锁,所以在事务结束前,其他事务无法修改该行,所以一个事务多次读取到的数据肯定是相同的,就不会存在不可重复读的问题了。 但是这个隔离级别下,由于只能锁住已存在的行,对insert进来的新数据,还是能读到的,即幻读。
串行化 事务在读取时,加表级共享锁,事务结束后才释放;事务在修改数据时,加表级排他锁。 这个级别下由于加了表锁,所以事务提交前就写不进来新数据,就不存在幻读的问题了。

3. MVCC(Multi-Version Concurrency Control)

通过锁虽然能实现事务间的隔离,但是开销还是太大了,系统性能肯定是扛不起高并发的,为了优化这个问题,尽量避免使用锁,提出了MVCC方式来解决事务并发问题。

3.1 InnoDB的MVCC实现

MVCC在InnoDB中是通过两个隐式字段undo logRead View实现的。

3.1.1 隐式字段

InnoDB会在每一行加上两个隐式字段:

  • DB_TRX_ID: 6bytes,最近修改事务的ID,记录这行记录最后一次修改的事务的ID
  • DB_ROLL_PTR: 7bytes,回滚指针,指向这条记录的上一个版本(存储于rollback segment中)

实际上还有两个字段,但是与MVCC无关。

  • DB_ROW_ID: 隐藏的自增ID(隐藏主键),如果没有主键,则InnoDB会自动以DB_ROW_ID产生一个聚簇索引
  • 一个隐藏的删除flag字段

image.png

3.1.2 undo log

undo log分为两种:

  • insert undo log: 事务在insert时产生,事务提交后可以立即丢弃
  • update undo log:事务在update/delete时产生,不仅在回滚时需要,快照读时也需要,不能随便删除,只有在快照读或者事务不涉及的时候才由purge线程去清除。

purge:为了实现MVCC,删除只是设置下记录的deleted_bit,并不真正删除,InnoDB 有专门的purge线程来回收标记删除的记录,为了不影响MVCC的工作,purge线程也维护一个自己的read view,如果某个记录的DB_TRX_ID相对于purge线程read view可见,那么这条记录就能被安全的删除。

执行流程如下:

1> 比如数据库中当前有一条记录:

name age DB_ROW_ID DB_TRX_ID DB_ROLL_PTR
n1 11 1 1 null

2> 新来一个事务 2修改了记录:update name=n2 where age = 11,流程如下:

  • 事务1修改改行记录时,InnoDB先对改行加排他锁
  • 把当前记录拷贝到undo log中,作为旧记录
  • 拷贝完了后修改name为n2,并且修改记录的DB_TRX_ID为当前事务的id,即:2。DR_ROLL_ID指向undo log中的旧记录,即它的上一个版本
  • 事务提交后,释放锁

image.png

3> 又来一个事务 3修改记录:update name=n3 where age=11,流程如下:

  • 事务1修改改行记录时,InnoDB先对改行加排他锁
  • 把当前记录拷贝到undo log中,作为旧记录,由于该行记录已经有undo log了,那么最新的旧记录作为链表头,插在undo log最前面
  • 拷贝完了后修改name为n3,并且修改记录的DB_TRX_ID为当前事务的id,即:3。DR_ROLL_ID指向undo log中的旧记录,即它的上一个版本
  • 事务提交后,释放锁

image.png

3.1.3 ReadView 读视图

ReadView中有四个比较重要的内容:

  • creator_trx_id: 表示生成该ReadView的事务ID。 (只有在执行insert、update、delete时才会分配事务ID,在一个只读的事务中事务id默认为0)
  • m_ids: 在生成ReadView时所有活跃的事务id集合,活跃事务是指开启还未提交的事务。
  • min_trx_id: m_ids最小值。
  • max_trx_id: 生成ReadView时系统应该分配的下一个事务ID,并非m_ids最大值。

有了这个ReadView,就可以这样判断一条记录是否对该事务可见:

  • 如果被访问版本的trx_id等于creator_trx_id,说明生成该版本的事务就是当前事务,所以可见
  • 如果被访问版本的trx_id小于min_trx_id,说明生成该版本的事务在当前事务生成ReadView前已提交,所以该版本可见
  • 如果被访问版本的trx_id大于等于max_trx_id,表示生成该版本的事务在当前事务生成ReadView之后才开启,所以不可见
  • 如果被访问版本trx_id在min_trx_id与max_trx_id之间,则判断是否在m_ids之中,如果在,说明创建ReadView时生成该版本的事务还活跃,所以不可见;如果不在m_ids中,则说明事务已提交所以可见。

如果某个版本的记录不可见就顺着版本链寻找下一个版本,依次判断是否可见,直到遍历到最后。

3.1.4 MVCC的实现

现在我们已经了解了undo log与ReadView,那么就来看下MVCC到底是如何实操的。

我们假设当前数据结构如下:

image.png

假设 事务20 与 事务30 并发执行,那么对于事务20,它的ReadView中m_ids=[20,30],min_trx_id=20,max_trx_id=31,creator_trx_id=20,对于事务30,它的ReadView 中m_ids=[20,30],min_trx_id=20,max_trx_id=31,creator_trx_id=30

如果此时 事务20 去读取数据,当前版本链中,数据最新版本的DB_TRX_ID为10,它小于 事务20 ReadView的min_trx_id,所以这个版本对 事务20 是可见的。

接着 事务30 修改了这行记录,数据结构就变成了下面这样:

image.png

这时 事务 20 再去读这行记录,当前版本链中,数据最新版本的DB_TRX_ID为30,30在 事务20 的m_ids中,所以这个版本数据对 事务20 不可见,继续顺着版本链读上一个版本,上一个版本DB_TRX_ID为10,可见,所以 事务20 就读到了 上一个版本的数据。

4. 几个概念

在了解InnoDB四种隔离级别的实现之前,我们先明确几个概念

4.1 锁定读和一致性非锁定读

  • 锁定读:在一个事务中主动给读加锁,eg. select … for update(排他锁)、select … lock in share mode(共享锁)
  • 一致性非锁定度:InnoDB通过MVCC向事务提供数据库某个时间点的快照,查询时只能查到当前事务开始前提交的修改,查不到该事务开始之后的修改。就是说事务开始后,事务看到的数据就是事务开始时的数据,后续其他事务的修改在当前事务不可见。

一致性非锁定读是InnoDB在RC和RR两个级别处理SELECT的默认模式,这个过程不用加锁,所以其他事务可以并发修改和读取。

4.2 当前读和快照读

  • 当前读:像update、delete、insert、select … for update、select … lock in share mode,读到的都是当前版本数据,读取时要保证其他并发事务不能修改当前记录,还要加锁
  • 快照读:读到的是快照版本,不加锁的select就是快照读,不加锁。前提是隔离级别不是未提交读和串行化,因为未提交读所有读都是当前读,串行化会对表加锁。

4.3 隐式锁定与显示锁定

  • 隐式锁定 InnoDB在事务执行过程中采用两阶段锁协议,InnoDB根据隔离级别在需要的时候自动加锁,直到事务提交或回滚之后才释放锁,所有的锁都在同一时刻释放。

  • 显示锁定 通过特定的语句显式锁定:

    select ... for update
    select ... lock in share mode
    

5. InnoDB隔离级别实现

InnoDB中,RC与RR两个隔离级别生成ReadView时机是不同的 * RC - 每次读取记录前都生成一个ReadView,而这就导致不可重复读问题 * RR - 在第一次读取时生成一个ReadView,这就解决了可重复读问题

事务隔离级别 实现
未提交读 事务对读都不加锁,都是当前读; 事务在更新的瞬间对其加行级共享锁(读锁),直到事务结束才释放。
提交读 事务对读不加锁,都是快照读;事务在更新的瞬间对其加行级排他锁(写锁),直到事务结束才释放。
可重复读 事务读不加锁,都是快照读;事务在更新时,加Next-Key Lock直到事务结束才释放
串行化读 事务在读取时,加表级共享锁,直到事务结束才释放,都是当前读;写入时加表级排他锁,直到事务结束才释放

我们再思考两个问题:

5.1 RC级别就是快照读了,那还存在不可重复读的问题吗?

答案是仍然存在,原因是InnoDB在这个级别每次读取记录前都生成一个ReadView。

5.2 很多文章提到InnoDB在RR级别就通过MVCC解决了幻读问题,真的吗?

我们先运行一个例子:

事务A 事务B
begin;
select * from users;

Empty set (0.00 sec)
begin;
insert into users(name,age) values('n1', 1);
commit;
select * from users;

Empty set (0.00 sec)

OK,看起来是解决了,这个例子中事务B的ID>=事务A的ReadView的max_trx_id,所以事务B写入的数据对事务A是不可见的。

不过先别着急下结论,再看下下面的这个例子:

事务A 事务B
begin;
select * from users;

Empty set (0.00 sec)
begin;
insert into users(name,age) values(‘n1’, 1);
commit;
update users set name=‘n2’ where id=1;
select * from users;

+—-+——+——+
| id | name | age |
+—-+——+——+
| 1 | n2 | 1 |
+—-+——+——+
1 row in set (0.00 sec)

这个例子中第二次查询给查出来了,原因在于update是当前读,执行update后生成了一个新的快照,而这个快照对事务A是可见的,所以给查出来了。

如果想第二次select查询结果跟第一次一致,还依赖间隙锁(Gap Lock),事务A的第一个

select * from users;

要显式加锁,即:

select * from users lock in share mode;

这样事务B在执行insert语句时会被阻塞住直到事务A提交。

那么什么是间隙锁呢?

5.3 Gap Lock

举个例子,age字段有普通索引,对于如下sql:

update users set name='n3' where age = 30;

不止会锁住30这一行记录,而且还会锁住两侧的区间(10,30]和(30,positive infinity)

( 表示包括这个, [ 表示不包括这个,间隙锁遵循前开后闭原则,就是说update … age=10,insert age=30的话是不会撞到锁的。

image.png

注意,如果age没有索引,那么会给所有行上一个Gap Lock!但是如果age为唯一索引,就只锁一行了。

5.4 Next-Key Lock

Record Lock与Gap Lock的结合,既锁住行也锁住索引之间的间隙。

参考资料

...

阅读全文 »

数据库隔离级别以及Mysql实操

Category Mysql
Tag Mysql
Posted on
View

1. 事务的ACID

ACID表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability),一个健壮的事务处理系统必须满足这四个特性。

  • 原子性 一个事务必须是一个不可分割的最小执行单元,事务中的所有操作要么都成功,要么失败回滚所有操作。
  • 一致性 数据库总是从一个一致性的状态转移到另一个一致性的状态,事务只要没有提交那么其中的所做的所有修改都不会落地到数据库。比如说A向B转账,A账户钱减少了,B账户钱没有响应增加,这时就处于一个不一致的状态。
  • 隔离性 一般来说一个事务未提交之前,它所做的操作对其他事务是不可见的。不同的隔离级别不可见的部分是不同的。
  • 持久性 事务一旦提交,其所做所有修改都会落地到数据库

2. 隔离级别

SQL标准中定义了四种隔离级别,隔离级别定义了在一个事务中所做的修改,哪些在事务内和事务间是可见的。高级的隔离级别实现起来更复杂,带来的开销也更高,支持的并发也更低。

每种存储引擎实现的隔离级别可能是不同的,可能会在较低的隔离级别上解决该级别的某些问题,从而具有了较高隔离级别的某些能力。例如InnoDB引擎在可重复读的级别上解决了幻读的问题。

  • READ UNCOMMITTED 未提交读 在未提交读级别,可以读到未提交事务中的修改,也被称为脏读。从性能上说该级别不会比其他级别高太多,所以一般不用。
  • READ COMMITTED 提交读 事务未提交的修改其他事务是读不到的,不存在脏读的问题,但是存在不可重复读的问题,即同样的一条查询两次读取读到的数据可能是不同的。
  • REPEATABLE READ 可重复读 可重复读不存在不可重复读的问题,即同样一条查询两次读取读的数据肯定是相同的,但是理论上存在幻读的问题,幻读是指同样一条查询第二次读取可能会读到另外一个事务刚刚新增的记录。不过InnoDB引擎在此级别通过MVCC(多版本并发控制,Multiversion Concurrency Control)解决了幻读的问题。Mysql默认的隔离级别即为该级别。
  • SERIALIZABLE可串行化 可串行化是最高的隔离级别,它通过强制事务串行化执行避免了幻读的问题,性能很差实际很少用。

3. Mysql实操

Mysql版本:Server version: 8.0.18 MySQL Community Server - GPL

3.1 查看mysql当前隔离级别

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

可以看到当前隔离级别为可重复读

3.2 修改mysql隔离级别

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

如果指定了SESSION则只在该对话中生效,指定了GLOBAL则全局修改隔离级别。下面我们将隔离级别修改为未提交读

mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED        |
+-------------------------+

可以看到隔离级别成功被设置为未提交读,下面我们在未提交读的隔离级别下观察下脏读的问题。

3.3 观察脏读问题

我们保持未提交读的隔离级别,然后创建一张实验表,写入两条数据

mysql> CREATE TABLE `t` (
    ->     `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    ->     `age` INT(11) NOT NULL,
    ->     `name` varchar(255) NOT NULL,
    ->     PRIMARY KEY (`id`)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected, 2 warnings (0.21 sec)

insert into `t`(age,name) values(10,'n1');
insert into `t`(age,name) values(11,'n2');

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
|  2 |  11 | n2   |
+----+-----+------+
2 rows in set (0.00 sec)

这时我们开启事务A,然后修改id为1的记录的name为’o1’,但是不要提交事务:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='o1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

此时我们新开一个窗口,查询下id=1的数据:

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

mysql> select * from t where id=1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
+----+-----+------+
1 row in set (0.00 sec)

在默认可重复读的隔离级别下读不到事务A的修改。

我们修改隔离级别为未提交读,再查下:

mysql> set session transaction isolation level READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
+----+-----+------+
1 row in set (0.00 sec)

可以看到事务A没有提交,但是我们仍然读到了修改,这就是脏读。

3.4 观察不可重复读问题

我们将事务隔离级别修改为提交读:

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

然后开启事务A,执行一条查询sql:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id =1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
+----+-----+------+
1 row in set (0.00 sec)

然后我们新开一个窗口,修改id=1的记录:

mysql> update t set name='o1' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

然后我们回到事务A,然后重新执行上一条查询:

mysql> select * from t where id =1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
+----+-----+------+
1 row in set (0.00 sec)

可以看到在一个事务中两次相同查询查到的结果是不同的,这就是不可重复读问题。

3.5 验证不可重复读隔离级别下是否解决了脏读问题

当前表数据为:

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
|  2 |  11 | n2   |
+----+-----+------+
2 rows in set (0.00 sec)

然后开启一个事务将id=1的记录的name改为’n1’,但是不要提交:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t set name='n1' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这时在另外一个窗口中查下:

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
|  2 |  11 | n2   |
+----+-----+------+

可以看到此时没有查询到未提交的事务中的修改,就是说提交读隔离级别解决了脏读问题。

3.6 验证可重复读隔离级别是否解决了不可重复读问题

首先将隔离级别修改为可重复读

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

然后我们开启一个事务A,查询下id=1的记录:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id=1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
+----+-----+------+

然后再另一个窗口中修改name为’n1’:

mysql> update t set name='n1' where id =1;
Query OK, 1 row affected (0.01 sec)

这时回到事务A中重新查询下id=1的记录:

mysql> select * from t where id=1;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | o1   |
+----+-----+------+

可以看到在一个事务中两次读到的是相同的,不可重复读问题已解决。

3.7 验证下InnoDB引擎是否解决了幻读问题

我们将表的存储引擎修改为InnoDB:

mysql> alter table t ENGINE=InnoDB;
Query OK, 3 rows affected (11.52 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

这时我们开启事务A,查询下所有表记录:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
|  2 |  11 | n2   |
|  3 |  12 | t3   |
+----+-----+------+

然后这时在另外一个窗口中新增一条记录:

mysql> insert into t(age,name) value (1, 't10');

执行完成后回到事务A,重新查一下:

mysql> select * from t;
+----+-----+------+
| id | age | name |
+----+-----+------+
|  1 |  10 | n1   |
|  2 |  11 | n2   |
|  3 |  12 | t3   |
+----+-----+------+

可以看到第二次查询跟第一次查询结果是相同的,就是说InnoDB解决了幻读问题。

...

阅读全文 »