RE: deadlock between a partition lock and an insert
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "oracledbaquestions@xxxxxxxxx" <oracledbaquestions@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 28 May 2013 22:04:31 +0000
There are a few key pieces of information in the trace file.
1.) Deadlock graph. Post this, and I can help you interpret.
2.) SQL the encountered the deadlock.
3.) Other SQLs involved in the deadlock.
4.) Rows waited on (This may or may not have valid information, depending on
whether locking was row-level or not.)
The process state dump is certainly not required, and that's the bulk of the
file.
If you can extract the 4 pieces listed above from the trace file, I'm sure
we'll be able to shed some light.
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Dba DBA
Sent: Tuesday, May 28, 2013 5:09 PM
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
Other related posts:
