TRUNCATE then ROLLBACK, but the rows are still gone — explain why
A developer ran this inside an explicit transaction and expected ROLLBACK to restore the table, but the rows never came back:
BEGIN;
TRUNCATE TABLE orders;
ROLLBACK;
SELECT COUNT(*) FROM orders; -- returns 0, not the original countDiagnose why the ROLLBACK did not restore the rows.
On MySQL and many engines TRUNCATE is DDL, and DDL triggers an implicit COMMIT of the open transaction before it runs. So TRUNCATE committed the instant it ran, and the later ROLLBACK had nothing to undo. A DELETE is DML and would have rolled back.
- ✗Believing TRUNCATE participates in the surrounding transaction like DELETE
- ✗Blaming timing or disk flushing rather than the implicit commit
- ✗Thinking ROLLBACK cannot undo any row removal at all, even DELETE
- →Which other statements cause the same implicit commit before they run?
- →How would you rewrite the operation so it is genuinely rollback-safe?
TRUNCATE is DDL, and on MySQL (and most engines) any DDL statement forces an implicit COMMIT of the open transaction before it executes. So BEGIN; TRUNCATE ... actually commits everything up to that point and empties the table outside the transaction's control; the following ROLLBACK has no open work to reverse.
To make the operation reversible, use DML instead:
BEGIN;
DELETE FROM orders; -- DML: participates in the transaction
ROLLBACK; -- restores every rowDELETE is logged row-by-row and is fully transactional, so ROLLBACK brings the rows back. The trade-off is speed: TRUNCATE is far faster on a large table precisely because it skips per-row logging — which is also why it cannot be rolled back.
