Details
-
Bug
-
Status: Open
-
Major
-
Resolution: Unresolved
-
10.13.1.1
-
None
-
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
Attachments
Issue Links
- is related to
-
DERBY-7002 Change the text of X0Y84 to advise users to tune the value of derby.language.sequence.preallocator
- Open
- relates to
-
DERBY-3609 Wrong functionality of auto-generated keys support
- Open
-
DERBY-6849 Statement.RETURN_GENERATED_KEYS returns a 1 row result set even if there are no auto-generated fields
- Open
-
DERBY-6975 ERROR 40XL1: A lock could not be obtained within the time requested
- Open