Oracle Bitmap索引导致的死锁

3legcat

项目中遇到一个死锁问题，一下是trace文件内容:

DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0030002b-00001695        76     138     X             83     369           S
TX-0006002e-00009522        83     369     X             76     138           S

session 138: DID 0001-004C-000003EC     session 369: DID 0001-0053-00000400
session 369: DID 0001-0053-00000400     session 138: DID 0001-004C-000003EC

Rows waited on:
  Session 138: obj - rowid = 0001A6BF - AAAaa/AAAAAAAAAAAA
  (dictionary objn - 108223, file - 0, block - 0, slot - 0)
  Session 369: obj - rowid = 0001A6BF - AAAaa/AAAAAAAAAAAA
  (dictionary objn - 108223, file - 0, block - 0, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 369:
  sid: 369 ser: 4463 audsid: 8732205 user: 2346/RSW
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40008) -/-
  pid: 83 O/S info: user: oracle, term: UNKNOWN, ospid: 16436
    image: oracle@svdg0061
  client details:
    O/S info: user: ccms, term: , ospid: 16393
    machine: svdg0061 program: sqlplus@svdg0061 (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  DELETE RSW_VALUATION WHERE BAT_ID = :"SYS_B_0"

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=0pdxtvt3mk8cn) -----
UPDATE RSW_VALUATION A SET A.TIP_FLG = :"SYS_B_0" WHERE A.TIP_FLG <> :"SYS_B_1" AND ( a.CLNT_ID,a.SEC_ID,a.POS_TYPE,a.SEC_ID_TYPE,a.LOT_NUM,a.PRTFO_ID,a.AF_DT
, A.BAT_ID ) IN ( SELECT b.CLNT_ID,b.SEC_ID,b.POS_TYPE,b.SEC_ID_TYPE,b.LOT_NUM,b.PRTFO_ID,b.AF_DT, MAX(B.BAT_ID) FROM RSW_VALUATION B  WHERE B.RKSI_ID = :"SYS
_B_2" GROUP BY b.CLNT_ID,b.SEC_ID,b.POS_TYPE,b.SEC_ID_TYPE,b.LOT_NUM,b.PRTFO_ID,b.AF_DT )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
39be4e4d0       285  package body RSW.RSW_PKG_INB_ROLLBACK
3eaa47f98         1  anonymous block
3fb32fc70       525  package body RSW.RSW_PKG_FRAMEWORK
3fb32fc70       409  package body RSW.RSW_PKG_FRAMEWORK
39bbd07a0         4  anonymous block
===================================================

    这个死锁产生的背景是两个不同的session用不同的参数执行相同的package。这个package先做delete操作，然后做update操作。表rsw_valuation的tip_flg字段上建有bitmap索引。
    从trace文件中可以看出，发生死锁的两个session都持有排他锁（X），等待共享锁（S）。这两个session分别是138，369（红色下划线部分）。
    发生死锁时，current session（下称S1）正在执行update语句，即其delete语句已经操作完成。other session（下称S2）正在执行delete语句。
死锁是这样发生的：
   S1，S2在执行delete（或update）语句都取得了相应索引块的排他锁X，此时S2的delete需要申请相应行的共享锁，由于S1拥有该行的排他锁，故S2被阻塞；同时S1的update语句同样申请相应行的共享锁，但被S2排他，无法取得。所以产生死锁。

oracle 11g 日志路径（Windows）：X:/app/$user/diag/rdbms/...