关于 DB2 数据库并发性的探讨之二

概述

OLTP 数据库通常是高并发的使用模式，具有高的并发性对 OLTP 系统来说至关重要，并发事务实际上取决于资源的使用状况，原则上应尽量减少对资源的锁定时间，减少对资源的锁定范围，从而能够尽量增加并发事务的数量，那 么影响并发的因素有哪些呢？这篇系列文章之二将从其他方面来探讨对 DB2 数据库并发性的影响，内容包括数据库设计、索引、应用程序设计

同时本文讨论及实验所基于的环境如下，见图 1：

图 1. DB2 版本

设置 DB2 注册表变量 DB2OPTIONS，将自动提交功能关闭，后面的实验默认都基于这个设置。

db2set DB2OPTIONS=+c db2 terminate db2stop db2start

 

回页首

数据库设计与并发性

YNS = WR 数据库设计概述

数据库设计是把现实世界的商业模型与需求转换成数据库的模型的过程，它是建立数据库应用系统的核心。设计的关键是如何使设计的数据库能合理地存储用户的数据，方便用户进行数据处理。

数据库设计完全是人的问题，而不是数据库管理系统的问题。数据库管理系统不管设计是好是坏，只要有请求过来，它就执行。数据库设计应当由数据库管理员和系统分析员一起和用户一道工作，了解用户的需求，共同为整个数据库做出恰当的、完整的设计。

数据库及其应用的性能都是建立在良好的数据库设计的基础上，数据库的数据是一切操作的基础，如果数据库设计不好，则其它一切调优方法对提高数 据库性能的效果都是有限的。最终目的除了满足功能上的需要之外，还应该尽量使 SQL 语句对资源的占用范围最小，SQL 语句执行时间最短，对资源的占用时间最短，从而可以提高并发性。

数据的规范化

究竟是否采用规范化设计完全取决于用户的需求，数据处理的要求；采用规范化设计可以消除数据冗余，更容易保证数据完整性，是否规范化的程度越 高越好？这要根据需要来决定，因为“分离”越深，产生的关系越多，关系过多，连接操作越频繁，而连接操作也是费时间的，特别对以查询为主的数据库应用来 说，频繁的连接会影响查询速度。所以，关系有时故意保留成非规范化的，或者规范化以后又反规范了，这样做通常是为了改进性能；当然采用非规范化设计带来的 问题也很多，会带来数据冗余，增加数据存储空间，很难维护数据的完整性——弄不好会导致更新异常、插入异常和删除异常，数据冗余越多，那么数据访问所产生 的物理 IO 可能就越多，查询速度可能就越慢。所以采用规范化设计与否，取决于 SQL 语句执行过程中物理 IO 比较耗时，还是连接比较耗时，最终目的都是为了尽量使 SQL 语句的执行时间最短，对资源的占用时间缩短，从而可以提高并发性。

当一个表是规范的，则其非主键列依赖于主键列。从关系模型的角度来看，表满足 3NF 最符合标准，这样的设计容易维护。一个完全规范化的设计并不总能生成最优的性能，因此通常是先按照 3NF 设计，如果有性能问题，再通过反规范来解决。

数据库中的数据规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的 I/O 次数减少，同时加快了增、删、改的速度，但是对完全规范的数据库查询，通常需要更多的连接操作，从而影响查询的速度。因此，有时为了提高某些查询或应用的 性能而破坏规范规则，即反规范。

反规范的好处是降低连接操作的需求、降低外键和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度， 但会降低修改速度。因此决定做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其它方法经常能够解决性能问题，而不必 采用反规范这种方法。

常用的反规范技术在进行反规范操作之前，要充分考虑数据的存取需求、常用表的大小、一些特殊的计算 ( 例如合计 )、数据的物理存储位置等。常用的反规范技术有增加冗余列、增加派生列、重新组表和分割表。

• 增加冗余列

  增加冗余列是指在多个表中具有相同的列，它常用来在查询时避免连接操作，但它需要更多的磁盘空间，同时增加表维护的工作量。

• 增加派生列

  增加派生列指增加的列来自其它表中的数据，由它们计算生成。它的作用是在查询时减少连接操作，避免使用集函数，列也具有与冗余列同样的缺点。

• 重新组表

  重新组表指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能，但需要更多的磁盘空间，同时也损失了数据在概念上的独立性。

• 分割表

  有时对表做分割可以提高性能。

表分割有两种方式：

1. 水平分割： 根据一列或多列数据的值把数据行放到两个独立的表中。水平分割通常在下面的情况下使用：

• 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
• 表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
• 需要把数据存放到多个介质上。

水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要 union 操作。在许多数据库应用中，这种复杂性会超过它带来的优点。

2.垂直分割： 把主键和一些列放到一个表，然后把主键和另外的列放到另一个表中。如果一个表中某些列常 用，而另外一些列不常用，则可以采用垂直分割，另外垂直分割可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少物理 I/O 次数。其缺点是需要管理冗余列，查询所有数据需要 join 操作。

　无论使用何种反规范技术，都需要一定的管理开销来维护数据的完整性，常用的方法是批处理维护、应用逻辑和触发器。批处理维护是指对复制列或 派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。数据的完整性也可由应用逻辑 来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维 护，容易遗漏，特别是在需求变化时，不易于维护。另一种方式就是使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且 相应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题的最好的办法。

回页首

索引与并发性

索引本身属于数据库设计的范畴，因为对数据库并发性的影响显著，所以我们这里单独抽出来讨论。在 where 相关字段上加索引，可以减少资源争用；索引对并发性的影响也是非常大的，我们通常知道索引对性能非常有好处，可以让 SQL 语句执行的更快（减少物理 IO），而实际上，索引对提高并发性也是大有好处的（减少锁范围，减少事务间资源争用）。

如果只是从大表中得到小的结果集，那么最好在 WHERE 条件字段上创建合适的索引，通过索引访问路径来获得结果。假如隔离级别为 RS，执行计划采用了索引访问路径，那么查询将首先通过索引得到结果行的物理位置 (ROWID)，然后才访问表获得结果行，所产生的锁为：对表加意向共享锁，对通过索引得到的结果行加 S 锁，和其他更新事务发生资源争用的可能性就小，反之如果不通过索引访问路径，而是通过全表扫描的话，那么执行过程中将临时在所有行上加行共享锁，和其他事 务发生资源争用的几率大大提高；假如隔离级别为 RR，应尽量通过主键字段或者 UNIQUE INDEX 字段进行查询，因为在这种情况下，只会产生表意向共享锁以及对符合条件的结果行加共享锁，反之任何情况下，会对表直接加共享锁，和其他事务发生锁冲突的可 能性非常大。

下面我们做个实验，验证索引对并发性的影响：

实验使用的数据模型见表 1:

表 1. 实验使用的数据模型

表名	表结构	索引
TEST		A 列上建有索引
TEST_NO_IND		没有索引

 

通过 session1 窗口更新 TEST_NO_IND 表，将 a= ’4 ’的记录 UPDATE 为’1000 ’，见图 2：

图 2. 执行 UPDATE 事务的 session1 窗口

通过 session2 窗口查询 TEST_NO_IND 表，统计 a= ’3 ’的记录数量，见图 3：

图 3. 执行查询事务的 session2 窗口

上述查询被锁住，是因为查询通过全表扫描访问的缘故，在全表扫描过程中会临时对所有行加锁，所以任何对 TEST_NO_IND 表更新（UPDATE/DELETE/INSERT）的事务都将和查询此表的事务产生锁冲突。

通过 session3 窗口更新 TEST 表，将 a= ’4 ’的记录更新为’ 1000 ’ ，见图 4：

图 4. 执行 UPDATE 事务的 session3 窗口

通过 session4 窗口查询 TEST 表，统计 a= ’3 ’的记录的数量，见图 5：

图 5. 执行查询事务的 session4 窗口

上述查询结果可以获得，这是因为 TEST 表的 A 列上创建了索引，查询通过索引路径访问的缘故，查询 TEST 表产生的锁（表 IS 锁，所有 a= ’ 3 ’的行 S 锁）与更新 TEST 表产生的锁（表 IX 锁，所有 a= ’ 4 ’的行 X 锁）不冲突，所以能够执行成功。

回页首

应用程序设计与并发性

数据库本质上是受客户端应用程序操纵的傀儡。客户端应用程序对服务器上获取的锁几乎有完全的控制 ( 并对锁负责 ) 。虽然 DB2 锁管理器自动使用锁保护事务，但这受客户端应用程序发出的事务类型和对结果的处理方式的直接鼓动。因此，大多数阻塞问题的解决方案都涉及检查客户端应用程 序。当来自应用程序的第一个连接控制锁而第二个连接需要相冲突的锁类型时，将发生阻塞。其结果是强制第二个连接等待，而在第一个连接上阻塞。不管是来自同 一应用程序还是另外一台客户机上单独的应用程序，一个连接都可以阻塞另一个连接。大多数阻塞问题的发生是因为一个进程控制锁的时间过长，导致阻塞的进程链 都在其它进程上等待锁。

阻塞问题常常要求检查应用程序提交的SQL 语句本身，以及应用程序行为本身。

设计应用程序以避免阻塞的准则包括：

• 不要使用或设计使用户得以填写编辑框的应用程序，编辑框会生成长时间运行的查询。例如，不要使用或设计提示用户输入的应用程序，允许某些字段保留空白或允许输入通配符。这可能导致应用程序提交运行时间过长的查询，从而导致阻塞问题。
• 不要使用或设计使用户得以在事务内输入内容的应用程序，使事务尽可能简短并尽快提交。

  用户输入内容等这些准备工作不应该包含在事务 之内，应该等准备工作完成以后再启动事务，通过尽可能晚地启动事务的第一个 SQL 语句（它启动一个事务）并使事务的更新（插入、更新和删除，这些操作要用到互斥锁）尽可能接近提交阶段，从而使事务的持续时间尽可能的短。当用户操作涉及 多个交互作用时，每个交互作用应当提交自己的事务并且应当在将活动返回给用户之前释放所有锁。

• 避免执行时间长的复杂查询。

  长时间运行的查询会阻塞其它 DML 事务，因此，一般不要将长时间运行的决策支持查询和联机事务处理操作混在一起。解决方案是想办法优化查询，如创建合适的索引、将大的复杂查询分成简单的查询或在空闲时间或单独的计算机上运行查询。　

  查询运行时间长并由此导致阻塞的另一个原因是这些查询不适当地使用游标。游标可能是在结果集中浏览的便利方法，但使用游标可能比使用面向集合的查询慢。

• 立即完成提取所有结果行。

  将查询发送到服务器后，所有应用程序必须立即完成提取所有结果行。如果应用程序没有提取所有结果行，锁可 能会留在表上较长时间而阻塞其他用户，如果应用程序迅速提取所有结果行，则锁会在尽可能短时间内释放掉，减轻阻塞问题。比如使用 CS 隔离级别的 FOR UPDATE 查询事务，应该尽快处理

• 显式控制连接管理。
• 模块对表的操作顺序应该保持一致。

  比如模块 1 对表的操作顺序依次为表 A、表 B、表 C，模块 2 对相应表的操作顺序也应该为表 A、表 B、表 C，而不应该为表 C、表 B、表 A，或者可能会产生死锁。

• 复杂的应用程序逻辑可能应该考虑在数据库服务器上实现，比如使用存储过程，避免过多的交互。
• 应用程序所占用的数据库资源情况，比如过大的更新事务可能导致联机日志被用完，这时可以考虑批量提交的方式。
• 不要返回不必要的数据，在查询时，应该尽量避免使用 SELECT * 这样的 SQL 语句，而应该返回必要的查询列表或者结果列表。
• 在所预计的并发用户全负荷下对应用程序进行压力测试。