RE: deadlock between a partition lock and an insert
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 29 May 2013 11:36:07 +0000
Jonathan is, of course, correct. I assumed ORA-00060. If it was a 4020,
you'll be hard pressed to find the items I mentioned in the trace file.
-Mark
Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
Apart from the comments supplied by Mark, it's also important to check whether
the deadlock in this case is an ORA-00060 or an error ORA04020.
The former is about data, the latter about metadata - and I have known 4020's
to appeared from time to time due to the internals of partition maintenance code
Regards
Jonathan Lews
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Dba DBA [oracledbaquestions@xxxxxxxxx]
Sent: 28 May 2013 22:08
To: ORACLE-L
Subject: deadlock between a partition lock and an insert
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--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
