Truncate Table Within Transaction

Truncate Table Within Transaction

In SQL Server, you can rollback a TRUNCATE from a transaction. It does write page deallocation to the log, as you mentioned.

After truncate , we can not rollback data in SQL?

Yes, a TRUNCATE can be rolled back in a transaction in SQL Server. There are actually only a few things that can't be rolled back with a transaction in SQL Server. For example, you can even roll back other DDL statements (such as the DROP and CREATE below):

USE Sandbox;
GO

CREATE TABLE dbo.Table1 (I int);
CREATE TABLE dbo.Table2 (I int);
GO
WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N))
INSERT INTO dbo.Table1
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3, N N4;

WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N))
INSERT INTO dbo.Table2
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3, N N4;
GO

BEGIN TRANSACTION SampleTran;

TRUNCATE TABLE dbo.Table1;

CREATE TABLE dbo.Table3 (I int);

INSERT INTO dbo.Table3
SELECT I
FROM dbo.Table2;

DROP TABLE dbo.Table2;

ROLLBACK TRANSACTION SampleTran;
GO

--Contains 10,000 rows
SELECT *
FROM dbo.Table1;
GO
--Still exists
SELECT *
FROM dbo.Table2;
GO
--Doesn't exist
SELECT *
FROM dbo.Table3;
GO

--Clean up    
DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;

Despite "intellisense" probably telling you that dbo.Table2 doesn't exist in the lower batches, it does, as that transaction was rolled back. (Intellisense will also think that dbo.Table3 still exists, which it will not.)

Unlike the myth that people seem to believe, TRUNCATE is logged. Unlike DELETE, however, TRUNCATE deletes the pages the data is stored on, not the individual rows. A log of what pages are deleted is still written, so a TRUNCATE can still be rolled back, as the deletion of those pages is simply not committed.

MySQL: Truncate Table within Transaction?

A better way to accomplish this might be to insert the data into a new table, and then use rename on both tables in order to swap them. A single rename is all that's needed for the swap, and this is an atomic action, which means the users won't even be able to detect that it happened, except for the new data showing up. You can then truncate/delete the old data.

Truncate followed by Insert in one transaction MySQL

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;

Sequelize: Using truncate and insert within same transaction

Apparently this had to do with:

Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement.

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

References:

https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html

https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

For my scenario, I could choose to go with delete instead of truncate:

           result = await models.sequelize.transaction(
                async (t) => {
                    //truncate table
                    await infx.destroy({
                        where: {},
                        transaction: t
                    });                     

                    //insert new bulk collection
                    return await infx.bulkCreate(dataCollection, {
                        transaction: t
                    });
                });
        } catch (error) {
            logger.error(error);
        }

How to safely TRUNCATE and re-populate a table in PostgreSQL?

Truncate and re-populate the table in a single transaction. The truncate isn't visible to concurrent readers, who continue to see the old data. Correction per @AlexanderEmelianov and the docs:

TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred. See Section 13.5 for more details.

so after the TRUNCATEing txn commits , concurrent txns started before the TRUNCATE will see the table as empty.

---

Any transaction attempting to write to the table after it's truncated will wait until the transaction doing the truncate either commits or rolls back.

BEGIN;

TRUNCATE TABLE my_table;

INSERT INTO my_table (blah) VALUES (blah), (blah), (blah);

COMMIT;

You can COPY instead of INSERT too. Anything in a normal transaction.

Even better, there's an optimisation in PostgreSQL that makes populating a table after a truncate faster than if you do it otherwise.

---

Related Topics