[MDEV-32902] Autocommit=0 Retains Locks for Read-Only Statements Created: 2023-11-28  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Locking, Replication
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Brandon Nesterenko Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 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.
Generated at Thu Feb 08 10:34:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.