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

SYSCS_COMPRESS_TABLE gets deadlock while executing concurrently with other user threads

    Details

    • Type: Bug
    • Status: Open
    • Priority: 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();

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated: