MySQLには行単位のロックだけではなく、テーブル単位、インスタンス単位のLOCK 〇〇構文も実装されています。LOCK TABLES構文は以前からありましたが、LOCK INSTANCE FOR BACKUPはバージョン8.
なお、今回利用しているMySQLのバージョンは8.
LOCK TABLES
LOCK TABLESは、その名の通りテーブルをロックするときに用いる構文です。LOCK TABLE <table_で実行します。
lock_
lock_
― t1は READ、t2はWRITEでLOCK TABLESを実行する s1> LOCK TABLE t1 READ, t2 WRITE; Query OK, 0 rows affected (0.00 sec) ― 他のセッションであってもSELECTでデータの参照はできる s2> SELECT * FROM t1; +------+ | id | +------+ | 1 | | 1 | +------+ 2 rows in set (0.00 sec) ― t1へのINSERTはエラーが出力される s1> INSERT INTO t1 VALUES(1); ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated ― DROPもできない s1> DROP TABLE t1; ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated ― lock_typeがWRITEだとlock_wait_timeoutまで待機する s2> SELECT * FROM t2; （メタデータロック待ちになる） s1> > TRUNCATE TABLE t2; Query OK, 0 rows affected (0.02 sec) s1> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)
LOCKされたテーブルを解除するにはUNLOCK TABLESを実行します。UNLOCK TABLESはテーブル名を指定することができないため、実行すると全てのLOCKされたテーブルが解除されます。
lock_
LOCK TABLES構文には以下のような特徴があります。
- トランザクション内でまだコミットされていない場合、そのトランザクション内でLOCK TABLESを実行すると暗黙的にコミットが実行されます。
- LOCK TABLESを実行中にトランザクションを開始すると、そのセッション内は暗黙的にコミットされてテーブルロックも開放されます。
- LOCK TABLESでLOCKされたテーブルは、performance_
schemaのmetadata_ locksで確認することができます。
mysql> SELECT * FROM performance_schema.metadata_locks WHERE object_name in ('t1','t2');
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | lock_test | t1 | NULL | 139827022691216 | SHARED_READ_ONLY | TRANSACTION | GRANTED | sql_parse.cc:6094 | 308 | 28 |
| TABLE | lock_test | t2 | NULL | 139827026190080 | SHARED_NO_READ_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6094 | 308 | 28 |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
2 rows in set (0.00 sec)
関連するテーブルはどうなるのか？
Viewや Foreign Key制約のある親子テーブル、TRIGGERなど、他のテーブルと関連がある場合には関連して同様のロックが取得されるものもあります。
View内で利用されているテーブルをLOCKする
たとえば、view内(v1)で利用されているテーブルt1にLOCK TABLE t1 WRITEを実行した場合、v1も同様に参照することができません。
s1> CREATE VIEW v1 AS SELECT * FROM t1; Query OK, 0 rows affected (0.01 sec) s1> LOCK TABLE t1 WRITE; Query OK, 0 rows affected (0.00 sec) s2> SELECT * FROM v1; (メタデータロック待ちになる)
Foreign Keyで参照されている場合
Foreign Key制約がある場合で、もし親テーブル側にLOCK TABLES parent_を実行した場合は、子テーブルも同様のLOCKが取得されます。子テーブル側でLOCKを取得した場合には、親テーブルへのLOCKは取得されません。
s1> LOCK TABLES parent WRITE; Query OK, 0 rows affected (0.00 sec) s2> SELECT * FROM child; (これもメタデータロック待ちになる)
TRIGGERのあるテーブルでLOCK TABLESを実行した場合
TRIGGERが設定されているテーブルt1でLOCK TABLES trg1 WRITEを実行した場合は、Trigger内で利用されているテーブルも同様にlock_
また、TRIGGERを再作成したいときなどにLOCK TABLES t1 READで取得しても、TRIGGERを削除することができません。LOCK TABLESを実行した状態でTRIGGERを再作成する場合は、LOCK TABLES t1 WRITEでロックを取得する必要があります。
s1> CREATE TRIGGER trg_t1_insert AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 SET id = NEW.id * 2; Query OK, 0 rows affected (0.01 sec) s1> LOCK TABLES t1 WRITE; Query OK, 0 rows affected (0.00 sec) s2> SELECT * FROM t2; (これもメタデータロック待ちになる)
LOCK INSTANCE FOR BACKUP
この構文はバージョン8.
詳細については、How Percona XtraBackup worksをご覧ください。
まとめ
今回はLOCK TABLES、LOCK INSTANCE FOR BACKUPについて説明しました。
LOCK TABLES構文を利用することで、特定のテーブルや機能のみをメンテナンスするときにそのテーブルだけ読み書きを制限したい場合などは、対象のテーブルにテーブルロックを掛けることで実現可能な場合があります。また、LOCK INSTANCE FOR BACKUPはバージョン8から追加されているため、知らなかった方もいるかも知れません。これらについてより詳しく知りたい方は公式ドキュメントを参照してください。