[データベース] MySQLのテーブルロック:LOCK/ UNLOCK TABLES――ロックの仕方・解除、情報の取得

2013 年 7 月 9 日 Categories: データベース | Tags:

前提

テーブルロック MyISAM, MEMORY
行ロック InnoDB

ロックの種類

READロック

現セッション

読み込み(SELECT)のみ可能
書き込み(UPDATE、INSERT、DELETE)するとエラー

別セッション

読み込み(SELECT)のみ可能
書き込み(UPDATE、INSERT、DELETE)は待ち

WRITEロック

現セッション

すべて可能

別セッション

すべて待ち

書き方

テーブルのロック

LOCK TABLES sample_table READ;
LOCK TABLES sample_table WRITE;

複数テーブルの場合、カンマ区切りでひとつひとつにREAD/WRITEを指定する。

LOCK TABLES sample_table_1 READ, sample_table_2 READ;

単純にLOCK TABLESを複数記述すると、以前の指定が取り消される。

LOCK TABLES sample_table_1 READ;
LOCK TABLES sample_table_2 READ;

この場合、sample_table_1の指定は無意味になる。

ロックの解除

UNLOCK TABLES;

すべてのテーブルのロックを解除する。

テーブルのロック関連の情報取得

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

MySQLリファレンスより)

Table_locks_immediate

すぐにテーブルをロックできた回数。要するに、別セッションのロックによって「待ち」状態にならなかった回数。

Table_locks_waited

テーブルをロックしようとしたときに「待ち」状態になった回数。これが多すぎる場合は、処理上の問題を抱えているということになる。

現在の状態の確認

「SHOW PROCESSLIST」

mysql> SHOW FULL PROCESSLIST\G
*************************** 1. row ***************************
Id: 1
User: system user
Host:
db: NULL
Command: Connect
Time: 1030455
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 1004
State: Has read all relay log; waiting for the slave ?
       I/O thread to update it
Info: NULL

MySQLリファレンスより)

アウトプットカラム「State」が「Locked」になっている場合、テーブルがロックされている。

行ロックに対してのテーブルロックの利点

MySQLリファレンスより)

以下の場合、行レベルロックに対してテーブルロックが優勢になります。

  • テーブルのほとんどのステートメントは read です。

  • 1つのキーリードで取得される1つの行に対して、write が更新もしくは削除される場合、read と write が混合となります。

    UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
    DELETE FROM tbl_name WHERE unique_key_col=key_value;
    
  • SELECTが同時INSERTステートメントとごく少数のUPDATEもしくはDELETEステートメントと混合されます。

  • writerを使用しない、全てのテーブルのGROUP BYオペレーションや多くのスキャン。