[
https://issues.apache.org/jira/browse/DERBY-6554?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13983364#comment-13983364
]
Rick Hillegas commented on DERBY-6554:
--------------------------------------
This is my analysis of what is going on:
1) The CREATE SEQUENCE statement puts a row into SYS.SYSSEQUENCES and slaps an
exclusive lock on it using the connection's execution transaction controller.
2) The subsequent NEXT VALUE FOR statement is the first attempt to use the
sequence generator. In trying to allocate the first range of values, it has to
update that row in SYS.SYSSEQUENCES. It tries to do this in a nested
subtransaction of the connection's execution transaction controller. But it
can't get a lock on the row. It is blocked by the parent transaction.
3) A "too much contention" exception is raised. That is a transaction severity
error. While cleaning up the transaction, the DataDictionary trips over an
assertion failure which makes the connection unusable:
{noformat}
New exception raised during cleanup ASSERT FAILED Number of DDL Users is <= 0
when finishing a transaction
org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED Number of
DDL Users is <= 0 when finishing a transaction
at
org.apache.derby.shared.common.sanity.SanityManager.ASSERT(SanityManager.java:120)
at
org.apache.derby.impl.sql.catalog.DataDictionaryImpl.transactionFinished(DataDictionaryImpl.java:1325)
{noformat}
Here is the script output showing the lock table just before the NEXT VALUE FOR
statement. I also instrumented SequenceUpdater to print out the transaction id
of the execution transaction. As you can see, it is the same transaction which
holds the lock on the row in SYS.SYSSEQUENCES.
{noformat}
ij version 10.11
ij> connect 'jdbc:derby:memory:db;create=true';
ij> autocommit off;
ij> create sequence seq;
0 rows inserted/updated/deleted
ij> select * from syscs_diag.lock_table;
XID |TYPE |MODE|TABLENAME
|LOCKNAME |STATE|TABLETYPE|LOCK&|INDEXNAME
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
167 |ROW |S |SYSSCHEMAS
|(1,17) |GRANT|S |2 |NULL
167 |TABLE|IS |SYSSEQUENCES
|Tablelock |GRANT|S |2 |NULL
167 |TABLE|IX |SYSSEQUENCES
|Tablelock |GRANT|S |3 |NULL
167 |TABLE|IS |SYSCONGLOMERATES
|Tablelock |GRANT|S |2 |NULL
167 |TABLE|IS |SYSSCHEMAS
|Tablelock |GRANT|S |2 |NULL
167 |TABLE|IS |SYSTABLES
|Tablelock |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,21) |GRANT|S |2 |NULL
167 |ROW |X |SYSSEQUENCES
|(1,7) |GRANT|S |3 |NULL
167 |TABLE|IS |SYSCOLUMNS
|Tablelock |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,30) |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,25) |GRANT|S |2 |NULL
167 |ROW |S |SYSCONGLOMERATES
|(6,10) |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,24) |GRANT|S |2 |NULL
167 |ROW |S |SYSCONGLOMERATES
|(6,9) |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,23) |GRANT|S |2 |NULL
167 |ROW |S |SYSCONGLOMERATES
|(6,8) |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,22) |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,29) |GRANT|S |2 |NULL
167 |ROW |S |SYSTABLES
|(1,27) |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,28) |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,27) |GRANT|S |2 |NULL
167 |ROW |S |SYSCOLUMNS
|(4,26) |GRANT|S |2 |NULL
22 rows selected
ij> values next value for seq;
1
-----------
XXX SequenceUpdater transaction id = 167
XXX SequenceUpdater transaction id = 167
ERROR X0Y84: Too much contention on sequence SEQ. This is probably caused by an
uncommitted scan of the SYS.SYSSEQUENCES catalog. Do not query this catalog
directly. Instead, use the SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE function to view
the current value of a sequence generator.
ij> select count(*) from sys.systables;
ERROR 08003: No current connection.
{noformat}
I'm unclear on the behavior of Derby nested subtransactions and I have a
question about step 2). Is it expected that the subtransaction should be
blocked by its parent transaction?
Thanks,
-Rick
> Too much contention followed by assert failure when accessing sequence in
> transaction that created it
> -----------------------------------------------------------------------------------------------------
>
> Key: DERBY-6554
> URL: https://issues.apache.org/jira/browse/DERBY-6554
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.9.1.0, 10.11.0.0, 10.10.2.0
> Reporter: Knut Anders Hatlen
>
> {noformat}
> ij version 10.11
> ij> connect 'jdbc:derby:memory:db;create=true' as c1;
> ij> autocommit off;
> ij> create sequence seq;
> 0 rows inserted/updated/deleted
> ij> values next value for seq;
> 1
> -----------
> ERROR X0Y84: Too much contention on sequence SEQ. This is probably caused by
> an uncommitted scan of the SYS.SYSSEQUENCES catalog. Do not query this
> catalog directly. Instead, use the SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE function
> to view the current value of a query generator.
> ij> rollback;
> ERROR 08003: No current connection.
> ij> connect 'jdbc:derby:memory:db' as c2;
> ij(C2)> autocommit off;
> ij(C2)> create sequence seq;
> 0 rows inserted/updated/deleted
> ij(C2)> values next value for seq;
> 1
> -----------
> ERROR 38000: The exception
> 'org.apache.derby.shared.common.sanity.AssertFailure: ASSERT FAILED Identity
> being changed on a live cacheable. Old uuidString =
> 0ddd00a9-0145-98ba-79df-000007d88b08' was thrown while evaluating an
> expression.
> ERROR XJ001: Java exception: 'ASSERT FAILED Identity being changed on a live
> cacheable. Old uuidString = 0ddd00a9-0145-98ba-79df-000007d88b08:
> org.apache.derby.shared.common.sanity.AssertFailure'.
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.2#6252)
