Bug #98473 group replication will be block after lock table
Submitted: 4 Feb 4:18 Modified: 2 Mar 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

[4 Feb 4:18] phoenix Zhang
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 |
+-----+-----------------+-----------------+------+---------+------+--------------------------------------+----------------------------------+-----------+---------------+
[26 Feb 5:10] MySQL Verification Team
Hi,

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

all best
Bogdan
[28 Feb 11:22] Nuno Carvalho
Posted by developer:
 
Hi,

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

Best regards,
Nuno Carvalho
[1 Mar 22:31] MySQL Verification Team
Hi Zhang,

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

thanks
Bogdan
[2 Mar 0:44] phoenix Zhang
This is the config file of port of 13000

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

[2 Mar 0:44] phoenix Zhang
This is the config file of port of 13001

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

[2 Mar 0:45] phoenix Zhang
This is the config file of port of 13002

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

[2 Mar 10:18] Nuno Carvalho
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