DB2 基础: 揭示围绕 DB2 UDB 数据移动工具的锁定行为

核心提示：为了防止无控制的数据访问，并提供并发性控制，DB2 基础: 揭示围绕 DB2 UDB 数据移动工具的锁定行为，IBM® DB2® Universal DatabaseTM (DB2 UDB) 数据库管理器在数据库资源（比如表行或整个表）上放置锁，锁将资源与应用程序（叫做锁拥有者）相关联， 因为锁不是持久

为了防止无控制的数据访问，并提供并发性控制，IBM® DB2® Universal Database^TM (DB2 UDB) 数据库管理器在数据库资源（比如表行或整个表）上放置锁。锁将资源与应用程序（叫做锁拥有者）相关联，以控制其他应用程序对同一资源的访问。简而言之，锁定是一种方法，通过该方法，当两个或多个应用程序进程同时请求访问同一数据时，可以保持数据完整性。

锁可以防止一个应用程序进程所做的未提交更改被另一个进程访问。当进程结束，或者当提交操作释放在工作单元（unit of work，UOW）期间获得的锁时，锁就被释放。UOW 是应用程序进程中的一个可恢复的操作序列；它由于提交操作、回滚操作或者结束应用程序进程而结束。

主要的 DB2 UDB 数据移动工具有 export、import 和 load。这些工具可以用于在数据库之间移动数据。export 工具将数据从数据库表复制到文件中，这些文件又可以用于将数据导入（import）或加载（load）到另一个数据库表中。

隔离级别

与应用程序进程相关联的隔离级别定义了进程与其他并发执行的应用程序进程相隔离的等级。隔离级别被指定为应用程序进程使用的包的属性。包（package）是在执行 BIND、PRECOMPILE 或 REBIND 命令期间创建的数据库对象，其中包含 SQL 编译器输出，并用于执行 SQL 语句。在您预编译（准备）应用程序时，或者将应用程序绑定到数据库时，可以指定一个适当的隔离级别。

当应用程序是预编译的（precompiled）时，嵌入式 SQL 语句将会被可以提交给 SQL 编译器的源代码所取代。

当应用程序是被绑定到（bound to）数据库的时候，SQL 编译器输出被转换到一个包中。

DB2 UDB 支持以下隔离级别：

可重复读（Repeatable read，RR） 确保工作单元（UOW）期间的任何表行读操作直到 UOW 完成，不会被其他应用程序进程更改。类似地，由另一个应用程序进程更改的任何行直到由该应用程序进程提交，不会被读取。运行在 RR 级别的应用程序进程是完全与并发应用程序进程的效果相隔离的。

读稳定性（Read stability，RS）类似于 RR。但是，运行在 RS 级别的应用程序进程不是 完全与并发应用程序进程的效果相隔离的。如果这样的应用程序进程不止一次发出同样的查询，它就会看到更改了的数据或者由其他应用程序进程添加的新的“幻影（phantom）”行。

游标稳定性（Cursor stability，CS）也确保由另一个应用程序进程更改的任何行直到被那个应用程序进程提交，不会被读取。但是 CS 隔离级别只确保每个可更新游标的当前行 不被其他应用程序进程更改；在 UOW 期间读过的行可以被其他应用程序进程更改。

未提交读（Uncommitted read，UR）对于某些操作，允许在 UOW 期间读过的任何行可以被其他应用程序进程更改，并允许读任何被另一个应用程序进程更改过的行，即使该更改还没有提交。对于其他操作，UR 类似于 CS。

在 Version 8 之前，DB2 UDB load 工具需要对与目标表相关联的表空间具有独占访问。现在不是这样了。到了 Version 8，load 工具在表本身上放置锁，因而您可以在运行数据加载操作的同时，对相同表空间中的其他表对象进行并发访问。实际上，load 工具比 import 工具要快得多，但是在加载海量（吉比特或更多）数据时，仍然会花相当长的时间。在这样的情况下，对加载操作的目标表中预先存在的数据的并发访问是非常重要的。

本文概要介绍了 DB2 UDB 数据移动工具的锁定行为，并概述了运行这些工具时，对数据的并发访问是如何管理的。

DB2 export 工具

DB2 UDB export 工具将数据从数据库表写到存储在数据库外面的一个或多个文件中。export 工具使用 SQL 从表中读取数据。export 工具将数据所写到的文件可以是几种标准文件格式的一种，一般是 PC/IXF 或 DEL。PC/IXF 文件格式是集成交换格式（Integration Exchange Format，IXF）数据交换架构的一个改编版本。分隔的 ASCII (DEL) 文件是 ASCII 字符流；数据流中的行由行分隔符分隔，而行中的列值由列分隔符分隔。除了要指定写到输出文件中的数据格式之外，还必须提供该文件的名称和一个用于指定输出数据的 SQL SELECT 语句。这就是启动基本的 DB2 导出操作所需的所有信息。图 1 展示了 EXPORT 命令的部分语法图，其中只包含必需的参数。

DB2 导出操作需要数据库连接。其他想要并发访问相同表中的数据的应用程序不需要与数据库断开连接，并且可以继续进行该表中的读或写操作。这是因为 export 工具被绑定为具有游标稳定性（CS）隔离级别（有关 DB2 隔离级别的描述，请参阅侧栏）。在导出操作期间，行级别锁定应用于导出表（exporting table），这意味着一次只持有一个锁。该锁是与正在被导出的行相关联的行锁。隔离级别是游标稳定性时，当游标在行间移动时就会释放锁。

图 1. DB2 EXPORT 命令的部分语法

使用下面的场景，我们可以相当容易地演示该命令。我们将使用到一个叫做 staffdata.del 的分隔的 ASCII 数据文件。该文件包含较大的数据量（22767 条记录），这对于这一类型的练习是必要的。可以快速装配这样的文件，方法是先将从 STAFF 表（在 DB2 UDB 附带的 SAMPLE 数据库中）导出的数据的许多副本粘贴到一个文本文件，然后用电子表格中生成的序列号重写 ID (SMALLINT) 列数据。这确保了 ID 列中的值是惟一的。

假设我们要使用 STAFF 表的表定义来创建一个新表 NEWSTAFF。在连接到 SAMPLE 数据库之后，我们通过发出下面这个 SQL 语句创建 NEWSTAFF 表：create table newstaff like staff。然后我们使用 DB2 IMPORT 命令，用大型 DEL 文件中包含的数据填充这个新表：import from staffdata.del of del modified by chardel"" coldel, decpt. insert into newstaff。

现在我们已经准备就绪，可以从 NEWSTAFF 表导出数据，并同步捕获数据库锁快照了。DB2 UDB 快照监视器用于捕获关于截取快照时的数据库和所连接应用程序的信息。从命令行处理器（command line processor，CLP）界面、从 SQL 表函数，或者在应用程序（比如 C 或 C++ 应用程序）中使用快照监视器管理的应用程序编程接口（application programming interface，API）函数，可以捕获快照。存在很多快照请求类型，每种类型返回其自己的一组监视数据。例如，锁快照捕获并返回数据库锁信息。

我们将分别从两个 DB2 命令窗口（会话）执行两个 CLP 脚本。第一个脚本被叫做 Application 1，第二个脚本则叫做 Application 2。Application 1 将执行导出操作，Application 2 将在 Application 1 运行的同时获得一个锁快照。

清单 1 中展示了该类型练习的一种输出例子。锁快照包含数据库摘要信息、应用程序信息，当然还有锁信息。锁信息捕获的是关于截取该快照时持有的每个锁的信息。这里有趣的是 NEWSTAFF 表持有的意图共享（intent share，IS）锁。有了这一类型的锁，并发应用程序就可以读或写已锁定的表。

清单 1. 一个数据库锁快照，揭示在数据导出期间使用了 IS 锁定

　　Application　1:　
connect　to　sample　
export　to　newstaffdata.del　of　del　select　*　from　newstaff　
SQL3104N　The　Export　utility　is　beginning　to　export　data　to　file　"newstaffdata.del".　
SQL3105N　The　Export　utility　has　finished　exporting　"22767"　rows.　
Number　of　rows　exported:　22767　
connect　reset　
Application　2:　
connect　to　sample　
get　snapshot　for　locks　on　sample　
　　　　　　Database　Lock　Snapshot　
...　
List　of　Locks　
...　
　Lock　Name　　　　　　　　　　=　0x0002000D000000000000000054　
　Lock　Attributes　　　　　　　=　0x00000000　
　Release　Flags　　　　　　　　=　0x00000001　
　Lock　Count　　　　　　　　　=　1　
　Hold　Count　　　　　　　　　=　0　
　Lock　Object　Name　　　　　　=　13　
　Object　Type　　　　　　　　　=　Table　
　Tablespace　Name　　　　　　　=　USERSPACE1　
　Table　Schema　　　　　　　　=　MELNYK　
　Table　Name　　　　　　　　　=　NEWSTAFF　
　Mode　　　　　　　　　　　　=　IS　
connect　reset　

DB2 import 工具

DB2 UDB import 工具将数据从输入文件写到数据库表或可更新视图。import 工具使用 SQL INSERT 语句（或 SQL UPDATE 语句）将数据写到目标表中。import 工具从中读取数据的文件可以是几种标准文件格式中的一种，一般是 PC/IXF、DEL 或 ASC。非分隔的 ASCII (ASC) 文件就是一个 ASCII 字符流；数据流中的行由行分隔符分隔，行中的每一列由起始位置和结束位置定义。除了要指定将从输入文件中读取的数据格式之外，还必须提供该文件的名称、目标表的名称，以及执行 import 工具的模式。这就是启动基本的 DB2 导入操作所需的所有信息。图 2 展示了 IMPORT 命令的部分语法图，其中只包含必需的参数。

图 2. DB2 IMPORT 命令的部分语法

查看原图（大图）

主要的 DB2 导入模式有：

INSERT 将输入数据添加到表中，并且不更改任何现有数据。

INSERT_UPDATE 将新的输入数据添加到表中，或者用具有匹配主键值的输入数据更新现有的行。

REPLACE （通过删除数据对象）删除表中的所有现有数据，然后再插入输入数据。

REPLACE_CREATE 删除现有表中的所有数据，然后再插入输入数据。如果该表不存在，该模式将优先于插入输入数据而创建表。该模式只对 PC/IXF 输入文件有效。

默认情况下，import 工具被绑定为具有读稳定性（RS）隔离级别（有关 DB2 隔离级别的描述，请参阅侧栏）。DB2 导入操作需要数据库连接。其他想要并发访问相同表中数据的应用程序不必与数据库断开连接；如果 IMPORT 命令上指定了 ALLOW WRITE ACCESS 子句，那么这样的应用程序可以同时具有对目标表的读和写访问。另一方面，ALLOW NO ACCESS（默认值）防止并发应用程序访问目标表数据。import 工具实现这一点的方法是在 ALLOW NO ACCESS 导入操作的开始请求目标表上的一个排他（X）锁。清单 2 展示了在表 NEWSTAFF2 的导入操作期间截取的一个锁快照的一部分，其中 NEWSTAFF2 创建成像 NEWSTAFF 表一样。该清单没有展示不是特定于导入操作的锁的信息。这些锁包括系统编录对象持有的锁，或者更加与插入或更新操作有关的所谓内部偏差锁（internal variation lock）或计划锁（plan lock）。

清单 2. 一个数据库锁快照，揭示在 ALLOW NO ACCESS 数据导入操作期间使用了 X 锁定

　　Application　1:　
connect　to　sample　
import　from　newstaffdata.del　of　del　modified　by　chardel""　coldel,　decpt.　
　insert　into　newstaff2　
　　
SQL3109N　The　utility　is　beginning　to　load　data　from　file　"newstaffdata.del".　
SQL3110N　The　utility　has　completed　processing.　
　　　　　"22767"　rows　were　read　from　the　input　file.　
SQL3221W　...Begin　COMMIT　WORK.　Input　Record　Count　=　"22767".　
SQL3222W　...COMMIT　of　any　database　changes　was　successful.　
SQL3149N　"22767"　rows　were　processed　from　the　input　file.　
　　　　　"22767"　rows　were　successfully　inserted　into　the　table.　
　　　　　　　"0"　rows　were　rejected.　
Number　of　rows　read　　　　　=　22767　
Number　of　rows　skipped　　　=　0　
Number　of　rows　inserted　　　=　22767　
Number　of　rows　updated　　　=　0　
Number　of　rows　rejected　　　=　0　
Number　of　rows　committed　　=　22767　
connect　reset　
Application　2:　
connect　to　sample　
get　snapshot　for　locks　on　sample　
　　　　　　Database　Lock　Snapshot　
...　
List　of　Locks　
...　
　Lock　Name　　　　　　　　　　=　0x0002000E000000000000000054　
　Lock　Attributes　　　　　　　=　0x00000000　
　Release　Flags　　　　　　　　=　0x40000000　
　Lock　Count　　　　　　　　　=　255　
　Hold　Count　　　　　　　　　=　0　
　Lock　Object　Name　　　　　　=　14　
　Object　Type　　　　　　　　　=　Table　
　Tablespace　Name　　　　　　　=　USERSPACE1　
　Table　Schema　　　　　　　　=　MELNYK　
　Table　Name　　　　　　　　　=　NEWSTAFF2　
　Mode　　　　　　　　　　　　=　X　
select　lock_mode,　lock_object_type,　substr(table_name,　1,　16)　as　table_name　
　from　table(sysproc.snapshot_lock('SAMPLE',-1))　as　lockinfo　
　where　lock_object_type　in　(1,2,4,5,6,11,18,19)　
connect　reset　

清单 2 也展示了一个针对 SNAPSHOT_LOCK 表函数返回的表的示例查询。SNAPSHOT_LOCK 表函数是一个 SQL 管理例程，它对快照监视器信息提供方便的访问。表 1 中的信息就基于这样的查询的示例输出。锁模式和锁对象类型都定义在 sqlmon.h 包含文件中，该文件将有助于破解表函数返回的数值当量（numerical equivalent）。

表 1. 基于 SNAPSHOT_LOCK 表函数的示例输出的锁快照信息

锁模式	锁对象类型	表名称
下一键共享（Next-key Share，NS）	Row	SYSTABLES
意图共享（Intent Share，IS）	Table	SYSTABLES
意图共享（Intent Share，IS）	Table	SYSCOLUMNS
排他（Exclusive，X）	Table	NEWSTAFF2

在 ALLOW WRITE ACCESS 导入操作（只对 INSERT 或 INSERT_UPDATE 导入模式有效）情况下，在准备插入第一行数据时，import 工具请求目标表上的一个意图排他（intent exclusive，IX）锁。当表持有 IX 锁时，锁拥有者和并发应用程序可以读和更新该表中的数据。import 工具周期性地提交已插入数据（参阅清单 3）。因为获得的锁不会跨事务边界存在，所以 import 工具将失去它的 IX 表锁，并且必须在每次提交之后请求另一个锁。如果一个并发连接的应用程序碰巧持有不兼容的行锁，那么 import 工具就会暂停，直到该应用程序提交或回滚其事务。

清单 3. 一个数据库锁快照，揭示在 ALLOW WRITE ACCESS (INSERT_UPDATE) 数据导入操作期间使用了 IX 锁定

　　Application　1:　
connect　to　sample　
create　table　newstaff　like　staff　
alter　table　newstaff　add　primary　key　(id)　
import　from　staff_data.del　of　del　modified　by　chardel""　coldel,　decpt.　
　insert　into　newstaff　
...　
Number　of　rows　committed　　=　35　
import　from　newstaffdata.del　of　del　modified　by　chardel""　coldel,　decpt.　
　allow　write　access　insert_update　into　newstaff　
SQL3109N　The　utility　is　beginning　to　load　data　from　file　"newstaffdata.del".　
SQL3186W　Data　was　not　loaded　into　the　database,　because　the　log　was　full　or　
the　lock　space　was　exhausted.　SQLCODE　"-912"　was　returned.　A　commit　will　be　
attempted　and　the　operation　will　continue　if　the　commit　is　successful.　
SQL0912N　The　maximum　number　of　lock　requests　has　been　reached　for　the　
database.　SQLSTATE=57011　
SQL3221W　...Begin　COMMIT　WORK.　Input　Record　Count　=　"1020".　
SQL3222W　...COMMIT　of　any　database　changes　was　successful.　
...　
SQL3186W　
SQL0912N　
SQL3221W　...Begin　COMMIT　WORK.　Input　Record　Count　=　"22356".　
SQL3222W　...COMMIT　of　any　database　changes　was　successful.　
SQL3110N　The　utility　has　completed　processing.　"22767"　rows　were　read　from　
the　input　file.　
SQL3221W　...Begin　COMMIT　WORK.　Input　Record　Count　=　"22767".　
SQL3222W　...COMMIT　of　any　database　changes　was　successful.　
...　
Number　of　rows　committed　　=　22767　
connect　reset　
Application　2:　
connect　to　sample　
select　lock_mode,　lock_object_type,　substr(table_name,　1,　16)　as　table_name　
　from　table(sysproc.snapshot_lock('SAMPLE',-1))　as　lockinfo　
　where　lock_object_type　in　(1,2,4,5,6,11,18,19)　
connect　reset　

这个例子包含两个导入操作：一个是用一些数据填充 NEWSTAFF 表，另一个是以 INSERT_UPDATE 模式导入更多数据，该模式需要在目标表上定义一个主键。基于对 SNAPSHOT_LOCK 表函数返回的表的查询的示例输出，表 2 展示在 ALLOW WRITE ACCESS 数据导入操作期间使用了 IX 表锁定。

表 2. 在 ALLOW WRITE ACCESS (INSERT_UPDATE) 数据导入操作期间持有的一些锁

锁模式	锁对象类型	表名称
下一键共享（Next-key Share，NS）	Row	SYSTABLES
排他（Exclusive，X）	Row	NEWSTAFF
意图共享（Intent Share，IS）	Table	SYSTABLES
意图排他（Intent Exclusive，IX）	Table	NEWSTAFF

DB2 load 工具

DB2 UDB load 工具非常高效地将一个或多个输入文件中的数据写到数据库表中。load 工具也从指定的管道、设备（比如磁带设备）或用户定义游标接受输入。load 工具与 import 工具不一样，不是使用 SQL 将数据写到目标表中；它是直接将格式化的页写到表中，因此比 import 工具要快得多。load 工具从中读取数据的文件可以是几种标准文件格式中的一种，一般是 PC/IXF、DEL 或 ASC。除了要指定从输入文件读取的数据格式之外，还必须提供该文件的名称、目标表的名称，以及执行 load 工具的模式。INSERT 模式添加输入数据到表中，并且不会更改任何现有数据。REPLACE 模式（通过删除数据对象）删除表中的所有现有数据，然后再插入输入数据。（另外还有 RESTART 模式和 TERMINATE 模式，但是这里不会详细介绍它们。这就是启动基本的 DB2 加载操作所需的所有信息。图 3 展示了 LOAD 命令的部分语法图，其中只包含必需的参数。

图 3. DB2 LOAD 命令的部分语法

查看原图（大图）

DB2 加载操作需要数据库连接。其他想要并发访问相同表中数据的应用程序不必与数据库断开连接；如果 LOAD 命令上指定了 ALLOW READ ACCESS 子句，那么这样的应用程序就可以对目标表具有读访问。该访问局限于开始加载操作之前已经存在的数据。另一方面，ALLOW NO ACCESS（默认值）防止并发应用程序访问目标表中的数据。load 工具实现这一点的方法是，请求目标表上的一个超级排他（Z）锁，并在加载操作期间一直持有这个锁（参阅清单 4）。当表持有 Z 锁时，任何并发应用程序都不能读取和更新该表中的数据。

清单 4. 一个数据库锁快照，揭示在 ALLOW NO ACCESS 数据加载操作期间使用了 Z 锁定

　　Application　1:　
connect　to　sample　
load　from　newstaffdata.del　of　del　modified　by　chardel""　coldel,　decpt.　
　insert　into　newstaff2　
SQL3501W　The　table　space(s)　in　which　the　table　resides　will　not　be　placed　in　
backup　pending　state　since　forward　recovery　is　disabled　for　the　database.　
SQL3109N　The　utility　is　beginning　to　load　data　from　file　
"/home/melnyk/newstaffdata.del".　
SQL3500W　The　utility　is　beginning　the　"LOAD"　phase　at　time　"12/07/2004　
17:39:18.311340".　
SQL3519W　Begin　Load　Consistency　Point.　Input　record　count　=　"0".　
SQL3520W　Load　Consistency　Point　was　successful.　
SQL3110N　The　utility　has　completed　processing.　"22767"　rows　were　read　from　
the　input　file.　
SQL3519W　Begin　Load　Consistency　Point.　Input　record　count　=　"22767".　
SQL3520W　Load　Consistency　Point　was　successful.　
SQL3515W　The　utility　has　finished　the　"LOAD"　phase　at　time　"12/07/2004　
17:39:19.630882".　
Number　of　rows　read　　　　　=　22767　
Number　of　rows　skipped　　　=　0　
Number　of　rows　loaded　　　　=　22767　
Number　of　rows　rejected　　　=　0　
Number　of　rows　deleted　　　=　0　
Number　of　rows　committed　　=　22767　
connect　reset　
Application　2:　
connect　to　sample　
get　snapshot　for　locks　on　sample　
　　　　　　Database　Lock　Snapshot　
...　
List　of　Locks　
...　
　Lock　Name　　　　　　　　　　=　0x0002000E000000000000000054　
　Lock　Attributes　　　　　　　=　0x00000000　
　Release　Flags　　　　　　　　=　0x40000000　
　Lock　Count　　　　　　　　　=　255　
　Hold　Count　　　　　　　　　=　0　
　Lock　Object　Name　　　　　　=　14　
　Object　Type　　　　　　　　　=　Table　
　Tablespace　Name　　　　　　　=　USERSPACE1　
　Table　Schema　　　　　　　　=　MELNYK　
　Table　Name　　　　　　　　　=　NEWSTAFF2　
　Mode　　　　　　　　　　　　=　Z　
connect　reset　

在 ALLOW READ ACCESS 加载操作情况下，load 工具在加载操作期间维护一个共享（S）锁，并在提交数据时将 S 锁升级为 Z 锁。S 锁允许使用只读查询的并发应用程序访问目标表，但是防止应用程序写数据到表中。当然不能同时请求 REPLACE 和 ALLOW READ ACCESS 加载操作：因为加载替换操作会在加载新数据之前删除所有现有的表数据，所以没有预先存在的数据可查询！

当有非常多的数据要加载时，ALLOW READ ACCESS 操作非常有用。这样的操作会花费一定的时间，因此您希望在工具运行期间具有对预先存在的表数据的虚拟不间断访问。此外，如果 ALLOW READ ACCESS 加载操作失败或者被中断，预先存在的表数据仍然可以被读取。

可以在 LOAD 命令上使用 LOCK WITH FORCE 选项，以迫使对目标表持有冲突锁的应用程序脱离系统；这样，加载操作就避免了等待锁时的超时风险。在 ALLOW NO ACCESS 加载操作的开始，该工具可能会压制那些试图读或写目标表的并发应用程序。然而在 ALLOW READ ACCESS 加载操作的开始，该工具可能会压制那些试图写表的应用程序，到了加载操作的结尾（当提交数据时），该工具可能会压制那些试图读或写目标表的应用程序。

因为锁不是持久的，所以 load 工具也使用表状态来控制对表的访问；即使加载操作中断，这些状态仍然有效。