はじめまして。インフラストラクチャサービスグループの吉田と申します。普段はデータベース管理者の業務を担当しています。
今回は、初めてMySQLを触る方々に、私がデータベース管理者（DBA）という仕事をする中で培った意外に知られていないナレッジを紹介したいと思います。
リレーショナルデータベース管理システム（RDBMS）といっても、有名なものだけでOracleやPostgreSQL、MySQLなどいろいろなプロダクトがあります。どれもSQL言語で、トランザクションも制御できる同じようなものだと思ってしまうことが多いのではないでしょうか。
しかし、実際に使ってみたら、当然できるものだと思っていた事ができず、最初から知っていれば良かったのに、と後悔することも。かくいう私もRDBMSにOracleから入り、違いに面食らった一人です。そこで、よく使うけど案外知らないMySQLのTipsを3つ紹介したいと思います。
1.テーブルを行ロックするには
テーブルを行ロックするUPDATE文を発行するSQLを書いてみます。
== 準備 == -- テーブル作成 mysql> CREATE TABLE test01 ( i int, c varchar(10) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- データ登録 mysql> INSERT INTO test01 VALUES (1, 'A'); mysql> INSERT INTO test01 VALUES (2, 'B'); mysql> INSERT INTO test01 VALUES (3, 'C'); -- データ確認 mysql> SELECT * FROM test01; +------+------+ | i | c | +------+------+ | 1 | A | | 2 | B | | 3 | C | +------+------+ 3 rows in set (0.00 sec)
まず1つめのDBへの接続を作成し、i=1 の行ロックを取得します。
/* DB接続①(MySQL) */ mysql> BEGIN; -- トランザクション開始 mysql> UPDATE test01 SET c = 'Z' WHERE i = 1; -- i=1の行ロックを取得しました。 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
2つめの接続からも別の i=2 の行ロックを取得しようとします。
/* DB接続②(MySQL) */ mysql> BEGIN; -- トランザクション開始 mysql> UPDATE test01 SET c = 'X' WHERE i = 2; -- i=2の行ロックの取得を試みます。
..... ここでしばらくプロンプトが返って来ず待たされて、
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
ロック待ちタイムアウトになって行ロックを取得できませんでした。
DB接続①と②は異なるレコードであるにも関わらず、行ロックを取得できませんでした。
ちなみにOracleの場合は、想像通りに行ロックを取得できます。
/* DB接続①(Oracle) */ SQL> UPDATE test01 SETc = 'Z' WHERE i = 1; -- i=1の行ロックを取得しました。 1行が更新されました。 /* DB接続②(Oracle) */ SQL> UPDATEtest01 SETc = 'X' WHERE i = 2; -- i=2の行ロックを取得しました。 1行が更新されました。
実はMySQLでは行ロックを取得するためにはインデックスが必要になります。 実際にインデックスを張って試してみましょう。
/* == 準備 == */ -- カラム i にインデックス作成 mysql> CREATE INDEX ind_test01 ON test01(i);
1つめのDBへの接続を作成し、i=1 の行ロックを取得します。
/* DB接続①(MySQL インデックス有り) */ mysql> BEGIN; -- トランザクション開始 mysql> UPDATE test01 SET c = 'Z' WHERE i = 1; -- i=1の行ロックを取得しました。 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
2つめの接続からも別の i=2の 行ロックを取得しようとします。
/* DB接続②(MySQL インデックス有り) */ mysql> BEGIN; -- トランザクション開始 mysql> UPDATE test01 SET c = 'X' WHERE i = 2; -- i=2の行ロックの取得を試みます。 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
問題無く異なる行でロックを取得できました。MySQLではインデックスを使ったクエリでないと行ロックを取得できませんので注意しましょう。なお、今回はトランザクションを使用するためにInnoDBストレージエンジンを使用しています。
ギャップロックに注意
テーブルにインデックスを張り、WHERE句を使用して行ロックを実現したとしても、トランザクション分離レベルがデフォルトのREPEATABLE-READの環境では、予期せぬロックでデッドロックになる場合もあるため、注意が必要です。
/* == 準備 == */ mysql> SELECT * FROM test01; +---+------+ | i | c | +---+------+ | 1 | A | | 2 | B | | 3 | C | +---+------+ 3 rows in set (0.00 sec)
カラム i にインデックスを張っています。
mysql> SHOW INDEX FROM test01 \G *************************** 1. row *************************** Table: test01 Non_unique: 1 Key_name: ind_test01 Seq_in_index: 1 Column_name: i Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec)
1つめのDB接続からテーブルの i=3 以上のデータを削除します。
/* DB接続①(MySQL インデックス有り) */ mysql> BEGIN; -- トランザクション開始 mysql> DELETE FROM test01 WHERE i >= 3; -- i=3以上のデータを削除します。
2つめのDB接続で i=10 のデータを挿入します。
/* DB接続②(MySQL インデックス有り) */ mysql> BEGIN; -- トランザクション開始 mysql> INSERT INTO test01 VALUES (10, 'Y');
...... ロックタイムアウトになります。
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
DB接続①の「i が3以上のデータの削除」というトランザクション処理が始まり、そのトランザクションが確定されるまでは、その範囲にある i=10 のデータ挿入の処理はロックされてしまいます。
これは、ギャップロックという仕組みによって、データの整合性を保つための挙動だそうです。
この仕組みに関しては、ここでは省略します。興味のある方は「ギャップロック」「ネクストキーロック」などのキーワードで調べてみてください。
少ししつこいですが、Oracleではこのような挙動にはならず、i=10 のデータ挿入が行えます。 OracleとMySQLでのロックの実装の違いだと思いますが、先入観にとらわれずMySQLの事象を理解して使っていく事が必要だと感じています。
このギャップロックやインデックスの不足によるテーブルロックが原因でデッドロックになるケースが弊社でも時折あるので十分な設計やテストをしないといけないと思っています。
2：大文字・小文字のデータを区別するには
CHARSETがutf8の設定でDBを作成している場合、COLLATIONというデータの照合順序がデフォルトでutf8_general_ciになっています。
mysql> SHOW CREATE DATABASE yoshida; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | yoshida | CREATE DATABASE `yoshida` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW CHARACTER SET LIKE 'utf8'; +---------+---------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+-------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | +---------+---------------+-------------------+--------+ 1 row in set (0.00 sec)
そのDBでテーブルを作成して、データを挿入します。
-- テーブル作成 mysql> CREATE TABLE test01 (i int, c varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8; mysql> SHOW CREATE TABLE test01 \G *************************** 1. row *************************** Table: test01 Create Table: CREATE TABLE `test01` ( `i` int(11) DEFAULT NULL, `c` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec) -- データ登録 mysql> INSERT INTO test01 VALUES (1, 'A'); mysql> INSERT INTO test01 VALUES (2, 'B'); mysql> INSERT INTO test01 VALUES (3, 'C'); mysql> INSERT INTO test01 VALUES (4, 'a'); -- データ確認 mysql> SELECT * FROM test01; +------+------+ | i | c | +------+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | a | +------+------+ 4 rows in set (0.00 sec)
大文字 A で検索しても、小文字 a もヒットします。
mysql> SELECT * FROM test01 WHERE c = 'A'; +------+------+ | i | c | +------+------+ | 1 | A | | 4 | a | +------+------+ 2 rows in set (0.01 sec)
このデフォルトのutf8_general_ciでは大文字・小文字を区別しません。ちなみに、Oracleでテーブル作成をした場合には、何もしなくても大文字・小文字を判別するようになっています。
そこで、文字列の入る c カラムを大文字・小文字を区別するようにしてみましょう。
mysql> ALTER TABLE test01 MODIFY c varchar(10) COLLATE utf8_bin; Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test01 \G *************************** 1. row *************************** Table: test01 Create Table: CREATE TABLE `test01` ( `i` int(11) DEFAULT NULL, `c` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec)
大文字・小文字が区別されるようになりました。
mysql> SELECT * FROM test01 WHERE c = 'A'; +------+------+ | i | c | +------+------+ | 1 | A | +------+------+ 1 row in set (0.01 sec)
3.オンラインDDLとメタデータロックでセッション滞留
オンラインDDLはDDL実行中でも参照はもちろん、更新もできるので、サービス停止を極力減らしてDBのメンテナンスが可能なため、頻繁に利用しています。
構文は、以下のようにALTER TABLEに「ALGORITHM = INPLACE , LOCK = NONE」を付けます。
ALTER TABLE <テーブル名> <カラム名> <型>, ALGORITHM = INPLACE, LOCK = NONE;
ただ、オンラインDDL実行前に該当テーブルへトランザクションを伴うアクセスや、長時間のクエリが実行中だったりすると、オンラインDDLはその処理が終わるまで待たされます。そして、そのオンラインDDLが終わるまでは、該当テーブルへのアクセスは全て Waiting for table metadata lock というステータスで待たされるので注意が必要です。
トランザクション内で該当テーブルを参照します。
/* DB接続① */ mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT i FROM test01 WHERE i = 1; +------+ | i | +------+ | 1 | +------+ 1 row in set (0.03 sec)
別のセッションよりオンラインDDLを実行します。
/* DB接続② */ mysql> ALTER TABLE test01 ADD i2 int, ALGORITHM = INPLACE, LOCK = NONE ;
…… 応答なし。
また、さらに別のセッションで該当テーブル参照をします。
/* DB接続③ */ mysql> SELECT i FROM test01;
……応答なし。
セッションの状態を確認します。
mysql> SHOW PROCESSLIST; +------+------------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+------------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------+ | 2924 | yoshida-te | localhost | yoshida-te | Query | 206 | Waiting for table metadata lock | alter table test01 add i2 int, ALGORITHM = INPLACE , LOCK = NONE | | 2925 | yoshida-te | localhost | yoshida-te | Sleep | 274 | | NULL | | 2927 | yoshida-te | localhost | yoshida-te | Query | 123 | Waiting for table metadata lock | select i from test01 | | 2928 | yoshida-te | localhost | mysql | Query | 0 | System lock | show processlist | +------+------------+-----------+------------+---------+------+---------------------------------+------------------------------------------------------------------+ 4 rows in set (0.00 sec) -- Id=2925 : DB接続① トランザクションが確定していないセッション -- Id=2924 : DB接続② オンラインDDLの実行 -- Id=2927 : DB接続③ 該当テーブルへの参照
このままDB接続①のトランザクションを確定しないと、DB接続③のようなテーブルへのアクセスのセッションが溜まり続けて、セッション溢れなどDBサーバーの障害を引き起こします。
DB接続①のトランザクションをコミットして、トランザクションを終了させてみます。
/* DB接続① */ mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)
DB接続①のコミットによりオンラインDDLが実行されます。
/* DB接続② */ mysql> ALTER TABLE test01 ADD i2 int, ALGORITHM = INPLACE, LOCK = NONE; Query OK, 0 rows affected (5 min 43.44 sec) Records: 0 Duplicates: 0 Warnings: 0
DB接続②のDDLが実行されてテーブル参照も実行されます。
/* DB接続③ */ mysql> SELECT i FROM test01; +------+ | i | +------+ | 1 | | 2 | | 3 | +------+ 3 rows in set (4 min 20.94 sec)
オンラインDDLではメタデータロックをするために、開始時と終了時にちょっとだけ排他ロックが必要となります。
まとめ
簡単なTipsですが、はじめから知っているといないでは、違うと思います。このブログが少しでもはじめてMySQLを使う人の助けになれば嬉しいです。
これからMySQLを使おうと思っている人は先入観にとらわれず、以下の事を意識しておいてくださいね。
- テーブルを行ロックするには、インデックスが必要です
- 意識してロックを制御しているつもりでもギャップロックなどのロックが発生するので注意が必要です
- テーブルのカラムで大文字・小文字の判定はどうなっているか確認してみてください
- オンラインDDLは便利ですが、実行時に注意しないとメタデータロックでセッションが溜まる可能性があります
