Hmm.... My reading of the situation is a bit different... You said:
> Compress is doing row locking as indicated by it requesting a (IX), intended
> share table lock.
To me IX is Intended Exclusive and the fact some other transaction has a shared
lock (S) on SOMETABLE shouldn't really matter, so going back to the error
report - and for a deadlock to exist - I would expect to see some transaction A
holding exclusive lock L1 while requesting exclusive lock L2, and another
transaction B holding L2 while requesting L1. This means I would expect to see
two X locks in the report between two tables but I don't - there is just one X
lock... Here's the report again for clarity:
'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)}
If my reading of this is accurate, what I see is that 27040324 has an X lock on
SOMETABLE and 27058697 is waiting to obtain the same lock (IX), while
27058697is not holding any other X locks for it to engage in a deadlock. At the
same time 27040324 is intending to obtain an exclusive lock on SYSCONGLOMERATES
but the report is not telling who's holding an X lock on it. Basically in this
report, and for a deadlock to occur, I am expecting to see that 27058697 also
has an X lock on SYSCONGLOMERATES, but I don't. Therefore, there is either some
sort of bug here, or I have to assume there must be a third transaction (not
reported here) that has this exclusive lock, and this would then appear to be a
3-way deadlock, but the report isn't telling me this, and detecting a 3-way
deadlock is a very difficult thing to begin with... so from this I conclude
that we are dealing with some sort of bug here...
The code is not accessing SYSCONGLOEMERATES directly or doing metadata queries
- threads are simply INSERTing while another thread runs stats periodically.
Thanks
________________________________
From: Mike Matrigali <[email protected]>
To: Derby Discussion <[email protected]>
Sent: Wednesday, May 27, 2009 12:28:50 PM
Subject: Re: syscs_compress_table deadlock
Is there any chance you can post a reproducible test case? The part of
this I am having a hard time understanding is that somehow the transaction
which is doing the inserts has gotten a table level read lock on
SYSCONGLOMERATES, as indicated by the following line from the
error:
Granted XID : {27040324, IS} , {27043904, S} , {27043904, IS} ,
> {27058697, S}
Transaction 27043904 has a S (shared) table lock on SYSCONGLOMERATES which is a
system table. The key is figuring out how that happened.
Compress is doing row locking as indicated by it requesting a (IX),
intended share table lock.
Any chance the inserting threads are either doing direct queries on
SYSCONGLOMERATES or doing database metadata queries? Setting
derby.language.logStatementText=true may help track down what the
insert threads are doing to get this unexpected lock. It is probably
prudent to commit after any database metadata query to release any locks
which may have been requested on system catalogs unless you require that
info for a consistent transaction.
Also search the documentation for derby.locks.monitor=true for ways to
get the system to print more information when it gets a deadlock.
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 ....
>
