两个保存点之间的嵌套事务回滚？ [英] Nested transaction rollback between two savepoints?

问题描述

对于三种SQL类型（MySql，SQLite和PostgreSQL），我希望/需要以相同的方式处理保存点。

For the three SQL types (MySql, SQLite and PostgreSQL) I want / need to handle save points identically.

现在，我有了我的应用程序，可以在其中更改不同的条目数据库是一个大事务，并且需要一些嵌套事务来实现程序的特殊行为。

Now I have my application to change different entries in the database in one big transaction and need some nested transactions for special behavior of the program.

所以问题是，如果我创建类似这样的东西：

So the question is, if i create something like:

BEGIN TRANSACTION;
--random insert/update statements
SET SAVEPOINT sp1;
--more random inserts/updates
SET SAVEPOINT sp2;
--inserts n stuff

（是的语法可能不正确，只是

(yes the syntax may not be correct, its just an example)

所以我想知道是否有可能在两个保存点 sp1 和 sp2 而不回滚 sp2 之后的插入/更新？

So i want to know if it is possible to do a rollback between the two save points sp1 and sp2 without rolling back the inserts/updates after sp2?

推荐答案

保存点将无法执行您想要的操作。当您回滚到一个保存点时，该保存点之后的所有内容都会被回退，而不管是否以后创建了保存点。

Savepoints will not do what you want. When you roll back to a savepoint, everything after that savepoint is rolled back, irrespective of whether later savepoints were created.

请像堆栈那样考虑保存点。您不能从堆栈的中间抽出东西，而必须将所有东西都移到所需的层。

Think of savepoints like a "stack". You can't pull something out of the middle of the stack, you have to remove everything down to the layer you want.

您可能正在寻找自动交易。您要使用的数据库均不支持它们。在PostgreSQL中，您可以使用dblink模块来解决此问题，以建立与数据库的新连接并进行处理。请参阅 http://www.postgresql.org/docs/current/static/dblink。 html 。我不知道MySQL或SQLite提供什么解决方案，但是既然您知道您要寻找的术语，Google就会为您提供帮助。

You are probably looking for autonomous transactions. None of the databases you want to use support them. In PostgreSQL you can work around this using the dblink module to make a new connection to the database and do work with it; see http://www.postgresql.org/docs/current/static/dblink.html . I don't know what solutions MySQL or SQLite offer, but Google will help now that you know the term you are looking for.

我建议您找到一种方法来尽可能解决此应用程序设计要求。让您的应用程序使用两个数据库连接和两个事务来完成所需的工作，并根据需要协调两者。

I recommend that you find a way to work around this application design requirement if possible. Have your application use two database connections and two transactions to do what you need, taking care of co-ordinating the two as required.

这篇关于两个保存点之间的嵌套事务回滚？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！