Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (athena.apache.org: local policy)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
  s=s1024; d=sbcglobal.net;
  h=Received:X-YMail-OSG:X-Yahoo-Newman-Property:Message-ID:Date:From:User-Agent:MIME-Version:To:Subject:References:In-Reply-To:Content-Type:Content-Transfer-Encoding;
  b=Cd+8lyd8ByRD7Atf5koRo5lT3ZSbFMFmuVkFZLAuviVYjIAWVyIUtVWbvFCjZK2QsFX9IGUQCxHjvIwh8XY/DKthfTFavCvwkEWBainpJtNZGlHyEnCYkcFhmrKlxdkLKCaz86vPjXkp9JHJQHkaS6Xjqa1vOJRNMsqR2GIYM2k=
  ;
Message-ID: <4A1D6A42.5020806@sbcglobal.net>
Date: Wed, 27 May 2009 09:28:50 -0700
From: Mike Matrigali <mikem_app@sbcglobal.net>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: syscs_compress_table deadlock
References: <200588.95837.qm@web58504.mail.re3.yahoo.com>
In-Reply-To: <200588.95837.qm@web58504.mail.re3.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Is there any chance you can post a reproducible test case?  The part of
this I am having a hard time understanding is that somehow the 
transaction which is doing the inserts has gotten a table level read 
lock on SYSCONGLOMERATES, as indicated by the following line from the
error:
Granted XID : {27040324, IS} , {27043904, S} , {27043904, IS} ,
 > {27058697, S}

Transaction 27043904 has a S (shared) table lock on SYSCONGLOMERATES 
which is a system table.  The key is figuring out how that happened.
Compress is doing row locking as indicated by it requesting a (IX),
intended share table lock.

Any chance the inserting threads are either doing direct queries on
SYSCONGLOMERATES or doing database metadata queries?  Setting
derby.language.logStatementText=true may help track down what the
insert threads are doing to get this unexpected lock.  It is probably
prudent to commit after any database metadata query to release any locks
which may have been requested on system catalogs unless you require that
info for a consistent transaction.

Also search the documentation for derby.locks.monitor=true for ways to
get the system to print more information when it gets a deadlock.


T K wrote:
> While we call this stored proc INSERTS keep coming in from other threads,
> resulting in the deadlock exception at the bottom, and I assume this is 
> expected
> although I did not see anything in the documentation. Can someone please
> confirm the proper conditions for calling this stored proc? Derby 10.3.3.0.
> 
> tia
> 
> com.XXX.pub.database.DatabaseException: code: -1 SQLState: 38000 
> Message: The exception 'java.sql.SQLException: A lock could not be 
> obtained due to a deadlock, cycle of locks and waiters is:
> Lock : TABLE, SYSCONGLOMERATES, Tablelock
>   Waiting XID : {27040324, IX} , MYSCHEMA, alter table 
> "MYSCHEMA"."SOMETABLE" compress sequential
>   Granted XID : {27040324, IS} , {27043904, S} , {27043904, IS} , 
> {27058697, S}
> Lock : TABLE, SOMETABLE, Tablelock
>   Waiting XID : {27058697, IX} , MYSCHEMA, insert into SOMETABLE 
> (COLUMN1) values (?)
>   Granted XID : {27040324, X}
> . The selected victim is XID : 27040324.' was thrown while evaluating an 
> expression.
> From: XXX
>     SQL: {call syscs_util.syscs_compress_table('MYSCHEMA','SOMETABLE',1)}
>     SQL Inserts:
> 
> Caused by SQL Problems.
> Problem #1, SQLState 38000, Error code -1: java.sql.SQLException: The 
> exception 'java.sql.SQLException: A lock could not be obtained due to a 
> deadlock, cycle of locks and waiters is:
> Lock : TABLE, SYSCONGLOMERATES, Tablelock
>   Waiting XID : {27040324, IX} , MYSCHEMA, alter table 
> "MYSCHEMA"."SOMETABLE" compress sequential
>   Granted XID : {27040324, IS} , {27043904, S} , {27043904, IS} , 
> {27058697, S}
> Lock : TABLE, SOMETABLE, Tablelock
>   Waiting XID : {27058697, IX} , MYSCHEMA, insert into SOMETABLE 
> (COLUMN1) values (?)
>   Granted XID : {27040324, X}
> . The selected victim is XID : 27040324.' was thrown while evaluating an 
> expression.
> Problem #2, SQLState 40001, Error code 99999: 
> java.sql.SQLNonTransientConnectionException: A lock could not be 
> obtained due to a deadlock, cycle of locks and waiters is:
> Lock : TABLE, SYSCONGLOMERATES, Tablelock
>   Waiting XID : {27040324, IX} , MYSCHEMA, alter table 
> "MYSCHEMA"."SOMETABLE" compress sequential
>   Granted XID : {27040324, IS} , {27043904, S} , {27043904, IS} , 
> {27058697, S}
> Lock : TABLE, SOMETABLE, Tablelock
>   Waiting XID : {27058697, IX} , MYSCHEMA, insert into SOMETABLE 
> (COLUMN1) values (?)
>   Granted XID : {27040324, X}
> . The selected victim is XID : 27040324.
>     at ....
>