HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Truncate temporary table commits transaction?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
temporarytruncatecommitstransactiontable

Problem

Is this correct that a "CREATE TEMPORARY TABLE IF NOT EXISTS.." does not commit current transaction (I like that), but a TRUNCATE TABLE, which is temporary, does implicitly commit any transaction?

Does this mean that the only way to have a fresh temporary table without committing the current transaction is to run (after above DDL statement):

DELETE FROM temp-table-name;

This TRUNCATE auto-commit behavior caused a rather nasty bug in my app -- the rollback didn't go all the way up because of the "TRUNCATE barrier" ;)

Solution

You can use DROP TEMPORARY TABLE instead of TRUNCATE, then CREATE TEMPORARY TABLE(...) again, it will not commit the transaction but you gain the speed of TRUNCATE statement.

"CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used"
Statements That Cause an Implicit Commit

Context

StackExchange Database Administrators Q#146031, answer score: 3

Revisions (0)

No revisions yet.