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