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

SYSCS_COMPRESS_TABLE gets deadlock while executing concurrently with other user threads

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Minor
    • Resolution: Unresolved
    • 10.4.1.3
    • None
    • SQL, Tools
    • Embedded Derby, Sun JDK 1.6.0_06-b02, Linux 2.6.9-67.0.15.EL i686
    • Normal
    • 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

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6907 ERROR 40XL1: A lock could not be obtained within the time requested

          • Major - Major loss of function.
          • Open
          is related to

          Bug - A problem which impairs or prevents the functions of the product. DERBY-5234 Unable to insert data into table. Failed due be "ERROR XSDG0: Page Page(51919,Container(0, 1104)) could not be read from disk."

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. DERBY-4275 Query executions fail when compressing a table using SYSCS_UTIL.SYSCS_COMPRESS_TABLE

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. DERBY-5876 Overhaul table compression code

          • Major - Major loss of function.
          • Open

          Activity

            People

              Unassigned Unassigned
              reggadyo Juha Heljoranta
              Votes:
              2 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated: