事务

什么是事务


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

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

已经有用户表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) 机制

事务就像一个包裹,把 一批 SQL操作 作为一个整体打包执行(术语叫 原子操作atomic ),

  • 如果里面的SQL操作 全部完成,那样最好了。

  • 如果执行到其中任何一个操作 失败了, 后续的操作当然不会再执行了, 而且前面已经执行过的更新也会被 撤销 ,术语叫回滚rollback。


上面的问题,我们可以这样使用事务,

-- 1. 创建一个事务
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步的更新操作也会撤销。


四种类型的 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表 添加数据的操作是否被撤销了。


扫码分享给朋友,一起学更有动力哦




上一页