重学MYSQL之管理事务处理

数据库 2020-09-11 761

1.事务处理

事务处理,通过确保成批的SQL操作要么完全执行,要么完全不执行,来维护数据库完整性。

当数据库在执行某项操作时,如断电,数据库故障的异常时,这个过程无法完成。

事务处理是一种机制,用来管理必须成批执行的SQL操作,保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们要么完全执行,要么完全不执行。如果没有错误,整组语句提交给数据库表;如果发生错误,则进行回退,将数据库恢复到某个已知且安全的状态。

下列是关于事务处理需要知道的几个术语:

  • 事务:指一组SQL语句
  • 回退:指撤销指定SQL语句的过程
  • 提交:指将为存储的SQL语句结果写入数据库表
  • 保留点:指事务处理中设置的临时占位符

提示:事务处理可以管理INSERT、UPDATE、DELETE语句。

2.控制事务处理

管理事务的关键自愈将SQL语句组分解为逻辑块,并明确规定数据何时应该回退,何时不应该回退。

有的数据库,会要求明确标识事务处理块的开始和结束。如在SQL Server中。标识如下:

BEGIN TRANSACTION
...
COMMIT TRANSACTION

MariaDB和MySQL中等同的代码为:

START TRANSACTION;
...

Oracle使用的语法

SET TRANSACTION
...

PostgreSQL中使用ANSI SQL语法:

BEGIN
....

2.1 使用ROLLBACK

ROLLBACK 命令用来回退SQL语句

DELETE FROM Orders ROLLBACK;

2.2 使用COMMIT

一般的SQL语句都是针对数据库表直接执行和编写。这就是所谓隐式提交,即提交(写或者保存)操作是自动进行的。

进行明确的提交,使用COMMIT语句。下面是一个SQL Server的例子:

BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION

作用:从系统中删除订单12345.因为涉及到2个表,所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用,但第二条失败,则DELETE不会提交。

以下代码为Oracle中的写法:

SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT

2.3使用保留点

使用简单的ROLLBACK和COMMIT语句,就可以写入或撤销整个事务。但是只对简单的事务才能这么做,复杂的事务可能需要部分提交或回退。例如前面描述的添加订单的过程就是一个事务。如果发生错误,只需要返回到添加Orders行之前即可。不需要回退到Customer表。

要支持回退部分事务,必须在事务处理块中的合适位置放置占位符。这样,如果需要回退,可以回退到某个占位符。在SQL中,这些占位符称为保留点。在MariaDB、MySQL和Oracle中创建占位符,可以使用SAVEPOINT语句。

SAVEPOINT delete1;

在SQL server中,如下进行:

SAVE TRANSACTION delete1;

 每个保留点都要能够标识它的唯一名字,以便在回退时,数据库知道回退到何处。要回退到本例给出的保留点,在SQLServer中可如下进行。

ROLLBACK TRANSACTION delete1;

MariaDB,MySQL和Oracle

ROLLBACK TO delete1;

下面是一个完整的SQL Server例子

BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name) VALUES('100000', 'Toys Emporium');
SAVE TRANASCTION StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(2020, '2020/10/1', '100000');
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price) VALUES(20100, 1, 'BR01', 100, 5.49);
if @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', 100, 10.99);
IF @@ERROR <> ROOLLBACK TRANSACTION StartOrder;
COMMIT TRANSCTION;

这里的事务处理块包含了4条INSERT语句。在第一条INSERT语句之后定义了一个保留点,因此,如果后面的任何一个INSERT操作失败,事务处理能够回退到这里。在SQLServer中,可检查一个名为@@ERROR的变量,看操作是否成功。(其他DB使用不同的函数或变量返回此信息)。如果@@ERROR返回一个非0的值,表示有错误发生,事务处理回退到保留点。如果整个事务处理成功,发布COMMIT已保留数据。

提示:保留点越多越好,这样可以更灵活的进行回退

 

标签:数据库

文章评论

评论列表

已有0条评论