mysql事务和事务隔离机制

Wesley13
• 阅读 852

阅读文本大概需要3分钟。

http://arthornye.github.io/2018/mysql/mysql%E4%BA%8B%E5%8A%A1%E5%92%8C%E4%BA%8B%E5%8A%A1%E9%9A%94%E7%A6%BB%E6%9C%BA%E5%88%B6

在学习mysql的事务隔离机制的过程中,对mysql的会话和事务的概念有点模糊不清,这里主要通过mysql可视化工具sequel pro来记录在实践过程中遇到的问题以及思考。

mysql会话begin自动提交事务

12

begin;update retail_order set order_status=111 where order_code='899120869590';

我们建立一个mysql连接,然后开启一个会话session1,执行上面的sql。这条sql会在行记录上加锁。但是当前的事务是没有提交的,mysql的默认事务隔离机制是RR(可重复读)。我们在另一个会话session2中执行:

123

begin;update retail_order set order_status=111 where order_code='899120869590';commit;

会话2会报超时:

1

Lock wait timeout exceeded; try restarting transaction

通过这种方式我们成功验证了session1的事务还在执行中,数据库的记录已经上锁。

那么我们再去验证,当前会话的某个事务还在执行中,当前会话是不是可以继续执行其他事务,执行下面的select

1

select order_status from retail_order where order_code='899120869590'

执行结果:

1

111

实际上这个事务没有被提交上去,但是在本会话中再提交select却可以读取到这个值。这里其实我们应该将其理解为实际上本事务并没有提交,数据库会检测是同一个会话提交的sql,将其整合为一个未提交的事务。所以这里的变更对其他会话的事务依旧是不可见的。

那么什么时候这个事务什么时候会被提交?我们可以联想到是不是在执行一个commit本会话的事务便会被提交,对其他的会话可见。我们在session1单独执行一句commit,在session2中执行:

1

select order_status from retail_order where order_code='899120869590'

结果:

1

111

可见我们的事务已经被提交了,同样的,我们发现在session1中执行一句begin,看session2同样查询的结果:

1

111

做个总结:

当我们忘了对一个事务进行提交的时候,该会话接下来执行的事务也会叠加,直到我们显示的去进去提交或者开启一个新的事务。一个会话的事务没有提交可能导致另一个会话获取不到锁。

mysql事务隔离机制

熟悉了我们的可视化工具,我们尝试分析mysql的事务隔离机制,相信对mysql有基本了解的都知道,mysql的四种隔离机制。这四种隔离机制是我们分析数据库锁机制的基本,我也将会在本篇介绍隔离机制之后介绍一下mysql的锁机制。接下来的介绍需要我们去改变mysql的事务隔离机制,我们可以通过语句:

1

select @@global.tx_isolation,@@tx_isolation;

1

REPEATABLE-READ    REPEATABLE-READ

mysql的默认隔离机制RR,前一个是全局session的隔离级别,后一个是当前会话的隔离级别,我们可以在当前会话中设置隔离级别,通过sql语句:

12

set session transaction isolation level read committed;set session transaction isolation level repeatable read;
Read Uncommited

这个基本不用考虑,因为如果事务没有被提交就被其他的事务看到这样的设计从逻辑上来看是不合理的,会造成大量的脏读。

Read Commited(不可重复读)

在这个隔离机制下,事务在提交之后对另一个事务可见。如果在一个事务A的执行过程中进行了两次查询,事务B在查询间隙进行了数据更新,这个隔离级别会存在脏读+幻读的可能。我们新建一个test_test表,开启session1,执行事务A,不提交,模拟不可重复读:

12

begin;select * from test_test where code=1

12345

1    wmm    12    wmm    13    wmm    14    wmm    15    wmm    1

开启session2,执行事务B,直接提交:

1

update test_test set name='wmm' where code=1;

回到session1,执行查询:

1

select * from test_test where code=1

12345

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    1

RC隔离级别下不可重复读,两次读的结果不一致。

用同样的方式,测试该隔离模式下会不会存在幻读:

12

begin;select * from test_test where code=1

12345

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    1

1

insert test_test values(6,'yqz',1);

123456

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    16    yqz    1

该模式下存在幻读(如果没有显式begin开始一个事务,事务都默认自动提交,部分语句没有加begin,commit,自动提交)。

Repeatable Read(可重复读)

首先通过恢复到该数据库隔离级别:

1

set session transaction isolation level repeatable read;

模拟是否存在脏读,session1:

12

begin;select * from test_test where code=1

123456

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    16    yqz    1

session2:

1

update test_test set name='pyx' where code=1;

session1:

1

select * from test_test where code=1

123456

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    16    yqz    1

RR隔离模式下不存在脏读,那么mysql是如何实现该隔离模式下的避免脏读?了解过juc的cas的都知道我们可以通过在共享内存标记一个版本号,来防止aba问题。那么类比mysql也是通过在每行的记录后面添加一列标记版本号,读的时候不会修改这个版本号,但是更新删除都会版本号+1。参考上面的例子,session1在执行事务A的时候,假设当前事务的版本号为1,当前事务并没有提交,然后session2执行了更新事务B,提交之后数据库该行记录的版本号变成了2。在该隔离模式下事务A只会读取到版本号不大于当前事务版本号的记录,也就是说,虽然这条记录的更改已经在数据库真实存在,但是事务A并不能读取到这条记录的变更。

  • SELECT时,读取创建版本号<=当前事务版本号,删除版本号为空或>当前事务版本号。

  • INSERT时,保存当前事务版本号为行的创建版本号。

  • DELETE时,保存当前事务版本号为行的删除版本号。

  • UPDATE时,插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行。

2018.11.20增加对MVCC多版本并发控制的理解:如果我们数据库中某一条记录的值经历的版本变化为4->3->2->1,那么数据库引擎同时会记录一个回滚日志,根据对应事务的版本号去回滚到事务对应的值。

mysql事务和事务隔离机制

同样的方式看该该隔离级别下是否存在幻读,session1,事务A:

12

begin;select * from test_test where code=1

123456

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    16    pyx    1

session2,事务B:

1

insert test_test values(7,'yqz',1);

session1,事务A:

1

select * from test_test where code=1

123456

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    16    pyx    1

session1,事务A:

1

commit;

1234567

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    16    pyx    17    yqz    1

可以看到这里是不存在幻读的,很多博客在介绍mysql的四种隔离级别的时候会说RR隔离模式下可以避免脏读但是不能避免幻读,我们通过实践看到这个级别是可以避免幻读的。

Serializable(串行化)

这个隔离级别,个人的理解可以类比RetrantWriteReadLock的设计。事务在执行的时候如果数据行存在写锁(排他锁),读锁是共享锁,这种情况下会等待写锁释放。同样的如果是事务尝试去获取某个数据行的写锁,发现共享锁的读锁没有完全释放开,也需要等待读锁全部释放完才可以获取到写锁。

1

set session transaction isolation level serializable;

将数据库隔离级别调整为串行,然后分别执行事务,session1,事务A:

12

begin;select * from test_test where code=1

12345678

1    pyx    12    pyx    13    pyx    14    pyx    15    pyx    16    pyx    17    yqz    18    yqz    1

session2,事务B:

1

update test_test set name='pyx' where id=1

执行结果,事务B,获取锁超时,Lock wait timeout exceeded; try restarting transaction。通过下面的语句也可以查看到当前存在锁竞争。

1

select * from information_schema.innodb_locks

12

5930258879:6688936:3:3    5930258879    X    RECORD    `souche_retail`.`test_test`    PRIMARY    6688936    3    3    NULL5930501833:6688936:3:3    5930501833    S    RECORD    `souche_retail`.`test_test`    PRIMARY    6688936    3    3    NULL

总结

本篇主要介绍了mysql的四种事务隔离级别,这里终结下它们的特点:

Read Uncommited

不可重复读

幻读

Read Commited

不可重复读

幻读

Repeatable Read

可重复读

不存在幻读

Serializable

不可重复读

不存在幻读

往期精彩

01 漫谈发版哪些事,好课程推荐

02 Linux的常用最危险的命令

03 互联网支付系统整体架构详解

04 优秀的Java程序员必须了解的GC哪些

05 IT大企业有哪些病,别被这些病毁了自己?

关注我每天进步一点点

mysql事务和事务隔离机制

mysql事务和事务隔离机制

你点的在看,我都当成了喜欢

本文分享自微信公众号 - JAVA乐园(happyhuangjinjin88)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
OMG!Java高级开发岗必问知识点
目录1.Mysql2.CHAR与VARCHAR的区别?3.能说下myisam和innodb的区别吗?4.你能说下事务的基本特性和隔离级别吗?5.并发问题脏读、不可重复读、幻读?6.事务的隔离级别?7.说说自增主键、UUID?8.mysql的约束分类?9.drop、delete与tru
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
MySQL数据库InnoDB存储引擎Log漫游(1)
作者:宋利兵来源:MySQL代码研究(mysqlcode)0、导读本文介绍了InnoDB引擎如何利用UndoLog和RedoLog来保证事务的原子性、持久性原理,以及InnoDB引擎实现UndoLog和RedoLog的基本思路。00–UndoLogUndoLog是为了实现事务的原子性,
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这