Can TRUNCATE TABLE be rolled back if it runs inside an open transaction?

## Why this question separates candidates Almost everyone can recite "TRUNCATE is faster than DELETE". Far fewer can say what happens when a truncation sits inside a transaction that later fails — and that is the difference between a recoverable mistake and a table you have to restore from backup. The honest answer is *engine-dependent*, and stating it as a universal rule in either direction is the mistake interviewers are listening for. ## The two behaviours **Transactional TRUNCATE.** PostgreSQL and SQL Server implement `TRUNCATE TABLE` as a transactional statement. It participates in the surrounding transaction like any other write: ```sql BEGIN; TRUNCATE TABLE staging_rows; SELECT count(*) FROM staging_rows; -- 0, inside this transaction ROLLBACK; SELECT count(*) FROM staging_rows; -- the original rows are back ``` You can also truncate and reload inside one transaction, so other sessions never observe the empty intermediate state — a genuinely useful pattern for refreshing a lookup or staging table. **Implicit-commit TRUNCATE.** MySQL (InnoDB) and Oracle classify `TRUNCATE TABLE` as DDL, and DDL in those engines commits implicitly. Concretely, in MySQL: ```sql START TRANSACTION; INSERT INTO audit_marker VALUES (1); -- work in the transaction TRUNCATE TABLE staging_rows; -- implicit COMMIT happens here ROLLBACK; -- no-op ``` Two things go wrong at once. The truncation is permanent, *and* the `INSERT` that preceded it was committed by the implicit commit — so the `ROLLBACK` does not undo that either. This second effect surprises people far more than the first: a statement you thought was one step in a transaction silently ended the transaction. ## What DELETE guarantees instead `DELETE` is DML in every engine. It never commits implicitly, it always rolls back, and it always leaves the enclosing transaction intact: ```sql BEGIN; DELETE FROM staging_rows; ROLLBACK; -- rows restored, everywhere, no exceptions ``` That universality is the reason to choose `DELETE` for a destructive operation you might need to abort, even when the table is large enough that `TRUNCATE` would be much faster. ## Savepoints do not rescue you A natural follow-up thought is "I will wrap the truncate in a savepoint". On an implicit-commit engine that does not help: the commit happens when the `TRUNCATE` executes, and once a transaction has been committed there are no savepoints left to roll back to. Savepoints only ever undo work inside a still-open transaction. ## Practical consequences for scripts If you write an ETL or migration script that does *truncate-then-reload*, ask which engine it will run on. - On PostgreSQL or SQL Server you can make the whole refresh atomic: `BEGIN; TRUNCATE TABLE dim_customer; INSERT INTO dim_customer SELECT ...; COMMIT;` — a failure mid-load leaves the old data in place. - On MySQL or Oracle you cannot. The instant the truncation runs the table is empty and committed, so a failed load leaves an empty table visible to everyone. Mitigations are structural rather than transactional: load into a new table and swap names, or use `DELETE` so the whole refresh really is one transaction. Also remember that a truncation you *can* roll back is still not free of consequences — an identity counter that was restarted, or a statement-level truncate trigger that already ran and wrote somewhere outside the database, may not be undone by the rollback in the way you assume. Rollback restores table contents; it does not un-send side effects that left the database. ## How to answer in an interview Say "it depends on the engine", then name which engines fall on each side and why: transactional versus DDL-with-implicit-commit. Add the practical rule — if you might need to undo it, use `DELETE`, and never assume a `TRUNCATE` you did not test is safe inside your transaction. Volunteering the implicit-commit side effect on preceding statements is the detail that reads as real experience.