db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Updated] (DERBY-6137) update/delete statement on table with trigger fails randomly with ERROR XSTA2
Date Tue, 09 Apr 2013 15:02:16 GMT

     [ https://issues.apache.org/jira/browse/DERBY-6137?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Rick Hillegas updated DERBY-6137:
---------------------------------

    Attachment: derby-6137-01-aa-dontClearSequenceCache.diff

Attaching derby-6137-01-aa-dontClearSequenceCache.diff. This patch disables the clearing of
the sequence cache during DataDictionary writes. I am running tests now.

The DataDictionary clears its cached information over-aggressively. I don't see any reason
to clear the sequence cache during DDL or SPS recompilation. When we implement ALTER SEQUENCE,
we will have to revisit this decision and adjust the sequence cache for that statement.

This patch makes the DataDictionary NOT clear the sequence cache when you call DataDictionary.startWriting().
I've added a test case to verify that SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE can't reach previously
cached sequences which are dropped.


Touches the following files:

-----------

M       java/storeless/org/apache/derby/impl/storeless/EmptyDictionary.java
M       java/engine/org/apache/derby/iapi/sql/dictionary/DataDictionary.java
M       java/engine/org/apache/derby/impl/sql/catalog/DataDictionaryImpl.java

Don't clear the sequence cache inside DataDictionary.startWriting().

-----------

M       java/testing/org/apache/derbyTesting/functionTests/tests/lang/SequenceTest.java

Tests for this new behavior.

                
> 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: Store
>    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
>         Attachments: derby-6137-01-aa-dontClearSequenceCache.diff, derby-6137.sql, 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

Mime
View raw message