On 1/14/15 7:00 AM, Chip Mitchell wrote:
Hey all - I was wondering if anyone has come accross this situation
before. We have a relatively simple table (below). And it seems that
during high insert loads, we see long running transactions and
locking. But what I was surprised to see was that the lock was coming
out of the SYSCOLUMNS table. The only thing I was suspect of was
whether or not that it's the IDENTITY on the first field that might be
contributing here.
Some additional info follows, but if there is a specific piece that
would also help, let me know and I can reply with it.
Derby: 10.8.2.2
Table Definition:
CREATE TABLE "TEST"."INFO" (
"SEQUENCE" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START
WITH 1, INCREMENT BY 1),
"ID" CHARACTER(18) NOT NULL,
"TYPE" INTEGER NOT NULL,
"DESC" CHARACTER(18) NOT NULL,
"DESC_TYPE" INTEGER NOT NULL,
"CREATED" TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
derby.locks.waitTimeout - 600
derby.locks.escalationThreshold - 70000000
XID |TYPE |MODE|LOCKCOUNT|LOCKNAME
|STATE|TABLETYPE /
LOCKOBJ |INDEXNAME / CONTAINER_ID / (MODE for LATCH
only) |TABLENAME / CONGLOM_ID |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*** The following row is the victim ***
1995383222|ROW |S |0 |(44,53)
|WAIT |S
|NULL
|SYSCOLUMNS |
*** The above row is the victim ***
1995383233|ROW |X |2 |(44,53)
|GRANT|S
|NULL
|SYSCOLUMNS |
1995383236|ROW |S |0 |(44,53)
|WAIT |S
|NULL
|SYSCOLUMNS |
1995383242|ROW |X |0 |(44,53)
|WAIT |S
|NULL
|SYSCOLUMNS |
1995383254|ROW |X |0 |(44,53)
|WAIT |S
|NULL
|SYSCOLUMNS |
1995383255|ROW |X |0 |(44,53)
|WAIT |S
|NULL
|SYSCOLUMNS |
1995383257|ROW |X |0 |(44,53)
|WAIT |S
|NULL
|SYSCOLUMNS
Any thoughts or insights are greatly appreciated.
Thank you all!
Hi Chip,
We have certainly seen lock contention on SYSCOLUMNS when identity
columns were stressed under heavy loads. Substantial work went into
10.11 to fix this problem. I recommend that you hard-upgrade your
database to 10.11 and see if that fixes your issue. Please see the
detailed note for DERBY-6542 listed in the 10.11.1.1 release notes:
http://db.apache.org/derby/releases/release-10.11.1.1.cgi#Issues
Hope this helps,
-Rick
