Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Critical
-
Resolution: Unresolved
-
N/A
-
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.
Attachments
Issue Links
- relates to
-
-
- In Review
-