[evanelias]

If I'm reading this correctly, it sounds like the connection was already using autocommit by default? In that situation, if you initiate a transaction, and then it gets rolled back, you're back in autocommit unless/until you initiate another transaction.

If so, that part is all totally normal and expected. It's just that due to a bug in the Python client library (16 years ago), the rollback was happening silently because the error was not surfaced properly by the client library.

[yencabulator]

Is there any scenario in a sane world where a transaction ceases to be in scope just because it went into an error state? I'd have expect the client to send an explicit ROLLBACK when they realize a transaction is in an error state, not for the server to end it and just notify the client. This is how psql appears to the end user.

  postgres=# begin;
  BEGIN
  postgres=*# bork;
  ERROR:  syntax error at or near "bork"
  LINE 1: bork;
          ^
  postgres=!# select 1;
  ERROR:  current transaction is aborted, commands ignored until end of transaction block
  postgres=!# rollback;
  ROLLBACK
  postgres=# select 1;
   ?column?
  ----------
          1
  (1 row)
  
  postgres=#

[evanelias]

Every DBMS handles errors slightly differently. In a sane world you shouldn't ever ignore errors from the database. It's unfortunate to hear that a Python MySQL client library had a bug that failed to expose errors properly in one specific situation 16 years ago, but that's not terribly relevant to today.

Postgres behavior with errors isn't even necessarily desirable -- in terms of ergonomics, why should every typo in an interactive session require me to start my transaction over from scratch?

[yencabulator]

> why should every typo in an interactive session require me to start my transaction over from scratch?

That part would hold even for the MySQL auto-rollback implied above.

[evanelias]

No, the situation described upthread is about a deadlock error, not a typo. In MySQL, syntax errors throw a statement-level error but it does not affect the transaction state in any way. If you were in a transaction, you're still in a transaction after a typo.

With deadlocks in MySQL, the error you receive is "Deadlock found when trying to get lock; try restarting transaction" i.e. it specifically tells you the transaction needs to start over in that situation.

In programmatic contexts, transactions are typically represented by an object/struct type, and a correctly-implemented database driver for MySQL handles this properly and invalidates the transaction object as appropriate if the error dictates it. So this isn't really even a common foot-gun in practical terms.

[o11c]

I would argue that it's a bug for it even to be possible to autocommit.

[evanelias]

What do you mean? Autocommit mode is the default mode in Postgres and MS SQL Server as well. This is by no means a MySQL-specific behavior!

When you're in autocommit mode, BEGIN starts an explicit transaction, but after that transaction (either COMMIT or ROLLBACK), you return to autocommit mode.

The situation being described upthread is a case where a transaction was started, and then rolled back by the server due to deadlock error. So it's totally normal that you're back in autocommit mode after the rollback. Most DBMS handle this identically.

The bug described was entirely in the client library failing to surface the deadlock error. There's simply no autocommit-related bug as it was described.

[o11c]

Yes, and most DBMS's are full of historical mistakes.

In a sane world, statements outside `BEGIN` would be an unconditional error.

[evanelias]

Lack of autocommit would be bad for performance at scale, since it would add latency to every single query. And the MVCC implications are non-trivial, especially for interactive queries (human taking their time typing) while using REPEATABLE READ isolation or stronger... every interactive query would effectively disrupt purge/vacuum until the user commits. And as the sibling comment noted, that would be quite harmful if the user completely forgets to commit, which is common.

In any case, that's a subjective opinion on database design, not a bug. Anyway it's fairly tangential to the client library bug described up-thread.

[grogers]

Autocommit mode is pretty handy for ad-hoc queries at least. You wouldn't want to have to remember to close the transaction since keeping a transaction open is often really bad for the DB