Group Replication は、マルチマスターのレプリケーション構成を行うためのMySQLの機能で、5.7.17からプラグインとして提供され利用可能となっています。
Group Replication ではレプリケーションを構成する各MySQLノードに書き込みができる Mulit Primary 構成と、レプリケーションを構成するいずれか１つのノードにのみ書き込みができる Single Primary 構成があります。 Single Primaryでは特定のノードにしか書き込みができないのでノードをまたぐロックについては気にする必要がありませんが、Multi-Primaryでは複数のノードから書き込みが行われるため、ノードをまたぐロックも気にしなければなりません。
しかし、Group Replication で Writeが行われた時のノード間の整合性チェックのプロセスにはこれらのロックについては考慮されていません。 異なるノードでのトランザクションによる更新処理は正常に実行され、楽観的ロックにより COMMIT 後にチェックが行われて不整合が生じた場合にエラーが返されるようです。
確認
準備
まずMulti Primary モードで Group Replication を開始しておきます。
root@mysql1[(none)] > SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ | group_replication_applier | 5c592b4a-961c-11e7-9da1-0204e4dfe4d8 | 192.168.10.65 | 3306 | ONLINE | | group_replication_applier | 6da540cb-961c-11e7-86b4-0204e4dfe4d8 | 192.168.10.66 | 3306 | ONLINE | | group_replication_applier | 8883dc93-961c-11e7-8438-0204e4dfe4d8 | 192.168.10.67 | 3306 | ONLINE | +---------------------------+--------------------------------------+---------------+-------------+--------------+ 3 rows in set (0.00 sec) root@mysql01[testdb] > show variables like "%group_replication_single_primary_mode%"; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | group_replication_single_primary_mode | OFF | +---------------------------------------+-------+ 1 row in set (0.00 sec)
テストように以下のようなテーブルを準備し、適当にデータを入れておきます。
root@mysql1[testdb] > desc user; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | user_id | int(11) | NO | PRI | NULL | auto_increment | | user_name | varchar(45) | YES | | NULL | | | user_age | int(11) | YES | | NULL | | | created_at | datetime | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) root@mysql1[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 28 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2016-03-26 23:28:43 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
このトランザクション分離レベルはMySQLのデフォルトです。
root@mysql01[(none)] > SELECT @@GLOBAL.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
このトランザクション分離レベルでは、あるトランザクションAで参照できる値はそのトランザクションAが開始された時点の値のままとなり、トランザクションAがコミットされるまで変わりません。別のトランザクションBが値を変更したとしてもそれによって結果に変化はありません(ここでトランザクションAがトランザクションBと同じカラムを更新しようとした場合ロックが発生します)。
とりあえずこれで準備完了です。
排他ロック
排他ロックについて確認してみます。
まずはノード1でトランザクション(tx1)を開始し、SELECT ... FOR UPDATEによる特定の行に排他ロックをかけます。
root@mysql1[testdb] > start transaction; Query OK, 0 rows affected (0.00 sec) root@mysql1[testdb] > select * from user where user_id='2' for update; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 28 | 2016-06-08 18:00:35 | +---------+-------------+----------+---------------------+ 1 row in set (0.01 sec)
続いて、ノード2でトランザクション(tx2)を開始し、SELECT ... FOR UPDATEで同じ行に排他ロックをかけます。 通常は、ここでトランザクションがブロックされるはずですが、ノードをまたいだ排他ロックをブロックすることができないため、そのまま通ってしまいます。
root@mysql2[testdb] > start transaction; Query OK, 0 rows affected (0.00 sec) root@mysql2[testdb] > select * from user where user_id='2' for update; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 28 | 2016-06-08 18:00:35 | +---------+-------------+----------+---------------------+ 1 row in set (0.00 sec)
ノード１で実行したトランザクション(tx1)で行を更新します。
root@mysql1[testdb] > update user set user_age = '30' where user_id = '2' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@mysql1[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 30 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2016-03-26 23:28:43 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
ノード2で実行したトランザクション(tx2)で行を更新します。
root@mysql2[testdb] Fri Aug 10 21:16:10 2017 > update user set user_age = '35' where user_id = '2' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@mysql1[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 35 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2016-03-26 23:28:43 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
ノード1のトランザクションをコミットすると成功します。
root@mysql1[testdb] > commit; Query OK, 0 rows affected (0.01 sec)
ノード2のトランザクションをコミットすると失敗します。
root@mysql2[testdb] > commit; ERROR 1180 (HY000): Got error 149 during COMMIT
結果をみてみます。
root@mysql1[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 30 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2016-03-26 23:28:43 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
root@mysql2[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 30 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2016-03-26 23:28:43 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
root@mysql3[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 30 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2016-03-26 23:28:43 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
ノードをまたいだ排他ロックのトランザクションはブロックされず、最初にコミットされた方は成功し、後にコミットされた方はデッドロックになります。
共有ロック
共有ロックについて確認してみます。
まずはノード1でトランザクション(tx1)を開始し、SELECT ... LOCK IN SHARE MODE による特定の行に排他ロックをかけます。
root@mysql01[testdb] > start transaction; Query OK, 0 rows affected (0.00 sec) root@mysql01[testdb] > select * from user where user_id='30' LOCK IN SHARE MODE; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 30 | TEST USER05 | 57 | 2016-03-26 23:28:43 | +---------+-------------+----------+---------------------+ 1 row in set (0.00 sec)
ノード2でトランザクション(tx2)を開始し、SELECT ... LOCK IN SHARE MODE による特定の行に排他ロックをかけます。共有ロックなのでこれは成功します。
root@mysql02[testdb] > start transaction; Query OK, 0 rows affected (0.00 sec) root@mysql02[testdb] > select * from user where user_id='30' LOCK IN SHARE MODE; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 30 | TEST USER05 | 57 | 2016-03-26 23:28:43 | +---------+-------------+----------+---------------------+ 1 row in set (0.00 sec)
ノード1でアップデートを実行します。 通常は、ここでトランザクションがブロックされるはずですが、ノードをまたいだ共有ロックをブロックすることができないため、そのまま通ってしまいます。
root@mysql01[testdb] > update user set created_at = '2013-11-23 09:30:29' where user_id = '30' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
ノード2でアップデートを実行します。これも通ってしまいます。
root@mysql02[testdb] > update user set created_at = '2013-09-10 22:24:21' where user_id = '30' ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
ノード１でコミットを実行します。
root@mysql01[testdb] > commit; Query OK, 0 rows affected (0.00 sec)
ノード2でコミットを実行します。後からコミットした方はエラーになります。
root@mysql02[testdb] > commit; ERROR 1180 (HY000): Got error 149 during COMMIT
root@mysql1[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 30 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2013-11-23 09:30:29 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
root@mysql2[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 30 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2013-11-23 09:30:29 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
root@mysql3[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 30 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2013-11-23 09:30:29 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
ノードをまたいだ共有ロックのトランザクションもブロックされず、最初にコミットされた方は成功し、後にコミットされた方はデッドロックになります。
テーブルロック
テーブルロックについて確認してみます。 まずノード1でテーブルロックをかけます。
root@mysql01[testdb] > lock tables user read;
Query OK, 0 rows affected (0.00 sec)
root@mysql01[testdb] > insert into user (user_name, user_age, created_at) values ('TEST USER06', 33 ,'2014-08-12 00:32:21');
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
ノード２で insert を行うと、通常ブロックされるはずですが、正常に実行されてしまいます。
root@mysql02[testdb] > insert into user (user_name, user_age, created_at) values ('TEST USER06', 33 ,'2014-08-12 00:32:21');
Query OK, 1 row affected (0.01 sec)
root@mysql02[testdb] > select * from user;
+---------+-------------+----------+---------------------+
| user_id | user_name | user_age | created_at |
+---------+-------------+----------+---------------------+
| 2 | TEST USER01 | 28 | 2016-06-08 18:00:35 |
| 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 |
| 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 |
| 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 |
| 30 | TEST USER05 | 57 | 2013-11-23 09:30:29 |
| 37 | TEST USER06 | 33 | 2014-08-12 00:32:21 |
+---------+-------------+----------+---------------------+
6 rows in set (0.00 sec)
ノード1はまだテーブルロック取得中のため、この更新の内容は見えません。
root@mysql01[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 28 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2013-11-23 09:30:29 | +---------+-------------+----------+---------------------+ 5 rows in set (0.00 sec)
ロックを解放した時点で見えるようになります。
root@mysql01[testdb] > unlock tables; Query OK, 0 rows affected (0.00 sec) root@mysql01[testdb] > select * from user; +---------+-------------+----------+---------------------+ | user_id | user_name | user_age | created_at | +---------+-------------+----------+---------------------+ | 2 | TEST USER01 | 28 | 2016-06-08 18:00:35 | | 9 | TEST USER02 | 7 | 2017-04-27 18:41:06 | | 16 | TEST USER03 | 75 | 2015-10-03 06:17:01 | | 23 | TEST USER04 | 24 | 2017-02-18 10:49:58 | | 30 | TEST USER05 | 57 | 2013-11-23 09:30:29 | | 37 | TEST USER06 | 33 | 2014-08-12 00:32:21 | +---------+-------------+----------+---------------------+ 6 rows in set (0.00 sec)
Group Replicationではノードをまたぐテーブルロックがサポートされないため、あるノードでテーブルロックを行っていても別ノードからカラムのinsertができてしまいました。
まとめ
Multi Master な MySQL Group Replication で複数のノードからトランザクションを用いた書き込みを行った場合、楽観的ロックにより先にコミットされた方が優先され、後からコミットされた方はコミット時点でデッドロックとなってしまう動作を簡単に確認しました。 コミット後のデッドロックについてはアプリケーション側での処理が必要になるため、これを許容しない場合には、Single Primaryでの運用にした方が良さそうです。
