MySQLのロック周りの動作確認(メンバーは48人までとかアプリレベルで制約かけようとしたらテーブルロックで死んだ・・・)

mysql_lock.txt

↓ターミナル１                                                               ↓ターミナル２
------------------------------------------------------------------------+--------------------------------------------------------------------------
mysql> select * from teams;
+----+------------+
| id | name       |
+----+------------+
|  1 | チームH    |
|  2 | チームK    |
|  3 | チームT    |
+----+------------+
3 rows in set (0.00 sec)

mysql> select * from team_members;
+----+---------+-----------+
| id | team_id | member_id |
+----+---------+-----------+
|  1 |       1 |       101 |
|  2 |       1 |       102 |
|  3 |       2 |       203 |
+----+---------+-----------+
3 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from team_members where team_id=1 for update;
+----+---------+-----------+
| id | team_id | member_id |
+----+---------+-----------+
|  1 |       1 |       101 |
|  2 |       1 |       102 |
+----+---------+-----------+
2 rows in set (0.00 sec)

                                                                             mysql> insert into team_members (team_id, member_id) values (1, 104);
                                                                             # (↓ターミナル１でのコミットまでロックが掛かるが、IDの採番はこのタイミングで行われる)

mysql> insert into team_members (team_id, member_id) values (1, 105);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

                                                                             Query OK, 1 row affected (31.74 sec)


mysql> select * from team_members;
+----+---------+-----------+
| id | team_id | member_id |
+----+---------+-----------+
|  1 |       1 |       101 |
|  2 |       1 |       102 |
|  3 |       2 |       203 |
|  4 |       1 |       104 |
|  5 |       1 |       105 |
+----+---------+-----------+
5 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from team_members where team_id=1 for update;
+----+---------+-----------+
| id | team_id | member_id |
+----+---------+-----------+
|  1 |       1 |       101 |
|  2 |       1 |       102 |
|  4 |       1 |       104 |
|  5 |       1 |       105 |
+----+---------+-----------+
4 rows in set (0.00 sec)

                                                                             mysql> insert into team_members (team_id, member_id) values (2, 206);
                                                                             # (↓team_id=2のレコード追加でもロックが掛かる。行ロックではなくテーブルロックのよう)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

                                                                             Query OK, 1 row affected (5.96 sec)

mysql> select * from team_members;
+----+---------+-----------+
| id | team_id | member_id |
+----+---------+-----------+
|  1 |       1 |       101 |
|  2 |       1 |       102 |
|  3 |       2 |       203 |
|  4 |       1 |       104 |
|  5 |       1 |       105 |
|  6 |       2 |       206 |
+----+---------+-----------+
6 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from team_members where id=1 for update;
+----+---------+-----------+
| id | team_id | member_id |
+----+---------+-----------+
|  1 |       1 |       101 |
+----+---------+-----------+
1 row in set (0.00 sec)

                                                                             mysql> insert into team_members (team_id, member_id) values (3, 307);
                                                                             Query OK, 1 row affected (0.00 sec)
                                                                             # (一意に識別可能な条件で検索した場合は行ロックになる)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from team_members;
+----+---------+-----------+
| id | team_id | member_id |
+----+---------+-----------+
|  1 |       1 |       101 |
|  2 |       1 |       102 |
|  3 |       2 |       203 |
|  4 |       1 |       104 |
|  5 |       1 |       105 |
|  6 |       2 |       206 |
|  7 |       3 |       307 |
+----+---------+-----------+
7 rows in set (0.00 sec)

teams <- team_members -> members のような連関エンティティをRailsライクなフレームワーク(≒サロゲートキーで物理テーブル設計)で実現することを想定（membersの例示は割愛）