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