|
Alexander Kuznetsov
芝加哥，伊利诺斯州
2002 年 9 月
防止“丢失更新”可能很有挑战性。本文描述了这个问题的一个简单而又非常有效的解决方案，该解决方案使用触发器和 SQL 存储过程在中间层生成唯一版本号。
简介
假定您希望租一部车到某地旅行，那里正在下雪。因为下雪，所以您在搜索条件中将牵引控制和 ABS 指定为必不可少的条件。就在您浏览可用于出租的汽车列表时，一位出租代理公司职员正在纠正 同一部 车（您刚选中的车，实际上它没有牵引控制）的数据项错误。在您单击 Reserve 时，数据库中的错误已得到纠正，但您却在不知情的情况下选择了一部没有牵引控制的车。
本文所描述的正是解决这个问题（也称为“丢失更新问题”）的一种简单方法。本文不打算对这个问题的不同解决方案进行任何比较；它描述了一个在中间层生成唯一版本号的简单、有效和可伸缩的方法。
使用唯一记录版本号来防止“丢失更新”
在 [1]中可以找到“丢失更新”问题的详细分析。让我们仔细研究简介中所描述的案例并考虑另一种解决问题的方法。
关于出租的汽车的数据存储在一个表中。该表中如下填入了样本数据：
CREATE TABLE CAR(PART_KEY SMALLINT NOT NULL,
ID INT NOT NULL,
MAKE_MODEL VARCHAR(100) NOT NULL,
TAG CHAR(9) NOT NULL,
HAS_ABS CHAR(1) NOT NULL DEFAULT 'Y',
HAS_TRACTION_CONTROL CHAR(1) NOT NULL DEFAULT 'N',
RESERVED_FOR VARCHAR(100),
VERSION_ID INT NOT NULL DEFAULT 1,
PRIMARY KEY(PART_KEY, ID));
INSERT INTO CAR
(PART_KEY, ID, MAKE_MODEL, TAG, HAS_TRACTION_CONTROL, VERSION_ID )
VALUES (1, 1, 'Chevrolet Prizm LSI 1998, White', 'RENT ME NOW', 'Y', 1);
|
在 Web 浏览器中指定了搜索条件 WHERE HAS_TRACTION_CONTROL = 'Y' AND HAS_ABS = 'Y' 之后，我向服务器发送了请求。作为对我请求的响应，服务器返回结果集并彻底清除了我的请求，这意味着没有游标是打开的，并且所有的锁都被释放了。但是，每条发送到我的 Web 浏览器的记录都获得了一个唯一记录版本号 VERSION_ID 。记录号 1 满足我的搜索条件，因而随它的版本号 1 一起被检索了出来（版本号 1 在整个表中是唯一的，这意味着其它记录的版本号不会是 1）。那个职员也检索了同一记录，快速地进行了更新，然后保存了更改。为了表明记录已被更新，中间层为这条记录生成了新版本号 — 2。
UPDATE CAR SET
HAS_TRACTION_CONTROL = 'N',
VERSION_ID = 2
WHERE ID = 1
AND VERSION_ID = 1;
|
这次更新成功了。请注意 WHERE 子句中的 AND VERSION_ID = 1 条件。我们的方法是要么更新已检索的同一版本，要么失败。我们不想覆盖他人的更改。要使这种模式正确地工作，我们必须在每次更新记录时提供不同的记录版本号。让我们创建 BEFORE UPDATE 触发器，以保证每条已更新的记录都能获得不同的版本号：
CREATE TRIGGER CAR_UPD_CHECK
NO CASCADE
BEFORE UPDATE ON CAR
REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
FOR EACH ROW
MODE DB2SQL
WHEN (NEW_ROW.VERSION_ID = OLD_ROW.VERSION_ID)
SIGNAL SQLSTATE '80000'
('An attempt to update without providing a different version number')
@
|
当我最终拿定主意并单击 Reserve时，中间层生成了一个新的记录版本号 3：
UPDATE CAR SET
RESERVED_FOR = 'Alexander Kuznetsov',
VERSION_ID = 3
WHERE ID = 1
AND VERSION_ID = 1;
SELECT * FROM CAR;
|
更新失败了，如预期的一样，因为记录的版本号不匹配（DB2 返回 SQLSTATE '02000' No row was found for FETCH, UPDATE or DELETE）。
这个方法最重要的好处是其简单性。此外，添加一个非常简单的触发器所造成的性能影响也很小。有一个现有的 ID 索引用于更新（它是为主键约束而创建的），因此不必创建额外的索引。
正如我们所见，有可能用非常简单的方法来防止“丢失更新”。现在，让我们讨论如何在中间层生成唯一版本号。
生成唯一版本号
如果您正计划在家里举办一个非正式的聚会，突然发现自己没有塑料杯子，那么您会驱车到最近的杂货店购买适当数量的杯子。也许是 50 、100 或 250 个；它们不会在车厢里占用太多空间。塑料杯子不易腐蚀。如果这次用不完，以后肯定还可以使用。如果您计算错了并需要再多买一些，您可能会再跑一趟买上几百个。您不会为每一位到来的宾客都到商店跑个来回，只买一只杯子，对不对？呵呵，我也不会，即使车子跑得很快、道路很平坦并且不拥挤。
要让我们的方案工作，我们需要为每条记录的每个版本提供唯一版本号。如果我们使用 DB2 UDB EEE V7.2，就没有使用序列的选项。 [2] 中描述了在这种情况下生成唯一版本号的几种方法。让我们考虑另一种方法：Java™ 类检索由存储过程生成的唯一版本号并将它们提供给其它中间层模块。 下载中提供了样本 Java 代码。
为了从在 DB2 中实现的序列获得唯一版本号，中间层进程通过网络发送请求。然后，运行 DB2 的服务器处理该请求。然后，通过网络返回结果。因此，从序列中检索唯一版本号必定会产生在网络上的往返传递信息的代价和数据库调用的开销，如 图 1 所示。
图 1. 检索单个唯一版本号
我们的实现多少有些不同（ 图 2）。
图 2. 检索唯一版本号区间
中间层进程一次请求一批唯一版本号，例如 100 个。存储过程存储这些唯一版本号的连续区间，例如从 201 到 300，然后返回该区间的端点，端点是一对数字（201，300）。现在这个中间层进程可以自行使用这些唯一版本号，或将它们提供给其它进程，或两者皆可……对服务器或网络来说，这不再产生负担。这 100 个唯一版本号只花费了 1 次开销。（哦，这不准确。基于存储过程的定制实现在性能方面可能不会胜过 DB2 本身所带的序列）。另一个中间层进程请求并接收了下一批唯一版本号，比如 1000 个，从 301 到 1300。对于服务器和网络来说，生成 1000 个唯一版本号和只生成 100 个一样容易。在生成唯一版本号时，数据库中的记录被更新，并且每次都提交更新。
正如我们所见，检索唯一值区间（ 图 2）比从服务器获得每个值（ 图 1）更有效率，因为：
- 一些工作负载被从服务器转移到了中间层。
- 网络流量显著减少了。
实现这个方法很简单。让我们创建一个表。在本例中，PART_KEY 是缺省情况下的分区键：
CREATE TABLE AVAILABLE_NUMBER
(PART_KEY SMALLINT NOT NULL,
AVAILABLE_NUMBER INT NOT NULL);
|
现在让我们填充表：
INSERT INTO AVAILABLE_NUMBER(PART_KEY, AVAILABLE_NUMBER)VALUES(0, 0);
|
以下是生成唯一版本号区间的存储过程：
CREATE PROCEDURE DB2INST2.GET_UNIQUE_VALUE_EXTENT
( IN NUM_VALUES INT,
OUT VALUES_START INT,
OUT VALUES_END INT,
OUT SQLSTATE_OUT CHAR(5),
OUT SQLCODE_OUT INT)
LANGUAGE SQLP1: BEGIN ATOMIC
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;
DECLARE UNDO HANDLER FOR SQLEXCEPTION
BEGIN
-- You must extract both SQLSTATE and SQLCODE in one statement
SELECT SQLSTATE, SQLCODE
INTO SQLSTATE_OUT,
SQLCODE_OUT
FROM SYSIBM.SYSDUMMY1;
END;
-- Setting default values to output parameters
SET VALUES_START=0;
SET VALUES_END=0;
SET SQLSTATE_OUT=SQLSTATE;
SET SQLCODE_OUT=SQLCODE;
-- First let's update the record.
-- Because the UPDATE statement is inside an ATOMIC block, an update lock
-- on the record is held until the transaction is committed.
UPDATE AVAILABLE_NUMBER SET AVAILABLE_NUMBER = AVAILABLE_NUMBER + NUM_VALUES;
-- It's assumed there is only one record.
-- The statement will fail if there are multiple records.
SELECT AVAILABLE_NUMBER INTO VALUES_END FROM AVAILABLE_NUMBER;
SET VALUES_START = VALUES_END - NUM_VALUES;
-- The client will COMMIT the transaction and release the update lock
END P1
|
用户未被授予对 AVAILABLE_NUMBER 表的任何权限，他们只对存储过程有 EXECUTE 特权。存储过程中的 SQL UPDATE 语句必须获得一个更新锁 — 只有那时值区间才会被检索和返回到中间层。对这个存储过程的另一次调用必须等待以获取其更新锁。无论有多少连接检索生成的值，这都保证了值的唯一性。注：当 AVAILABLE_NUMBER 超出其最大正值时，会产生一个错误，并且不再生成更多的值。
结束语
正如我们所见，实现简单的算法来避免“丢失更新”是有可能的。当然，这样做的代价是：在每个“受保护”的表上添加一列和一个更新触发器。我们还看到了如何在中间层生成唯一版本号，并讨论了这种方法的好处。
参考资料
- [1]Bill Wong 和 George Baklarz 合著的 DB2 Universal Database Version 7.1 for UNIX, Linux, Windows 和 0S/2 Database Administration Certification Guide，第四版。
- [2]Richard Yevich、Warwick Ford 和 Susan Lawson 合著的 DB2 High Performance Design and Tuning。
致谢
作者要感谢 Serge Rielau 所作的非常有用的讨论。作者还要感谢妻子 Anna Krylova 和朋友 Elden Bastian 的帮助和鼓励。
下载
这个 zip 文件包括两个程序：
- Sequence.java 检索了在存储过程中生成的唯一版本号并将它们提供给其它模块。
- SequenceTester.java 是使用生成的唯一版本号的示例。
| 描述 |
文件类型 |
文件大小 |
下载方法 |
| sequencesample.zip |
zip |
3 KB |
HTTP  或 FTP |
 我该选择哪种下载 方法？
下载 FAQ
关于作者
 Alexander Kuznetsov有 14 年从事软件设计、开发和数据库管理的经验。目前他从事 DB2 UDB EEE 中的多 TB 级群集数据库的设计工作。Alexander 是 IBM 认证的高级技术专家（DB2 群集）（IBM Certified Advanced Technical Expert (DB2 Cluster)）和 IBM 认证解决方案专家（数据库管理和应用程序开发）（IBM Certified Solutions Expert (Database Administration and Application Development)）。可通过 comp.databases.ibm-db2 新闻组与他联系。 |
|
