db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: SYSCOLUMNS Locking Under High Load
Date Wed, 14 Jan 2015 21:04:22 GMT
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

Mime
View raw message