We have an application that was running on non-partitioned tables that we decided to partition so that historical partitions could be made read-only. After we partitioned the tables and indexes we are experiencing significant ora-00060 deadlocks on insert to certain tables. Most of the indexes created are local indexes except for primary-key indexes and unique-constraint indexes. Tables are range partitioned with the top partition on maxvalue on columns that are coming from sequences.
Some of the deadlock graphs show the resources in wait, and others show now rows as below:
Resource Name process session holds waits process session holds waits
TM-001d4fd4-00000000 24 18 SX SSX 27 617 SX SSX
TM-001d4fd4-00000000 27 617 SX SSX 24 18 SX SSX
session 18: DID 0001-0018-00000643 session 617: DID 0001-001B-00000124
session 617: DID 0001-001B-00000124 session 18: DID 0001-0018-00000643
Rows waited on:
Session 18: no row
Session 617: no row
What would cause the deadlocks to occur on partitioned tables when there were none before?
What can we do to resolve the deadlocks ?
Your sessions are holding mode 3 TM locks, requesting mode 5 TM locks, on object id 1920980.
Look up that object id from user/dba_objects and double check the indexes involved in foreign keys, particularly on delete cascase.
Futher information on the statements involved and the relevant relationships would be useful, as well as confirmation of the index now being local or global.
Thank you very much to everyone who responded so promptly to this thread. I appreciate your willingness to provide great information.
It appeared that the deadlocks were occurring as a result of the partitioning, but upon further investigation, there were many constraints that were enabled with novalidate during the redefinition process. We were able to enable and validate all the constraints and it appears that the deadlock issue is resolved.
Thanks again for your assistance.
Edited by: mfpsdba on Feb 5, 2013 7:33 AM