Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8451

SAVEPOINT does not set @@in_transaction

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Not a Bug
    • Affects Version/s: 5.3.12, 10.1, 10.0, 5.5
    • Fix Version/s: N/A
    • Component/s: OTHER
    • 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.

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            f_razzoli Federico Razzoli added a comment -

            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.

            Show
            f_razzoli Federico Razzoli added a comment - 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.
            Hide
            elenst Elena Stepanova added a comment -

            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.

            Show
            elenst Elena Stepanova added a comment - 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.
            Hide
            elenst Elena Stepanova added a comment -

            Federico 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.

            Show
            elenst Elena Stepanova added a comment - Federico 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.
            Hide
            f_razzoli Federico Razzoli added a comment -

            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?

            Show
            f_razzoli Federico Razzoli added a comment - 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?
            Hide
            elenst Elena Stepanova added a comment -

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

            Show
            elenst Elena Stepanova added a comment - It looks like a bug to me, but lets see what Sergei Golubchik says. I've changed the summary a bit, please feel free to edit it further as you see fit.
            Hide
            serg Sergei Golubchik added a comment -

            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.

            Show
            serg Sergei Golubchik added a comment - 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.

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                f_razzoli Federico Razzoli
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: