3

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" ;)

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Slawomir
  • 453
  • 4
  • 18

3 Answers3

3

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

kroczu
  • 31
  • 3
  • Welcome to the DBA StackExchange. Your answer is "on the money" when it comes to using CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE. The documentation says CREATE TABLE and DROP TABLE statements do not commit a transaction if the TEMPORARY keyword is used. (This does not apply to other operations on temporary tables such as ALTER TABLE and CREATE INDEX, which do cause a commit.). This means TRUNCATE TABLE will cause implicit commit becasue it is not design to look under the hood and see that the table is temporary. Nice Work Around. +1 !!! – RolandoMySQLDBA Apr 24 '19 at 15:27
2

It is true that CREATE TEMPORARY TABLE IF NOT EXISTS.. is DDL but did you know that TRUNCATE TABLE is also itself DDL ??? I have mentioned this many times over the years ...

The MySQL Documentation on TRUNCATE TABLE says the following:

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways in MySQL 5.6:

  • Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.

  • Truncate operations cause an implicit commit, and so cannot be rolled back.

  • Truncation operations cannot be performed if the session holds an active table lock.

  • TRUNCATE TABLE fails for an InnoDB table or NDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.

  • Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is “0 rows affected,” which should be interpreted as “no information.”

  • As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted.

  • Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

  • When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected.

  • The TRUNCATE TABLE statement does not invoke ON DELETE triggers.

So, to answer your question, the way you are handling it is the only way.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • I get that part of truncate being a DDL. The reason why I got into this is because CREATE TEMP TABLE (clearly a DDL) is an exception and does not commit, yet truncating the very TEMP table is committing. TO me, this is error prone, that's all. If they could make one work that way, they should have made the the other also work the same way. Meaning both CREATE TEMP and TRUNCATE (temp) should not commit. As a full-stack developer, not a DB expert, this one nuance can really get you. – Slawomir Aug 05 '16 at 18:57
0

CREATE TEMPORARY TABLE IF NOT EXISTS ... ENGINE=MyISAM

That way that table would take part in the transaction.

Rick James
  • 78,038
  • 5
  • 47
  • 113