4 Replies Latest reply on Feb 5, 2013 3:34 PM by 74667

    Inserts causing deadlocks after partitioning tables

      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 ?