Description:
Select for update statements are threaded as local operation. Even if those statements work perfectly with asynchronous replication, with Group Replication it might be different depending of the "primary mode" used.
In single-primary mode there is no problem but in multi-primary as the lock is not shared across the members of the group, the expectation for such statement might not be reached (the extended lockset is not encoded in the replication writeset).
We should make this clear in the documentation.
How to repeat:
On a single instance:
session1 mysql2> start transaction;
Query OK, 0 rows affected (0.00 sec)
session1 mysql2> select id from sbtest1 where id < 10 for update;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+----+
9 rows in set (0.00 sec)
session2 mysql2> update sbtest1 set k = 49830 where id =8;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
On a multi-primary group:
mysql3> show global variables like 'group%primary_mode';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | OFF |
+---------------------------------------+-------+
1 row in set (0.00 sec)
mysql2> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql2> select id from sbtest1 where id < 10 for update;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+----+
9 rows in set (0.00 sec)
myyql3> update sbtest1 set k = 49830 where id =8;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql2> update sbtest1 set k=k+1 where id<10;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Suggested fix:
Add a point in the GR limitations or in the locks
