INSERT INTO T SELECT ... FROM S WHERE ... は T に挿入された各行に、ギャップロックなしの排他インデックスレコードロックを設定します。innodb_locks_unsafe_for_binlog が有効であるかトランザクション遮断レベルが READ COMMITTED である場合には、InnoDB は S での検索を一貫性読み取り (ロックなし) として行います。それ以外の場合、InnoDB は S から取得した行に共有ネクストキーロックを設定します。InnoDB は後者の場合にロックを設定する必要があります。バックアップからの前進復旧では、すべての SQL ステートメントはそれが元々行われたのとまったく同じ方法で実行されなければいけません。
ということらしいです。
ということで、ちょっと実験。
debian lenny 32bitのpercona-server5.5で試してます。
session1> SELECT @@version; +-----------+ | @@version | +-----------+ | 5.5.20-55 | +-----------+ 1 row in set (0.00 sec)
- hogeテーブルと同じ構造のhoge_copyを作成する。
- session1でINSERT hoge_copy SELELCT * FROM hoge;でhoge_copyにhogeの内容をコピーしてみる
- session1でトランザクションを放置したまま、session2でhogeに対して更新をかけてみる
この状態で、session2がロックを取得できずにタイムアウトするはずです。
session1> SHOW CREATE TABLE hoge\G *************************** 1. row *************************** Table: hoge Create Table: CREATE TABLE `hoge` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) session1> INSERT INTO hoge (id, name) VALUES (1,'foo'), (2,'bar'), (5,'baz'), (8,'mataz'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 session1> SELECT * FROM hoge; +----+-------+ | id | name | +----+-------+ | 1 | foo | | 2 | bar | | 5 | baz | | 8 | mataz | +----+-------+ 4 rows in set (0.00 sec)
読み取り元のhogeはこんな感じ。
session1> CREATE TABLE hoge_copy LIKE hoge; Query OK, 0 rows affected (0.02 sec)
hogeと同じ定義のhoge_copyを作ってみる
session1> session1> BEGIN WORK; Query OK, 0 rows affected (0.00 sec) session1> INSERT INTO hoge_copy SELECT * FROM hoge WHERE id IN (1,2,3,4,5,6,7); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
コピーしたままトランザクションを放置する。(長時間テーブルコピーをしている状況を模擬
session2> INSERT INTO hoge (id,name) VALUES (9,'yahoo'); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
で、ロックが取得できないので、タイムアウトする。と。
ちなみに、トランザクション分離レベルがデフォルト(REPEATABLE-READ)の状態の場合のときだけロックを取得します。それ以外の分離レベルのときやinnodb_locks_unsafe_for_binlog変数の値が変わっているときはこの限りじゃないです。
session2> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; +-----------------------+-----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+-----------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec)
問題になりそうなこととして
よく立ち上げたばかりのDBのメモリを暖める手段として既存のテーブルと同じ構造のBLACKHOLEテーブルを作ってそこに全データを流し込むってのがあります。
このときSlaveでかつSQL_THREADを稼働させたまま、並列で実行すると、タイミングによってはdeadlockを起こして、MasterとSlaveでデータの不整合が起きてしまうという死ねることが起きるかも知れないです。
Masterでカジュアルにテーブルをコピーしようと実行した時にも、予想に反してSELECTしたテーブル(正確には取得したレコード)にロックを取得してしまうので更新がその間ブロックされて死ねるということがあるかもです。
