Derby
  1. Derby
  2. DERBY-3683

SYSCS_COMPRESS_TABLE gets deadlock while executing concurrently with other user threads

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.4.1.3
    • Fix Version/s: None
    • Component/s: SQL, Tools
    • Environment:
      Embedded Derby, Sun JDK 1.6.0_06-b02, Linux 2.6.9-67.0.15.EL i686
    • Urgency:
      Normal
    • Issue & fix info:
      Workaround attached

      Description

      Derby documentation about SYSCS_COMPRESS_TABLE says: "...procedure acquires an exclusive table lock on the table being compressed." However, I get:

      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 : ROW, SYSCONGLOMERATES, (7,16)
      Waiting XID :

      {13091, X} , APP, alter table "APP"."XYZ" compress sequential
      Granted XID : {13091, S} , {13087, S}
      Lock : TABLE, XYZ, Tablelock
      Waiting XID : {13087, IX} , APP, update XYZ set FOO = ? where BAR = ? AND ID = ?
      Granted XID : {13091, X}


      . The selected victim is XID : 13091.' was thrown while evaluating an expression.

      if another thread is updating the same table while another thread executes:

      CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_COMPRESS_TABLE(?, ?, ?)");
      cs.setString(1, "APP");
      cs.setString(2, "XYZ");
      cs.setShort(3, (short) 1);
      cs.execute();
      conn.commit();

      Problem goes away if I acquire exclusive table lock manually right before the compress table statement:

      conn.prepareStatement("LOCK TABLE XYZ IN EXCLUSIVE MODE").executeUpdate();

        Issue Links

          Activity

          Juha Heljoranta created issue -
          Mike Matrigali made changes -
          Field Original Value New Value
          Summary SYSCS_COMPRESS_TABLE fails to aquire exclusive table lock SYSCS_COMPRESS_TABLE gets deadlock while executing concurrently with other user threads
          Kathey Marsden made changes -
          Component/s SQL [ 11408 ]
          Knut Anders Hatlen made changes -
          Urgency Normal
          Issue & fix info [Workaround attached]
          Knut Anders Hatlen made changes -
          Component/s Tools [ 11414 ]
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Knut Anders Hatlen made changes -
          Link This issue is related to DERBY-4275 [ DERBY-4275 ]
          Rick Hillegas made changes -
          Link This issue is related to DERBY-5234 [ DERBY-5234 ]
          Rick Hillegas made changes -
          Link This issue is related to DERBY-5876 [ DERBY-5876 ]
          Gavin made changes -
          Workflow jira [ 12431520 ] Default workflow, editable Closed status [ 12801061 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Juha Heljoranta
            • Votes:
              1 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:

                Development