1 2 Previous Next 19 Replies Latest reply: May 25, 2011 10:46 AM by 408327 Go to original post RSS
      • 15. Re: 11.2.0.1: Deadlock not detected
        Dom Brooks
        What's the bigger picture?
        What data are you inserting?
        Does it already have a unique attribute that you could use to sort by in the application before batching up the inserts to the database?

        I presume these JDBC sessions are pulling the same data from somewhere and all trying to do roughly the same thing at the same time with different sessions containing supersets and subsets of the same data for insertion.

        And I presume you have all these sessions trying to do some of the same work in the belief that somehow it's highly scalable and highly concurrent.
        But surely having even a 3 second wait every now and then whilst you rely on deadlock detection is going to kill these goals.

        Could you not order these in the application according to some unique attribute on the "thing" prior to insertion? This should prevent the deadlock scenario being relevant.

        I've seen such a thing before in a "highliy concurrent" message processing system. The messages were ordered by a unique GUID attribute they already had when pulled off a queue before being batched up and sent for insert into the db in the name of "write-behind" "the database is not that important".
        Any unique constraint error could then be chucked away as another session had got there first. Deadlocks were never a problem
        • 16. Re: 11.2.0.1: Deadlock not detected
          408327
          In fact, the software is using the unique key violated constraint to deduplicate rows and we have to use this software wich is in production on several system and is working fine.

          However, i understand that this is highly concurrent scenario but this is the most efficient to deduplicate rows and the only way to do it with this software. To reduce the contention, we have partionned the table/index on a short interval of time so the the inserts should spread on severals partitions.

          I know that the deadlock are mostly a design problem, but in this case it is a normal behaviour as we have to batch the inserts to reach the performances.

          Rgds
          • 17. Re: 11.2.0.1: Deadlock not detected
            408327
            In fact, the software is using the unique key violated constraint to deduplicate rows and we have to use this software wich is in production on several system and is working fine.

            However, i understand that this is highly concurrent scenario but this is the most efficient to deduplicate rows and the only way to do it with this software. To reduce the contention, we have partionned the table/index on a short interval of time so the the inserts should spread on severals partitions.

            I know that the deadlock are mostly a design problem, but in this case it is a normal behaviour as we have to batch the inserts to reach the performances.

            Rgds
            • 18. Re: 11.2.0.1: Deadlock not detected
              408327
              In fact, the software is using the unique key violated constraint to deduplicate rows and we have to use this software wich is in production on several system and is working fine.

              However, i understand that this is highly concurrent scenario but this is the most efficient to deduplicate rows and the only way to do it with this software. To reduce the contention, we have partionned the table/index on a short interval of time so the the inserts should spread on severals partitions.

              I know that the deadlock are mostly a design problem, but in this case it is a normal behaviour as we have to batch the inserts to reach the performances.

              Rgds
              • 19. Re: 11.2.0.1: Deadlock not detected
                Dom Brooks
                Sure.
                But, just to reiterate, if the threads or whatever are able to order by the unique attribute/s first before batching to the database, then you'll still de-dupe by getting the unique constraint error from the db. You'll still get waiting/locking. But you shouldn't get deadlock.
                1 2 Previous Next