[DERBY-6137] update/delete statement on table with trigger fails randomly with ERROR XSTA2

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.9.1.0, 10.11.1.1
• Fix Version/s: 10.9.2.2, 10.10.2.0, 10.11.1.1
• Component/s: SQL
• Labels: derby_backport_reject_10_8
• Environment: $ 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
• Issue & fix info: Repro attached
• Bug behavior facts: Crash, Seen in production
• Assignee: Richard N. Hillegas
• Reporter: Andrew Clemons
• Created: 02/Apr/13 00:51
• Updated: 03/Jun/14 13:09
• Resolved: 22/Apr/13 12:00

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.

Issue Links

• is related to: DERBY-6595 CheckToursDBTest failed while updating sequence value on disk
• relates to: DERBY-6176 Couple failures in SequenceGeneratorTest suite on trunk(1466748) with weme 6.2