This discussion is archived
4 Replies Latest reply: Feb 5, 2013 7:34 AM by 74667 RSS

Inserts causing deadlocks after partitioning tables

74667 Newbie
Currently Being Moderated
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:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
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 ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points