Hi Tia,
Some comments inline...
T K wrote:
> 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;
This means that Derby is flagging to itself the fact that it needs to
re-compile other prepared statements which mention the table. This is
because the compression creates new statistics on the table, which the
optimizer will want to consider.
> 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:
Perhaps someone more familiar with the internals of the compression can
comment on the deadlock. You seem to have a deadlock between the
compressed table and the metadata which describes tables and indexes
(SYSCONGLOMERATES).
Hope this helps,
-Rick
>
> 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 ....
> >
>
>
|