deadlock between a partition lock and an insert

  • From: Dba DBA <oracledbaquestions@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 28 May 2013 17:08:30 -0400

Oracle 11.2.0.3
-- hourly partitions
-- inserting by 4 java processes simulataneous
-- oracle job runs once/hour to drop partitions over a set number of hours
old.
-- the java processes are using a jdbc 'batch' features which bundles up
many inserts in 1 network pass and then commits them
-- we are popping data from a websphere managed MQ Series Queue. This
requires us to enable 3 phase committing in the jdbc. So that Oracle acks
back to websphere when its commit is done and MQ Series removes the records
from the queue. This is native to Oracle and MQ Series so we don't have any
custom code to handle this
-- this is during testing we ran over the weekend.

Issue: Getting Deadlock issues between a partition lock and an insert
statement from java.

Using the following 11g new feature to get around known issues with
dropping partitions while the table is being accessed:

alter session set ddl_lock_timeout=1800

The deadlock occurred repeatedly and the deadlock file that oracle
generated showed 2 sqls


Session 1
Following is called by the alter table drop partition recursively. Note the
'wait 1800'

 LOCK TABLE MY_TABLE PARTITION ("B4_27_MAY_2013_12")  IN EXCLUSIVE MODE
WAIT 1800

Session 2: Insert values to this table. It is almost certainly inserting
records to this partiton. We are using bind variables so I can't see for
sure.


Does anyone know how to read the rest of a Deadlock file? I am not sure
what to look for. I am wondering if I am deadlocking on some recursive SQL
statement called by the LOCK TABLE and by the insert?

Any tips on where to look in the Deadlock file? At last resort I can open a
ticket, but my experience with oracle support is that all Ill get is a link
to the deadlock docks and generic responses.

Note: In theory we should not receive data in the past, but we need to
handle for it since data comes from a 3rd party system. For right now, I am
just looking for help with reading the deadlock file.


--
//www.freelists.org/webpage/oracle-l


Other related posts: