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

Create sequence causing implicit commit

Details

    Description

      CREATE SEQUENCE and
      CREATE TEMPORARY SEQUENCE
      causes implicit commit to happen. This is not documented, and possibly the intention was not trigger implicit committing. To reproduce, try e.g.:

      MariaDB [test]> create table t1 (i int primary key) engine=innodb;
      Query OK, 0 rows affected (0,062 sec)
       
      MariaDB [test]> begin;
      Query OK, 0 rows affected (0,004 sec)
       
      MariaDB [test]> insert into t1 values (1);
      Query OK, 1 row affected (0,042 sec)
       
      MariaDB [test]> create sequence s1 NOCACHE engine=innodb;
      Query OK, 0 rows affected (0,069 sec)
       
      MariaDB [test]> rollback;
      Query OK, 0 rows affected (0,003 sec)
       
      MariaDB [test]> select * from t1;
      +---+
      | i |
      +---+
      | 1 |
      +---+
      
      

      Same happens if the sequence is created like:
      create temporary sequence s1 NOCACHE engine=innodb;

      DBUG traces show that for "create sequence" execution, in mysql_execute_command() the test for implicit commit is carried out by: stmt_causes_implicit_commit(), which returns true and the implicit commit follows. So it looks like the implicit commit was done on purpose for this case. But then, "create sequence" statement should be added to the list of statements causing implicit commit.
      otoh, for "create temporary sequence", the stmt_causes_implicit_commit() returns false, and implicit commit is skipped in mysql_execute_command(). However, in later execution traces show that:

      T@19   : | | | | | | >sequence_insert
      T@19   : | | | | | | | >handler::ha_write_row
      T@19   : | | | | | | | | >ha_sequence::write_row
      T@19   : | | | | | | | | | >ha_innobase::write_row
      T@19   : | | | | | | | >trans_commit_implicit
      

      In sequence_insert() implicit commit is called unconditionally. This looks like a bug.

      Attachments

        Activity

          seppo Seppo Jaakola created issue -
          danblack Daniel Black added a comment -

          Most DDL is an implicit commit - https://mariadb.com/kb/en/sql-statements-that-cause-an-implicit-commit/

          I just added sequences to that page.

          danblack Daniel Black added a comment - Most DDL is an implicit commit - https://mariadb.com/kb/en/sql-statements-that-cause-an-implicit-commit/ I just added sequences to that page.
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Fix Version/s N/A [ 14700 ]
          greenman Ian Gilfillan made changes -
          Resolution Fixed [ 1 ]
          Status Open [ 1 ] Closed [ 6 ]

          People

            greenman Ian Gilfillan
            seppo Seppo Jaakola
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.