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