Truncating and inserting in a safe transaction on MySQL

Question

I am trying to TRUNCATE and INSERT data into a MySQL InnoDB table.

I wanted to make sure that if any statement in the transaction failed, the data will be untouched.

I can't have an empty or corrupted table if something fails.

This is how I planned to do it.

BEGIN TRANSACTION
TRUNCATE TABLE table_name
INSERT INTO table_name
COMMIT

I then learned that TRUNCATE TABLE table_name causes an implicit COMMIT, which makes the whole transcation pointless.

I then replaced the TRUNCATE TABLE table_name with a DELETE FROM table_name approach, which wasn't ideal - but that too causes an implicit COMMIT!

DROP TABLE table_name and CREATE TABLE table_name also causes an implicit COMMIT!

What method / logic is best to perform what I am trying to do?

Using MySQL 5.6.17 64-bit server running on Windows Server 2008 R2

I searched for DELETE on that page. DELETE does not appear at all. — RolandoMySQLDBA, Nov 05 '14 at 17:47
http://dba.stackexchange.com/questions/58709/row-locking-within-acid-transaction-innodb/58712#58712 — RolandoMySQLDBA, Nov 05 '14 at 17:56

RolandoMySQLDBA · Accepted Answer · 2014-11-05T18:16:45.663

7

Since TRUNCATE TABLE does an implicit commit, don't use it.

As long as the table does not have foreign keys, you can do it quickly like this:

CREATE TABLE mytable_new LIKE mytable; 
RENAME TABLE mytable TO mytable_old,mytable_new TO mytable;
INSERT INTO mytable ... ;

If the INSERT succeeds, then run this

DROP TABLE mytable_old;

If the INSERT rolls back, just revert back

RENAME TABLE mytable TO mytable_zap,mytable_old TO mytable;
DROP TABLE mytable_zap;

Give it a Try !!!

edited Nov 05 '14 at 18:16

answered Nov 05 '14 at 17:57

RolandoMySQLDBA

182,700
33
317
520

1

This leaves the database in a state where mytable does not exist. Use a RENAME table statement instead, which allows for two atomic renames – Shlomi Noach Nov 05 '14 at 18:12
@ShlomiNoach Thank you for the quick peer review :-) – RolandoMySQLDBA Nov 05 '14 at 18:17
Does it make more sense to insert into the new table before renaming it? – Haitham Gad Jun 13 '23 at 18:42

score 4 · Answer 2 · answered Nov 05 '14 at 18:18

DELETE FROM t

Does not cause an implicit commit and should work well. I'm actually using this exact solution in production and see no issue transaction-wise (of course deleting all rows from a large table has a significant impact on the size of the transaction, followed possibly by increased IO, increased size of binary logs etc.)

Truncating and inserting in a safe transaction on MySQL

2 Answers2