Hans Hans - 6 months ago 19
SQL Question

Truncate followed by Insert in one transaction MySQL

I have a database with frequently changing raw data.
There is a pretty complex process that creates a summary table of this data.

I've created a Stored Procedure to create this table which uses a sequence of three tables. As I don't yet track what data has been updated (I'm working on that) I simply populate these queries with a sequence of:

TRUNCATE tablename ...
INSERT INTO tablename ...


I have moved all of these three queries into a single transaction:

START TRANSACTION READ WRITE;

[Sequence of TRUNCATE / INSERT queries]

COMMIT;


I expected that after the commit I would immediately see the resulting table but there seems to be a 2-3 second period where the table is empty. Is there a simple way to fix this?

Answer

NO!! , you cant use TRUNCATE in a transaction. Truncate deletes the hole Table File an re-creates it . Truncate operations cause an implicit commit.

a idea:

It can be work with RENAME. Rename is a single transaction

CREATE TABLE table_copy like your_table;
INSERT DATA in table_copy;
RENAME your_table TO your_table_old, table_copy TO your_table;
DROP table_copy;