关于mysql管理事务处理

时间:2022-09-21 20:12:58

前提:数据库引擎必须是InnoDB类型。
在mysql中,MyISAM和InnoDB是最常用的两种引擎,(其中MyISAM是默认引擎),其中 MyISAM 支持全文本搜索,但不支持事务;而 InnoDB 不支持全文本搜索,但支持事务。因此,这里我们定义被操作的表引擎为 InnoDB.

事务处理可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行。

现在,我们先了解这么些术语:
1、事务(transaction):指一组SQL语句
2、回退(rollback): 指撤销指定的SQL语句的过程
3、提交(commit): 指将未存储的SQL语句结果写入数据库
4、保留点(savepoint): 指事务处理中设置的临时占位符,你可以利用它来发布回退(与回退整个事务处理不同)。

现在假想有这么一个小问题:有两张表,orders 和 orderdetail orders表里面存 用户ID 和 订单号,orderdetail 里面存对应订单号的商品信息。假如在存两张表时出现意外,orders表存成功了,但orderdetail 存失败,产生了空的订单,没意义。orderdetail存成功了,但orders存失败了,没有订单,哪来的订单信息?

如何解决这个问题?我们就需要用到事务,保证两张表要么同时成功,要么同时失败。

先建表,表引擎为InnoDB:

//orders表
CREATE TABLE orders(
id INT(11) NOT NULL AUTO_INCREMENT,
user_id INT(11) NULL,
order_no VARCHAR(32) NULL,
PRIMARY KEY (id)
)ENGINE=INNODB;


//orderdetail表
CREATE TABLE orderdetail(
id INT(11) NOT NULL AUTO_INCREMENT,
order_no VARCHAR(32) NULL,
detail TEXT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB;

先往里面存点数据吧。。。

INSERT INTO orders(user_id,order_no) 
VALUES(10,'123456'),(11,'456789'),(12,'123789');


INSERT INTO orderdetail(order_no,detail)
VALUES('123456','煎饼果子'),('456789','手抓饼'),('123789','肉夹馍');

一、事务的开始:
我们用下面的句子标志事务的开始:

START TRANSACTION;

二、使用ROLLBACK:
MySQL的ROLLBACK命令用来回退(撤销)MySQL语句,下面的例子:

SELECT * FROM orders;
START TRANSACTION;//开启事务
DELETE FROM orders;//删除orders表所有的数据
SELECT * FROM orders;//这里返回的是空,因为前面已经清空所有的数据。
ROLLBACK;//回退MySQL到前一个安全状态
SELECT * FROM orders;//返回三条数据

由上面的例子,我们可以用ROLLBACK来撤销我们的Mysql语句。
显然,ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后)。
注意:事务处理用来管理 INSERT、UPDATE、DELETE语句,你不能回退SELECT语句。

三、使用commit
一般的MySQL语句都是直接对数据表进行执行和编写的,这就是所谓的隐含提交,即提交(写和保存)操作是自动进行的。
但是,当你开启了事务处理后,提交不会隐含地提交,而是当我们执行了 commit 命令后才会提交到数据库进行操作。
例如下面的例子:

START TRANSCATION;
DELETE FROM orders WHERE order_no='123456';
DELETE FROM orderdetail WHERE order_no='123456';
//删除两张表的的订单号为123456的订单
COMMIT;//提交删除操作

在这里,由于这两张表要么同时删除,要么同时不删除,所以用事务处理保证订单不被部分删除。最后的COMMIT语句仅在不出错的时候提交更改,如果第一条DELETE起作用了,但第二条失败,则DELETE操作不会被提交,实际上,它是被自动撤销了。

隐含事务关闭:
当COMMIT或ROLLBACK语句执行后,事务会自动关闭,这意味着后面的MYSQL操作隐含提交。

四、使用保留点
简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理,
亦即影响的是从 START TRANSACTION 到 ROLLBACK(COMMIT) 这段代码,但是当我们用到部分提交或部分回退的时候怎么做?

我先建一个 customer 表:

CREATE TABLE customer(
id INT(11) NOT NULL AUTO_INCREMENT,
name TEXT NULL,
age INT(3) NULL,
PRIMARY KEY (id)
)ENGINE=INNODB;

//引擎为InnoDB,因为要用到事务

现在假想这么一个问题,当一个新用户购买我的东西的时候,我要分别存customer表、orders表、orderdetail表,假如某个故障阻止了这个存储过程,那么数据库会发生什么?
1、customer表存失败了,那么我们就不能让它继续往orders和orderdetail表存。
2、customer成功了,但orders表失败了,那么customer是可以允许成功的,毕竟某个用户没有订单是完全合法的,但是orders失败了,orderdetail就不能往下存了,因为orders和orderdetail是同步的。

怎么实现?这里用保留点实现部分回退事务。
创建保留点:

SAVEPOINT ins;//创建一个名为ins的保留点,注意保留点名要唯一

回退至保留点:

ROLLBACK TO SAVEPOINT ins;

下面我们解决刚才那个问题:

//开启事务
START TRANSACTION;
INSERT INTO customer(name,age) VALUES(LSGO实验室,20);//这里我故意写错
SAVEPOINT err1;//创建保留点
INSERT INTO orders(user_id,order_no) VALUES(10,'456123');
INSERT INTO orderdetail(order_no,detail) VALUES('456123','螺蛳粉');
ROLLBACK TO SAVEPOINT err1;
//如果不加commit,那么数据并没有写入到数据库中,只是写到了缓存中,直接查询数据库是没有插入的值的
COMMIT;

执行上面的SQL语句,你会发现,表全都没有成功,因为是第一句INSERT 失败了,后面的操作都被撤回去了。
再看下面的:

//开启事务
START TRANSACTION;
INSERT INTO customer(name,age) VALUES('LSGO实验室',20);
SAVEPOINT err2;//创建保留点
INSERT INTO orders(user_id,order_no) VALUES(10,'456123');
INSERT INTO orderdetail(order_no,detail) VALUES('456123',螺蛳粉);//这里我故意写错了
ROLLBACK TO SAVEPOINT err2;
//如果不加commit,那么数据并没有写入到数据库中,只是写到了缓存中,直接查询数据库是没有插入的值的
COMMIT;

结果是,customer表存成功了,但是orders和orderdetail表失败了。这也解决了我们上面的问题。

五、更改默认的提交行为:
默认的MYSQL行为是自动提交的,也就是说你的SQL语句只要一执行,就会马上生效,为了实现MYSQL不自动提交,可以设置sutocommit = 0:

SET autocommit = 0;

autocommit标志决定是否是自动提交更改,而不管有没有COMMIT语句。

由于我对MYSQL流程控制语句不是太熟悉,因此在这里没法写出更高级的例子,例如什么时候才 ROLLBACK ,什么时候 COMMIT。在我的另一篇博客里我用 PHP 来实现 mysql 的事务处理。

本博客参考自《mysql必知必会》