概要
とある外部システムからの連携データを取り込む際に、うまくトランザクション処理ができなかったので、その際調べたことをまとめる。
発生した事象
とある業務で外部システムからの連携データ（CSVファイル）を一定の期間毎にテーブルに反映するという要件があり、下記のように一旦全レコードを削除してから連携データを挿入しようとした。
TRUNCATE TABLE table1;
LOAD DATA LOCAL INFILE 'hoge.csv' INTO TABLE table1;
上記の処理を1つのトランザクション処理として記述した所、loadに失敗した場合でもtable1が空になっており、ロールバックされない事象が発生してしまった。
原因1
よくよく調べてみると、下記のような記述を発見。
切り捨て操作は暗黙的なコミットを発生させるため、ロールバックできません。
(引用元：https://dev.mysql.com/doc/refman/5.6/ja/truncate-table.html)
truncateのようないわゆるDDLはトランザクション処理の外で実行されるためロールバックできないとのこと・・・。
そこで、truncateをdeleteに変更し、下記のように実装してみた。
DELETE FROM table1;
LOAD DATA LOCAL INFILE 'hoge.csv' INTO TABLE table1;
これでいけるだろうと思いきや、またしてもロールバックできず・・・。
原因2
さらに調べるとMySQLでは下記のようにストレージエンジンによってトランザクション処理自体が使えない場合があるとのこと（これまでSQL Server中心に触ってきたので知らなかった）。
・InnoDB：行ロック、トランザクション処理可能
・MyISAM：テーブルロック、トランザクション処理不可
今回対象にしていたテーブルのストレージエンジンを調べるとMyISAM・・・。そもそも、トランザクション処理できませんでしたというお話(T_T)
対処法
トランザクション処理ができないので、結局、先に挿入してから差分を削除する実装に変更。
テンポラリーテーブルにデータを全部持ってきて、挿入時に現在時刻を入れておくことで、後から時刻を比較して更新されていないものを削除する方式。
CREATE TEMPORARY TABLE tmp AS SELECT * FROM table1;
LOAD DATA LOCAL INFILE 'hoge.csv' REPLACE INFILE table1 SET created_at = now();
DELETE t1 FROM table1 AS t1 LEFT OUTER JOIN tmp AS t ON t1.key = t.key WHERE t1.created_at = t.created_at
根本的な解決になっていないが、とりあえずロードに失敗してもテーブルが空になることは避けられた。
本当はストレージエンジンをinnoDBに変更してトランザクション処理をかけたいが、常時稼働中のテーブルのため難しかった。
おまけ
MySQLやOracle以外のSQLではDDLもトランザクション処理の対象になっているらしい。しばらくはMySQL中心だろうから試すことはなさそう。
コメント
SQLとかDB周りはアプリ開発に必要な部分をちょこちょことつまんでるだけなので、知識が断片的になりがちだなと感じた。一度、体系的に勉強したほうがいいなあ。