关闭

史上最简单的 MySQL 教程(三十九)「事务(下)」

标签: MySQL事务
4574人阅读 评论(5) 收藏 举报
分类:

温馨提示:本系列博文已经同步到 GitHub,地址为「mysql-tutorial」,欢迎感兴趣的童鞋StarFork,纠错。

温馨提示:在「事务(上)」中,我们已经了解了如何在 MySQL 中开启事务,以及事务的一些基本操作。在本文中,我们将进一步学习事务的知识,包括事务原理、自动事务、回滚点和事务特性等。

事务原理

事务原理:在事务开启之后,所有的操作都会被临时存储到事务日志,事务日志只有在收到commit命令之后,才会将操作同步到数据表,其他任何情况都会清空事务日志,例如突然断开连接、收到rollback命令等。

接下来,我们简单分析一下 MySQL 的操作过程:

  • Step 1:客户端与服务端建立连接,同时开启一个临时的事务日志,此事务日志只作用于当前用户的当次连接;
  • Step 2:在客户端用 SQL 语句执行写操作,客户端收到 SQL 语句,执行,将结果直接写入到数据表,并将数据表同步到数据库;
  • Step 3:我们在客户端开启事务,则服务端原来的操作机制被改变,后续所有操作都会被先写入到临时日志文件;
  • Step 4:在客户端执行 SQL 语句(例如写操作),服务端收到 SQL 语句,执行,将结果写入到临时日志文件,并不将结果同步到数据库;
  • Step 5:在客户端执行查询操作,服务端直接从临时日志文件中捞取数据,返回给客户端;
  • Step 6:在客户端执行commit或者rollback命令,清空临时日志文件,如果是commit命令,则将结果同步到数据库;如果是rollback命令,则不同步。

通过上面的分析,我们就知道了为什么在我们同时开启两个 MySQL 客户端窗口(两次连接)时,当一个窗口开启事务并执行 SQL 操作之后,另一个窗口在查询时并不会收到同步数据。原因就在于,当我们开启事务之后,服务端会将后续的操作都写入到临时日志文件,而另一个窗口在查询的时候,则是直接从数据库捞取数据,并会不走前一个的临时日志文件。

回滚点

回滚点:在某个操作成功完成之后,后续的操作有可能成功也有可能失败,但无论后续操作的结果如何,前一次操作都已经成功了,因此我们可以在当前成功的位置,设置一个操作点,其可以供后续操作返回该位置,而不是返回所有操作,这个点称之为回滚点。关于回滚点的基本语法为,

  • 设置回滚点savepoint + 回滚点名称;
  • 返回回滚点rollback to + 回滚点名称;

执行如下 SQL 语句,进行测试:

-- 测试回滚点
-- 查询 bank_account 表数据
select * from bank_account;

-- 开启事务
start transaction;

-- 事务操作 1:给 Charies 发工资 1000 元
update bank_account set money = money + 10000 where id = 1;

-- 设置回滚点
savepoint spone;

-- 银行扣税:错误
update bank_account set money = money - 10000 * 0.05 where id = 2;

-- 查询 bank_account 表数据
select * from bank_account;

save

执行如下 SQL 语句,继续进行测试:

-- 测试回滚点
-- 返回回滚点
rollback to spone;

-- 银行扣税:正确
update bank_account set money = money - 10000 * 0.05 where id = 1;

-- 查询 bank_account 表数据
select * from bank_account;

-- 提交事务
commit;

rollback

如上图所示,显然在执行返回回滚点的操作之后,我们之前的错误操作得到了修正。

自动事务

在 MySQL 中,默认的都是自动事务处理,即用户在操作完成之后,其操作结果会立即被同步到数据库中。

自动事务是通过autocommit变量控制的,我们可以通过如下 SQL 语句,进行查看:

-- 查询自动事务
show variables like 'autocommit';

autocommit

如上图所示,此为 MySQL 的默认设置。实际上,我们可以自己选择是否开启自动事务处理,其基本语法为,

  • 开启自动事务处理set autocommit = on / 1;
  • 关闭自动事务处理set autocommit = off / 0;

在此,我们以关闭自动事务处理为例,进行演示:

-- 关闭自动事务处理
set autocommit = 0;

-- 查看自动事务处理
show variables like 'autocommit';

-- 查看 bank_account 表数据
select * from bank_account;

-- 修改 bank_account 表数据
update bank_account set money = money + 1000 where id = 1;

-- 查看 bank_account 表数据
select * from bank_account;

0ff

如上图所示,我们并没有开启事务,仅是关闭了自动事务处理,但是我们发现,在我们修改了bank_account表中数据之后,其结果并不会立即同步到数据库。实际上,这就是关闭了自动事务处理的正常现象。在我们执行commit命令之后,上述操作的结果即可进行同步:

-- 提交
commit;

-- 查看 bank_account 表数据
select * from bank_account;

commit

当然,如果我们不执行commit命令,而是执行rollback命令,那么之前的所用操作都会回滚到初始的状态。在此,我们需要注意的是:通常情况下,我们是应该开启自动事务处理的,否则的话,每次操作完成之后都需要我们手动提交,那岂不是要被累死了

事务特性

事务的特性,可以简单的概括为ACID,具体为:

  • 原子性Atomic,表示事务的整个操作是一个整体,是不可分割的,要么全部成功,要么全部失败;
  • 一致性Consistency,表示事务操作的前后,数据表中的数据处于一致状态;
  • 隔离性Isolation,表示不同的事务操作之间是相互隔离的,互不影响;
  • 持久性Durability,表示事务一旦提交,将不可修改,永久性的改变数据表中的数据。

对于上述事务的四个特性,其中原子性、一致性、持久性比较容易理解,但是隔离性却需要格外注意。例如,开启两个客户端窗口,分别执行如下 SQL 语句,进行测试:

-- 演示隔离性操作:窗口 1
-- 开始事务
start transaction;

-- 修改 id 为 1 的数据
update bank_account set money = money + 666 where id = 1;

-- 查看 bank_account 表数据
select * from bank_account;

---------   万人迷分割线   ---------

-- 演示隔离性操作:窗口 2
-- 开始事务
start transaction;

-- 修改 id 为 2 的数据
update bank_account set money = money + 666 where id = 2;

-- 查看 bank_account 表数据
select * from bank_account;

isolation

如上图所示,其完美的展示了事务隔离性的效果,即窗口 1 的中的事务操作,没有影响到窗口 2 的事务操作;窗口 2 的中的事务操作,也没有影响到窗口 1 的事务操作。But,在我们执行下面的 SQL 语句之后,我们将会看到不同的效果:

-- 演示隔离性操作:窗口 1
-- 开始事务
start transaction;

-- 修改 name 为 Charies 的数据
update bank_account set money = money + 666 where name = 'Charies';

-- 查看 bank_account 表数据
select * from bank_account;

---------   万人迷分割线   ---------

-- 演示隔离性操作:窗口 2
-- 开始事务
start transaction;

-- 修改 name 为 Gavin 的数据
update bank_account set money = money + 666 where name = 'Gavin';

-- 查看 bank_account 表数据
select * from bank_account;

geli

如上图所示,窗口 1 的事务可以正常执行,但是窗口 2 的事务开启成功,但是在修改数据的时候被“卡”住了,并且在持续一段时间之后,报出了一个 Lock wait timeout exceeded的错误:

geliiiii

那么到底是什么原因导致了上述错误的发生呢?这就是涉及到了数据库的另外一个知识点 锁机制 啦!

实际上,MySQL 使用的默认存储引擎是 InnoDB,而 InnoDB 默认使用的锁机制是 行锁(锁住操作的当前行),但是如果在事务操作的过程中,我们没有使用索引字段,那么系统就会自动进行全表检索,也就是其自动将行锁升级为 表锁(锁住操作的当前表)。

现在回想一下,我们在第一次测试的时候,使用的条件id为主键索引,所以两个事务可以表示出很好的隔离性,互不影响;在第二次测试的时候,我们将条件换为name,而name并不是索引字段,因此在第二次测试的时候,窗口 1 的事务使用了表锁,锁住了整张表,而在事务提交或回滚之前,其并不释放锁,所以所有试图修改被锁住表的数据的操作,都会陷入等待状态。等待超时,自然就报错啦!

对于锁机制,在「基础教程」篇,我们并不做过多的介绍,在后续的「性能优化」篇中在详细的进行讨论。


温馨提示:符号[]括起来的内容,表示可选项;符号+,则表示连接的意思。


———— ☆☆☆ —— 返回 -> 史上最简单的 MySQL 教程 <- 目录 —— ☆☆☆ ————

11
2
查看评论

史上最简单的 MyBatis 教程

1 前言  MyBatis 源于 Apache 的一个开源项目 iBatis,而 iBatis 一词则来源于“internet”和“abatis”的组合,2010年这个项目由 Apache Software Foundation 迁移到了 Google Code,并且改名为MyBatis ,2013...
  • qq_35246620
  • qq_35246620
  • 2017-02-01 00:49
  • 6658

史上最简单的SpringCloud教程 | 第一篇: 服务的注册与发现(Eureka)

一、spring cloud简介 spring cloud 为开发人员提供了快速构建分布式系统的一些工具,包括配置管理、服务发现、断路器、路由、微代理、事件总线、全局锁、决策竞选、分布式会话等等。它运行环境简单,可以在开发人员的电脑上跑。另外说明spring cloud是基于spri...
  • fend0875
  • fend0875
  • 2017-04-21 08:39
  • 1602

史上最简单的SpringCloud教程

转载请标明出处:  http://blog.csdn.net/forezp/article/details/70148833  本文出自方志朋的博客 错过了这一篇,你可能再也学不会 Spring Cloud 了!Spring Boot做为下一代 web 框架,Spr...
  • zhongzh86
  • zhongzh86
  • 2017-11-22 14:15
  • 51

微服务:史上最简单的 SpringCloud 教程 | 终章

http://blog.csdn.net/forezp/article/details/70148833  本文出自方志朋的博客 案例全部采用Spring Boot 1.5.x ,Spring Cloud版本为Dalston.RELEASE  码农下载:https://gi...
  • superdangbo
  • superdangbo
  • 2017-11-01 17:20
  • 295

史上最简单的 SpringCloud 教程 | 第一篇: 服务的注册与发现(Eureka)

spring cloud 为开发人员提供了快速构建分布式系统的一些工具,包括配置管理、服务发现、断路器、路由、微代理、事件总线、全局锁、决策竞选、分布式会话等等。它运行环境简单,可以在开发人员的电脑上跑。另外说明spring cloud是基于springboot的。本文主要介绍了服务的注册与发现。
  • forezp
  • forezp
  • 2017-04-08 18:16
  • 313718

史上最直白的logistic regression教程 之 五

史上最直白的logistic regression教程整理稿,将4篇博文整理成一个完整的pdf文档,且修改成学术语境。 链接在这里: http://download.csdn.net/detail/u011539200/9290695 0积分下载,求rp,^_^
  • u011539200
  • u011539200
  • 2015-11-22 15:57
  • 665

史上最简单的SpringCloud教程 | 第三篇: 服务消费者(Feign)

这篇文章主要讲述通过feign去消费服务。Feign是一个声明式的web服务客户端,它使得写web服务变得更简单。使用Feign,只需要创建一个接口并注解。它具有可插拔的注解特性,包括Feign 注解和JAX-RS注解。Feign同时支持可插拔的编码器和解码器。
  • forezp
  • forezp
  • 2017-04-09 11:53
  • 190145

log4j 史上最简单的配置

log4j.properties 文件内容:具体需求课根据自己的意愿选择不同的权限# Log4j properties log4j.rootLogger=INFO, stdoutlog4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j....
  • forever_insist
  • forever_insist
  • 2017-11-20 11:26
  • 110

史上最简单的 MySQL 教程(二十一)「数据的高级操作 之 蠕虫复制」

数据的高级操作蠕虫复制蠕虫复制:从已有的数据表中获取数据,然后将数据进行新增操作,数据成倍(以指数形式)的增加。根据已有表创建新表,即复制表结构,其基本语法为: create table + 表名 + like + [数据库名.]表名; 执行如上 SQL 语句,进行测试:-- 根据已有表,创建新表,...
  • qq_35246620
  • qq_35246620
  • 2017-06-03 21:14
  • 2629

史上最直白的logistic regression教程 之 三

在线性拟合的基础上,我们实现logistic regression了。如前所述,样本集是 {x1,y1},{x2,y2},...,{xn,yn}[1]\{x_1,y_1\}, \{x_2, y_2\}, ..., \{x_n, y_n\}[1] 其中,xi=[1,xi,1,xi,2,xi,3,....
  • u011539200
  • u011539200
  • 2015-11-19 16:09
  • 1368
    个人资料
    • 访问:972597次
    • 积分:12998
    • 等级:
    • 排名:第1251名
    • 原创:258篇
    • 转载:86篇
    • 译文:11篇
    • 评论:957条
    博主的 GitHub 账号
    GitHub : Charies Gavin

        鉴于 CSDN 糟糕的用户体验,博主会将一些优质的文章迁移到 Charies Gavin's Blog  欢迎大家在 GitHub 上 Follow 博主,以及 Fork、Star、Watch 博主的项目。


      青春不老 奋斗不止


      好学若饥虚心若愚
    博客专栏