[MDEV-32652] Commit/rollback must emit an error when not inside active transaction Created: 2023-11-01  Updated: 2024-01-22

Status: Open
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Major
Reporter: Michael Assignee: Unassigned
Resolution: Unresolved Votes: 1
Labels: None


 Description   

All commands aplicable in an active transaction only, ie. commit/rollback/savepoint/release savepoint, must emit an error if they are executed in a connection without active transaction.

Also start transaction should emit an error if there is an active transaction already.

repro: https://dbfiddle.uk/ipMQHAT7

 Comments   
Comment by Kristian Nielsen [ 2023-11-01 ]

That's not really possible to implement by default without severely breaking backwards compatibility.
I wonder what the SQL standard says is the correct behaviour, if it is covered in there?

It might be possible to implement a non-default SQL_MODE or similar that would enable this behaviour, if deemed sufficiently important.

Comment by Sergei Golubchik [ 2023-11-01 ]

According to the SQL Standard (2016), COMMIT and ROLLBACK must fail

1. If the current SQL-transaction is part of an encompassing transaction that is controlled by an agent other than the SQL-agent
2. If the current SQL-session has an atomic execution context

otherwise they don't. That is, they must not emit an error in MariaDB either.

But as for the START TRANSACTION statement, the standard says, indeed

If an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state — active SQL-transaction.

We might want to have the same behavior for compatibility reasons.

Comment by Tingyao Nian [ 2023-12-06 ]

I can work on this. I think it's a fair point that we should make "start transaction" to emit errors. The current behavior is:
https://mariadb.com/kb/en/start-transaction/

START TRANSACTION and SET autocommit = 1 implicitly commit the current transaction, if any.

However this will break backward compatibility. The best way I believe is to add "NO_IMPLICIT_COMMIT" to SQL_MODE as @sergei suggested.

Comment by Tingyao Nian [ 2024-01-22 ]

PR: https://github.com/MariaDB/server/pull/2965

Generated at Thu Feb 08 10:32:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.