RE: deadlock between a partition lock and an insert

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 29 2013 - 00:04:31 CEST