文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

MySQLのLOAD DATAの罠:その2

続、LOAD DATAの罠を紹介です〜


f:id:treeapps:20180418131549p:plain

今回は LOAD DATA のトランザクションと強制コミットについてです。

今回試すのは、1つのSQL中で、トランザクション開始 → delete文発行 → LOAD DATAでエラーが発生、という事が起きた場合、delete文はどうなる?という実験です。

一見すると「そんなのトランザクションが効くに決まってるじゃん」と思いますが、果たしてそうでしょうか?

テストデータ

mysql> select * from t_del;
 +----+
 | id |
 +----+
 |  1 |
 +----+
1 row in set (0.00 sec)

mysql> select * from t_test;
Empty set (0.00 sec)

さて、ここから t_delをdeleteし、t_testをload dataしていきます。

読み込むcsvと実行するためのシェルは前回と同じく、必ずDuplicate Key Errorが起きるcsvを使います。

DELETEとLOAD DATA(エラー)を1トランザクション内で実行する

begin;
delete from t_del;
load data local infile 't_test.csv' into table t_test fields terminated by ',' enclosed by '' lines terminated by '\n';
commit;

t_delを全件deleteした後、わざとLOAD DATAでDuplicate Key Errorを起こしてrollbackさせます。t_delへのdeleteはトランザクションによってrollbackされるでしょうか?

実行結果

$ ./loadData.sh 
success

success。Duplicate Key Errorなのにエラーが返らないのは前回書いた通り、LOCALオプションを付けた場合、自動的にIGNOREオプションが付くためです。

さて、肝心のt_delテーブルのdeleteはrollbackされているでしょうか。

最初から1件データが有るテーブルに対してdelete文を発行しているので、1件データが有る状態に復元されると予想されますね。

mysql> select * from t_del;
Empty set (0.00 sec)

・・・rollbackされてません。delete文が有効になってしまい、最初に有った1件のデータはrollbackされませんでした

もしかしたらLOAD DATAのエラーが特殊なだけかもしれないので、前回と同じくSQLでわざとSyntaxErrorを起こしてみたら、結果はどうなるでしょうか。

LOAD DATAでSyntax errorが発生するとDELETEはどうなる?

begin;
delete from t_del;
エラー起こすぞ〜
load data local infile 't_test.csv' into table t_test fields terminated by ',' enclosed by '' lines terminated by '\n';
commit;

SQL中に日本語をドーンと挟んでみました。

これはどうなるか・・・

実行結果

$ ./loadData.sh 
ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'orz
load data local infile 't_test.csv' into table t_test fields terminated by '' at line 1
error

ふむ。errorが返りました。ではt_delは・・・

mysql> select * from t_del;
Empty set (0.00 sec)

おおおお!?またしてもrollbackされていません!完全にトランザクションが効いてません!!

調べてみた結果、オフィシャルサイトにこんな事が書いてありました。


http://dev.mysql.com/doc/refman/5.1-olh/ja/implicit-commit.html
データロード用ステートメント。LOAD DATA INFILE。MySQL 5.1.12 より前のバージョンでは、LOAD DATA INFILE は、すべてのストレージエンジンに対して暗黙的なコミットを引き起こしました。MySQL 5.1.12 の時点では、NDB ストレージエンジンを使用しているテーブルに対してのみ暗黙的なコミットを引き起こします。更なる情報については、バグ #11151 を参照してください。

どうも古いバージョンのMySQLでは、LOAD DATAは必ず暗黙的なcommitをしてしまうようで、例えbegin; commit;としても、途中のLOAD DATAがcommitしてトランザクションを強制終了させてしまう、との事でした。

LOAD DATAする際はトランザクション内に含めてしまうと暗黙のcommitが走ってトランザクションの意味がなくなるので注意が必要ですね。

MySQLのLOAD DATAの罠:その1 - 文系プログラマによるTIPSブログ
MySQLのLOAD DATAの罠:その2 - 文系プログラマによるTIPSブログ
MySQLのLOAD DATAの罠:その3 - 文系プログラマによるTIPSブログ