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 "Jim Newsham (JIRA)" <ji...@apache.org> on 2007/09/28 23:22:50 UTC
[jira] Created: (DERBY-3093) intermittent transaction failure
caused by internal duplicate savepoint name, for tirgger
intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
-----------------------------------------------------------------------------------------
Key: DERBY-3093
URL: https://issues.apache.org/jira/browse/DERBY-3093
Project: Derby
Issue Type: Bug
Affects Versions: 10.3.1.4
Environment: Java 1.6.0_02
Reporter: Jim Newsham
While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
• At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
• We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
• One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
2. Here's the stack trace:
Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
... 7 more
Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 18 more
Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
... 12 more
3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
create table time (
id int not null generated always as identity,
time timestamp not null,
constraint time_pk primary key (id),
constraint time_unique unique (time)
);
create table int_sample (
fk_band_id int not null,
fk_time_id int not null,
value int not null,
constraint int_sample_pk primary key (fk_band_id, fk_time_id),
constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
);
create trigger cascade_delete_unused_time_for_int_sample_trigger
after delete on int_sample
referencing old as oldrow
for each row
delete from time where
time.id = oldrow.fk_time_id
and id not in (select fk_time_id from int_sample)
and id not in (select fk_time_id from long_sample)
and id not in (select fk_time_id from float_sample)
and id not in (select fk_time_id from double_sample)
and id not in (select fk_time_id from boolean_sample)
and id not in (select fk_time_id from string_sample);
4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
within transaction {
create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
for (time_id in stmt1) {
PreparedStatement stmt2 = conn.prepareStatement(String.format(
"delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
set stmt2 parameters and executeUpdate()
}
}
}
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536770 ]
Knut Anders Hatlen commented on DERBY-3093:
-------------------------------------------
Isn't this a cost that you only get when you prepare a statement, not during execution? But Dan is right, that was the reason why I initially suggested names unique only within transaction.
Still, without the patch, Derby's behaviour is buggy, so I don't think backing the patch out will improve the situation. Anyone is free to improve the code, though.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.2.2.1, 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536782 ]
Daniel John Debrunner commented on DERBY-3093:
----------------------------------------------
No, this is an execution time cost for every statement. Though the first two statements on the stack will not hit this as Derby caches two statement contexts per connection.
Non-final static fields are really something to be avoided in Java, especially in Derby. This synchronizes statements across all connections in all databases using the same Derby version within a single vm.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.2.2.1, 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-3093) intermittent transaction failure
caused by internal duplicate savepoint name, for tirgger
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams reassigned DERBY-3093:
-------------------------------------
Assignee: James F. Adams
> intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536565 ]
Myrna van Lunteren commented on DERBY-3093:
-------------------------------------------
I am hoping that this issue is the cause of the intermittent test failures that have been reported with some of the IBM jvms, see DERBY-2808, and see for instance http://people.apache.org/~fuzzylogic/derby_test_results/main/testSummary-582051.html
(Although, apparently, these results haven't been updated for several weeks).
I'll keep an eye out for new reports, or run some tests myself, and see if we can close that one off as a duplicate, or as 'fixed'.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Comment Edited: (DERBY-3093) intermittent transaction
failure caused by internal duplicate savepoint name, for tirgger
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536446 ]
james.f.adams edited comment on DERBY-3093 at 10/20/07 11:37 AM:
------------------------------------------------------------------
Attached is a patch proposal (derby-3093_diff.txt) for review.
Files modified: org.apache.derby.impl.sql.conn.GenericStatementContext.java
I changed the way the internal savepoint name isgenerated that should provide unique savepoint names.
Derbyall and suites.All ran cleanly.
was (Author: james.f.adams):
Attached is a patch proposal (derby-3093_diff.txt) for review.
Files modified: org.apache.derby.impl.sql.conn.GenericStatementContext.java
I changed the way the internal savepoint name isgenerated that should provide unique savepoint names.
> intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12542005 ]
Myrna van Lunteren commented on DERBY-3093:
-------------------------------------------
The improved fix went in more than two weeks ago, and it seems to have made a difference in the occurrences of DERBY-2808 in trunk.
Any objections to backporting this to 10.3, 10.2 and 10.1?
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Kristian Waagan (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kristian Waagan updated DERBY-3093:
-----------------------------------
Component/s: SQL
Affects Version/s: 10.1.3.1
10.2.2.0
Summary: Intermittent transaction failure caused by internal duplicate savepoint name for triggers (was: intermittent transaction failure caused by internal duplicate savepoint name, for tirgger)
Fixed typo in summary (typo was in a keyword, fixed it to make searching easier) and classified the bug as SQL. Also added version 10.2.2.0 and 10.1.3.1 to the affects list, as the same naming scheme is used there.
In my opinion, this fix should at least be ported to the 10.2 branch, possibly also the 10.1 branch.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536529 ]
James F. Adams commented on DERBY-3093:
---------------------------------------
Bryan,
With regards to your comments:
1) Yes it was intentional. It seemed the most straight forward to implement the name creation without making it unique per transaction.
2) I am not sure how we could test this but any suggestions are of course welcome.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-3093:
----------------------------------
Derby Info: [Patch Available]
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams resolved DERBY-3093.
-----------------------------------
Resolution: Fixed
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-3093:
--------------------------------------
Fix Version/s: (was: 10.3.1.5)
(was: 10.2.2.1)
Mike, I have backed out the fix from 10.2 and 10.3. Leaving it in trunk since James said he'll make the improvements.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536542 ]
Knut Anders Hatlen commented on DERBY-3093:
-------------------------------------------
As to Bryan's questions about tests, I think this bug is inherently intermittent, and even if we were able to write a test to reproduce it, it would probably be very sensitive to JVM and gc/timing. Combined with the low probability of reintroducing this bug, and the fact that we already have tests that will start failing intermittently if it is reintroduced, I feel comfortable with committing the patch without adding tests.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) intermittent transaction failure
caused by internal duplicate savepoint name, for tirgger
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536478 ]
Bryan Pendleton commented on DERBY-3093:
----------------------------------------
Thanks for working on this problem James!
Two questions:
1) The comments in the JIRA issue talked about making a counter which was unique-per-transaction,
but the patch looks like it chose a static field instead. Your solution seems fine to me, but since I
noticed this I thought I'd mention it, and make sure that this was intentional.
2) It would be nice to have a test program for this situation, particularly since the problem was
intermittent. Is there a way to describe a test that demonstrates the superiority of the new
savepoint naming technique?
> intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-3093:
--------------------------------------
Derby Info: (was: [Patch Available])
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) intermittent transaction failure
caused by internal duplicate savepoint name, for tirgger
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12535384 ]
James F. Adams commented on DERBY-3093:
---------------------------------------
In briefly looking into this issue I noticed that the internal savepoint name is created in GenericStatementContext's constructor with the following line of code:
internalSavePointName = "ISSP" + hashCode();
>From the javadoc for Object:
It is not required that if two objects are unequal according to the equals(java.lang.Object) method, then calling the hashCode method on each of the two objects must produce distinct integer results. However, the programmer should be aware that producing distinct integer results for unequal objects may improve the performance of hashtables.
As much as is reasonably practical, the hashCode method defined by class Object does return distinct integers for distinct objects. (This is typically implemented by converting the internal address of the object into an integer, but this implementation technique is not required by the JavaTM programming language.)
Another method of creating unique names should be implemented.
> intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-3093:
----------------------------------
Given dan's observation will this be backed out of the branches until the correct fix is available.
If someone is signed up to work on it actively I am not as concerned about the trunk.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.2.2.1, 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3093:
----------------------------------
Fix Version/s: 10.3.1.5
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Reopened: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reopened DERBY-3093:
---------------------------------------
Thanks Dan. In that case I agree that this should be addressed before the issue is closed. James, are you willing to work on making the proposed changes?
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.2.2.1, 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536545 ]
Bryan Pendleton commented on DERBY-3093:
----------------------------------------
> James's simpler solution sounds better
I agree. The new code is simple and clear. Thanks for the follow-up!
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-3093.
---------------------------------------
Resolution: Fixed
Fix Version/s: 10.2.2.1
Committed to 10.2 with revision 587034.
Thanks James for fixing the bug, and Myrna for volunteering to keep an eye on the other failures.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.2.2.1, 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) intermittent transaction failure
caused by internal duplicate savepoint name, for tirgger
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12535673 ]
Knut Anders Hatlen commented on DERBY-3093:
-------------------------------------------
That's a good catch, James! Seems like you have found the bug. If the savepoint name only needs to be unique within a transaction, I think we can fix it by maintaining a counter in GenericLanguageConnectionContext and adding a getUniqueSavepointName() method which returns "ISSP1", "ISSP2", ...
> intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536873 ]
James F. Adams commented on DERBY-3093:
---------------------------------------
I will attempt to make the necessary changes.
In looking a bit further I found that the LanguageConnectionContext that is passed into the constructor for GenericStatementContext has a method called getUniqueSavepointName that has the following description:
Get a connection unique system generated name for an unnamed savepoint.
Would it be appropriate to use this method to create the savepoint name? If so I will create a new patch that incorporates it.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.2.2.1, 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536722 ]
Daniel John Debrunner commented on DERBY-3093:
----------------------------------------------
I'm close to -1 on this patch, it introduces a single point of synchronization where one is not required. Knut Anders (I think?) has already shown the cost of one of these in Derby with the synchronization required for ensuring the statement plan is up to date.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.2.2.1, 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-3093:
--------------------------------------
Derby Info: (was: [Patch Available])
Fix Version/s: 10.4.0.0
10.3.1.5
When I suggested a savepoint name that was unique within a transaction, instead of a globally unique name, it was primarily to avoid the need for synchronization. But when I thought more about it, I think that it would be unnecessarily complex without adding significant value, so James's simpler solution sounds better to me.
Committed the patch to trunk with revision 586956 and to 10.3 with revision 586957.
I intend to commit the fix on the 10.2 branch as well, as requested by Kristian.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.3.1.5, 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12537035 ]
Daniel John Debrunner commented on DERBY-3093:
----------------------------------------------
getUniqueSavepointName will work - good find.
The main relationship for these objects is:
EmbedConnection 1-1 LanguageConnectionContext 1-1* TransactionController
Only one thread is active within these objects at any time (save for garbage collection), this is enforced by synchronization at the JDBC (EmbedConnection) layer.
* A nested internal transaction leads to another TransactionController but not relevant here.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-3093:
----------------------------------
Attachment: derby-3093-3_diff.txt
Updated patch replacing
internalSavePointName = "ISSP" + lcc.getUniqueSavepointName();
with
internalSavePointName = lcc.getUniqueSavepointName();
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-3093:
----------------------------------
Fix Version/s: 10.2.2.1
10.1.3.2
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.1.3.2, 10.2.2.1, 10.3.2.1, 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12543617 ]
Kathey Marsden commented on DERBY-3093:
---------------------------------------
I plan to merge this fix to 10.3. Please let me know if you have any concerns.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) intermittent transaction failure
caused by internal duplicate savepoint name, for tirgger
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-3093:
----------------------------------
Derby Info: [Patch Available]
> intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536947 ]
Knut Anders Hatlen commented on DERBY-3093:
-------------------------------------------
James, I think LanguageConnectionContext.getUniqueSavepointName() should work. That is, unless there's a possibility that two LCC instances have the same TransactionController, it shouldn't cause duplicate savepoint names. It would be great if someone who's more familiar with this area of the code could comment on whether that's possible.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12537569 ]
Knut Anders Hatlen commented on DERBY-3093:
-------------------------------------------
The fix looks good and the changes to the master files make sense. All tests ran cleanly in my environment. Committed revision 588202.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "Daniel John Debrunner (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12537181 ]
Daniel John Debrunner commented on DERBY-3093:
----------------------------------------------
I think we can just use getUniqueSavepointName() as-is.
ie. replace
internalSavePointName = "ISSP" + lcc.getUniqueSavepointName();
with
internalSavePointName = lcc.getUniqueSavepointName();
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) intermittent transaction failure
caused by internal duplicate savepoint name, for tirgger
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-3093:
----------------------------------
Attachment: derby-3093_diff.txt
Attached is a patch proposal (derby-3093_diff.txt) for review.
Files modified: org.apache.derby.impl.sql.conn.GenericStatementContext.java
I changed the way the internal savepoint name isgenerated that should provide unique savepoint names.
> intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Issue Comment Edited: (DERBY-3093) Intermittent transaction
failure caused by internal duplicate savepoint name for triggers
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12537194 ]
james.f.adams edited comment on DERBY-3093 at 10/25/07 1:13 AM:
-----------------------------------------------------------------
Updated patch replacing
internalSavePointName = "ISSP" + lcc.getUniqueSavepointName();
with
internalSavePointName = lcc.getUniqueSavepointName();
Ran Derbyall and suites.All with no new errors.
was (Author: james.f.adams):
Updated patch replacing
internalSavePointName = "ISSP" + lcc.getUniqueSavepointName();
with
internalSavePointName = lcc.getUniqueSavepointName();
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093-3_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3093) Intermittent transaction failure
caused by internal duplicate savepoint name for triggers
Posted by "James F. Adams (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James F. Adams updated DERBY-3093:
----------------------------------
Attachment: derby-3093-2_diff.txt
Attached is an updated patch proposal that uses LanguageConnectionContext.getUniqueSavepointName to generate the savepoint names. The test cannons for savepointJdbc30_JSR169 and savepointJdbc30_XA needed to be modified as some exception text was affected by the change.
Ran Derbyall and suites.All with no new errors.
> Intermittent transaction failure caused by internal duplicate savepoint name for triggers
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.1.3.1, 10.2.2.0, 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Fix For: 10.4.0.0
>
> Attachments: derby-3093-2_diff.txt, derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-3093) intermittent transaction failure
caused by internal duplicate savepoint name, for tirgger
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-3093?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12536476 ]
Knut Anders Hatlen commented on DERBY-3093:
-------------------------------------------
Thanks for the patch. It looks like a good solution to me. I'll run some tests and commit it.
> intermittent transaction failure caused by internal duplicate savepoint name, for tirgger
> -----------------------------------------------------------------------------------------
>
> Key: DERBY-3093
> URL: https://issues.apache.org/jira/browse/DERBY-3093
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.3.1.4
> Environment: Java 1.6.0_02
> Reporter: Jim Newsham
> Assignee: James F. Adams
> Attachments: derby-3093_diff.txt
>
>
> While running our app overnight, performing some intensive database operations (primarily deletes), we experienced a transaction failure, with error message "A SAVEPOINT with the passed name already exists in the current transaction". This failure appears to be very intermittent, as I've run the same operation successfully for hours and hours without failure. Some more information:
> 1. I see that there are four JIRA issues which report the same message (DERBY-2773, DERBY-1457, DERBY-2808, DERBY-2804). I am not sure to what extent if any, the problem I experienced is related to these issues.
> • At least three of the above JIRA issues are related to triggers. Our transaction failure occurred while performing a delete in a table which has delete triggers.
> • We aren't using savepoints explicitly. Of course, it can be seen in the stack trace that the trigger operation is setting a savepoint.
> • One of the JIRA issues mentions exceeding maximum depth of nested triggers. Not sure what a nested trigger is, but the trigger we are performing should not (in theory) cause another trigger event.
> 2. Here's the stack trace:
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown Source)
> at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown Source)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase$3.perform(DerbySampleBase.java:682)
> at com.referentia.sdf.monitor.samplebase.derby.DerbySampleBase.performInTransaction(DerbySampleBase.java:2747)
> ... 7 more
> Caused by: java.sql.SQLException: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
> at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
> ... 18 more
> Caused by: ERROR 3B501: A SAVEPOINT with the passed name already exists in the current transaction.
> at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
> at org.apache.derby.impl.store.raw.xact.Xact.setSavePoint(Unknown Source)
> at org.apache.derby.impl.store.access.RAMTransaction.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.conn.GenericStatementContext.setSavePoint(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> at org.apache.derby.impl.sql.execute.GenericTriggerExecutor.executeSPS(Unknown Source)
> at org.apache.derby.impl.sql.execute.RowTriggerExecutor.fireTrigger(Unknown Source)
> at org.apache.derby.impl.sql.execute.TriggerEventActivator.notifyEvent(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.fireAfterTriggers(Unknown Source)
> at org.apache.derby.impl.sql.execute.DeleteResultSet.open(Unknown Source)
> at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
> ... 12 more
> 3. Here are the relevant tables and triggers. We have different tables for different "sample" data types in our application (currently int, long, float, double, Boolean, string). I have shown the int_sample table; the other tables are identical but have a different data type for the value field.
> Sample records are first class; time records are second class - they only exist to support sample records. When there are no remaining sample records for a given time record, the time record can be removed. It is the job of the trigger to perform this cleanup: when a record is deleted from any of the sample tables, the time record corresponding to *_sample.fk_time_id is deleted only if there are no remaining records in any of the sample tables which have the same fk_time_id. So although there are cascading deletes (deleting a time record deletes all of its samples), the trigger should not recurse because the trigger only deletes when there are no associated records.
>
> create table time (
> id int not null generated always as identity,
> time timestamp not null,
> constraint time_pk primary key (id),
> constraint time_unique unique (time)
> );
> create table int_sample (
> fk_band_id int not null,
> fk_time_id int not null,
> value int not null,
> constraint int_sample_pk primary key (fk_band_id, fk_time_id),
> constraint int_sample_fk_band foreign key (fk_band_id) references band (id) on delete cascade,
> constraint int_sample_fk_time foreign key (fk_time_id) references time (id) on delete cascade
> );
> create trigger cascade_delete_unused_time_for_int_sample_trigger
> after delete on int_sample
> referencing old as oldrow
> for each row
> delete from time where
> time.id = oldrow.fk_time_id
> and id not in (select fk_time_id from int_sample)
> and id not in (select fk_time_id from long_sample)
> and id not in (select fk_time_id from float_sample)
> and id not in (select fk_time_id from double_sample)
> and id not in (select fk_time_id from boolean_sample)
> and id not in (select fk_time_id from string_sample);
> 4. Invocation. This is very rough pseudocode focusing on the core logic. In reality, there's a lot more code, but I'm doing the usual stuff, such as using prepared statements, and closing all resources when done. The key thing is that I'm using two statements in a nested fashion; in the outer statement, I iterate over records in the time table, and in the inner loop I use a second statement to delete records in one of the sample tables. I can provide the actual code if desired.
> deleteSamples(int bandId, String sampleTable, Object someQueryCriteria) {
> within transaction {
> create stmt1 iterating over time ids in time table (restricted by someQueryCriteria)
> for (time_id in stmt1) {
> PreparedStatement stmt2 = conn.prepareStatement(String.format(
> "delete from %s where fk_band_id = ? and fk_time_id = ?", sampleTable));
> set stmt2 parameters and executeUpdate()
> }
> }
> }
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.