Truncate Table operation
Go Up to Truncate Table
The Truncate Table command is executed in two phases:
- A locking phase.
- A truncation phase.
Upon command, the returned target tables are always locked for exclusive access. If the DEFERRED qualifier is specified, the truncation phase of the operation does not occur until transaction COMMIT. Specifically, foreign dependent tables with non-ON DELETE CASCADE reference constraints are only locked for protected write.
Because these tables are not being physically dismantled, reads can be allowed without blocking on the empty table. The protected write lock prevents insertion of new rows that might have a valid reference on a table with an imminent truncation.
If the CASCADE qualifier is specified, then the target table is locked as well as referencing tables with foreign key constraints that depend on the target table. The locking protocol works in a top-down fashion, locking the target table first followed by the referencing tables and recursively applied to those referencing tables with ON DELETE CASCADE foreign key constraints. This is referred to as a dependent table tree hierarchy.
The truncation protocol works in a bottom-up fashion. First, it truncates foreign dependent tables, this prevents dangling foreign key references if the total execution fails unexpectedly before completion. During this phase, all table data, index and blob storage is released back to the database for reuse. Once the tables have been truncated, the table locks are downgraded to the level they would have acquired for normal write access. For a consistency mode transaction this is protected write. For a concurrency mode transaction this is shared write.