请教两个DB2上的两个隔离/锁相关的问题

Pythagoras

For the #1 question, in spite of the diffenrent format of lock report between LUW and mainframe, I can conclude that the timeout/suspension occured on the data row(03000500010001000000000052), which is qualified by C1=11. That is, the second select query needs to lock the row, which is the real important point for this question.
In theory, C1 =22 OR C1 =33, or C1 IN (22,33) re-written by DB2 optimizer, I mean IN LIST predicate, is indexable and stage 1 (sargable), I guess it is so called index-sargable, mentioned in the previous post. In mainframe world, all indexable is sargable, but not all sargable is indexable. I believe most likely, it is still true in LUW world, because the mainframe and LUW optimizers are pretty close.
So, I assume C1 =22 OR C1 =33 is indexable and sargable, and thus not needs to scan/lock the data row(03000500010001000000000052).
Then I conclude there must be another mechanism to force DB2 to do table scan, but not shown in the explain result,  puzzled.
Now, I suggest the post owner to insert more records into the base table, for example 20 records totally, to retry this test, please. Just my guess.

[ 本帖最后由 Pythagoras 于 2009-8-6 11:25 编辑 ]

wangzhonnew

sargable index predicate means scanning all records in the index to find a match.
another two types of predicates are start/stop key predicate, and residual predicate:
    * Range delimiting predicates are those used to bracket an index scan; they provide start or stop key values for the index search. These predicates are evaluated by the index manager.
    * Index sargable predicates are not used to bracket a search, but are evaluated from the index if one is chosen, because the columns involved in the predicate are part of the index key. These predicates are also evaluated by the index manager.
    * Data sargable predicates are predicates that cannot be evaluated by the index manager, but can be evaluated by Data Management Services (DMS). Typically, these predicates require the access of individual rows from a base table. If necessary, DMS will retrieve the columns needed to evaluate the predicate, as well as any others to satisfy the columns in the SELECT list that could not be obtained from the index.
    * Residual predicates are those that require I/O beyond the simple accessing of a base table. Examples of residual predicates include those using quantified subqueries (subqueries with ANY, ALL, SOME, or IN), or reading LONG VARCHAR or large object (LOB) data that is stored separately from the table. These predicates are evaluated by Relational Data Services (RDS) and are the most expensive of the four categories of predicates.

mdkii

基本同意的unixnewbie分析，
从explain中看，db2 认为 in list 是一个 index sargable precicates。
index sargable 应该是在做index scan时就可以apply predicates以减小data page的fetch。
否则就像楼主说的那跟table scan没区别了，还多了一个index scan。

不过还是有两个疑问：
1、照unixnewbie的分析，应该是index node的lock wait，为何db2pd 显示的却是data row的lock wait。
2、对于in list的操作，db2 为何认为是index sargable predicates呢，这样不是降低效率了吗？难道是表不够大，
如果可以，请楼主把表增加到1W条记录以上，看看对in list的操作会不会改变，谢谢。



以下是ibm 对于in-list的解释（这段话是db2 for z/os里的，他这里的matching index scans类似于我们start key，stop key）：

An IN-list index scan is a special case of the matching index scan, in which a single indexable IN predicate is used as a matching equal predicate.

You can regard the IN-list index scan as a series of matching index scans with the values in the IN predicate being used for each matching index scan. The following example has an index on (C1,C2,C3,C4) and might use an IN-list index scan:

SELECT * FROM T
  WHERE C1=1 AND C2 IN (1,2,3)
    AND C3>0 AND C4<100;

The plan table shows MATCHCOLS = 3 and ACCESSTYPE = N. The IN-list scan is performed as the following three matching index scans:

(C1=1,C2=1,C3>0), (C1=1,C2=2,C3>0), (C1=1,C2=3,C3>0)

unixnewbie

原帖由 mdkii 于 6/8/2009 14:02 发表
基本同意的unixnewbie分析，
从explain中看，db2 认为 in list 是一个 index sargable precicates。
index sargable 应该是在做index scan时就可以apply predicates以减小data page的fetch。
否则就像楼主说的那跟table scan没区别了，还多了一个index scan。

不过还是有两个疑问：
1、照unixnewbie的分析，应该是index node的lock wait，为何db2pd 显示的却是data row的lock wait。
2、对于in list的操作，db2 为何认为是index sargable predicates呢，这样不是降低效率了吗？难道是表不够大，
如果可以，请楼主把表增加到1W条记录以上，看看对in list的操作会不会改变，谢谢。

对于你的疑问，我的看法
1. 我的猜想是因为DB2里LOCK所apply的对象(Lock Object Type)除了internal的一些object (比如package）外就只有Row和TABLE（对于普通的Table)，对于MDC则多了block level，对于partitioned table则多了table partition level。其没有一种类型是index key lock。所以当需要lock index key record时，因为index key record与ROW是一一对应（当然pseduo delete的key除外）的，所以锁ROW即锁了index key record。

2. DB2的optimizor算法我们无从知道。我个人focus在怎样影响DB2 optimizor方面努力。

Pythagoras

From wangzhonnew's reply, I know there are some diffenrence in respect of predicate type between LUW and mainframe. But anyway, C1 =22 OR C1 =33 is an index sargable predicate, and should be evaluated by the index manager, no data access is needed for unqualified entries.
And for mdkii's reply,
1. DB2 only locks data, not index entries, at least for DB2 z/OS. I believe same behavior for DB2 LUW.
2. Index sargable predicate is effective enough, and should be the correct choice for DB2 optimizer.

But, if too few records in the table, the optimizer will choose table scan instead of index, based on cost, and this should be shown in the explain result. Unfortunately, we didn't see table scan in the explain result  for this case.
At last, for DB2 z/OS, it is possible that index scan shown in the explain result, but real access path is table scan. I mean, DB2 optimizer originally chooses to use index access, but finally have to fall into table scan access, under some restrict conditions. For DB2 LUW, I don't whether it is possible.

I still suggest the post owner to increase the total cardinality of the table and retry. The purpose is, avoid DB2 fall into table scan, because with many more records, table scan is much more expensive than index. And let us see the result, to verify the guess.

[ 本帖最后由 Pythagoras 于 2009-8-6 15:44 编辑 ]

zhiliyang

原帖由 unixnewbie 于 2009-8-6 10:46 发表


证明DB2需要读取整个INDEX，所以肯定要“经过”你在另一进程中刚刚插入或修改的数据所对应在index中的那个record。由于DB2中的行锁只在ROW或TABLE上，所以锁ROW data也就是mapping到锁住index中对应的那个Record。

彻底Agree了。
另外用了一个hierarchy表，里面3个column：PKEY(CHAR 3), CKEY(CHAR 3), NUM(SMALL INT)
指定PKEY,CKEY为key.

插入若干数据后，在一个窗口执行
+C UPDATE HIERARCHY SET NUM = 111 WHERE PKEY = 'AAA' AND CKEY = 'BBB';

然后再在另外一个窗口执行
select pkey,ckey
from hierarchy
where         (pkey='BBB' AND  ckey ='CCC')
OR        (PKEY = 'CCC' AND CKEY = 'EEE')
成功返回。


access plan为：

1. Original Statement:
   
2. ------------------
   
3. select pkey,ckey
   
4. from hierarchy
   
5. where (pkey='BBB' AND ckey ='CCC')OR(PKEY = 'CCC' AND CKEY = 'EEE')
   
6. 
   
7. 
   
8. Optimized Statement:
   
9. -------------------
   
10. SELECT Q1.PKEY AS "PKEY", Q1.CKEY AS "CKEY"
    
11. FROM YANGLU.HIERARCHY AS Q1
    
12. WHERE (((Q1.PKEY = 'BBB') AND (Q1.CKEY = 'CCC')) OR ((Q1.PKEY = 'CCC') AND
    
13.         (Q1.CKEY = 'EEE')))
    
14. 
    
15. Access Plan:
    
16. -----------
    
17.         Total Cost:                 0.0213858
    
18.         Query Degree:                1
    
19. 
    
20.         Rows
    
21.        RETURN
    
22.        (   1)
    
23.         Cost
    
24.          I/O
    
25.          |
    
26.         1.75
    
27.        IXSCAN
    
28.        (   2)
    
29.       0.0213858
    
30.           0
    
31.          |
    
32.           7
    
33.    INDEX: SYSIBM  
    
34. SQL090721132539560
    
35. 
    
36. ....
    
37.         2) IXSCAN: (Index Scan)
    
38.                 Cumulative Total Cost:                 0.0213858
    
39.                 Cumulative CPU Cost:                 66257.4
    
40.                 Cumulative I/O Cost:                 0
    
41.                 Cumulative Re-Total Cost:         0.00994012
    
42.                 Cumulative Re-CPU Cost:         30796.4
    
43.                 Cumulative Re-I/O Cost:         0
    
44.                 Cumulative First Row Cost:         0.0169107
    
45.                 Estimated Bufferpool Buffers:         1
    
46. 
    
47.                 Arguments:
    
48.                 ---------
    
49.                 MAXPAGES: (Maximum pages for prefetch)
    
50.                         ALL
    
51.                 PREFETCH: (Type of Prefetch)
    
52.                         NONE
    
53.                 ROWLOCK : (Row Lock intent)
    
54.                         NEXT KEY SHARE
    
55.                 SCANDIR : (Scan Direction)
    
56.                         FORWARD
    
57.                 TABLOCK : (Table Lock intent)
    
58.                         INTENT SHARE
    
59. 
    
60.                 Predicates:
    
61.                 ----------
    
62.                 2) Sargable Predicate
    
63.                         Comparison Operator:                 Not Applicable
    
64.                         Subquery Input Required:         No
    
65.                         Filter Factor:                         0.0535714
    
66. 
    
67.                         Predicate Text:
    
68.                         --------------
    
69.                         (((Q1.PKEY = 'BBB') AND (Q1.CKEY = 'CCC')) OR
    
70.                                 ((Q1.PKEY = 'CCC') AND (Q1.CKEY = 'EEE')))
    
71. 
    
72. 
    
73.                 Input Streams:
    
74.                 -------------
    
75.                         1) From Object SYSIBM.SQL090721132539560
    
76. 
    
77.                                 Estimated number of rows:         7
    
78.                                 Number of columns:                 3
    
79.                                 Subquery predicate ID:                 Not Applicable
    
80. 
    
81.                                 Column Names:
    
82.                                 ------------
    
83.                                 +Q1.$RID$+Q1.CKEY+Q1.PKEY
    
84. 
    
85. 
    
86.                 Output Streams:
    
87.                 --------------
    
88.                         2) To Operator #1
    
89. 
    
90.                                 Estimated number of rows:         1.75
    
91.                                 Number of columns:                 2
    
92.                                 Subquery predicate ID:                 Not Applicable
    
93. 
    
94.                                 Column Names:
    
95.                                 ------------
    
96.                                 +Q2.CKEY+Q2.PKEY

复制代码

然而如果在第一个update的sql里面修改了PKEY/CKEY的值的话，where ... or ...这个语句的access plan里面的Sargable Predicate会导致整个index检索和对比，从而又等待了一个行锁上。

zhiliyang

这个执行计划可能更贴近最开始的情况：

1. Original Statement:
   
2. ------------------
   
3. select pkey,ckey
   
4. from hierarchy
   
5. where pkey in ('BBB','CCC','DDD')
   
6. 
   
7. 
   
8. Optimized Statement:
   
9. -------------------
   
10. SELECT Q3.PKEY AS "PKEY", Q3.CKEY AS "CKEY"
    
11. FROM YANGLU.HIERARCHY AS Q3
    
12. WHERE Q3.PKEY IN ('BBB', 'CCC', 'DDD')
    
13. 
    
14. Access Plan:
    
15. -----------
    
16.         Total Cost:                 0.0176306
    
17.         Query Degree:                1
    
18. 
    
19.         Rows
    
20.        RETURN
    
21.        (   1)
    
22.         Cost
    
23.          I/O
    
24.          |
    
25.          3.5
    
26.        IXSCAN
    
27.        (   2)
    
28.       0.0176306
    
29.           0
    
30.          |
    
31.           7
    
32.    INDEX: SYSIBM  
    
33. SQL090721132539560
    
34. 
    
35. 
    
36. .....
    
37. 
    
38.         2) IXSCAN: (Index Scan)
    
39.                 Cumulative Total Cost:                 0.0176306
    
40.                 Cumulative CPU Cost:                 54623
    
41.                 Cumulative I/O Cost:                 0
    
42.                 Cumulative Re-Total Cost:         0.00618489
    
43.                 Cumulative Re-CPU Cost:         19162
    
44.                 Cumulative Re-I/O Cost:         0
    
45.                 Cumulative First Row Cost:         0.0136769
    
46.                 Estimated Bufferpool Buffers:         1
    
47. 
    
48.                 Arguments:
    
49.                 ---------
    
50.                 MAXPAGES: (Maximum pages for prefetch)
    
51.                         ALL
    
52.                 PREFETCH: (Type of Prefetch)
    
53.                         NONE
    
54.                 ROWLOCK : (Row Lock intent)
    
55.                         NEXT KEY SHARE
    
56.                 SCANDIR : (Scan Direction)
    
57.                         FORWARD
    
58.                 TABLOCK : (Table Lock intent)
    
59.                         INTENT SHARE
    
60. 
    
61.                 Predicates:
    
62.                 ----------
    
63.                 3) Sargable Predicate
    
64.                         Comparison Operator:                 In List (IN), evaluated by binary search (list sorted at compile-time)
    
65.                         Subquery Input Required:         No
    
66.                         Filter Factor:                         0.5
    
67. 
    
68.                         Predicate Text:
    
69.                         --------------
    
70.                         Q3.PKEY IN ('BBB', 'CCC', 'DDD')
    
71. 
    
72. 
    
73.                 Input Streams:
    
74.                 -------------
    
75.                         1) From Object SYSIBM.SQL090721132539560
    
76. 
    
77.                                 Estimated number of rows:         7
    
78.                                 Number of columns:                 3
    
79.                                 Subquery predicate ID:                 Not Applicable
    
80. 
    
81.                                 Column Names:
    
82.                                 ------------
    
83.                                 +Q3.PKEY(A)+Q3.CKEY(A)+Q3.$RID$
    
84. 
    
85. 
    
86.                 Output Streams:
    
87.                 --------------
    
88.                         2) To Operator #1
    
89. 
    
90.                                 Estimated number of rows:         3.5
    
91.                                 Number of columns:                 2
    
92.                                 Subquery predicate ID:                 Not Applicable
    
93. 
    
94.                                 Column Names:
    
95.                                 ------------
    
96.                                 +Q4.PKEY(A)+Q4.CKEY(A)

复制代码

在uncommit Update的record没有修改index 所在column的时候，sargable predicate on index scan是能够正常返回而不等待锁的

mdkii

我把测试表数据加到1w后，终于见到我们想见的。

in list 操作变成了 start key， stop key。

因此，我认为 db2 选择 index total scan 是因为表里的记录太少，做index direct lookup的代价大于
index total scan。

当表里记录数增加到一定数量后，db2 就选择start key stop key （index direct lookup） 方式了。
在这种情况下，update就不在阻塞select了。
这也再次证明 unixnewbie的分析是正确的。

对于楼主的后面两个例子，我认为是不是因为没有update index key，
因此update操作没有对index node 加写锁，所以select操作可以scan index。

感谢大家的热情参与，让我学了不少东西。
包括 unixnewbie 的精彩分析，  
Pythagoras  的热情参与（虽然你是做主机的，但能够参与到开放平台的讨论当中很值得表扬）
还有楼主提供这么好的例子。。。。。

diablo2

来迟了。

只能射精了

zhiliyang

原帖由 mdkii 于 2009-8-4 17:56 发表
对于问题一，牛博士的书里有很好的解释。
DB2默认会加锁是在apply predicate 之前。
如果你要改变这个行为，请使用注册变量
DB2_EVALUNCOMMITTED

mdkii和unixnewbie随便说两句话偶都得google上一整子，加翻半天书。
果然是牛13啊。

刚刚按两位的推荐验证了牛博士书上的DB2_EVALUNCOMMITTED，DB2_SKIPDELETED， DB2_SKIPINSERTED三个变量的作用。
叹服叹服。


To：Pythagoras
老兄慢走啊，偶也是跑在大机上的。
回头还得多多请教了