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 15:22:41 GMT
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 ....
> >
>
>


Mime
View raw message