事务

为什么需要事务机制


点击这里,看视频讲解,学习下面内容

我们设计了一个图书馆系统,可以管理图书借阅信息。

已经有用户表user、图书表book 和 借阅登记表borrow。

如下所示:

CREATE TABLE user (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username varchar(150)  NOT NULL,
  `password` varchar(128)  NOT NULL,
  realname varchar(30)  NOT NULL
) ;


CREATE TABLE book (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(150)  NOT NULL,
  `desc` varchar(128)  NOT NULL,
  status int  NOT NULL
) ;


CREATE TABLE borrow (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id      int  NOT NULL,
  book_id  int  NOT NULL,
  
  FOREIGN KEY (user_id)
   REFERENCES user(id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT,

  
  FOREIGN KEY (book_id)
   REFERENCES book(id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
) ;

其中

book 表中的status 字段 记录了 某本图书的 状态 值为 0 表示 没有借出, 1 表示借出了。

借阅登记表 borrow 里面 的user_id就是借阅者id,是一个外键,引用了 user表, 而book_id 也是一个外键,引用了 book表。

当一个用户(假设id为 666)借阅某本图书(假设id为 888)时,系统要对 两张表 进行操作,

  • 修改book 表 中 id为 888 的记录中的 status值为1 表示借出

  • 在 borrow 表中添加一个记录,记录是谁借出了哪本书。

似乎,这个问题不大,学习了前面的课程,大家可以很轻易的写出如下两段SQL

update book set status=1 where id=888;

insert into borrow  (user_id,book_id) VALUES (666,888);

朋友,想想,这里面有什么问题吗?

我们分析这个业务需求,这两个操作必须一起完成。

如果出现 第一个SQL操作(修改图书状态)完成了,第二个操作由于某种原因却失败了。 这就会导致数据状态不一致的问题。

这种事情在 安全性健壮性要求特别高 的金融系统中 是 万万不允许出现的。

解决这个问题的办法,就是使用数据库服务的 事务(transaction) 机制

事务 和 autocommit

点击这里,看视频讲解,学习下面内容

对于InnoDB格式的表, 所有的客户端请求 都是在事务中处理的。 每个事务必须提交才能生效。

MySQL服务对每个客户端连接有个设置开关: autocommit

  • 如果值为 1 ,处于自动提交模式

    执行 每条 SQL语句,都是自动放入一个事务中,执行完该语句后, 自动提交 事务

  • 如果值为 0 ,处于手动提交模式

    手动提交模式打开后,自动创建一个事务; 执行一条SQL语句后, 不会自动提交 事务,接着等待执行后续的SQL语句。

    所有已经执行的SQL语句的结果,都是临时保存在缓冲区 innodb_log_buffer 中,外部看不到这些改动

    如果接收执行了一条 COMMIT 语句,就把前面所有的更新从缓冲区写入磁盘生效, 并且自动开始下一个事务。这时,外部客户端可以看见更新生效了。

    如果接收执行了一条 ROLLBACK 语句,就把前面所有缓冲的更新删除, 并且自动开始下一个事务。这时,外部客户端发现数据库状态几乎和操作前一样,没有更新。(除了一些 AUTO_INCREMENT字段 的当前自增值 可能发生了改变)


当 MySQL 客户端连接到服务端,缺省 autocommit 值就是1,表示自动提交。 这就是为什么我们在 MySQL命令行客户端 执行每条SQL语句都不需要执行 COMMIT,就可以生效的原因。

如果,我们想取消自动提交模式,执行 如下命令即可

SET autocommit = 0;

而前面我们学习过的python mysql 客户端库 mysqlclient 创建连接后,该库底层就自动执行了 上面的命令,取消了自动提交模式。 这就是为什么,我们执行完每个 改动数据库的操作 都要commit 一下的原因。

更多关于 autocommit的信息,点击这里参考官方文档


如果,一个客户端连接 当前是 自动提交模式(autocommit为1),根据前面的描述,我们知道 每条SQL语句执行 都是自动放入一个事务中,自动提交了。

如果,我们想 把 多条SQL语句 放入 同一个事务 中,可以这样:

-- 1. 创建一个 多条SQL语句 事务
START TRANSACTION;

-- 2. 更新book 表
update book set status=1 where id=888;

-- 3. 更新 borrow表
insert into borrow  (user_id,book_id) VALUES (666,888);

-- 4. 提交事务 
COMMIT;
-- 或者回滚事务
ROLLBACK;

如果在执行完第3步,没有错误,更新borrow表成功了, 整个事务里面的操作都没有问题, 我们可以选择执行 语句 COMMIT 来提交事务,这样第2步,和第3步的更新操作才会真正成功。

如果在执行完第3步,更新borrow表失败了, 我们的程序可以选择执行 ROLLBACK 来回滚事务,这样第2步的更新操作也会撤销。


如果,一个客户端连接 当前是 手动提交模式(autocommit为0)

多条SQL语句自动放入事务中,如下所示:

-- 开始自动处于一个事务中,无需执行  START TRANSACTION;

-- 1. 更新book 表
update book set status=1 where id=888;

-- 2. 更新 borrow表
insert into borrow  (user_id,book_id) VALUES (666,888);

-- 3. 提交事务 ,并且自动启动下一个事务
COMMIT;
-- 或者回滚事务 ,并且自动启动下一个事务
ROLLBACK;

可以发现自动提交模式 和手动提交模式 的区别在于 手动提交模式,开始不需要我们执行 START TRANSACTION; 显式的创建一个事务


通常我们开发程序操作数据库,建议使用手动提交模式, 这样如果一批操作中途出现问题时,可以很方便的回复数据库到最初的数据状态。

四种类型的 SQL 语句

SQL 包括四种类型的语句:

DDL(Data Definition Language)数据定义语言,它处理 数据库和表的结构定义,比如 CREATE,DROP,ALTER等。

DML(Data Manipulation Language)数据操作语言,它处理具体的数据内容,包括最常见的SQL语句,如SELECT,INSERT,UPDATE,DELETE等,它用于存储,修改,检索,删除和更新数据库中的数据。

DCL(Data Control Language)数据控制语言,包括GRANT等命令,主要涉及数据库系统的权限等控制。

TCL(Transaction Control Language)是事务控制语言的简称,包括BEGIN Transaction、COMMIT Transaction、ROLLBACK Transaction等命令,主要涉及数据库系统的权限,权限和其他控制。


作业和练习

前面的作业,我们创建了Prac库,在其中添加了 user 表 、 medicine表 和 药品统计表 stats_medine,

请用SQL语句,创建一个事务,在事务里面 做如下操作

  • 在user表中,添加一条记录,保证数据是正确的,能够添加成功;

  • 在medicine表中,添加一条记录,保证数据是正确的,能够添加成功;

  • 在stats_medine表中,添加一条记录,故意将sales_amount_total数据错写成字符串数据;

执行上面的事务,看看错误提示是什么,然后检查数据库中 user 表 、 medicine表 添加数据的操作是否被撤销了。

您需要高效学习,找工作? 点击咨询 报名实战班

点击查看学员就业情况

上一页