Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6934

Lock conflicts between Statement.getGeneratedKeys() and sequence-generator-based identity columns

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 10.13.1.1
    • None
    • JDBC
    • None
    • Normal
    • Repro attached
    • Seen in production, Wrong query result

    Description

      Lock conflicts between Statement.getGeneratedKeys() and sequence-generator-based identity columns

      Statement.getGeneratedKeys() is implemented on top of IDENTITY_VAL_LOCAL(), which does not play well with sequence generators. This is a problem for applications built on top of Hibernate. That is because Hibernate calls Statement.getGeneratedKeys() after inserting into a table with an identity-generator. This issue was raised on the following email thread: http://apache-database.10148.n7.nabble.com/Identity-column-and-40XL1-error-td147382.html

      To see the lock contention, put IVL and derby.jar on the classpath and run the program like this:

      java IVL 10 100

      That will start up 10 threads, each inserting 100 times into a table with an identity column and then calling Statement.getGeneratedKeys(). Here is sample output from that experiment:

      Running experiment with 10 threads and 100 inserts per thread.
      Preparing 'create table t
      (
      	keyCol bigint generated always as identity,
      	threadIDCol varchar(50),
      	counterCol int
      )'...
      Starting thread0
      Starting thread1
      Starting thread2
      Starting thread3
      Starting thread4
      Starting thread5
      Starting thread6
      Starting thread7
      Starting thread8
      Starting thread9
      thread9 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread7 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread5 caught exception A lock could not be obtained within the time requested
      thread5's last good key was 797
      thread8 caught exception A lock could not be obtained within the time requested
      thread4 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread0 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread1 caught exception A lock could not be obtained within the time requested
      thread3 caught exception A lock could not be obtained within the time requested
      thread2 caught exception A lock could not be obtained within the time requested
      thread9 caught exception A lock could not be obtained within the time requested
      thread6 caught exception A lock could not be obtained within the time requested
      thread8 caught exception A lock could not be obtained within the time requested
      thread1's last good key was 813
      thread0 caught exception A lock could not be obtained within the time requested
      thread3's last good key was 826
      thread4's last good key was 830
      thread0's last good key was 838
      thread9's last good key was 840
      thread2's last good key was 842
      thread8's last good key was 843
      thread7's last good key was 846
      thread6's last good key was 849
      Done!
      

      Attachments

        1. Derby_6934.java
          2 kB
          Richard N. Hillegas
        2. IVL.java
          4 kB
          Richard N. Hillegas

        Issue Links

          Activity

            People

              Unassigned Unassigned
              rhillegas Richard N. Hillegas
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated: