Rick - Thanks for pointing out DERBY-6542. I was focusing too much of my Jira search on SYSCOLUMNS and likely skimmed past this ticket. Will try an upgrade to 10.11.1.1. Thanks so much again,Chip
Date: Wed, 14 Jan 2015 13:04:22 -0800 From: [email protected] To: [email protected] Subject: Re: SYSCOLUMNS Locking Under High Load 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
