db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chip Mitchell <chipmitch...@outlook.com>
Subject RE: SYSCOLUMNS Locking Under High Load
Date Thu, 15 Jan 2015 13:35:38 GMT
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: rick.hillegas@gmail.com
To: derby-user@db.apache.org
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
 		 	   		  
Mime
View raw message