現場での実践SQL（６）

１．一連の処理をひとまとめにするトランザクション

　INSERT、UPDATE、DELETEを連続で実行し、一つの処理として実行したい場合があります。いわゆる処理のグループ化のイメージです。トランザクションの理論についてはこちらで詳しくまとめていますのでご参照下さい。

strat transaction; -- トランザクション開始

insert into A values('20180920','10000');
update B set AMOUNT=AMOUNT+10000 where KOZA='02001100';

commit; -- コミット：トランザクションを確定

-- rollback; 中止する場合

　start transactionでトランザクション開始を宣言し、この場合はINSERTとUPDATEの連続処理を行った後にコミットを実行します。この書き方をすれば、仮にINSERT完了後に障害が発生したとしてもコミットされていないので、データを復元した際はロールバックされた状態となり、もう一度最初からトランザクションを実行すれば良いだけとなります。

　さらに応用テクとしてsavepointというトランザクション内での中間コミットを作成することもできます。これは分岐が多く処理数の多いストアドプロシージャ内でトランザクションを利用する場合に効果的ですが、トランザクションの原子性に合わない処理で、現場により利用を推奨されない場合がありますので注意して下さい。

トランザクション開始時に分離レベルを指定する場合

　トランザクションの分離レベルについてはこちらで詳しくまとめていますのでご参照下さい。

　DBMS全体の分離レベルを高く設定していれば問題はありませんが、レスポンス性能を優先して分離レベルを低く運用している場合は、以下の通りトランザクション開始の際に実行するトランザクションの分離レベルを明示的に高くする必要があります。

-- 以下４行の内分離レベルに応じて一つ選ぶ
set transaction isolation level read uncommitted;
set transaction isolation level read committed;
set transaction isolation level repeatable read;
set transaction isolation level serializable;

strat transaction; -- トランザクション開始
～以下上の例と書き方同じ～

２．INSERTの応用

２－１．BULK INSERT

　テーブルにレコードをインサートする場合、以下のようにINSERT文を書きます。

insert into A values('1','100');
insert into A values('2','200');
insert into A values('3','300');

　これに対してBULK INSERTは、以下のようにINSERT文の一文にインサートしたいデータをまとめて書き、一度にインサートを行います。

insert into A values('1','100'),('2','200'),('3','200');

　サーバーではINSERT、UPDATE、DELETE等、データの更新を行う際、都度トランザクション処理を実行しています。このトランザクション、数回であれば、それこそトランザクションの存在を意識することもないスピードで処理されますが、大量にインサートを行う際には、このトランザクションの時間がボトルネックとなります。これを解消するため、BULK INSERTを利用してトランザクションの回数を減らすことで、インサートにかかる時間を短縮します。
　上記の例の場合、通常インサートでは３回トランザクションが発生しますが、BULK INSERTの場合は１回のトランザクションで済みます。

２－２．トランザクション内でINSERT

strat transaction; -- トランザクション開始

insert into A values('1','100');
insert into A values('2','200');
insert into A values('3','300');

commit; -- コミット：トランザクションを確定

-- rollback; 中止する場合

　BULK INERTではトランザクションの回数を減らすことでインサート時間の短縮を図りましたが、仕組みは同じです。１回のトランザクションで全てのインサートを行います。

２－３．REPLACE INTO

replace into A values('1','100');

　REPLACE INTOは、INSERT INTOとは違い、仮に同じプライマリーキーのレコードが存在する場合はUPDATE、ない場合はINSERTを行ってくれる構文です。INSERT INTOでは同一プライマリキーのレコードが存在する場合にエラーになりますが、REPLACE INTOではエラーにならず上書きとなります。

３．UPDATEの応用

３－１．UPDATEのレコードロック

start transaction;

select * from A where KEY='1' for update;
update A set KEY=KEY+1 where KEY='1';

commit;

-- rollback; 中止する場合

　INSERT、DELETEの際はそれほど気にする必要はないのですが、UPDATEについては、同時利用人数が多くなれば多くなるほど不整合が発生するリスクが高まります。このときDBMS全体の分離レベルを高く設定していれば問題はありませんが、レスポンス性能を優先して分離レベルを低く運用している場合は、上記トランザクション開始の際に実行するトランザクションの分離レベルを明示的に高くするか、ここで紹介するようにUPDATE文を利用する際、SELECT FOR UPDATEを利用して行ロックをかけた上でUPDATEを行います。尚、この行ロックはトランザクションを宣言(start transaction;)した上で実行しないと有効にならないので注意が必要です。このロックは占有ロックです。

select * from A where KEY='1' LOCK IN SHARE MODE;
update A set KEY=KEY+1 where KEY='1';

　このロックは共有ロックです。通常UPDATEする場合これらのキーワードは書きませんがその場合はDBMS全体の分離レベルによります。

４．DELETEの応用

４－１．TRUNCATE

truncate table A;

　TRUNCATE文はトランザクションを実行せず、テーブルの内容を全削除します。このためDELETEに比べて高速に削除できますが、DELETEと違いWHERE文の指定が出来ないため、レコードを全削除する場合にしか利用できません。