各RDBMSでTRUNCATEはROLLBACKできるのかを確認する

はじめに

データベースのメンテナンス作業でTRUNCATEコマンドを使うことがある。
TRUNCATEは大量のデータを高速で削除できる便利なコマンドだが、実行後にROLLBACKできるかどうかはRDBMSによって異なる。

今回は、いくつかのRDBMSでTRUNCATEがROLLBACKできるかを調査し、実際にPostgreSQLとMySQLで検証してみる。

環境

Windows 11 Professional
WSL2 (Ubuntu 24.04.2 LTS)
Docker 27.0.3
PostgreSQL 17.2
MySQL 8.0.40

各RDBMSでの`TRUNCATE` `ROLLBACK`対応状況

主要なRDBMSでのTRUNCATE後のROLLBACK可否をまとめた。

RDBMS	バージョン	ROLLBACK可否	備考	公式ドキュメント
`PostgreSQL`	8.x以降	✅ 可能	トランザクション内で実行すれば`ROLLBACK`可能	PostgreSQL TRUNCATE
`MySQL`	全バージョン	❌ 不可能	DDL文として扱われ、暗黙的コミットが発生	MySQL TRUNCATE TABLE
`SQL Server`	全バージョン	✅ 可能	トランザクション内で実行すれば`ROLLBACK`可能	SQL Server TRUNCATE TABLE
`Oracle Database`	全バージョン	❌ 不可能	DDL文として扱われ、暗黙的コミットが発生	Oracle TRUNCATE TABLE
`SQLite`	全バージョン	✅ 可能	`DELETE`文として実装されているため	SQLite DELETE
`MariaDB`	全バージョン	❌ 不可能	MySQLと同様の動作	MariaDB TRUNCATE TABLE

バージョン情報の検証結果

各RDBMSの公式ドキュメントを調査して、TRUNCATEのROLLBACKについて確認をしてみた。
※とはいえ、全バージョンは見れていないので参考程度に…。

PostgreSQLの歴史

PostgreSQL 8.x時代から既にTRUNCATE TABLEはトランザクション安全性を持っていた
PostgreSQL 9.0では「Improve performance of TRUNCATE when the table was created or truncated earlier in the same transaction」としてパフォーマンス改善が実装された
現在のドキュメントでは「TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.」と明記されている

MySQLの歴史

MySQL 5.0版の公式ドキュメントでも既に「Truncate operations cause an implicit commit, and so cannot be rolled back」と記載されている
この動作は現在も変わらず一貫している

SQL Serverの歴史

SQL Server公式ドキュメントには「C. Roll back a truncate operation」という例が明示的に記載されており、TRUNCATE TABLEがROLLBACK可能であることを詳細に説明している
この機能は長期間にわたって一貫してサポートされている

SQLiteの歴史

SQLiteには専用のTRUNCATE TABLEステートメントは存在しない
DELETE FROM table;（WHERE句なし）に対する「truncate optimization」が適用されるが、これは通常のDELETEと同様にROLLBACK可能
この最適化は2008年のVersion 3.6.5で問題修正が行われたが、ROLLBACK機能自体は常に利用可能

`TRUNCATE`の`ROLLBACK`について

PostgreSQL

PostgreSQLの公式ドキュメントより

“TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.”
“TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.”

出典: PostgreSQL Documentation - TRUNCATE

日本語にすると…

「TRUNCATEはテーブル群からすべての行を素早く削除する。各テーブルに対する条件なしDELETEと同じ効果を持つが、実際にはテーブルをスキャンしないのでより高速だ。さらに、後続のVACUUM操作を必要とせず、即座にディスク領域を回収する。」
「TRUNCATEはテーブル内のデータに関してトランザクション安全だ：周囲のトランザクションがコミットされない場合、切り詰めは安全にロールバックされる。」

PostgreSQLでは明確に「TRUNCATEはトランザクション安全」「安全にロールバックされる」と記載されており、トランザクション内でのROLLBACKが可能であることが確認できる。

MySQL

MySQL公式ドキュメントより

“TRUNCATE TABLE empties a table completely. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements.”
“Truncate operations cause an implicit commit, and so cannot be rolled back.”

出典: MySQL Documentation - TRUNCATE TABLE Statement

日本語にすると…

「TRUNCATE TABLEはテーブルを完全に空にする。DROP権限が必要だ。論理的に、TRUNCATE TABLEはすべての行を削除するDELETE文、またはDROP TABLEとCREATE TABLEの一連の文に似ている。」
「切り詰め操作は暗黙的コミットを引き起こすため、ロールバックできない。」

MySQLでは「暗黙的コミットを引き起こす」「ロールバックできない」と明確に記載されており、TRUNCATEがDDL文として扱われ、トランザクション制御の対象外であることが確認できる。

SQL Server

Microsoft公式ドキュメントより

“Compared to the DELETE statement, TRUNCATE TABLE has the following advantages: Less transaction log space is used. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.”
“A TRUNCATE TABLE operation can be rolled back within a transaction.”

出典: Microsoft SQL Server Documentation

日本語にすると…

「DELETE文と比較して、TRUNCATE TABLEには次の利点がある：使用されるトランザクションログ領域が少ない。DELETE文は一度に1行ずつ削除し、削除された各行のトランザクションログにエントリを記録する。TRUNCATE TABLEは、テーブルデータの格納に使用されるデータページの割り当てを解除してデータを削除し、ページの割り当て解除のみをトランザクションログに記録する。」
「TRUNCATE TABLE操作はトランザクション内でロールバックできる。」

SQL Serverでは「トランザクション内でロールバックできる」と明確に記載されており、実際に公式ドキュメントにはROLLBACKの実例も掲載されている。ページ割り当て解除のみをログに記録する仕組みにより、ROLLBACKが可能になっている。

Oracle Database

Oracle公式ドキュメントより

“Use the TRUNCATE TABLE statement to remove all rows from a table.”
“You cannot roll back a TRUNCATE TABLE statement, nor can you use a FLASHBACK TABLE statement to retrieve the contents of a table that has been truncated.”
“Removing rows with the TRUNCATE TABLE statement can be more efficient than dropping and re-creating a table.”

出典: Oracle Database SQL Language Reference - TRUNCATE TABLE

日本語にすると…

「TRUNCATE TABLE文を使用してテーブルからすべての行を削除する。」
「TRUNCATE TABLE文はロールバックできない。また、FLASHBACK TABLE文を使用して切り詰められたテーブルの内容を取得することもできない。」
「TRUNCATE TABLE文による行の削除は、テーブルの削除および再作成よりも効率的だ。」

Oracle Databaseでは「ロールバックできない」と明確に否定しており、さらに「FLASHBACK TABLEでも復旧できない」と記載されている。Oracleでは確実にTRUNCATE後のデータ復旧ができないことが確認できる。

SQLite

SQLite公式ドキュメントより：

“The DELETE command removes records from the table identified by the qualified-table-name.”
“If the WHERE clause is not present, all records in the table are deleted.”
“When the WHERE clause and RETURNING clause are both omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This ’truncate’ optimization makes the delete run much faster.”
“The truncate optimization can be permanently disabled for all queries by recompiling SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.”

出典: SQLite Documentation - DELETE

日本語にすると…

「DELETEコマンドは、qualified-table-nameで識別されるテーブルからレコードを削除する。」「WHERE句が存在しない場合、テーブル内のすべてのレコードが削除される。」「WHERE句とRETURNING句の両方がDELETE文から省略され、削除されるテーブルにトリガーがない場合、SQLiteは最適化を使用してテーブルの各行を個別に訪問することなく、テーブルの内容全体を消去する。この「切り詰め」最適化により、削除がはるかに高速になる。」「切り詰め最適化は、SQLITE_OMIT_TRUNCATE_OPTIMIZATIONコンパイル時スイッチを使用してSQLiteを再コンパイルすることにより、すべてのクエリに対して永続的に無効にできる。」

SQLiteでは専用のTRUNCATE文は存在せず、DELETE文の最適化として「truncate optimization」が動作する。しかし、これは本質的にはDELETE文なので、通常のトランザクション制御が適用され、ROLLBACKが可能。

MariaDB

MariaDB公式ドキュメントより

“TRUNCATE TABLE empties a table completely. It requires the DROP privilege.”
“Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.”
“Truncate operations cause an implicit commit.”
“For the purposes of binary logging and replication, TRUNCATE TABLE is treated as DROP TABLE followed by CREATE TABLE (DDL rather than DML).”

出典: MariaDB Documentation - TRUNCATE TABLE

日本語にすると…

「TRUNCATE TABLEはテーブルを完全に空にする。DROP権限が必要である。」
「論理的に、TRUNCATE TABLEはすべての行を削除するDELETE文と同等であるが、状況によっては実用的な違いがある。」
「切り詰め操作は暗黙的コミットを引き起こす。」
「バイナリロギングとレプリケーションの目的では、TRUNCATE TABLEはDROP TABLEに続いてCREATE TABLE（DMLではなくDDL）として扱われる。」

MariaDBでは「暗黙的コミットを引き起こす」「DDLとして扱われる」と記載されており、MySQLと同様の動作でROLLBACKができないことが確認できる。実装もMySQLベースのため、同じ制限がある。

実際に検証してみる

PostgreSQLとMySQLでTRUNCATEのROLLBACK動作を実際に確認してみる。
PostgreSQLではROLLBACK可能、MySQLでは不可能であることを検証する。

PostgreSQL での検証

環境構築

# PostgreSQL コンテナを起動
docker run --name postgres-test \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=testdb \
  -p 5432:5432 \
  -d postgres:17.2

# 接続確認
docker exec -it postgres-test psql -U postgres -d testdb

テストデータの準備

-- テスト用テーブル作成
CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- テストデータ挿入
INSERT INTO test_table (name) 
VALUES 
    ('データ1'),
    ('データ2'),
    ('データ3'),
    ('データ4'),
    ('データ5');

-- データ確認
SELECT * FROM test_table;

`TRUNCATE` + `ROLLBACK` テスト

-- トランザクション開始
BEGIN;

-- 削除前の件数確認
SELECT COUNT(*) as "削除前の件数" FROM test_table;

-- `TRUNCATE`実行
TRUNCATE TABLE test_table;

-- 削除後の件数確認
SELECT COUNT(*) as "TRUNCATE後の件数" FROM test_table;

-- `ROLLBACK`実行
ROLLBACK;

-- `ROLLBACK`後の件数確認
SELECT COUNT(*) as "ROLLBACK後の件数" FROM test_table;
SELECT * FROM test_table;

期待結果

削除前の件数：5
TRUNCATE後の件数：0
ROLLBACK後の件数：5（データが復旧される）

おぉ！できてる！

MySQL での検証

MySQL環境構築

# MySQL コンテナを起動
docker run --name mysql-test \
  -e MYSQL_ROOT_PASSWORD=password \
  -e MYSQL_DATABASE=testdb \
  -p 3306:3306 \
  -d mysql:8.0.40

# 接続確認
docker exec -it mysql-test mysql -u root -p testdb

InnoDBでのテスト（`ROLLBACK`不可の確認）

テストデータの作成

-- テスト用テーブル作成（InnoDBエンジン）
CREATE TABLE test_table_innodb (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- テストデータ挿入
INSERT INTO test_table_innodb (name) 
VALUES 
    ('データ1'),
    ('データ2'),
    ('データ3'),
    ('データ4'),
    ('データ5');

試してみる

-- トランザクション開始
START TRANSACTION;

-- 削除前の件数確認
SELECT COUNT(*) as '削除前の件数' FROM test_table_innodb;

-- `TRUNCATE`実行（暗黙的`COMMIT`が発生）
TRUNCATE TABLE test_table_innodb;

-- 削除後の件数確認
SELECT COUNT(*) as 'TRUNCATE後の件数' FROM test_table_innodb;

-- `ROLLBACK`実行（効果なし）
ROLLBACK;

-- `ROLLBACK`後の件数確認
SELECT COUNT(*) as 'ROLLBACK後の件数' FROM test_table_innodb;

MyISAMでのテスト（`ROLLBACK`不可の確認）

テストデータの作成

-- テスト用テーブル作成（MyISAMエンジン）
CREATE TABLE test_table_myisam (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM;

-- テストデータ挿入
INSERT INTO test_table_myisam (name) 
VALUES 
    ('データ1'),
    ('データ2'),
    ('データ3'),
    ('データ4'),
    ('データ5');

試してみる

-- トランザクション開始（MyISAMでは意味なし）
START TRANSACTION;

-- 削除前の件数確認
SELECT COUNT(*) as '削除前の件数' FROM test_table_myisam;

-- `TRUNCATE`実行
TRUNCATE TABLE test_table_myisam;

-- 削除後の件数確認
SELECT COUNT(*) as 'TRUNCATE後の件数' FROM test_table_myisam;

-- `ROLLBACK`実行（効果なし）
ROLLBACK;

-- `ROLLBACK`後の件数確認
SELECT COUNT(*) as 'ROLLBACK後の件数' FROM test_table_myisam;

期待結果

InnoDBの場合：ROLLBACKしてもデータは復旧しない（暗黙的COMMITのため）
MyISAMの場合：ROLLBACKしてもデータは復旧しない（トランザクション非対応のため）

検証結果

PostgreSQL の結果

testdb=# SELECT COUNT(*) as "削除前の件数" FROM test_table;
 削除前の件数 
--------
        5

testdb=# TRUNCATE TABLE test_table;
TRUNCATE TABLE

testdb=# SELECT COUNT(*) as "TRUNCATE後の件数" FROM test_table;
 TRUNCATE後の件数 
------------
           0

testdb=# ROLLBACK;
ROLLBACK

testdb=# SELECT COUNT(*) as "ROLLBACK後の件数" FROM test_table;
 ROLLBACK後の件数 
------------
            5

✅ PostgreSQLではTRUNCATEをROLLBACKできることが確認できる

MySQL InnoDBの結果

mysql> SELECT COUNT(*) as '削除前の件数' FROM test_table_innodb;
+----------+
| 削除前の件数 |
+----------+
|        5 |
+----------+

mysql> TRUNCATE TABLE test_table_innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT COUNT(*) as 'TRUNCATE後の件数' FROM test_table_innodb;
+-------------+
| TRUNCATE後の件数 |
+-------------+
|           0 |
+-------------+

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) as 'ROLLBACK後の件数' FROM test_table_innodb;
+-------------+
| ROLLBACK後の件数 |
+-------------+
|            0 |
+-------------+

❌ MySQL InnoDBでもTRUNCATEをROLLBACKできないことが確認できる
（TRUNCATEの実行時に暗黙的COMMITが発生するため）

MySQL MyISAMの結果

mysql> SELECT COUNT(*) as '削除前の件数' FROM test_table_myisam;
+----------+
| 削除前の件数 |
+----------+
|        5 |
+----------+

mysql> TRUNCATE TABLE test_table_myisam;
Query OK, 5 rows affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) as 'ROLLBACK後の件数' FROM test_table_myisam;
+-------------+
| ROLLBACK後の件数 |
+-------------+
|            0 |
+-------------+

❌ MySQL MyISAMではTRUNCATEをROLLBACKできないことが確認できる

`TRUNCATE`が`ROLLBACK`できない場合の代替手段

ROLLBACK機能が必要な場合は、TRUNCATE の代わりに DELETE を使用する選択肢がある。
ただし、速度の面で課題がある。

-- 全件削除（`ROLLBACK`可能）
DELETE FROM table_name;

PostgreSQLとMySQLの公式ドキュメントによると、条件なしDELETEはTRUNCATE相当には最適化されない。

PostgreSQL公式ドキュメントより

“TRUNCATE provides a faster mechanism to remove all rows from a table.”

MySQL公式ドキュメントより

“When you do not need to know the number of deleted rows, the TRUNCATE TABLE statement is a faster way to empty a table than a DELETE statement with no WHERE clause.”

両者とも「テーブルを空にする場合はTRUNCATE TABLEの方が高速」と明記されており、実際にはDELETEは行単位での削除処理となる。

SQLiteのように「DELETE FROM table;」に対して「truncate optimization」を適用するRDBMSも存在するが、PostgreSQL・MySQLではそのような最適化は行われない。

出典

参考

PostgreSQL Documentation - TRUNCATE
https://www.postgresql.org/docs/current/sql-truncate.html
MySQL Documentation - TRUNCATE TABLE Statement
https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html
SQL Server Documentation - TRUNCATE TABLE
https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql
Oracle Database SQL Language Reference - TRUNCATE TABLE
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TRUNCATE-TABLE.html

おわりに

今回の検証で、TRUNCATEのROLLBACK対応はRDBMSによって異なることが確認できた。

ROLLBACK可能: PostgreSQL、SQL Server、SQLite
ROLLBACK不可: MySQL (InnoDB/MyISAM)、Oracle Database、MariaDB

実際の業務では、使用するRDBMSの特性を理解した上で、適切にバックアップやトランザクションを使用することが重要だと思う。
特に本番環境でのTRUNCATE実行前は、必ずバックアップを取得し、ROLLBACK不可能なRDBMSではROLLBACKに頼らない運用とするのが良いだろうな…。

はじめに

環境

各RDBMSでのTRUNCATE ROLLBACK対応状況

バージョン情報の検証結果

PostgreSQLの歴史

MySQLの歴史

SQL Serverの歴史

SQLiteの歴史

TRUNCATEのROLLBACKについて

PostgreSQL

MySQL

SQL Server

Oracle Database

SQLite

MariaDB

実際に検証してみる

PostgreSQL での検証

環境構築

テストデータの準備

TRUNCATE + ROLLBACK テスト

MySQL での検証

MySQL環境構築

InnoDBでのテスト（ROLLBACK不可の確認）

MyISAMでのテスト（ROLLBACK不可の確認）

検証結果

PostgreSQL の結果

MySQL InnoDBの結果

MySQL MyISAMの結果

TRUNCATEがROLLBACKできない場合の代替手段

参考

おわりに

各RDBMSでの`TRUNCATE` `ROLLBACK`対応状況

`TRUNCATE`の`ROLLBACK`について

`TRUNCATE` + `ROLLBACK` テスト

InnoDBでのテスト（`ROLLBACK`不可の確認）

MyISAMでのテスト（`ROLLBACK`不可の確認）

`TRUNCATE`が`ROLLBACK`できない場合の代替手段