You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2017/05/21 15:08:04 UTC
[jira] [Commented] (DERBY-6934) Lock conflicts between
Statement.getGeneratedKeys() and sequence-generator-based identity columns
[ https://issues.apache.org/jira/browse/DERBY-6934?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16018858#comment-16018858 ]
Rick Hillegas commented on DERBY-6934:
--------------------------------------
The lock contention disappears if you set the pre-allocation range to the maximum possble value:
-Dderby.language.sequence.preallocator=2147483647
This is a workaround for in-memory databases.
> Lock conflicts between Statement.getGeneratedKeys() and sequence-generator-based identity columns
> -------------------------------------------------------------------------------------------------
>
> Key: DERBY-6934
> URL: https://issues.apache.org/jira/browse/DERBY-6934
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.13.1.1
> Reporter: Rick Hillegas
> Attachments: IVL.java
>
>
> 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:
> {noformat}
> 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!
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)