[MDEV-8451] SAVEPOINT does not set @@in_transaction Created: 2015-07-11  Updated: 2015-07-17  Resolved: 2015-07-17

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 5.3.12, 5.5, 10.0, 10.1
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Federico Razzoli Assignee: Sergei Golubchik
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

MariaDB [test]> SET SESSION autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> SAVEPOINT x;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> SELECT @@in_transaction;
+------------------+
| @@in_transaction |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

In this case, SAVEPOINT did nothing. I think that one of the following alternatives should be implemented:

1) Implicitly start a transaction, and then create the savepoint.
2) Issue an error.
3) Like 1, but also issue a warning because there is probably a bug in an application.



 Comments   
Comment by Federico Razzoli [ 2015-07-11 ]

Note that 1 would be very useful for stored procedures in a reusable context. You may want to enclose a procedure in a transaction. But you don't know if the user wants to execute it as part of another transaction, or if a transaction is already active.

Comment by Elena Stepanova [ 2015-07-13 ]

I couldn't find any indication in MySQL manual or MariaDB documentation whether it should be one way or another: neither about SAVEPOINT officially starting/not starting a transaction, nor about conditions when @@in_transaction is supposed to be set. However, I think semantically the complaint is valid. First, a savepoint by its nature belongs to transaction, so if it exists, it should be inside a transaction. Second, the following looks inconsistent:

MariaDB [test]> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> select @@in_transaction;
+------------------+
| @@in_transaction |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
 
MariaDB [test]> savepoint sp;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> select @@in_transaction;
+------------------+
| @@in_transaction |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
 
MariaDB [test]> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
 
MariaDB [test]> select @@in_transaction;
+------------------+
| @@in_transaction |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
 
MariaDB [test]> rollback to savepoint sp;
Query OK, 0 rows affected (0.02 sec)
 
MariaDB [test]> select @@in_transaction;
+------------------+
| @@in_transaction |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
 
MariaDB [test]> rollback;
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [test]> select @@in_transaction;
+------------------+
| @@in_transaction |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

If after rolling back to savepoint we are still in the transaction, we should probably be in it right after creating the savepoint.

Comment by Elena Stepanova [ 2015-07-13 ]

f_razzoli,

That said, I think the description is not quite accurate (or maybe I misunderstood your point). It's not like SAVEPOINT does nothing.
The SAVEPOINT x statement did what it usually does – created the savepoint, to which you could roll back later if you had any transactional changes. What it did not do is set in_transaction to TRUE.

Comment by Federico Razzoli [ 2015-07-13 ]

True - I trusted @in_transaction, and for this reason I thought that SAVEPOINT in the example did nothing. I was wrong. But then, the bug seems to be in @in_transaction. Or, if it is not a bug, this confusing behavior should be well documented: under which conditions should I trust @in_transaction?

Comment by Elena Stepanova [ 2015-07-13 ]

It looks like a bug to me, but lets see what serg says.
I've changed the summary a bit, please feel free to edit it further as you see fit.

Comment by Sergei Golubchik [ 2015-07-17 ]

This is intentional. A <savepoint statement> is an SQL-transaction statement (See SQL Standard 2003, Part 2 Foundation, 4.33.2.4 SQL-transaction statements). Later in 4.33.4 (SQL-statements and transaction states), it is written that

The following SQL-statements are not transaction-initiating SQL-statements, i.e., if there is no current SQL-transaction, and a statement of this class is executed, no SQL-transaction is initiated.
— All SQL-transaction statements except <start transaction statement>s and <commit statement>s and <rollback statement>s that specify AND CHAIN.

In other words, SAVEPOINT should not start a transaction.

Generated at Thu Feb 08 07:27:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.