一个锁案例，求诊断过程和思路

gtlions

如题，偶尔遇到这样的案例，但是诊断和排查思路一直不是很明晰，求ML和各位大拿指点。
Global Enqueue Services Deadlock detected. More info in file
/opt/oracle/product/admin/dbsid/bdump/portal1_lmd0_11173.trc.
附件是告警日志。环境是hpunix+10gr2rac。

gtlions

多谢ML了，还是的继续多阅读啊，不然被trc闷了。

gtlions

嗯，虽然我在看了ddl之后也推测是这个原因，但是如何从trc信息中获得关键的指示呢？

Maclean Liu(刘相兵

create bitmap index IDX_RBT_GIFT_ACTIVE_LOTTERY on T_RBT_GIFT_ACTIVE (LOTTERY)
  tablespace RBTIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create index IDX_RBT_GIFT_ACTIVE_PH on T_RBT_GIFT_ACTIVE (PHONE_NUMBER)
  tablespace RBTIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
create bitmap index IDX_RBT_GIFT_ACTIVE_TYPE on T_RBT_GIFT_ACTIVE (TYPE)
  tablespace RBTIDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );



应当是由  BITMAP INDEX引起的  INSERT锁问题造成了出现死锁， 尝试在测试环境中 DROP这些INDEX 并重复以测试

gtlions

貌似是滥用的bitmap的结果吧~？

gtlions

1. SQL> Select kind,Count(*) From  T_RBT_GIFT_ACTIVE Group By kind;
   
2. 
   
3. KIND   COUNT(*)
   
4. ---- ----------
   
5.    1        608
   
6.    2    1072480
   
7.    3     169356
   
8.    4     172633
   
9. SQL> Select LOTTERY,Count(*) From  T_RBT_GIFT_ACTIVE Group By LOTTERY;
   
10. 
    
11. LOTTERY   COUNT(*)
    
12. ------- ----------
    
13.      -7          1
    
14.      -4          1
    
15.      -1         31
    
16.       1     191485
    
17.       2     240403
    
18.       3      85183
    
19.      -3          5
    
20.      -2          3
    
21.      -6          1
    
22.       0     898001
    
23. 
    
24. 10 rows selected
    
25. SQL> Select TYPE,Count(*) From  T_RBT_GIFT_ACTIVE Group By TYPE;
    
26. 
    
27. TYPE   COUNT(*)
    
28. ---- ----------
    
29.    5     168913
    
30.    1     710188
    
31.    2     531469
    
32.    3       4067
    
33.    4        503
    

复制代码

gtlions

1. -- Create table
   
2. create table T_RBT_GIFT_ACTIVE
   
3. (
   
4.   id           NUMBER(10) not null,
   
5.   phone_number VARCHAR2(20) not null,
   
6.   content      VARCHAR2(80),
   
7.   kind         NUMBER(2) not null,
   
8.   type         NUMBER(2) not null,
   
9.   lottery      NUMBER(2) not null,
   
10.   update_date  DATE not null,
    
11.   create_date  DATE not null
    
12. )
    
13. tablespace RBT
    
14.   pctfree 10
    
15.   initrans 1
    
16.   maxtrans 255
    
17.   storage
    
18.   (
    
19.     initial 64K
    
20.     minextents 1
    
21.     maxextents unlimited
    
22.   );
    
23. 
    
24. -- Create/Recreate primary, unique and foreign key constraints
    
25. alter table T_RBT_GIFT_ACTIVE
    
26.   add constraint PK_RBT_GIFT_ACTIVE primary key (ID)
    
27.   using index
    
28.   tablespace RBTIDX
    
29.   pctfree 10
    
30.   initrans 2
    
31.   maxtrans 255
    
32.   storage
    
33.   (
    
34.     initial 64K
    
35.     minextents 1
    
36.     maxextents unlimited
    
37.   );
    
38. -- Create/Recreate indexes
    
39. create index IDX_RBT_GIFT_ACTIVE_CDATE on T_RBT_GIFT_ACTIVE (CREATE_DATE)
    
40.   tablespace RBTIDX
    
41.   pctfree 10
    
42.   initrans 2
    
43.   maxtrans 255
    
44.   storage
    
45.   (
    
46.     initial 64K
    
47.     minextents 1
    
48.     maxextents unlimited
    
49.   );
    
50. create bitmap index IDX_RBT_GIFT_ACTIVE_KIND on T_RBT_GIFT_ACTIVE (KIND)
    
51.   tablespace RBTIDX
    
52.   pctfree 10
    
53.   initrans 2
    
54.   maxtrans 255
    
55.   storage
    
56.   (
    
57.     initial 64K
    
58.     minextents 1
    
59.     maxextents unlimited
    
60.   );
    
61. create bitmap index IDX_RBT_GIFT_ACTIVE_LOTTERY on T_RBT_GIFT_ACTIVE (LOTTERY)
    
62.   tablespace RBTIDX
    
63.   pctfree 10
    
64.   initrans 2
    
65.   maxtrans 255
    
66.   storage
    
67.   (
    
68.     initial 64K
    
69.     minextents 1
    
70.     maxextents unlimited
    
71.   );
    
72. create index IDX_RBT_GIFT_ACTIVE_PH on T_RBT_GIFT_ACTIVE (PHONE_NUMBER)
    
73.   tablespace RBTIDX
    
74.   pctfree 10
    
75.   initrans 2
    
76.   maxtrans 255
    
77.   storage
    
78.   (
    
79.     initial 64K
    
80.     minextents 1
    
81.     maxextents unlimited
    
82.   );
    
83. create bitmap index IDX_RBT_GIFT_ACTIVE_TYPE on T_RBT_GIFT_ACTIVE (TYPE)
    
84.   tablespace RBTIDX
    
85.   pctfree 10
    
86.   initrans 2
    
87.   maxtrans 255
    
88.   storage
    
89.   (
    
90.     initial 64K
    
91.     minextents 1
    
92.     maxextents unlimited
    
93.   );

复制代码

Maclean Liu(刘相兵

user session for deadlock lock 0xc00000023c8ad080
  pid=136 serial=4551 audsid=30604716 user: 59/RBT
  O/S info: user: root, term: unknown, ospid: 1234, machine: rbt108
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  INSERT INTO T_RBT_GIFT_ACTIVE (ID, PHONE_NUMBER, CONTENT, KIND, TYPE, LOTTERY, UPDATE_DATE, CREATE_DATE) VALUES (:B4 , :B3 , :B2 , 3, 2, 0, :B1 , :B1 )
user session for deadlock lock 0xc00000023c8ad9c8
  pid=138 serial=5628 audsid=30604596 user: 59/RBT
  O/S info: user: root, term: unknown, ospid: 1234, machine: rbt108
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  INSERT INTO T_RBT_GIFT_ACTIVE (ID, PHONE_NUMBER, CONTENT, KIND, TYPE, LOTTERY, UPDATE_DATE, CREATE_DATE) VALUES (:B5 , :B4 , :B3 , 4, 2, :B2 , :B1 , :B1 )
user session for deadlock lock 0xc00000023c8ae058
  pid=138 serial=5628 audsid=30604596 user: 59/RBT
  O/S info: user: root, term: unknown, ospid: 1234, machine: rbt108
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  INSERT INTO T_RBT_GIFT_ACTIVE (ID, PHONE_NUMBER, CONTENT, KIND, TYPE, LOTTERY, UPDATE_DATE, CREATE_DATE) VALUES (:B5 , :B4 , :B3 , 4, 2, :B2 , :B1 , :B1 )
user session for deadlock lock 0xc00000023c8ad878
  pid=136 serial=4551 audsid=30604716 user: 59/RBT
  O/S info: user: root, term: unknown, ospid: 1234, machine: rbt108
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  INSERT INTO T_RBT_GIFT_ACTIVE (ID, PHONE_NUMBER, CONTENT, KIND, TYPE, LOTTERY, UPDATE_DATE, CREATE_DATE) VALUES (:B4 , :B3 , :B2 , 3, 2, 0, :B1 , :B1 )



请列出  T_RBT_GIFT_ACTIVE  表和其所有索引的DDL语句