DB2 9.7: 自治事务

核心提示：简介本文将介绍 DB2 9.7 中引入的自治事务特性，自治事务与数据库管理员和应用程序开发人员都有密切的关系，DB2 9.7: 自治事务， 本文将讨论 DB2 9.7 for Linux, UNIX, and Windows，掌握 DB2 命令行处理器（CLP）和 SQL PL 的知识将非常有帮助，现在，您应该理解什么

简介

本文将介绍 DB2 9.7 中引入的自治事务特性。自治事务与数据库管理员和应用程序开发人员都有密切的关系。

本文将讨论 DB2 9.7 for Linux, UNIX, and Windows。掌握 DB2 命令行处理器（CLP）和 SQL PL 的知识将非常有帮助。

要运行本文中的示例，需要访问 DB2 9.7 for Linux, UNIX, and Windows 数据库。

了解一些事务背景

事务是以文本、列（或同时使用两者）的形式表现的来自真实世界的实体，将由数据库管理系统处理。它们可以作为针对数据库执行的操作，并且必须作为一组操作执行。

例如，从用户 A 的帐户将 X 金额传输到用户 B 的帐户，这个请求是一个非常简单的事务。这个事务可以被分解为两个 SQL 语句，如清单 1 所示：

清单 1. 简单事务的示例
Update　table　AccountInfo　set　CurrentBalance　=　CurrentBalance　-　X,　where　UserName=A　
Update　table　AccountInfo　set　CurrentBalance　=　CurrentBalance　+　X,　where　UserName=B　

只有在两条 SQL 语句都能够成功更新表的情况下，事务才会得到成功调用。为了确保两条语句都能够生效或都不生效，应用程序将以这样的方式运行：直到 COMMIT 发生之前，数据库不会做出任何更改。发生 COMMIT 时，所有未提交的语句（自最近一次 COMMIT 之后的所有语句）将同时生效，确保数据的完整性。这类似于禁用命令行处理器（CLP）的 AUTO COMMIT 行为，然后发出一组语句并手动完成 COMMIT 操作。ROLLBACK 将移除所有未提交的修改。因此 COMMIT 和 ROLLBACK 语句是事务实现的重要构建块。

自治事务简介

自治事务拥有自己的 COMMIT 和 ROLLBACK 范围，确保它的结果不会影响到调用方的未提交的变更。此外，调用会话中的 COMMITs 和 ROLLBACKs 不应当影响自治事务本身完成时发生的最后更改。

注意，调用会话将被暂停，直到被调用的会话返回控制权。自治事务的支持不应该视为支持并行执行会话。

创建自治事务

在 DB2 中，自治事务通过自治过程实现。存储过程为将语句绑定到块中提供了一种自然的方式。要创建自治过程，需要在 CREATE PROCEDURE 语句中指定关键字 AUTONOMOUS，如清单 12 所示。

清单 2. CREATE PROCEDURE 语句示例
CREATE　OR　REPLACE　your_procedure_name　
LANGUAGE　SQL　
AUTONOMOUS　
BEGIN　　　
　do　autonomous　work　;　
END　

在调用自治过程时，将在独立的会话中执行，以提供必需的事务独立性。成功的自治过程将使用隐式方式提交，而失败的自治过程将执行回滚。无论哪一种情况，都不会影响调用事务。

一个真实的用例

银行 B 希望针对包含客户敏感数据的表的每一个查询都被正确记录。要实现这个目标，银行 B 的应用程序开发人员获得了一组接口，他们可以用这些接口访问敏感数据。每个接口被实现为一个存储过程。存储过程从表中返回所需的信息，与此同时，记录发出查询的雇员的用户 ID 和被查询的客户记录的帐户编号，包含日期和时间。

前提条件

SQL 作出以下所有假设：

存在数据库连接

关闭自动提交功能

语句终止符被设置为 %，方式是在启动新的 DB2 CLP 会话时输入 DB2 CLP 语句 db2 +c -td%

开始

首先创建必要的表。需要用一个表保存客户敏感信息，用另一个表保存在每次访问敏感信息时记录的信息。清单 3 展示了一个例子。

清单 3. 创建两个示例表
DROP　TABLE　customerSensitiveInfo　%　
CREATE　TABLE　customerSensitiveInfo(customerAccountNumber　integer,　amountOverdue　integer)　%　
　
DROP　TABLE　log_table　%　
CREATE　TABLE　
　　log_table(queryingEmployeeID　varchar(100),　
　　　　　　　customerAccNumber　integer,　when　timestamp)　%　
COMMIT　%　

接下来，创建一个过程以在敏感信息被访问时写入到 log_table，如清单 4 所示。

清单 4. 写入到日志表
CREATE　OR　REPLACE　PROCEDURE　
　　log_query　(in　queryingEmployee　varchar(100),　
　　　　　　　in　accNumber　integer,　in　when　timestamp)　
LANGUAGE　SQL　
BEGIN　
　　insert　into　log_table　values　(queryingEmployee,　accNumber,　when);　
END　%　
COMMIT　%　

银行 B 为其应用程序开发人员提供的接口之一可以查询某个给定帐户的过期程度。过程 get_AmountOverdue 将首先对 log_query 过程发出调用，表示它将访问敏感数据。然后从 customerSensitiveInfo 表中执行 select，获取给定帐户编号的过期时间量。清单 5 展示了一个例子。

清单 5. get_AmountOverdue 过程
CREATE　OR　REPLACE　PROCEDURE　
　　get_AmountOverdue(in　accountNumber　integer,　out　overdue　integer)　
LANGUAGE　SQL　
BEGIN　
　　DECLARE　due　integer;　
　　DECLARE　currentTime　timestamp;　
　
　　SET　currentTime=　CURRENT　TIMESTAMP;　
　
　　CALL　log_query(CURRENT　USER,　accountNumber,　currentTime　);　
　
　　SELECT　amountOverdue　INTO　due　FROM　customerSensitiveInfo　
　　　　　　　　　　　　　WHERE　customerAccountNumber=　accountNumber;　
　　　　　　　
　　SET　overdue=due;　
END　%　
　
COMMIT　%　

创建了 get_AmountOverdue 接口后，向 customerSensitiveInfo 表中添加一些客户数据。接下来，执行清单 6 中的语句创建表。

清单 6. 创建示例表的语句
INSERT　INTO　customerSensitiveInfo　VALUES(12345,　10000),(12346,20000)　%　
COMMIT　%　

结果生成的表包含如表 1 所示的信息。

表 1. CustomerSensitiveInfo

CustomerAccountNumber	AmountOverdue
12345	10,000
12346	20,000

现在表中已经填充了数据，并且可以使用某种方式访问它，那么现在从帐户 12345 中检索过期时间量。由于您只对查看数据感兴趣，因此希望以匿名方式执行，在调用之后立即发出一条回滚语句来隐藏您的踪迹，如清单 7 所示。

清单 7. 向 get_AmountOverdue 代码添加一条回滚语句
CALL　get_AmountOverdue(12345,　?)　%　
　　　　　
Value　of　output　parameters　
--------------------------　
Parameter　Name　:　OVERDUE　
Parameter　Value　:　10,000　
　　　　　
return　Status　=　0　
　　　　　
ROLLBACK　%　

检查 log_table 的状态，应当如清单 8 所示。

清单 8. log_table
SELECT　*　FROM　log_table　%　
　　　　　
QUERYINGEMPLOYEEID　　　CUSTOMERACCNUMBER　　　　WHEN　
　　　　　
0　record(s)　selected.　

如您所料，log 表现在是空的，因为包含实际表访问和日志表插入的事务被回滚了。这不是我们希望的行为。向 log_query 过程添加 AUTONOMOUS 关键字，如清单 9 所示。

清单 9. 向 log_query 过程添加 AUTONOMOUS 语句
CREATE　OR　REPLACE　PROCEDURE　
　　log_query　(in　queryingEmployee　varchar(100),　
　　　　　　　in　accNumber　integer,　in　when　timestamp)　
LANGUAGE　SQL　
AUTONOMOUS　
BEGIN　
　　INSERT　INTO　log_table　values　(queryingEmployee,　accNumber,　when);　
END　%　
　
COMMIT　%　

现在从帐户 12345 检索过期时间量，然后再次回滚该事务，如清单 10 所示。

清单 10. 包含回滚语句的 get_AmountOverdue 代码
CALL　get_AmountOverdue(12345,　?)　%　
　　　　　
Value　of　output　parameters　
--------------------------　
Parameter　Name　:　OVERDUE　
Parameter　Value　:　10,000　
　　　　　
return　Status　=　0　
　　　　　
ROLLBACK　%　

再次检查 log_table 的状态，现在应当如清单 11 所示。

清单 11. 添加了 AUTONOMOUS 语句之后的 log_table
SELECT　*　FROM　log_table　%　
　　　
QUERYINGEMPLOYEEID　　　CUSTOMERACCNUMBER　　　　WHEN　
98765　　　　　　　　　12345　　　　　　　　　　2009:05:25:12.00.00.000000　
　　　
1　record(s)　selected.　

这一次，我们获得了理想的结果。即使读取敏感信息的事务本身被回滚，log_table 的条目也会被提交。通过这种方式，您可以维护数据访问历史，即使访问本身没有被提交。

结束语

本文介绍了自治事务的概念。现在，您应该理解什么是自治事务，以及如何在 DB2 内创建和使用自治事务。