Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
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
Attachments
Issue Links
- relates to
-
MDEV-35335 implicit commit at START TRANSACTION doesn't reset characteristics
-
- Closed
-
- links to
Activity
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.
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.
MDEV-35335 is an example of what could happen with the duplicated BEGIN or START TRANSACTION.
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.