db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: syscs_compress_table deadlock
Date Wed, 27 May 2009 13:17:30 GMT
Hi Tia,

SYSCS_UTIL.SYSCS_COMPRESS_TABLE obtains an exclusive lock on the table 
which is being compressed. That means that the session which is 
compressing the table expects that it is the only session accessing the 
table. This is described at the end of the documentation for this 
procedure, which you can find in the Derby Reference Guide: 
http://db.apache.org/derby/docs/10.3/ref/ref-single.html#rrefaltertablecompress 


Hope this helps,
-Rick


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 ....
>


Mime
View raw message