db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6934) Lock conflicts between Statement.getGeneratedKeys() and sequence-generator-based identity columns
Date Sun, 21 May 2017 18:05:04 GMT

    [ https://issues.apache.org/jira/browse/DERBY-6934?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16018924#comment-16018924
] 

Rick Hillegas commented on DERBY-6934:
--------------------------------------

In addition to its concurrency problems, Statement.getGeneratedKeys() returns the wrong result.
This is because it is implemented on top of the limited functionality of IDENTITY_VAL_LOCAL().
IDENTITY_VAL_LOCAL() retrieves the last identity value generated for a single row INSERT issued
by the session. It could return an identity value generated for another table. It ignores
multi-row INSERTs.

In contrast, Statement.getGeneratedKeys() is supposed to return a ResultSet of the identity
values generated by the current statement. So, there should be multiple key values for a multi-row
INSERT.

Attaching Derby_6934.java, a program which demonstrates this defect. Here is the program's
output:

{noformat}
Preparing 'create table t
(
	keyCol bigint generated always as identity,
	counterCol int
)'...
'insert into t(counterCol) values (100)' produced these keys: [[1]] vs. expected result: [[1]]
'insert into t(counterCol) values (200), (201)' produced these keys: [[1]] vs. expected result:
[[2],[3]]
'insert into t(counterCol) values (300), (301), (302)' produced these keys: [[1]] vs. expected
result: [[4],[5],[6]]
'insert into t(counterCol) values (400)' produced these keys: [[7]] vs. expected result: [[7]]
{noformat}

> 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)

Mime
View raw message