I'm executing some code through this anonymous DO code feature. I am observing that whenever it fails, all changes made are discarded and the database is rolled back to a state as it was before it began. How can I stop this functionality?
Postgresql – Stop rollback of anonymous DO code block
postgresqlpostgresql-12rollback
Related Solutions
No they aren't local to a stored procedure.
If you run the following in a database with simple recovery model
checkpoint
begin tran;
exec dbo.foo_test_tran_outer;
commit tran;
select * from dbo.footest;
select Operation,
Context,
[Savepoint Name],
[Transaction Name] ,
Description
from sys.fn_dblog(NULL,NULL)
It gives these results
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Operation | Context | Savepoint Name | Transaction Name | Description |
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| LOP_BEGIN_CKPT | LCX_NULL | NULL | NULL | |
| LOP_END_CKPT | LCX_NULL | NULL | NULL | log_minRecoveryLsn 00000112:00001ce6:008e;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000 |
| LOP_BEGIN_XACT | LCX_NULL | NULL | user_transaction | user_transaction;0x010500000000000515000000007dfcff481d5bed8a729370ee030000 |
| LOP_MODIFY_ROW | LCX_HEAP | NULL | NULL | |
| LOP_MARK_SAVEPOINT | LCX_NULL | the_constant_name | NULL | |
| LOP_MODIFY_ROW | LCX_HEAP | NULL | NULL | |
| LOP_MODIFY_ROW | LCX_HEAP | NULL | NULL | |
| LOP_MARK_SAVEPOINT | LCX_NULL | the_constant_name | NULL | |
| LOP_MODIFY_ROW | LCX_HEAP | NULL | NULL | |
| LOP_MODIFY_ROW | LCX_HEAP | NULL | NULL | COMPENSATION |
| LOP_LOCK_XACT | LCX_NULL | NULL | NULL | COMPENSATION |
| LOP_MODIFY_ROW | LCX_HEAP | NULL | NULL | COMPENSATION |
| LOP_MODIFY_ROW | LCX_HEAP | NULL | NULL | COMPENSATION |
| LOP_LOCK_XACT | LCX_NULL | NULL | NULL | COMPENSATION |
| LOP_COMMIT_XACT | LCX_NULL | NULL | NULL | |
+--------------------+----------+-------------------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The [Savepoint Name]
recorded in the transaction log does not contain any unique identifier related to stored procedure.
If you remove the rollback tran the_constant_name;
from foo_test_tran_inner
then the result of the final select changes to Inner, before savepoint
showing that the rollback tran the_constant_name;
executed from foo_test_tran_outer
just rolls back to the most recent (unrolled back) savepoint of that name and takes no account of stored procedure nesting.
When a MongoDB instance gets into a Rollback state, and the rollback data is greater than 300MB of data, you have to manually intervene. It will stay in a rollback state until you take action to save/remove/move that data, the (now secondary) should then be resynced to bring it back in line with the primary. This does not have to be a full resync, but that is the simplest way.
Multiple rollbacks are a symptom rather than the cause of a problem. Rollback only happens when a secondary that was not in sync (either due to lag or an issue with replication) becomes primary and takes writes. So, the problems that cause that to happen in the first place are what need to be taken care of - the rollback itself is something you need to deal with as an admin - there are too many potential pitfalls for MongoDB to reconcile the data automatically.
If you want to simulate this again for testing purposes, I have outlined how to do so here:
http://comerford.cc/2012/05/28/simulating-rollback-on-mongodb/
Eventually, this data will be stored in a collection (in the local DB) rather than dumped to disk, which will present opportunities to deal with it more effectively:
https://jira.mongodb.org/browse/SERVER-4375
At the moment though, once a rollback occurs, as you found, manual intervention is required.
Finally, the manual contains similar information to Kristina's blog now:
Related Question
- Sql-server – Identifying if spid on SQL Server is in rollback after low disk space
- PostgreSQL 9.5 Transactions – Does COMMIT Work Within Anonymous plpgsql Function?
- Sql-server – Why does a rollback of a CREATE INDEX statement take longer than expected
- SQL Server – Can You ROLLBACK After COMMIT?
- PostgreSQL – Rollback to Savepoint with Repeatable Read Isolation
- PostgreSQL – Handling Transactions Without Rollback on Error
- PostgreSQL – Best Practices for Allowing Rollback of Individual Customers
- Mysql – What could be the reason for running rollback after a commit
Best Answer
You can commit inside the DO, as long as it is not already inside a transaction.