You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by T K <sa...@yahoo.com> on 2009/05/27 00:08:12 UTC
syscs_compress_table deadlock
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 ....
Re: syscs_compress_table deadlock
Posted by T K <sa...@yahoo.com>.
Thank you.
> I assume the inserts are part of a multi-statement transaction, is that true?
Yes, batch and non-batch.
> Does the transaction include the creation of the table?
No, the schema is fixed and created beforehand. This is a simple case of straight forward INSERTs as new data pours into the system for later analysis, with a daemon thread trying to run stats on a periodic basis, in a stress-test situation.
So I am not sure where to go from here... table-level locking by default, which I assume will fix this, isn't an option; rerouting the data to a temp table is an option, albeit ugly.
I assume no one else has ever seen this before?
________________________________
From: Mike Matrigali <mi...@sbcglobal.net>
To: Derby Discussion <de...@db.apache.org>
Sent: Wednesday, May 27, 2009 3:05:08 PM
Subject: Re: syscs_compress_table deadlock
The table locking nomenclature is somewhat confusing. Let me try to
explain. Derby always uses 2 level locking so that it can support
both table level and row level locking.
What people usually refer to as "table" locking are the cases of
S and X locks on "TABLE" locks. An S table lock logically means a
shared lock on every row in the table. An X table lock logically means
an exclusive lock on every row in the table.
Derby update row locking always first gets an IX table lock (intent to
get x row locks), and read row locking gets an IS table lock (intent to
get s row locks). In derby the only purpose for these intent locks is
to block as appropriate the S and X table locks.
So X table lock is not compatible with any other table lock.
An S table lock is not compatible with X or IX. It is compatible with IS.
transaction 27040324 (compress transaction):
o is waiting to get an IX table lock on SYSCONGLOMERATES
o owns a an X table lock on SOMETABLE
transaction 27058697 (insert transaction):
o is waiting on a IX lock on SOMETABLE (which is not compatible with
the above X lock held by 27040324
o is granted a S table lock on SYSCONGLOMERATES (which is not
compatible with the above IX table lock request on SYSCONGLOMERATES.
More on Derby isolation and locking can be found in the docs:
http://db.apache.org/derby/docs/10.5/devguide/cdevconcepts30291.html
I assume the inserts are part of a multi-statement transaction, is
that true? Does the transaction include the creation of the table?
T K wrote:
> 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 27058697 is 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 <mi...@sbcglobal.net>
> *To:* Derby Discussion <de...@db.apache.org>
> *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 ....
> >
>
>
Re: syscs_compress_table deadlock
Posted by Mike Matrigali <mi...@sbcglobal.net>.
The table locking nomenclature is somewhat confusing. Let me try to
explain. Derby always uses 2 level locking so that it can support
both table level and row level locking.
What people usually refer to as "table" locking are the cases of
S and X locks on "TABLE" locks. An S table lock logically means a
shared lock on every row in the table. An X table lock logically means
an exclusive lock on every row in the table.
Derby update row locking always first gets an IX table lock (intent to
get x row locks), and read row locking gets an IS table lock (intent to
get s row locks). In derby the only purpose for these intent locks is
to block as appropriate the S and X table locks.
So X table lock is not compatible with any other table lock.
An S table lock is not compatible with X or IX. It is compatible with IS.
transaction 27040324 (compress transaction):
o is waiting to get an IX table lock on SYSCONGLOMERATES
o owns a an X table lock on SOMETABLE
transaction 27058697 (insert transaction):
o is waiting on a IX lock on SOMETABLE (which is not compatible with
the above X lock held by 27040324
o is granted a S table lock on SYSCONGLOMERATES (which is not
compatible with the above IX table lock request on SYSCONGLOMERATES.
More on Derby isolation and locking can be found in the docs:
http://db.apache.org/derby/docs/10.5/devguide/cdevconcepts30291.html
I assume the inserts are part of a multi-statement transaction, is
that true? Does the transaction include the creation of the table?
T K wrote:
> 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 27058697 is 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 <mi...@sbcglobal.net>
> *To:* Derby Discussion <de...@db.apache.org>
> *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 ....
> >
>
>
Re: syscs_compress_table deadlock
Posted by T K <sa...@yahoo.com>.
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 <mi...@sbcglobal.net>
To: Derby Discussion <de...@db.apache.org>
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 ....
>
Re: syscs_compress_table deadlock
Posted by Mike Matrigali <mi...@sbcglobal.net>.
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 ....
>
Re: syscs_compress_table deadlock
Posted by Rick Hillegas <Ri...@Sun.COM>.
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 <Ri...@Sun.COM>
> *To:* Derby Discussion <de...@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 ....
> >
>
>
Re: syscs_compress_table deadlock
Posted by T K <sa...@yahoo.com>.
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;
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:
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 <Ri...@Sun.COM>
To: Derby Discussion <de...@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 ....
>
Re: syscs_compress_table deadlock
Posted by Rick Hillegas <Ri...@Sun.COM>.
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 ....
>