INSERT 和 DELETE 在同一个事务中? [英] INSERT and DELETE in the same transaction?

问题描述

我有一个包含一些数据的 Temp_Table.根据Temp_Table中的数据，删除其他表中的相关行，然后将Temp-table中的所有数据插入table1.就像下面的例子.

I have a Temp_Table with some data. Based on the data in the Temp_Table, I will delete the related rows from other tables, and after that, insert all data from the Temp-table to table1. Like the example below.

我可以通过什么方式在 Server2.Table1 上放置一个锁(我认为是独占的)并在释放该锁之前同时运行 Delete 和 Insert 语句?我不想让任何人阅读或在我执行删除/插入操作时写入 Table1.请注意，Delete 必须在插入语句之前运行.

In what way could I place a lock on Server2.Table1 (exclusive I supose) and run both the Delete and Insert statements before letting go of that lock? I don't want anyone to read or write to Table1 while I am doing my Delete / Inserts. Note that the Delete must be run before the Insert statement.

SSIS 有没有办法做到这一点?或者我可以在开始时使用开始事务并在语句之后提交事务吗?我担心删除和插入会在同一个事务中同时运行..

Is there a way is SSIS to do this? Or can I use Begin transaction in the beginning and commit transaction after the statements? I fear that both the Delete and Insert will run simultaneously while in the same transaction..

DELETE Table1 
FROM Table1 t1
    INNER JOIN (    
       SELECT Column2 FROM Temp_Table
       GROUP BY Column2
    ) t2 ON t1.Column2 = t2.Column2

INSERT INTO Table1
SELECT (Column1, Column2...)
FROM Temp_Table

推荐答案

SteveR 的评论是正确的，事务中的 Insert 和 Delete 语句没有同时运行.在对非生产数据库进行了一些测试后，我得到了以下结果:

SteveRs comment was correct, the Insert and Delete statements within a transaction didn't run simultaneously. After some testing on a non-production database, I ended up with the following:

BEGIN TRY
    BEGIN TRAN T1;

DELETE Table1 
FROM Table1 t1
    INNER JOIN (    
       SELECT Column2 FROM Temp_Table
       GROUP BY Column2
    ) t2 ON t1.Column2 = t2.Column2

INSERT INTO Table1 (Column 1, Column2...)
SELECT (Column1, Column2...)
FROM Temp_Table

COMMIT TRAN T1;

END TRY
BEGIN CATCH
        ROLLBACK TRAN T1;
END CATCH

先执行 Delete 语句，然后执行 Insert 语句.并且在语句之间不会释放 Exclusive 锁.

The Delete statement is executed first, then the Insert statement. And the Exclusive lock will not be released between the statements.

这篇关于INSERT 和 DELETE 在同一个事务中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！