oracle的共享锁不起作用,共享锁导致的死锁，求原因

以下是死锁发生时的部分trace:

*** 2011-03-17 01:26:51.740

*** SESSION ID

65.27432) 2011-03-17 01:26:51.702

DEADLOCK DETECTED

Current SQL statement for this session:

INSERT INTO P_PAR_LOCK (item, user_id) VALUES (:1, :2)

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-000d0032-000b0d63       100      65     X            109     109           S

TX-000c000b-000ad8f8       109     109     X            100      65           S

session 65: DID 0001-0064-0011F35C      session 109: DID 0001-006D-0008FDBC

session 109: DID 0001-006D-0008FDBC     session 65: DID 0001-0064-0011F35C

Rows waited on:

Session 109: obj - rowid = 00007675 - AAAAAAAAJAAACyMAAA

(dictionary objn - 30325, file - 9, block - 11404, slot - 0)

Session 65: no row

Information on the OTHER waiting sessions:

Session 109:

pid=109 serial=12604 audsid=0 user: 35/MES

O/S info: user: , term: , ospid: , machine:

program:

Current SQL Statement:

INSERT INTO p_par_lock VALUES (:b1, 'QTIME')

End of information on OTHER waiting sessions.

===================================================

资源竞争的对象居然是index:

SQL> select t.owner, t.object_name, t.object_type

2  from all_objects t

3  where t.data_object_id = 30325;

OWNER                               OBJECT_NAME                      OBJECT_TYPE

------------------------------ ------------------------------ ------------------

MES                               P_PAR_LOCK_ID01                      INDEX

但是却不是bitmap index:

SQL> select index_name, index_type from user_indexes where table_name = 'P_PAR_LOCK';

INDEX_NAME                       INDEX_TYPE

------------------------------ ---------------------------

PK_P_PAR_LOCK                       NORMAL

P_PAR_LOCK_ID01                NORMAL

请问，为什么两个insert会造成b-tree index竞争的死锁？

[本帖最后由 vinciho 于 2011-3-18 08:58 编辑]

2011-3-18 08:58 上传

点击文件名下载附件

156.38 KB, 下载次数: 4