1.TruncateとDeleteの違い
SQLでテーブルのレコードを削除するときに検索で引っかかってくる2つのコマンド
Truncateのほうが早い以外に何か違いはないものか
ハマって違いを見つけました。
そのハマり所はどこかというと
『トランザクション』
です。
トランザクションについて、ざっくりした説明をするとAとBの処理を順次実行してどちらかでこけたらAもBも実行する前の状態に戻す（ロールバック）一連の処理のこと。
なんでハマったかというと
『Truncateはトランザクション外で実行される』
ということを知らなかったから。
（どっちのコマンドを採用するかのところで調べておくべきでした。普通やるべきこと。）
トランザクションの外で実行されるということは下の図に示した通り、処理Bで発生した例外によってロールバックされる先が変わるということになります。
画像左側（DELETE）では、処理Aおよび処理Bがトランザクションの適用範囲内に入っているため、処理Bで例外発生すると処理Aを行う前の状態、つまりレコード削除前の状態に戻ります。
一方、画像右側（TRUNCATE）においても、処理Aより前にBeginTransaction()を行っており、コードの見た目の配置上処理Aもトランザクションの適用範囲となっています。しかし、TRUNCATEコマンドはトランザクションの外で実行されることから、トランザクションの適用範囲は処理Bのみであり、処理Bで例外が発生した場合、処理Bを行う前、つまり、レコード削除の処理Aが終了した状態までしたロールバックされません。
こんなに重大な違い（トランザクション上の振る舞いの違い）があるとは。
2.TRANCATEがトランザクションの外で実行される理由
Microsoft公式ページを見ると下記記述がありました。
トランザクション ログが使用する領域が削減されます。
DELETE ステートメントは、一度に 1 行ずつ削除し、削除した各行のエントリをトランザクション ログに記録します。 TRUNCATE TABLE は、テーブル データを格納するのに使用するデータ ページの割り当てを解除することによってデータを削除し、ページの割り当ての解除だけをトランザクション ログに記録します。
MicrosoftのTRUNCATEページ
TRUNCATE TABLE (Transact-SQL)
①データを格納している領域の割り当てを解除することによってデータの削除を行うコマンドであること
②テーブルの削除＆作成を行っている間は、トランザクションログを出力しないコマンドであること。
この2点がわかります。
一方、DELETEコマンドについては、同じくMicrosoft公式ページでは、
既定では、DELETE ステートメントは、常に、そのステートメントで変更するテーブルについて排他 (X) ロックを獲得し、トランザクションが完了するまでそのロックを保持します～（中略）～
ヒープから行を削除するときには、 データベース エンジン によって、操作に行またはページ ロックが使用されることがあります。 その結果、削除操作で空になったページがヒープに割り当てられたままになります。 空のページの割り当てが解除されないと、データベース内の他のオブジェクトで該当の領域を再利用できなくなります。
MicrosoftのDELETEページ
DELETE (Transact-SQL)
①DELETEは、データ削除を行う前に、トランザクションによって該当データ領域がロックされるコマンドであること。
②行を削除した領域は空のページが割り当てられたままとなり、領域が解放されないコマンドであること。
以上2点がわかります。
公式ページからの情報をまとめると
| TRUNCATE | DELETE | ||
| 1 | データ領域 | 解放することによりデータ削除 | 領域を解放せずデータのみ削除 |
| 2 | 空ページ | 領域開放により削除 ※テーブル定義除き | 領域を確保したまま存在 |
| 3 | ロック対象 | テーブル全体 ※行はロックせず | 対象各行 |
まとめた表の2が特にトランザクションの外で実行される理由になるのではないでしょうか
トランザクションは、テーブルに対しての操作を一連の処理です。ということは、操作対象のテーブルがあってこそ成立するもの。
表の2にまとめたように、TRUNCATEはテーブルのデータ実体を保存した領域をロックしたうえ、領域開放によってデータ削除さします。そのため、操作対象となるテーブルが一時的にない状態となり、トランザクションの適用対象がない状態がつくられる。ということではないでしょうか。
適用対象のテーブルがないのでは、トランザクションを行えませんね。
3.結論
TRUNCATEとDELETEの違いは、データ削除方法の違いによるものである。テーブル定義を除いてデータ領域を解放してデータ削除を行うTRUNCATEは、トランザクションの対象となるテーブルが存在しない状態を一時的に作り出すため、トランザクションを適用できなくなる。
本日もご覧いただきありがとうございます。
明日がりたい自分に近づく一日でありますように。
次回もご覧いただけますと幸いです。