Global Enqueue Services Deadlock detected

qhd2004

今天接到通知一个rac环境有问题，上去检查发现是ora-01555和ges deadlock，下面是运行环境和alert、trc，望刘大帮忙指点下。
操作系统：[oracle@oracle1 bdump]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.2 (Tikanga)

数据库版本：
[oracle@oracle1 bdump]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 29 12:34:52 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

环境为rac
[oracle@oracle1 bdump]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host        
----------------------------------------------------------------------
ora.moe.db     application    0/0    0/1    ONLINE    ONLINE    oracle1     
ora....e1.inst application    0/5    0/0    ONLINE    ONLINE    oracle1     
ora....e2.inst application    0/5    0/0    ONLINE    ONLINE    oracle2     
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    oracle1     
ora....E1.lsnr application    0/5    0/0    ONLINE    ONLINE    oracle1     
ora....le1.gsd application    0/5    0/0    ONLINE    ONLINE    oracle1     
ora....le1.ons application    0/3    0/0    ONLINE    ONLINE    oracle1     
ora....le1.vip application    0/0    0/0    ONLINE    ONLINE    oracle1     
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    oracle2     
ora....E2.lsnr application    0/5    0/0    ONLINE    ONLINE    oracle2     
ora....le2.gsd application    0/5    0/0    ONLINE    ONLINE    oracle2     
ora....le2.ons application    0/3    0/0    ONLINE    ONLINE    oracle2     
ora....le2.vip application    0/0    0/0    ONLINE    ONLINE    oracle2

附件是trace和alert，如果需要其他信息我再上传。

Maclean Liu(刘相兵

ODM DATA:

*** 2012-05-29 12:11:01.027
user session for deadlock lock 0x44b0ce8d8
  pid=71 serial=8228 audsid=3310853 user: 66/MOE
  O/S info: user: moe, term: unknown, ospid: 1234, machine: scschoolhouse3
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  DELETE FROM YW_SJLRJZQKHZ_TB_TMP NOLOGGING
ENQUEUE DUMP REQUEST: from 1.15934 on [0x150021][0x2d70b],[TX] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x150021][0x2d70b],[TX]


引起 deadlock的语句之一是  DELETE FROM YW_SJLRJZQKHZ_TB_TMP NOLOGGING

该语句意味 全表删除YW_SJLRJZQKHZ_TB_TMP ， 这里 nologging对delete是无效的 ， nologging会被当做alias


----------resource 0x0x45e2d6db8----------------------
resname       : [0x150021][0x2d70b],[TX]
Local node    : 0
dir_node      : 0
master_node   : 0
hv idx        : 0
hv last r.inc : 4
current inc   : 4
hv status     : 0
hv master     : 0
open options  : dd
grant_bits    : KJUSERNL KJUSEREX
grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX
count         : 1         0         0         0         0         1
val_state     : KJUSERVS_NOVALUE
valblk        : 0x00000000000000000000000000000000 .
access_node   : 0
vbreq_state   : 0
state         : x0
resp          : 0x45e2d6db8
On Scan_q?    : N
Total accesses: 48
Imm.  accesses: 40
Granted_locks : 1
Cvting_locks  : 1
value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 0x42db606a8 gl KJUSEREX rp 0x45e2d6db8 [0x150021][0x2d70b],[TX]
  master 0 gl owner 0x457d9a158 possible pid 8634 xid 40000-0001-000001E9 bast 0 rseq 5 mseq 0 history 0x4977d495
  open opt KJUSERDEADLOCK  
CONVERT_Q:
lp 0x42db607f8 gl KJUSERNL rl KJUSEREX rp 0x45e2d6db8 [0x150021][0x2d70b],[TX]
  master 0 owner 1  bast 1 rseq 5 mseq 0x40001 history 0xd497adaa
  convert opt KJUSERGETVALUE  
----------enqueue 0x0x42db606a8------------------------
lock version     : 27
Owner node       : 0
grant_level      : KJUSEREX
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : (nil)
resp             : 0x45e2d6db8
procp            : 0x44f188c08
pid              : 16806
proc version     : 0
oprocp           : (nil)
opid             : 0
group lock owner : 0x457d9a158
possible pid     : 8634
xid              : 40000-0001-000001E9
dd_time          : 0.0 secs
dd_count         : 0
timeout          : 0.0 secs
On_timer_q?      : N
On_dd_q?         : N
lock_state       : GRANTED
Open Options     : KJUSERDEADLOCK
Convert options  : KJUSERNOQUEUE
History          : 0x4977d495
Msg_Seq          : 0x0
res_seq          : 5
valblk           : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
  possible owner[64.8634] on resource TX-00150021-0002D70B
Submitting asynchronized dump request [28]
----------enqueue 0x0x42db607f8------------------------
lock version     : 25
Owner node       : 1
grant_level      : KJUSERNL
req_level        : KJUSEREX
bast_level       : KJUSERNL
notify_func      : 0xd55e98
resp             : 0x45e2d6db8
procp            : 0x44f191728
pid              : 0
proc version     : 0
oprocp           : (nil)
opid             : 0
group lock owner : (nil)
xid              : 0000-0000-00000000
dd_time          : 0.0 secs
dd_count         : 0
timeout          : 0.0 secs
On_timer_q?      : N
On_dd_q?         : N
lock_state       : GRANTED
Open Options     :  KJUSERNO_XID
Convert options  : KJUSERGETVALUE
History          : 0xd497adaa
Msg_Seq          : 0x40001
res_seq          : 5
valblk           : 0x00000000000000000000000000000000 .
user session for deadlock lock 0x42db606a8
  pid=64 serial=48799 audsid=3310727 user: 66/MOE
  O/S info: user: moe, term: unknown, ospid: 1234, machine: scschoolhouse3
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  
                  UPDATE (
                         SELECT /*+ BYPASS_UJVC */ A1,T.N
                         FROM YW_SJLRJZQKHZ_TB_TMP YW
                         LEFT OUTER JOIN (SELECT CASE WHEN REGION_CODE_XIAN IS NOT NULL AND REGION_CODE_SHI IS NOT NULL AND REGION_CODE_SHENG IS NOT NULL THEN REGION_CODE_XIAN
                                  WHEN REGION_CODE_XIAN IS NULL AND REGION_CODE_SHI IS NOT NULL AND REGION_CODE_SHENG IS NOT NULL THEN REGION_CODE_SHI
                                  WHEN REGION_CODE_XIAN IS NULL AND REGION_CODE_SHI IS NULL AND REGION_CODE_SHENG IS NOT NULL THEN REGION_CODE_SHENG
                             END REGIONCODE,
                             CASE WHEN REGION_CODE_XIAN=REGION_CODE_SHI THEN 0 else 1 END status,
                             COUNT(1) N  FROM
                      XX_JBXX,VIEW_REGIN WHERE XX_JBXX.ZGJYBMQH=VIEW_REGIN.REGION_CODE_XIAN  and XX_JBXX.SFSFXQ='0' AND NOT EXISTS(SELECT 1 FROM SYS_DELETE_DATA D WHERE D.TABLENAME='XX_JBXX' AND D.RECORD_ID=XX_JBXX.ID)  and XX_JBXX.ZGJYBMQH LIKE '51%' group by rollup(VIEW_REGIN.REGION_CODE_SHENG,VIEW_REGIN.REGION_CODE_SHI,VIEW_REGIN.REGION_CODE_XIAN) ) T ON T.REGIONCODE=YW.XZQH AND T.STATUS='1'
                         WHERE PCID='C123194174C18151E040A8C00ABF21BA' AND TJFSDM='1'
                  ) NOLOGGING
                  SET A1=NVL(N,0)
         
user session for deadlock lock 0x454e199e0
  pid=64 serial=48799 audsid=3310727 user: 66/MOE
  O/S info: user: moe, term: unknown, ospid: 1234, machine: scschoolhouse3
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  
                  UPDATE (
                         SELECT /*+ BYPASS_UJVC */ A1,T.N
                         FROM YW_SJLRJZQKHZ_TB_TMP YW
                         LEFT OUTER JOIN (SELECT CASE WHEN REGION_CODE_XIAN IS NOT NULL AND REGION_CODE_SHI IS NOT NULL AND REGION_CODE_SHENG IS NOT NULL THEN REGION_CODE_XIAN
                                  WHEN REGION_CODE_XIAN IS NULL AND REGION_CODE_SHI IS NOT NULL AND REGION_CODE_SHENG IS NOT NULL THEN REGION_CODE_SHI
                                  WHEN REGION_CODE_XIAN IS NULL AND REGION_CODE_SHI IS NULL AND REGION_CODE_SHENG IS NOT NULL THEN REGION_CODE_SHENG
                             END REGIONCODE,
                             CASE WHEN REGION_CODE_XIAN=REGION_CODE_SHI THEN 0 else 1 END status,
                             COUNT(1) N  FROM
                      XX_JBXX,VIEW_REGIN WHERE XX_JBXX.ZGJYBMQH=VIEW_REGIN.REGION_CODE_XIAN  and XX_JBXX.SFSFXQ='0' AND NOT EXISTS(SELECT 1 FROM SYS_DELETE_DATA D WHERE D.TABLENAME='XX_JBXX' AND D.RECORD_ID=XX_JBXX.ID)  and XX_JBXX.ZGJYBMQH LIKE '51%' group by rollup(VIEW_REGIN.REGION_CODE_SHENG,VIEW_REGIN.REGION_CODE_SHI,VIEW_REGIN.REGION_CODE_XIAN) ) T ON T.REGIONCODE=YW.XZQH AND T.STATUS='1'
                         WHERE PCID='C123194174C18151E040A8C00ABF21BA' AND TJFSDM='1'
                  ) NOLOGGING
                  SET A1=NVL(N,0)



这个语句也用了nologging ，nologging 对update是无效的
         
user session for deadlock lock 0x44b0ce8d8
  pid=71 serial=8228 audsid=3310853 user: 66/MOE
  O/S info: user: moe, term: unknown, ospid: 1234, machine: scschoolhouse3
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  DELETE FROM YW_SJLRJZQKHZ_TB_TMP NOLOGGING

[0xa000e][0x157a9e],[TX]
holder 0x44f188c08       KJUSEREX  PID=16806  node 0
blocked 0x44f191728    KJUSEREX  PID=0         node1

[0x140025][0xa4de4],[TX]
requstor  0x44f190840  KJUSEREX  PID=23286   node 0

  possible owner[59.23286] on resource TX-00140025-000A4DE4


建议：

nologging对update、delete操作无效， delete全表会持续很长时间 回滚时仍会持有锁，这很容易造成dead lock，如果你要全表删除大表 那么考虑使用Truncate ，但是注意在空闲时段truncate。