COMMIT 语句(在 SQL 中)会失败吗?如何? [英] Can a COMMIT statement (in SQL) ever fail? How?

问题描述

在处理数据库事务时，假设事务中的所有语句都已执行且没有问题，有哪些可能的条件(如果有)会导致事务中的最终 COMMIT 语句失败?

When working with database transactions, what are the possible conditions (if any) that would cause the final COMMIT statement in a transaction to fail, presuming that all statements within the transaction already executed without issue?

例如...假设您有一些两阶段或三阶段提交协议 在这里你做一堆语句，然后等待一些主进程告诉你什么时候可以最终提交事务:

For example... let's say you have some two-phase or three-phase commit protocol where you do a bunch of statements, then wait for some master process to tell you when it is ok to finally commit the transaction:

-- <initial handshaking stuff>
START TRANSACTION;
-- <Execute a bunch of SQL statements>
-- <Inform master of readiness to commit>
-- <Time passes... background transactions happening while we wait>
-- <Receive approval to commit from master (finally!)>
COMMIT;

如果您的代码到达最后的 COMMIT 语句并将其发送到您的 DBMS，您是否会在该语句中遇到错误(唯一性问题、数据库已满等)?什么错误?为什么?它们如何出现?它是否因您运行的 DBMS 而异?

If your code gets to that final COMMIT statement and sends it to your DBMS, can you ever get an error (uniqueness issue, database full, etc) at that statement? What errors? Why? How do they appear? Does it vary depending on what DBMS you run?

推荐答案

COMMIT 可能会失败.您可能有足够的资源来记录您希望进行的所有更改，但缺乏实际实施更改的资源.

COMMIT may fail. You might have had sufficent resources to log all the changes you wished to make, but lack resources to actually implement the changes.

这还没有考虑可能失败的其他原因:

And that's not considering other reasons it might fail:

1. 更改本身可能不符合数据库的约束.

1. The change itself might not fit the constraints of the database.

断电会导致事情无法完成.

Power loss stops things from completing.

请求的选择并发级别可能不允许更新(例如，游标更新修改后的表).

The level of requested selection concurrency might disallow an update (cursors updating a modified table, for example).

提交可能会超时或连接因饥饿问题而超时.

The commit might time out or be on a connection which times out due to starvation issues.

客户端和数据库之间的网络连接可能会丢失.

The network connection between the client and the database may be lost.

以及所有其他简单"的原因，这些原因不在我的考虑范围之内.

And all the other "simple" reasons that aren't on the top of my head.

这篇关于COMMIT 语句(在 SQL 中)会失败吗?如何?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！