MySQL Bugs: #98473: group replication will be block after lock table

Bug #98473	group replication will be block after lock table
Submitted:	4 Feb 2020 4:18	Modified:	2 Mar 2020 10:18
Reporter:	phoenix Zhang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Group Replication	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	group replication

Description:
For multiple primary mode of group replication cluster, if do LOCK TABLES ... WRITE in one node, and do insert in other node, both nodes will block.

How to repeat:
First, i build an mgr with 3 nodes. like below.

mysql> SELECT * FROM `performance_schema`.`replication_group_members`;
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 5592aa0d-4703-11ea-9f7e-ec5c6826bca3 | 127.0.0.1   |       13002 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | 559c0644-4703-11ea-a732-ec5c6826bca3 | 127.0.0.1   |       13000 | ONLINE       | PRIMARY     | 8.0.18         |
| group_replication_applier | 55a14e7c-4703-11ea-9e85-ec5c6826bca3 | 127.0.0.1   |       13001 | ONLINE       | PRIMARY     | 8.0.18         |
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+

1. connect to 13000 port, create table and lock it
mysql> create table t1 (c1 int primary key, c2 int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t1 values (1,1);
Query OK, 1 row affected (0.02 sec)

mysql> lock tables t1 write;
Query OK, 0 rows affected (0.00 sec)

2. connect to 13001, do insert, it will not return
mysql> insert into t1 values (2,2);

3. back to the 13000 connect, try start an transaction, and it will not return
mysql> begin;

4. open another connection of 13000, check the state, find Executing hook on transaction begin.
mysql> show processlist;
+-----+-----------------+-----------------+------+---------+------+--------------------------------------+----------------------------------+-----------+---------------+
| Id  | User            | Host            | db   | Command | Time | State                                | Info                             | Rows_sent | Rows_examined |
+-----+-----------------+-----------------+------+---------+------+--------------------------------------+----------------------------------+-----------+---------------+
|   4 | event_scheduler | localhost       | NULL | Daemon  |  260 | Waiting on empty queue               | NULL                             |         0 |             0 |
|   9 | root            | localhost       | test | Sleep   |  259 |                                      | NULL                             |         1 |             1 |
|  10 | root            | localhost:56696 | test | Sleep   |  254 |                                      | NULL                             |         1 |             1 |
|  11 | root            | localhost:56714 | test | Sleep   |  258 |                                      | NULL                             |         0 |             0 |
|  13 | system user     |                 | NULL | Connect |  258 | waiting for handler commit           | Group replication applier module |         0 |             0 |
|  16 | system user     |                 | NULL | Query   |  176 | Waiting for table metadata lock      | NULL                             |         0 |             0 |
|  18 | root            | localhost:56736 | test | Query   |  172 | Executing hook on transaction begin. | begin                            |         0 |             0 |
| 104 | root            | localhost:58612 | test | Query   |    0 | starting                             | show processlist                 |         0 |             0 |
+-----+-----------------+-----------------+------+---------+------+--------------------------------------+----------------------------------+-----------+---------------+

Hi,

Thank you for the report. This does look like a bug.

all best
Bogdan

Posted by developer:
 
Hi,

Thank you for the bug report, can you please add your servers configuration?

Best regards,
Nuno Carvalho

Hi Zhang,

Do you have "enforce-gtid-consistency" in your config?

thanks
Bogdan

This is the config file of port of 13000

Attachment: my13000.cnf (application/octet-stream, text), 1.04 KiB.

This is the config file of port of 13001

Attachment: my13001.cnf (application/octet-stream, text), 1.04 KiB.

This is the config file of port of 13002

Attachment: my13002.cnf (application/octet-stream, text), 1.04 KiB.

Posted by developer:
 
Hi Zhang,

You have
  group_replication_consistency= BEFORE_AND_AFTER
on your configuration.
Thence the observed behaviour is the expected one.

When you execute:
  server1: insert into t1 values (1,1);
  server1: lock tables t1 write;
  server2: insert into t1 values (2,2);
Apply on server1 will be blocked by the LOCK TABLE.
No other transaction can be committed until this one is complete.
Otherwise we cannot guarantee "A RW transaction waits for 1) all preceding transactions to complete before being applied and 2) until its changes have been applied on other members."

https://dev.mysql.com/doc/refman/8.0/en/group-replication-consistency-guarantees.html
https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html#sysvar_group_replic...

If you rely need to do the LOCK TABLE, then you should change the consistency guarantee to BEFORE, that way, only server1 will be impacted by it.
BEFORE consistency only impacts the local server, it does not wait for others servers commit.

Best regards,
Nuno Carvalho