db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-4274) SYSCS_UPDATE_STATISTICS takes unnecessary table lock
Date Tue, 16 Jun 2009 08:50:07 GMT
SYSCS_UPDATE_STATISTICS takes unnecessary table lock
----------------------------------------------------

                 Key: DERBY-4274
                 URL: https://issues.apache.org/jira/browse/DERBY-4274
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.5.1.1
            Reporter: Knut Anders Hatlen
            Assignee: Knut Anders Hatlen


I'm using SYSCS_UTIL.SYSCS_UPDATE_STATISTICS in an application and see deadlocks similar to
this one from time to time:

Caused by: java.sql.SQLTransactionRollbackException: A lock could not be obtained due to a
deadlock, cycle of locks and waiters is:
Lock : ROW, SYSSTATISTICS, (2,20)
  Waiting XID : {20137, S} , APP, SELECT CS.REVISION, A.NAME, CS.TIME, CS.MESSAGE, F2.PATH
FROM CHANGESETS CS, FILECHANGES FC, REPOSITORIES R, FILES F, AUTHORS A, FILECHANGES FC2, FILES
F2 WHERE R.PATH = ? AND F.PATH LIKE ? ESCAPE '#' AND F.REPOSITORY = R.ID AND A.REPOSITORY
= R.ID AND CS.ID = FC.CHANGESET AND R.ID = CS.REPOSITORY AND FC.FILE = F.ID AND A.ID = CS.AUTHOR
AND CS.ID = FC2.CHANGESET AND FC2.FILE = F2.ID ORDER BY CS.ID DESC
  Granted XID : {20134, X} 
Lock : TABLE, CHANGESETS, Tablelock
  Waiting XID : {20134, X} , APP, alter table "APP"."CHANGESETS" all update statistics 
  Granted XID : {20137, IS} 
. The selected victim is XID : 20137.

Here, a select statement is being re-prepared because update statistics has invalidated it,
but it has to wait for update statistics to finish in order to read the new statistics from
SYSSTATISTICS. Then update statistics attempts to obtain an exclusive lock on the table whose
indexes are being updated, but it has to wait because the select statement is holding an intention
lock on the table. Both transactions wait for each other, so we have a deadlock.

Since SYSCS_UPDATE_STATISTICS does not update the table, only its entries in SYSSTATISTICS,
there is no need to take an exclusive table lock. If it didn't lock the table exclusively,
this deadlock situation would go away.

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


Mime
View raw message