Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4274

SYSCS_UPDATE_STATISTICS takes unnecessary table lock

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.5.1.1
    • Fix Version/s: 10.5.2.0, 10.6.1.0
    • Component/s: SQL
    • Labels:
      None

      Description

      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.

        Attachments

        1. d4274-1a.diff
          2 kB
          Knut Anders Hatlen

          Activity

            People

            • Assignee:
              knutanders Knut Anders Hatlen
              Reporter:
              knutanders Knut Anders Hatlen
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: