patternsqlMinor
Truncate temporary table commits transaction?
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" ;)
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
"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.