Deadlock when inserting lob
I am getting several deadlocks.
I have three client machines connecting via JDBC to our Oracle 10g server. Each of these clients insert about 10,000 rows a minute into a specific table. This table is defined as follows:
create table contextdata (
accdate timestamp(0) not null,
hash varchar2(32) not null,
content blob,
primary key (hash)
);
The data is inserted into the table as follows:
1.insert into contextdata (accdate, hash, content) values (:1, :2, empty_blob());
2.select content from contextdata where hash = :1 for update;
3.Move data into the lob.
4.Commit;
We get three or four deadlocks a minute. A portion of a trace file follows:
/opt/oracle/product/10.1.0/db_1/admin/dev/udump/dev_ora_966.trc
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /opt/oracle/product/10.1.0/db_1
System name: Linux
Node name: oracle10lt
Release: 2.6.11.10-grsec
Version: #2 SMP Thu Jun 2 16:42:30 MDT 2005
Machine: i686
Instance name: dev
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 966, image: oracledev@oracle10lt
*** 2005-06-20 05:33:49.318
*** SERVICE NAME:(SYS$USERS) 2005-06-20 05:33:49.318
*** SESSION ID:(182.52820) 2005-06-20 05:33:49.318
DEADLOCK DETECTED
Current SQL statement for this session:
SELECT CONTENT FROM CONTEXTDATA WHERE HASH=:1 FOR UPDATE
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-0003002a-00013139 23 182 X 30 229 X
TX-00080005-00000913 30 229 X 23 182 X
session 182: DID 0001-0017-0000219B session 229: DID 0001-001E-00001D08
session 229: DID 0001-001E-00001D08 session 182: DID 0001-0017-0000219B
Rows waited on:
Session 229: obj - rowid = 0001FACD - AAAfrNAAEAAD6oHAAQ
(dictionary objn - 129741, file - 4, block - 1026567, slot - 16)
Session 182: obj - rowid = 0001FACD - AAAfrNAAEAAD5jYAAX
(dictionary objn - 129741, file - 4, block - 1022168, slot - 23)
Information on the OTHER waiting sessions:
Session 229:
pid=30 serial=25679 audsid=83986 user: 111/LTCLUSTER1
O/S info: user: , term: , ospid: 1234, machine: qacluster3.oakleynetworks.com
program:
Current SQL Statement:
SELECT CONTENT FROM CONTEXTDATA WHERE HASH=:1 FOR UPDATE
End of information on OTHER waiting sessions.
===================================================
The conflict seems to be between competing sessions; one session is doing an insert while another session is doing the select for update. They cannot be going after the same row because the value for the hash column is the primary key and the select for update is done immediately after the insert and within a transaction. If they were using the same value for hash, the insert would fail with a duplicate value or the duplicate insert would not return until the first session did a commit, at which time I would expect to get a duplicate key failure.
There is no other activity against the database.
Any help in resolving this deadlock would be greatly appreciated.
Thanks
Brent