这几天老报死锁问题 所以仔细研究了一下
环境: 前台应用时java语言编写的多线程入库程序 由于并发的插入到一个表中,报了ora-00060 的死锁错误
要了 alert 日志以及trace文件 根据bdump下的alert 发现trace文件在udump下面
*** 2010-10-18 14:44:31.313
*** SERVICE NAME:(SYS$USERS) 2010-10-18 14:44:31.307
*** SESSION ID:(648.60050) 2010-10-18 14:44:31.307
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-000b002b-0003fea3 28 648 X 24 619 S
TX-000a0034-00033382 24 619 X 28 648 S
session 648: DID 0001-001C-0001705C session 619: DID 0001-0018-00029ED1
session 619: DID 0001-0018-00029ED1 session 648: DID 0001-001C-0001705C
============================================================
Dumping Session Wait History
for 'enq: TX - row lock contention' count=1 wait_time=2936941
name|mode=54580004, usn<<16 | slot=a0034, sequence=33382
for 'SQL*Net more data from client' count=1 wait_time=8
driver id=74637000, #bytes=1, =0
for 'SQL*Net more data from client' count=1 wait_time=11
driver id=74637000, #bytes=2, =0
for 'SQL*Net more data from client' count=1 wait_time=7140
driver id=74637000, #bytes=1, =0
for 'SQL*Net more data from client' count=1 wait_time=12
driver id=74637000, #bytes=1, =0
for 'SQL*Net more data from client' count=1 wait_time=14
driver id=74637000, #bytes=2, =0
for 'SQL*Net more data from client' count=1 wait_time=67
driver id=74637000, #bytes=1, =0
for 'SQL*Net message from client' count=1 wait_time=86239
driver id=74637000, #bytes=1, =0
for 'SQL*Net message to client' count=1 wait_time=3
driver id=74637000, #bytes=1, =0
for 'log file sync' count=1 wait_time=48154
buffer#=179a, =0, =0
temporary object counter: 0
=========================================================
报了行锁竞争 由于是插入所以之前考虑不应该是表级的行锁引起的 所以联想到可能是索引的竞争
又联想到之前确实在这个表的一个字段建立了位图索引为了加快总数的查询,所以应该就是这块的问题
毕竟位图索引一加入得话引发的锁机制代价很高,因为位图索引引发的锁不支持行级锁定,是一个范围级别的锁定
所以会造成死锁
如果有主键或者唯一索引同样会引发这种死锁,原因也是一样因为是锁定了一定范围的数据造成的
如果把唯一性索引变为位图索引一样
create bitmap index t_bitmap on t(id);