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

Autocommit=0 Retains Locks for Read-Only Statements

Details

    Description

      The documentation for the autocommit option mentions:

      autocommit influences implicit transactions. An implicit transaction is started when no transaction has been started and a SQL statement is issued which modifies a table.

      Along with the list of implicit-commit creating statements implies that a select statement should not retain its locks after execution; however, it does. See the following MTR test and output.

      Test:

      --source include/have_metadata_lock_info.inc
      		 
       
      		 
      create table t1 (a int);
      		 
      insert into t1 values (1);
      		 
       
      		 
       
      		 
      --echo #
      		 
      --echo # With autocommit=1, no locks exist after the select
      		 
      set session autocommit=1;
      		 
      select * from t1;
      		 
      select * from INFORMATION_SCHEMA.METADATA_LOCK_INFO;
      		 
       
      		 
      --echo #
      		 
      --echo # With autocommit=0, the lock exists
      		 
      set session autocommit=0;
      		 
      select * from t1;
      		 
      select * from INFORMATION_SCHEMA.METADATA_LOCK_INFO;
      		 
      show variables like 'in_transaction';
      		 
      COMMIT;
      		 
      select * from INFORMATION_SCHEMA.METADATA_LOCK_INFO;

      Output:

      create table t1 (a int);
      		 
      insert into t1 values (1);
      		 
      #
      		 
      # With autocommit=1, no locks exist after the select
      		 
      set session autocommit=1;
      		 
      select * from t1;
      		 
      a
      		 
      1
      		 
      select * from INFORMATION_SCHEMA.METADATA_LOCK_INFO;
      		 
      THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
      		 
      #
      		 
      # With autocommit=0, the lock exists
      		 
      set session autocommit=0;
      		 
      select * from t1;
      		 
      a
      		 
      1
      		 
      select * from INFORMATION_SCHEMA.METADATA_LOCK_INFO;
      		 
      THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME
      		 
      4	MDL_SHARED_READ	NULL	Table metadata lock	test	t1
      		 
      show variables like 'in_transaction';
      		 
      Variable_name	Value
      		 
      in_transaction	0
      		 
      COMMIT;
      		 
      select * from INFORMATION_SCHEMA.METADATA_LOCK_INFO;
      		 
      THREAD_ID	LOCK_MODE	LOCK_DURATION	LOCK_TYPE	TABLE_SCHEMA	TABLE_NAME

      If this is intended, there are inconsistencies both in the documentation (read-only behavior is not mentioned), and the behavior of the server itself (in_transaction reports 0, yet there are locks that exist that can only be cleared by a COMMIT/ROLLBACK).

      Though a more serious impact of this is in replication. Deadlocks have occurred for users with autocommit=0 because they run read-only queries on tables, that will block DDL events from replicating.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-33752 explain update ,.set..select freeze replica with autocommit=0

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled

          Activity

            Transition Time In Source Status Execution Times
            Sergei Golubchik made transition -
            Open Confirmed
            		132d 2h 9m 1

            People

              dbart Daniel Bartholomew
              bnestere Brandon Nesterenko
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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