事务，什么时候应该被丢弃和回滚
我正在尝试调试应用程序（在 PostgreSQL 下）并遇到以下错误： “当前事务被中止，命令被忽略”。
据我所知，“事务”只是与底层数据库连接相关的概念。
如果连接的自动提交为“false”，则只要没有失败，您就可以通过同一语句执行查询。 在这种情况下你应该回滚。
如果自动提交为“true”，那么只要所有查询都被视为原子的，就没有关系。
使用 auto commit false，即使一个简单的
select * from foo
失败，我也会收到 PostgreSQL 的上述错误，这让我问，在哪种情况下 SQLException 是被认为无效的“事务”，应该回滚或不用于另一个查询？
使用 MacOS 10.5、Java 1.5.0_16、PostgreSQL 8.3 和 JDBC 驱动程序 8.1-407.jdbc3
I'm trying to debug an application (under PostgreSQL) and came across the following error:
"current transaction is aborted, commands ignored".
As far as I can understand a "transaction" is just a notion related to the underlying database connection.
If the connection has an auto commit "false", you can execute queries through the same Statement as long as it isn't failing. In which case you should rollback.
If auto commit is "true" then it doesn't matter as long as all your queries are considered atomic.
Using auto commit false, I get the aforementioned error by PostgreSQL even when a simple
select * from foo
fails, which makes me ask, under which SQLException(s) is a "transaction" considered invalid and should be rolled backed or not used for another query?
using MacOS 10.5, Java 1.5.0_16, PostgreSQL 8.3 with JDBC driver 8.1-407.jdbc3
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
该错误意味着事务中发送的查询之一失败，因此其余查询将被忽略，直到当前事务结束（这将自动回滚）。 对于 PostgreSQL 来说，事务已经失败，并且在任何情况下都会在错误发生后回滚，但有一个例外。 您必须采取适当的措施，其中之一就是
启用查询日志记录以查看哪个查询失败者及其原因。
无论如何，您问题的确切答案是任何 SQLException 都应该意味着发送事务结束命令时发生回滚，即发出 COMMIT 或 ROLLBACK （或 END）时发生。 这就是它的工作原理，如果您使用保存点，您仍然会受到相同规则的约束，您将能够返回到保存的位置并尝试其他操作。
That error means that one of the queries sent in a transaction has failed, so the rest of the queries are ignored until the end of the current transaction (which will automatically be a rollback). To PostgreSQL the transaction has failed, and it will be rolled back in any case after the error with one exception. You have to take appropriate measures, one of
Enable query logging to see which query is the failing one and why.
In any case the exact answer to your question is that any SQLException should mean a rollback happened when the end of transaction command is sent, that is when a COMMIT or ROLLBACK (or END) is issued. This is how it works, if you use savepoints you'll still be bound by the same rules, you'll just be able to get back to where you saved and try something else.
这似乎是 PostgreSQL 的一个特征行为，大多数其他 DBMS 都没有共享。 一般来说（在 PostgreSQL 之外），您可以让一项操作因错误而失败，然后在同一事务中尝试其他会成功的操作，以补偿错误。 一个例子：考虑合并（插入/更新）操作。 如果您尝试 INSERT 新记录但发现它已存在，则可以切换到更改现有记录的 UPDATE 操作。 这在所有主要的 DBMS 中都可以正常工作。 我不确定它在 PostgreSQL 中不起作用，但我在其他地方以及在这个问题中看到的描述表明，当尝试插入时意味着事务中的任何进一步活动也注定会失败。 这充其量是严酷的，最坏的是“无法使用”。
It seems to be a characteristic behaviour of PostgreSQL that is not shared by most other DBMS. In general (outside of PostgreSQL), you can have one operation fail because of an error and then, in the same transaction, can try alternative actions that will succeed, compensating for the error. One example: consider a merging (insert/update) operation. If you try to INSERT the new record but find that it already exists, you can switch to an UPDATE operation that changes the existing record instead. This works fine in all the main DBMS. I'm not certain that it does not work in PostgreSQL, but the descriptions I've seen elsewhere, as well as in this question, suggest that when the attempted INSERT means that any further activity in the transaction is doomed to fail too. Which is at best draconian and at worst 'unusable'.