当 BEGIN 在自动提交模式下的连接上运行时，Postgres 会做什么？
我试图在使用 Postgres (psycopg) 连接时更好地理解“自动提交”的概念。假设我有一个新连接，将其隔离级别设置为 ISOLATION_LEVEL_AUTOCOMMIT，然后直接运行此 SQL，而不使用游标开始/回滚方法（作为练习；并不是说我实际上想这样做）：
INSERT A
INSERT B
BEGIN
INSERT C
INSERT D
ROLLBACK
INSERTs C & 会发生什么; D？
自动提交纯粹是 psycopg 中的一个内部设置，会影响它发出 BEGIN 的方式吗？在这种情况下，上面的SQL不受影响；插入 A 和B 完成后立即提交，而 C 和 C 则在完成后立即提交。 D 在事务中运行并回滚。该事务在什么隔离级别下运行？
或者自动提交是连接本身的真实设置吗？那么，它如何影响 BEGIN 的处理呢？它是被忽略，还是覆盖自动提交设置以实际启动事务？该事务在什么隔离级别下运行？
还是我完全偏离了目标？
I'm trying to better understand the concept of 'autocommit' when working with a Postgres (psycopg) connection. Let's say I have a fresh connection, set its isolation level to ISOLATION_LEVEL_AUTOCOMMIT, then run this SQL directly, without using the cursor begin/rollback methods (as an exercise; not saying I actually want to do this):
INSERT A
INSERT B
BEGIN
INSERT C
INSERT D
ROLLBACK
What happens to INSERTs C & D?
Is autocommit is purely an internal setting in psycopg that affects how it issues BEGINs? In that case, the above SQL is unafected; INSERTs A & B are committed as soon as they're done, while C & D are run in a transaction and rolled back. What isolation level is that transaction run under?
Or is autocommit a real setting on the connection itself? In that case, how does it affect the handling of BEGIN? Is it ignored, or does it override the autocommit setting to actually start a transaction? What isolation level is that transaction run under?
Or am I completely off-target?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
自动提交模式意味着每个语句隐式地开始和结束事务。
在您的情况下，如果自动提交关闭：
BEGIN将发出警告，表示事务已启动ROLLBACK将回滚所有四个语句当自动提交打开时，仅回滚
c和d。请注意，自
8.0以来，PostgreSQL没有内部AUTOCOMMIT行为：所有自动提交功能都依赖于客户端。Autocommit mode means that each statement implicitly begins and ends the transaction.
In your case, if autocommit is off:
BEGINwill issue a warning saying that the transaction is already startedROLLBACKwill roll back all four statementsWhen autocommit is on, only the
canddare rolled back.Note that
PostgreSQLhas no internalAUTOCOMMITbehavior since8.0: all autocommit features are relied upon the clients.默认情况下，PostgreSQL 启用自动提交，这意味着每个语句都作为一个事务处理。如果您明确告诉它启动事务（如您的示例所示），那么这些项目将位于新事务中。
在您的示例中，A 和 B 将被提交，C 和 D 将被回滚。
By default, PostgreSQL has autocommit on, meaning that each statement is handled as a transaction. If you explicitly tell it to start a transaction, as in your example, those items are in a new transaction.
In your example, A and B would be committed, C and D would be rolled back.
当启用自动提交时，psycopg 只会将所有内容发送到 PostgreSQL 后端，而不会尝试为您管理事务。如果您不使用 BEGIN/COMMIT/ROLLBACK，则每个 .execute() 调用都会立即执行并提交。您可以通过发出 BEGIN/COMMIT/ROLLBACK 命令来进行自己的事务管理。显然，在自动提交模式下，您不能调用 conn.commit() 或 conn.rollback()，因为 psycopg 不会跟踪事务，而只是将您 .execute() 直接发送到后端的任何内容。
在您的示例中，A 和 B 将被提交，C 和 D 将被回滚。
When autocommit is on psycopg just sends everything to the PostgreSQL backend without trying to manage the transaction for you. If you don't use BEGIN/COMMIT/ROLLBACK then every .execute() call is immediately executed and committed. You can do your own transaction management by issuing BEGIN/COMMIT/ROLLBACK commands. Obviously in autocommit mode you can't call conn.commit() or conn.rollback() because psycopg is not keeping track of the transactions but just sending anything you .execute() straight to the backend.
In your example A and B would be committed, C and D would be rolled back.