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

zhiliyang

To mdkii ：
加锁不是问题，我奇怪的是在有index的情况下，select * from deadtable where c1=2 or c1=3的时候，为什么会锁等待在其他记录行上。
（在执行这个select以前，在另外一个线程里面提交了一个没有commit的update c1=4 where c1 = 44）

感觉这个select是table scan的，但是从plan上面来看，确实是使用了index的啊

zhiliyang

刚刚再做了一次检查，报告如下：
执行完  +C UPDATE DEADTABLE SET C1 = 4 WHERE C1 = 44 后的锁情况

1. C:\Program Files\IBM\SQLLIB\BIN>
   
2. C:\Program Files\IBM\SQLLIB\BIN>DB2PD -D ROMMEL_T -LOCKS
   
3. Database Partition 0 -- Database ROMMEL_T -- Active -- Up 0 days 00:09:06
   
4. Locks:
   
5. Address    TranHdl    Lockname                   Type       Mode Sts Owner Dur HoldCount  Att  ReleaseFlg
   
6. 0x7F8B0B40 2          03000500010001000000000052 Row        ..X  G   2     1   0          0x20 0x40000000
   
7. 0x7F8B08A0 2          53514C4332473133B7F3CE3241 Internal P ..S  G   2     1   0          0x00 0x40000000
   
8. 0x7F8B0900 2          03000500000000000000000054 Table      .IX  G   2     1   0          0x00 0x40000000
   
9. C:\Program Files\IBM\SQLLIB\BIN>

复制代码

执行完 +C SELECT * FROM DEADTABLE WHERE C1 = 2 OR C1 = 3 后的锁情形

1. 
   
2. C:\Program Files\IBM\SQLLIB\BIN>
   
3. C:\Program Files\IBM\SQLLIB\BIN>DB2PD -D ROMMEL_T -LOCKS
   
4. Database Partition 0 -- Database ROMMEL_T -- Active -- Up 0 days 00:09:49
   
5. Locks:
   
6. Address    TranHdl    Lockname                   Type       Mode Sts Owner Dur HoldCount  Att  ReleaseFlg
   
7. 0x7F8B1960 8          53514C4445464C5428DD630641 Internal P ..S  G   8     1   0          0x00 0x40000000
   
8. 0x7F8B1870 8          010000000100000001009E0056 Internal V ..S  G   8     1   0          0x00 0x40000000
   
9. 0x7F8B0B40 2          03000500010001000000000052 Row        ..X  G   2     1   0          0x20 0x40000000
   
10. 0x7F8B18D0 8          03000500010001000000000052 Row        .NS  W   0     1   0          0x00 0x00000001
    
11. 0x7F8B08A0 2          53514C4332473133B7F3CE3241 Internal P ..S  G   2     1   0          0x00 0x40000000
    
12. 0x7F8B1930 8          53514C4332473133B7F3CE3241 Internal P ..S  G   8     1   0          0x00 0x40000000
    
13. 0x7F8B0900 2          03000500000000000000000054 Table      .IX  G   2     1   0          0x00 0x40000000
    
14. 0x7F8B1900 8          03000500000000000000000054 Table      .IS  G   8     1   0          0x00 0x00000001
    
15. C:\Program Files\IBM\SQLLIB\BIN>

复制代码

[ 本帖最后由 zhiliyang 于 2009-8-5 14:26 编辑 ]

biti.vector

-locks 可以加showlocks选项， 看起来更清楚一些。

biti.vector

原帖由 zhiliyang 于 2009-8-5 13:19 发表
To mdkii ：
加锁不是问题，我奇怪的是在有index的情况下，select * from deadtable where c1=2 or c1=3的时候，为什么会锁等待在其他记录行上。
（在执行这个select以前，在另外一个线程里面提交了一个没有commit的update c1=4 where c1 = 44）

感觉这个select是table scan的，但是从plan上面来看，确实是使用了index的啊

有可能锁在index上。

zhiliyang

下面的第一个db2pd是在+c update后就做的，第二个是在select后做的。
锁等待还是发生在Update的那一行上。

1. C:\Program Files\IBM\SQLLIB\BIN>
   
2. C:\Program Files\IBM\SQLLIB\BIN>db2 +c update deadtable set c1 = 4 where c1 = 44
   
3. 
   
4. DB20000I  The SQL command completed successfully.
   
5. 
   
6. C:\Program Files\IBM\SQLLIB\BIN>
   
7. C:\Program Files\IBM\SQLLIB\BIN>
   
8. C:\Program Files\IBM\SQLLIB\BIN>db2pd -d rommel_t -locks showlocks
   
9. 
   
10. Database Partition 0 -- Database ROMMEL_T -- Active -- Up 0 days 03:29:11
    
11. 
    
12. Locks:
    
13. Address    TranHdl    Lockname                   Type       Mode Sts Owner
    
14. Dur HoldCount  Att  ReleaseFlg
    
15. 0x7F8B2540 3          03000500010001000000000052 Row        ..X  G   3
    
16. 1   0          0x20 0x40000000  TbspaceID 3     TableID 5      PartitionID 0 Pag
    
17. e 1 Slot 1
    
18. 0x7F8B2510 3          53514C4332473133B7F3CE3241 Internal P ..S  G   3
    
19. 1   0          0x00 0x40000000  Pkg UniqueID 434c5153  33314732 Name 32cef3b7 Lo
    
20. ading = 0
    
21. 0x7F8B1AE0 3          03000500000000000000000054 Table      .IX  G   3
    
22. 1   0          0x00 0x40000000  TbspaceID 3     TableID 5
    
23. 
    
24. C:\Program Files\IBM\SQLLIB\BIN>
    
25. C:\Program Files\IBM\SQLLIB\BIN>
    
26. C:\Program Files\IBM\SQLLIB\BIN>db2pd -d rommel_t -locks showlocks
    
27. 
    
28. Database Partition 0 -- Database ROMMEL_T -- Active -- Up 0 days 03:30:07
    
29. 
    
30. Locks:
    
31. Address    TranHdl    Lockname                   Type       Mode Sts Owner
    
32. Dur HoldCount  Att  ReleaseFlg
    
33. 0x7F8B0990 2          03000000010000000100D20056 Internal V ..S  G   2
    
34. 1   0          0x00 0x40000000  Anchor 210 Stmt 3 Env 1 Var 1 Loading 0
    
35. 0x7F8B2540 3          03000500010001000000000052 Row        ..X  G   3
    
36. 1   0          0x20 0x40000000  TbspaceID 3     TableID 5      PartitionID 0 Pag
    
37. e 1 Slot 1
    
38. 0x7F8B1A20 2          03000500010001000000000052 Row        .NS  W   3
    
39. 1   0          0x00 0x00000001  TbspaceID 3     TableID 5      PartitionID 0 Pag
    
40. e 1 Slot 1
    
41. 0x7F8B2510 3          53514C4332473133B7F3CE3241 Internal P ..S  G   3
    
42. 1   0          0x00 0x40000000  Pkg UniqueID 434c5153  33314732 Name 32cef3b7 Lo
    
43. ading = 0
    
44. 0x7F8B0CC0 2          53514C4332473133B7F3CE3241 Internal P ..S  G   2
    
45. 1   0          0x00 0x40000000  Pkg UniqueID 434c5153  33314732 Name 32cef3b7 Lo
    
46. ading = 0
    
47. 0x7F8B1AE0 3          03000500000000000000000054 Table      .IX  G   3
    
48. 1   0          0x00 0x40000000  TbspaceID 3     TableID 5
    
49. 0x7F8B09C0 2          03000500000000000000000054 Table      .IS  G   2
    
50. 1   0          0x00 0x00000001  TbspaceID 3     TableID 5
    
51. 
    
52. C:\Program Files\IBM\SQLLIB\BIN>
    
53. C:\Program Files\IBM\SQLLIB\BIN>
    
54. C:\Program Files\IBM\SQLLIB\BIN>db2 "select substr(tabschema,1,9) as tabschema,
    
55. substr(tabname,1,12) as tabname, tableid,tbspaceid from syscat.tables where tbsp
    
56. aceid = 3 and tableid = 5"
    
57. 
    
58. TABSCHEMA TABNAME      TABLEID TBSPACEID
    
59. --------- ------------ ------- ---------
    
60. YANGLU    DEADTABLE          5         3
    
61. 
    
62.   1 record(s) selected.
    
63. 
    
64. 
    
65. C:\Program Files\IBM\SQLLIB\BIN>
    

复制代码

执行的sql: select * from deadtable where c1 =2 or c1 =3

[ 本帖最后由 zhiliyang 于 2009-8-5 17:38 编辑 ]

biti.vector

原帖由 zhiliyang 于 2009-8-5 13:14 发表

...

To biti.vector:
Are u sure? 第一次听见这个说法。
而且就从上面的说明看，in也是属于最慢的存取方式啊。

I'm pretty sure, It's done in DB2 optimizer's rewrite step.

Just assume DB2 doesn't rewrite it this way,  DB2 will need to get RIDs for predicate [c1=2], and then get RIDs for predicate [c1=3], and then "OR"ing these RIDs, and finally get records out using these RIDs.

For the statement that query could be slow using "in", "any", "some", it's not the same case as yours.  Your case is in a list, not in a sub-query, that makes a great difference.

[ 本帖最后由 biti.vector 于 2009-8-5 20:51 编辑 ]

unixnewbie

原帖由 zhiliyang 于 5/8/2009 11:58 发表
兄弟在此先谢一个先。

下面是explain的结果：
DB2 Universal Database Version 9.5, 5622-044 (c) Copyright IBM Corp. 1991, 2007
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Original Statement:
------------------
select *
from deadtable
where c1=2 or c1 = 3


Optimized Statement:
-------------------
SELECT Q3.C1 AS "C1"
FROM YANGLU.DEADTABLE AS Q3
WHERE Q3.C1 IN (2, 3)

Access Plan:
-----------
        Total Cost:                 0.0160821
        Query Degree:                1

      Rows
     RETURN
     (   1)
      Cost
       I/O
       |
        2
     IXSCAN
     (   2)
    0.0160821
        0
       |
        5
INDEX: YANGLU  
      INDX1






        2) IXSCAN: (Index Scan)
                Cumulative Total Cost:                 0.0160821
                Cumulative CPU Cost:                 49825.5
                Cumulative I/O Cost:                 0
                Cumulative Re-Total Cost:         0.00463641
                Cumulative Re-CPU Cost:         14364.5
                Cumulative Re-I/O Cost:         0
                Cumulative First Row Cost:         0.0139097
                Estimated Bufferpool Buffers:         1

                Arguments:
                ---------
                MAXPAGES: (Maximum pages for prefetch)
                        ALL
                PREFETCH: (Type of Prefetch)
                        NONE
                ROWLOCK : (Row Lock intent)
                        NEXT KEY SHARE
                SCANDIR : (Scan Direction)
                        FORWARD
                TABLOCK : (Table Lock intent)
                        INTENT SHARE

                Predicates:
                ----------
                3) Sargable Predicate
                        Comparison Operator:                 In List (IN), evaluated by binary search (list sorted at compile-time)
                        Subquery Input Required:         No
                        Filter Factor:                         0.4

                        Predicate Text:
                        --------------
                        Q3.C1 IN (2, 3)



查阅部分资料后觉得可能是红色的那部分的问题。
难道它是不是就是所谓的Residual Predicates？




另外，or怎么就被解释成了In了？  哎，看来偶还是很小白的。
继续查东西去吧

你的这个explain和后面的db2pd -lock进一步印证了我的猜想。
我的猜想如下：
1. 在进程A，你update的是所有C1 = 11的rows
2. 当你分别搜索C1 =22和C1 =33时，DB2 均使用了index scan (but with start/stop keys)。 所以跟C1=11完全没有关系，所以没有被block。
3. 从以上Explain可以看到DB2使用的是index scan with sargable predicate。意思就是DB2 需要搜索整个index来测试这个in 条件，所以必然“经过”C1=11的key item。所以在缺省情况下，这个进程必须等待进程A提交。除非使用DB2_EVALUNCOMMITTED来改变这个behaviour。
4. 至于这个in在这里是否属于residual，我不认为是。一来explain中没有明确说这个是residual，二来infor center中所指的residual可能会在In 中用到这种情况是比如 C1 in (select xxx from xxx where xxxxxx）。即in测试的是另一个fullselet的情况。

zhiliyang

谢谢楼上两位。
在下面这个MM的文章里面也有一个关于index scan的问题，然后有点点说明，但是偶不太确定是否理解正确
（http://www.ibm.com/developerworks/data/library/techarticle/dm-0511bond/index.html?S_TACT=105AGX52&S_CMP=cn-a-db2）
...an index-sargable predicate (that is, it cannot be applied as a start/stop key on the index scan).
...
Without an index sarg, an index scan will use the index to access the data, but will return every key in the index.
...
Because the predicate on the main table cannot be applied to the index, we have to read all the rows from the table, using the index, and then apply the predicate before passing the qualifying rows up to the join.
And as before, we have to lock the rows before we can evaluate the predicate.

这个意思是不是如果IXSCAN的时候，没有start/stop key的话，那么db2实际上只是用index去访问表中每一条记录，然后再使用predicate来得到结果集。
而不是在对index使用predicate，用得到的在index上的一个结果集，去访问db，读取对应的数据。

另外，如果是这样的话，那么是不是这个IXSCAN实际上相当于退化成了TABLE SCAN（特别是对于一个tablespace里面只有一个table的情形）

Pythagoras

I'm a mainframe DB2 DBA, but interested in this post.
For the #2 question, no doubt, mainframe DB2 will block the UPDATE till the SELECT committed, because S locks granted on the qualified pages, and not compatible with X lock. The recommendation is to use WITH UR for the SELECT COUNT query if you can tolerate dirty read, and as always, increase COMMIT frequence to reduce lock timeout.

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

unixnewbie

原帖由 zhiliyang 于 6/8/2009 11:57 发表
谢谢楼上两位。
在下面这个MM的文章里面也有一个关于index scan的问题，然后有点点说明，但是偶不太确定是否理解正确
（http://www.ibm.com/developerwork ... &S_CMP=cn-a-db2）
...an index-sargable predicate (that is, it cannot be applied as a start/stop key on the index scan).
...
Without an index sarg, an index scan will use the index to access the data, but will return every key in the index.
...
Because the predicate on the main table cannot be applied to the index, we have to read all the rows from the table, using the index, and then apply the predicate before passing the qualifying rows up to the join.
And as before, we have to lock the rows before we can evaluate the predicate.

这个意思是不是如果IXSCAN的时候，没有start/stop key的话，那么db2实际上只是用index去访问表中每一条记录，然后再使用predicate来得到结果集。
而不是在对index使用predicate，用得到的在index上的一个结果集，去访问db，读取对应的数据。

另外，如果是这样的话，那么是不是这个IXSCAN实际上相当于退化成了TABLE SCAN（特别是对于一个tablespace里面只有一个table的情形）

我看了上面文章里的例子。如果你直白地去理解‘Because the predicate on the main table cannot be applied to the index, we have to read all the rows from the table, using the index, and then apply the predicate before passing the qualifying rows up to the join.
And as before, we have to lock the rows before we can evaluate the predicate’，那我认为他这个说明是错的。

因为很显眼plan中只是去scan整个main table的primary index，因为index中已经包括所有需要的数据，所以这是index only scan。肯定不需要读index再读data。所以你说的“退化”并不存在。

另外他这个例子跟你碰到的情况相似，因为都只是用Sargable predict, i.e.

      Predicates:
      ----------
      2) Sargable Predicate
         Relational Operator:       Equal (=)
         Subquery Input Required:   No
         Filter Factor:          0.2

         Predicate Text:
         --------------
         (Q2.MAIN_DATA_COLUMN = 'deadlock 1')

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

文章作者的表述最多只能理解成这种映射锁DATA行和对应index中record的关系。如果说DB2回头还需要读再读一次TABLE中数据，那这种理解肯定是错误的。