Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1994045654.1219596464447.JavaMail.jira@brutus>
Date: Sun, 24 Aug 2008 09:47:44 -0700 (PDT)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-3693) Deadlocks accessing DB metadata
In-Reply-To: <2083617985.1211921819871.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


    [ https://issues.apache.org/jira/browse/DERBY-3693?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12625185#action_12625185 ] 

Bryan Pendleton commented on DERBY-3693:
----------------------------------------

Hi Knut, thanks for working on this problem. I think your approach is excellent -- good catch
on figuring out that the previous implementation was only working for table-level locks.

I read through dontWait.diff and it seems quite clear; the new LockOwner interface
seems like a straightforward way to increase the communication between the locking
services and the higher-level client code without introducing a lot of coupling, very nice.

I did feel, however, that the patch could use significantly more comments. Looking just
at the patch, without referring to the detailed comments in this JIRA report, I found it
hard to see when it would be appropriate to call setNoLockWait(true).

In other words, I think it would be nice if the patch could include sufficient comments
that future code which creates a nested transaction could determine when to use
this new API or not. Should *all* nested transactions use no-wait locking? If not, which
ones should, and why? 

I think that the crucial bit of missing information involves the fact(s) that:
 a) the nested transaction might be blocking on locks held by the parent transaction
 b) some callers are smart enough to know that if a lock-related problem occurs with
     the nested transaction, they can re-try the work using the parent transaction

If possible, I think that some useful places for such information would be:
 - LockOwner.noWait: more information about why a lock owner might return true from noWait
   (e.g., because its a nested transaction which might be blocking on its own parent)
 - Xact.setNoLockWait: more information about why a client might want to configure
   a transaction to use no-wait behavior, when that is appropriate, when it won't work,
   and what the caller should be prepared to do when the lock requests fail rather than waiting
  (e.g., catch the failure and retry the operation on the parent xact)
 - SPSDescriptor.java: more information about why nested transactions which access
   stored prepared statements are able to use lock-failure behavior (that is, because
   the code is smart enough to catch the failure in the nested transaction and retry
   the acces using the parent transaction)

Again, thanks *so* much for this fix, I think this is a big improvement. 
 

> Deadlocks accessing DB metadata
> -------------------------------
>
>                 Key: DERBY-3693
>                 URL: https://issues.apache.org/jira/browse/DERBY-3693
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.3.2.1, 10.3.3.0, 10.4.1.3
>         Environment: Linux amd64 or Windows 32bit
>            Reporter: Svata Dedic
>            Assignee: Knut Anders Hatlen
>             Fix For: 10.5.0.0
>
>         Attachments: d3693.java, deadlock.diff, derby.log, dontWait.diff, dontWait.stat, GetTables.java, nested_transaction.diff, nested_transaction_v2.diff
>
>
> My code changes DB structure (create a column), then immediately after setting autocommit back to true, the code rescans the DB metadata
> DatabaseMetaData.getColumns(catalog, schemaName, tableName, null);
> I am sometimes getting a deadlock with these operations:
> 2008-04-16 19:50:47.833 GMT Thread[Default RequestProcessor,1,system] (XID = 569844), (SESSIONID = 2), (DATABASE = /..../a3/.config/localdb/db), (DRDAID = null), Cleanup action starting 2008-04-16 19:50:47.833 GMT Thread[Default RequestProcessor,1,system] (XID = 569844), (SESSIONID = 2),  (DATABASE = /..../IJCProjects/a3/.config/localdb/db), (DRDAID = null), Failed Statement is: EXECUTE STATEMENT SYS."getColumns" ERROR 40XL2: A lock could not be obtained within the time requested.
> The lockTable dump is:
> 2008-04-16 19:50:47.796 GMT
> XID       |TYPE         |MODE|LOCKCOUNT|LOCKNAME                                                 |STATE|TABLETYPE /LOCKOBJ                   |INDEXNAME / CONTAINER_ID / MODE for LATCH only)  |TABLENAME / CONGLOM_ID                |
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> *** The following row is the victim ***
> 569852    |ROW          |X   |0        |(44,7)                                                |WAIT |S                      |NULL |SYSSTATEMENTS                         | 
> *** The above row is the victim ***
> The stacktrace of the operation causing the deadlock is 
>         at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
>         at org.apache.derby.impl.services.locks.Timeout.createException(Unknown Source)
>         at org.apache.derby.impl.services.locks.Timeout.buildException(Unknown Source)
>         at org.apache.derby.impl.services.locks.ConcurrentLockSet.lockObject(Unknown Source)
>         at org.apache.derby.impl.services.locks.AbstractPool.lockObject(Unknown Source)
>         at org.apache.derby.impl.services.locks.ConcurrentPool.lockObject(Unknown Source)
>         at org.apache.derby.impl.store.raw.xact.RowLocking3.lockRecordForWrite(Unknown Source)
>         at org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)
>         at org.apache.derby.impl.store.access.heap.HeapController.lockRow(Unknown Source)
>         at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3.lockRowOnPage(Unknown Source)
>         at org.apache.derby.impl.store.access.btree.index.B2IRowLocking3._lockScanRow(Unknown Source)
>         at org.apache.derby.impl.store.access.btree.index.B2IRowLockingRR.lockScanRow(Unknown Source)
>         at org.apache.derby.impl.store.access.btree.BTreeForwardScan.fetchRows(Unknown Source)
>         at org.apache.derby.impl.store.access.btree.BTreeScan.fetchNext(Unknown Source) 
>         at org.apache.derby.impl.sql.catalog.TabInfoImpl.updateRow(Unknown Source) 
>         at org.apache.derby.impl.sql.catalog.TabInfoImpl.updateRow(Unknown Source)
>         at org.apache.derby.impl.sql.catalog.DataDictionaryImpl.updateSPS(Unknown Source )
>         at org.apache.derby.iapi.sql.dictionary.SPSDescriptor.updateSYSSTATEMENTS(Unknown Source)
>         at org.apache.derby.iapi.sql.dictionary.SPSDescriptor.getPreparedStatement(Unknown Source)
>         at org.apache.derby.iapi.sql.dictionary.SPSDescriptor.getPreparedStatement(Unknown Source)
>         at org.apache.derby.impl.sql.compile.ExecSPSNode.generate(Unknown Source)
>         at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
>         at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.rePrepare(Unknown Source)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(Unknown Source) 
>         at org.apache.derby.impl.jdbc.EmbedDatabaseMetaData.doGetCols(Unknown Source)
>         at org.apache.derby.impl.jdbc.EmbedDatabaseMetaData.getColumns(Unknown Source)
> I've seen the deadlock occur during several get-metadata operations (getImportedKeys, ...), each time waiting on the SYSSTATEMENTS apparently because of internally constructed PreparedStatement. The lock eventually times out and the locked out operation completes without error.
> When the deadlock occurs, the "real" SQL into systables is being compiled (I traced this sql to be the value of "getColumns" key in org/apache/derby/impl/jdbc/metadata.properties file):
> ---------%<-----------------------%<--------------
> Begin compiling prepared statement: SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, S.SCHEMANAME AS PKTABLE_SCHEM, TABLENAME AS
> PKTABLE_NAME, COLS.COLUMNNAME AS PKCOLUMN_NAME, CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FKCOLUMN_NAME, CAST ...
> ---------%<-----------------------%<--------------

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.