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