锁与应用程序设计

最新推荐文章于 2025-11-25 06:00:46 发布

转载最新推荐文章于 2025-11-25 06:00:46 发布 · 914 阅读

标签

#优化 #delete #db2 #编译器 #access

技术technology 专栏收录该内容

6 篇文章

订阅专栏

摘自《循序渐进DB2-系统管理、运行维护与应用案例》

为了确定锁定属性，可以将应用程序处理划分为下列类型的其中一种：

只读

此类型包括所有这样的选择语句，它们本身是只读的，并具有一个显式的FOR READ ONLY子句；或者是模糊的(但是查询编译器因为PREP或BIND命令指定了BLOCKING选项的值而假定它们是只读的)。此处理类型只要求"共享"锁定(S、NS或IS)。

更改意向

此类型包括具有 FOR UPDATE的SELECT语句，或者查询编译器解释有歧义的语句以表示想进行更改。此类型使用"共享"和"更新"锁定(对于行，为S、U和X；对于表，为IX、U和X)。

更改

此类型包括UPDATE、INSERT及DELETE，但不包括UPDATE WHERE CURRENT OF或DELETE WHERE CURRENT OF。此类型要求"互斥"锁定(X或IX)。

受控游标

此类型包括UPDATE WHERE CURRENT OF和DELETE WHERE CURRENT OF。此类型也要求"互斥"锁定(X或IX)。

根据子查询语句的结果，在目标表中进行插入、更新或删除数据的语句，执行两种类型的处理：只读处理规则确定对在子选择语句中返回的表的锁定；更改处理规则确定对目标表的锁定。

优化器假定应用程序必须检索由SELECT语句指定的所有行，此假设最适合于OLTP和批处理环境。但是，在"浏览"应用程序中，查询经常定义一个可能很大的答案集，但它们只检索前几行，通常只检索填满该屏幕所需的那么多行。

要提高这种应用程序的性能，可以按下列方式修改SELECT语句：

使用FOR UPDATE子句来指定可由后续定位的UPDATE语句更新的列；

使用FOR READ/FETCH ONLY子句来使返回的列为只读的；

使用OPTIMIZE FOR n ROWS子句来授予检索整个结果集中前n行的优先级；

使用FETCH FIRST n ROWS ONLY子句来仅检索指定的几行；

使用DECLARE CURSOR WITH HOLD语句来每次检索一行。

注意：

如果使用FOR UPDATE、FETCH FIRST n ROWS ONLY或OPTIMIZE FOR n ROWS子句，或者您声明游标为"滚动"，那么会影响行分块。

FOR UPDATE子句

FOR UPDATE子句通过仅包含可由后续定位的UPDATE语句更新的列来限制结果集。如果不带列名指定FOR UPDATE子句，那么包括表或视图的全部可更新列。如果指定列名，那么每个名称必须是非限定的，且必须标识表或视图的某一列。

在下列情况下，不能使用FOR UPDATE子句：

不能删除与SELECT语句关联的游标；

选择的列中至少有一列是目录表的不可更新列，且没有在FOR UPDATE子句中排除掉。

由于相同的目的，可将DB2 CLI连接属性SQL_ATTR_ACCESS_MODE用于CLI应用程序中。

FOR READ或FETCH ONLY子句

FOR READ ONLY子句或FOR FETCH ONLY子句确保返回只读结果。因为被定义为只读的视图上SELECT的结果表也是只读的，所以允许此子句但没有作用。

对于允许更新和删除的结果表，如果数据库管理器可以检索数据块而不是互斥锁定，那么指定FOR READ ONLY可能会提高FETCH操作的性能。不要对用于定位的UPDATE或DELETE语句的查询使用FOR READ ONLY子句。

由于相同的目的，可将DB2 CLI连接属性SQL_ATTR_ACCESS_MODE用于CLI应用程序。

OPTIMIZE FOR n ROWS子句

OPTIMIZE FOR子句声明它只想检索结果的一个子集或优先检索前几行。优化器然后可以优先选择把检索前几行的响应时间减至最短的访问方案。另外，作为单个块发送到客户机的行数由OPTIMIZE FOR子句中的"n"值来限制。因此，OPTIMIZE FOR子句既影响服务器从数据库检索合格行的方式，又影响将合格行返回到客户机的方式。

例如，假设您定期查询职员表，来查找具有最高工资的职员。

SELECT LASTNAME,FIRSTNAME,EMPNO,SALARY
FROM EMPLOYEE ORDER BY SALARY DESC

您定义了一个基于SALARY 列的降序索引。但是，由于职员是按职员号排序的，所以工资索引可能很难集群。为了尽量避免许多随机的同步I/O，优化器将可能选择使用列表预取访问方法，此方法需要对合格的所有行的行标识排序。在将前几个合格行返回至应用程序前，此排序可能导致一个延迟。要防止此延迟，向语句添加OPTIMIZE FOR子句，如下所示：

SELECT LASTNAME,FIRSTNAME,EMPNO,SALARY FROM EMPLOYEE  
RDER BY SALARY DESC          OPTIMIZE FOR 20 ROWS

在此情况下，优化器可能选择直接使用SALARY索引，因为只检索具有最高工资的20个职员。不管可以将多少行分块，而只把每20行组成的一个块返回至客户机。

使用OPTIMIZE FOR子句，优化器优先选择可以避免大量操作或中断行流动(如排序)的访问方案。使用OPTIMIZE FOR 1 ROW最有可能影响访问路径。使用此子句可以有下列作用：

连接方法可以更改。一个嵌套循环连接是非常可能的选择，因为它具有低开销成本，并且通常在检索少量行时更有效率。

一个与ORDER BY子句匹配的索引更可能，因为对于ORDER BY不需要排序。

列表预取不太可能，因为此访问方法需要排序。

顺序预取不太可能，因为只需要知道少量的几行。

在一个连接查询中，在ORDER BY子句中包含列的表可能被选作外部表，前提是该外部表上的一个索引提供ORDER BY子句所需的排序。

虽然 OPTIMIZE FOR子句适用于所有优化级别，但它在优化级别3和更高级别下工作得最好。因为级别3以下的级别使用"贪婪"连接枚举方法，此方法有时会产生一个不能使它们自己很快检索前几行的多表连接的访问方案。

OPTIMIZE FOR子句不阻止您检索全部合格行。如果确实要检索全部合格行，那么总耗用时间可能大大高于优化器为整个答案集进行优化所需的时间。

如果已打包的应用程序使用调用级接口(DB2 CLI或ODBC)，可在db2cli.ini配置文件中使用OPTIMIZE FOR N ROWS关键字，让DB2 CLI自动将一个OPTIMIZE FOR n ROWS子句追加至每个查询语句的末尾。

FETCH FIRST n ROWS ONLY子句

FETCH FIRST n ROWS ONLY子句设置可检索的最大行数。将结果表限制为只包含前几行可提高性能。无论结果集可能另外包含多少行，只检索n行。

如果同时指定了FETCH FIRST子句和OPTIMIZE FOR子句，那么这两个值中较小的一个影响通信缓冲区(RQRIOBLK，最大请求I/O块)大小。为了达到最优化，将这两个值看做是互不相关的。

DECLARE CURSOR WITH HOLD语句

当用包括WITH HOLD子句的DECLARE CURSOR语句声明游标时，在落实该事务时任何打开的游标仍然打开，并且释放所有锁定(保护打开的WITH HOLD游标的当前游标位置的锁定除外)。

如果回滚事务，那么关闭所有打开的游标并释放所有锁定。