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 <[email protected]> To: Derby Discussion <[email protected]> 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 <[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 .... > > > >
