You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2013/07/09 14:37:48 UTC
[jira] [Commented] (DERBY-6137) update/delete statement on table
with trigger fails randomly with ERROR XSTA2
[ https://issues.apache.org/jira/browse/DERBY-6137?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13703214#comment-13703214 ]
Rick Hillegas commented on DERBY-6137:
--------------------------------------
I think it should be possible to backport this fix to 10.8. However, the fix adds a test case to SequenceTest, which verifies the behavior of SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE. That test case would have to be modified because that system function doesn't exist in 10.8.
> update/delete statement on table with trigger fails randomly with ERROR XSTA2
> -----------------------------------------------------------------------------
>
> Key: DERBY-6137
> URL: https://issues.apache.org/jira/browse/DERBY-6137
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.9.1.0, 10.11.0.0
> 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
> Reporter: Andrew Clemons
> Assignee: Rick Hillegas
> Fix For: 10.9.2.2, 10.10.1.3, 10.11.0.0
>
> Attachments: derby-6137-01-aa-dontClearSequenceCache.diff, derby-6137-01-ab-dontClearSequenceCache.diff, derby-6137-02-aa-balanceFindWithRelease.diff, derby-6137.sql, derby_db_identity.log, derby_db.log
>
>
> 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.
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira