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 <[email protected]> To: Derby Discussion <[email protected]> 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 .... >
