db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From T K <sanokist...@yahoo.com>
Subject Re: syscs_compress_table deadlock
Date Wed, 27 May 2009 14:42:33 GMT
Thanks, but I am bit perplexed. I believe you are referring to the following note in the documentation:

> Note:  This procedure acquires
an exclusive table lock on the table being compressed. All statement plans
dependent on the table or its indexes are invalidated.

a) The language does not make it obvious that this implies deadlocks;
b) not sure what "invalidated" really means - this is not language that any other db vendor
that I know of uses; 
c) if the procedure acquires an exclusive lock on the entire table, I would expect other SPIDs
to block, not deadlock - and in fact I do also get blocking exceptions as well, as shown below:

ERROR vaes022k3|192.168.200.20|SOAP|PegaAES|Events|logAlert|A5C344560A119864A48BC02A4D4D70F83
 - Error in commit()
com.XXX.database.DatabaseException: Database-General    Problem writing an instance to the
database    -1    40XL1    A lock could not be obtained within the time requested
From: (unknown) 
Caused by SQL Problems.
Problem #1, SQLState 40XL1, Error code -1: java.sql.SQLTransactionRollbackException: A lock
could not be obtained within the time requested

So blocking is expected, but deadlocks are not nor explained by an exclusive lock on the table.

Thoughts?




________________________________
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
To: Derby Discussion <derby-user@db.apache.org>
Sent: Wednesday, May 27, 2009 9:17:30 AM
Subject: Re: syscs_compress_table deadlock

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