Bug #80160 slave_master_info is locked with SERIALIZABLE

• Submitted: 26 Jan 2016 18:21
• Modified: 28 Nov 2016 6:48
• Reporter: Andrii Nikitin
• Status: Not a Bug
• Category: MySQL Server: Replication
• Severity: S3 (Non-critical)
• Version: 5.6.27
• OS: Any
• CPU Architecture: Any

[26 Jan 2016 18:21] Andrii Nikitin

Description:
STOP SLAVE hangs up to innodb_lock_wait_timeout when server has tx_isolation SERIALIZABLE and doesn't update slave_master_info

I suspect more potential danger is in this problem.

How to repeat:
Make sure Slave has configuration:

relay_log_recovery = ON 
sync_relay_log = 1
master-info-repository=table
relay-log-info-repository=table

transaction_isolation=serializable may be configured at global level or presumably used by some client which has access to mysql.slave_master_info

# slave thread1:

start transaction;
set tx_isolation='serializable'; // if it is not set at global level

select * from mysql.slave_master_info;

# make changes on master

# slave thread2:
stop slave;

Observe slave thread2: is hanging for innodb_lock_wait_timeout . slave_master_info is not updated.

Suggested fix:
Not sure. slave_master_info is system table, so reads shouldn't block other operations even with highest tx_isolation

[28 Nov 2016 6:48] Thirunarayanan Balathandayuthapani

Isolation level applies to internal system tables as well.  In serializable
isolation level, Select on internal system tables (such as slave_master_info, InnoDB persistent statistics table etc.), would acquire shared lock on the
records, thus blocks any DDL that affects these tables/records.

So closing this bug as "not a bug".