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

update/delete statement on table with trigger fails randomly with ERROR XSTA2

Attach filesAttach ScreenshotVotersWatch issueWatchersCreate sub-taskLinkCloneUpdate Comment AuthorReplace String in CommentUpdate Comment VisibilityDelete Comments
    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.9.1.0, 10.11.1.1
    • 10.9.2.2, 10.10.2.0, 10.11.1.1
    • SQL
    • $ java -version
      java version "1.6.0_43"
      Java(TM) SE Runtime Environment (build 1.6.0_43-b01)
      Java HotSpot(TM) 64-Bit Server VM (build 20.14-b01, mixed mode)

      Linux x86_64
    • Repro attached
    • Crash, Seen in production

    Description

      I have an AFTER DELETE trigger on an exchange rate table which inserts data into a history table. It uses a sequence for the key in the history table.

      Simplified it looks like this:
      CREATE TRIGGER TRG_EXCHANGE_RATES_HIST_DEL
      AFTER DELETE ON EXCHANGE_RATES
      REFERENCING OLD ROW AS OLD
      FOR EACH ROW
      INSERT INTO EXCHANGE_RATE_HISTORY (ID_HISTORY, CUR_FROM, AMNT_FROM, CUR_TO, AMNT_TO, AMNT_RATE, DAT_CREATION)
      VALUES (( NEXT VALUE for HIST_SEQ ), OLD.CUR_FROM, OLD.AMNT_FROM, OLD.CUR_TO, OLD.AMNT_TO, OLD.AMNT_RATE, CURRENT_TIMESTAMP);

      The sequence is defined as:
      create sequence HIST_SEQ
      increment by 1
      start with 10000001
      no maxvalue
      minvalue 10000001
      no cycle;

      Randomly when deleting data the statement will fail with:
      ERROR XSTA2: A transaction was already active, when attempt was made to make another transaction active.

      I will attach the full stack trace. It looks like the sequence cache needs to be cleared which causes a nested user transaction to start. But my delete statement is already running as part of a transaction (non XA - spring managed - hibernate).

      We do not have this exception when testing with derby 10.8.2.2.

      I get the same exception after building and running the latest trunk version (revision 1463340, Bundle-Version: 10.11.0000000.1463340)

      To rule out the sequence on the history table, I switched it to use a generated column (GENERATED ALWAYS AS IDENTITY (START WITH 10000001, INCREMENT BY 1)) but I still randomly get the exception.

      All tables in the application use sequences for their primary keys (through Hibernate's SequenceHiLoGenerator) so it seems to be possibly related to that.

      Attachments

        1. derby_db_identity.log
          12 kB
          Andrew Clemons
        2. derby_db.log
          11 kB
          Andrew Clemons
        3. derby-6137.sql
          0.8 kB
          Richard N. Hillegas
        4. derby-6137-01-aa-dontClearSequenceCache.diff
          6 kB
          Richard N. Hillegas
        5. derby-6137-01-ab-dontClearSequenceCache.diff
          7 kB
          Richard N. Hillegas
        6. derby-6137-02-aa-balanceFindWithRelease.diff
          1.0 kB
          Richard N. Hillegas

        Issue Links

        Activity

          This comment will be Viewable by All Users Viewable by All Users
          Cancel

          People

            rhillegas Richard N. Hillegas
            andrew.clemons Andrew Clemons
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Slack

                Issue deployment