db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: syscs_compress_table deadlock
Date Wed, 27 May 2009 19:05:08 GMT
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 <mikem_app@sbcglobal.net>
> *To:* Derby Discussion <derby-user@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 ....
>  >
> 
> 


Mime
View raw message