何时在SQL Server中使用事务？

Question

何时在SQL Server中使用事务？

t-sqltransactionstry-catch

40

有很多问题是关于如何使用事务的，但我想知道的是何时使用它？在什么情况下使用？适用于哪些查询类型？是否可以仅使用Try-Catch块？等等...

我设计了一个包含约20个表和20个存储过程的数据库。目前，我的所有存储过程都没有使用事务，但是其中有许多Try-Catch块。原因是每次尝试将它们包装在一个事务中时，存储过程将停止运行，我将遗失数据，比起不使用事务要更糟。

所以再次提问...

何时使用事务是合适的？
作为跟进问题，如果我使用了事务，如何使用它们以仅防止其他存储过程同时访问相同的数据，以避免损坏而不是导致我的存储过程完全无法运行？

这是我写的一个重命名产品的小样例SP:

CREATE PROCEDURE spRenameProduct
    @pKey int = NULL,
    @pName varchar(50)
AS
BEGIN
    BEGIN TRY
        IF LTRIM(RTRIM(@pName)) = '' SET @pName = NULL
        IF NOT @pKey IS NULL AND NOT @pName IS NULL BEGIN
            declare @pKeyExisting int = (select MIN(ID) from rProduct where Product like @pName and not ID = @pKey)
            IF @pKeyExisting is null BEGIN
                update rProduct set IsValid = 1, Product = @pName where ID = @pKey
            END ELSE BEGIN
                update Request set ProductID = @pKeyExisting where ProductID = @pKey
                update StatusReport set ProductID = @pKeyExisting where ProductID = @pKey
                delete from rProduct where ID = @pKey
            END
        END
    END TRY BEGIN CATCH END CATCH
END

现在如果有两个人同时使用这个，会怎么样呢？我真的不想也没有时间（不幸的是）变得很复杂。在这种情况下，简单易行是最好的选择 :)

- Chiramisu

1

你正在混淆隔离级别和事务。使用隔离级别，您可以在单个语句的持续时间内锁定其他更新。如果您需要一组更新作为整体成功或失败，则将它们包装在事务中。如果您希望两个更新和一个删除保持同步，则事务可以实现这一点。 - paparazzo

@BalamBalam 所以换句话说，将两个更新和一个删除操作封装在事务中可能是有意义的，但总是封装整个存储过程似乎没有太多意义？即便如此，我觉得有时候使用事务会让工作变得太麻烦，因为开发人员有时会犯错误。我不希望应用程序因为一个无害的异常（比如空引用之类的）而失败。有什么建议吗？我还是新手 :) - Chiramisu

我不知道该如何更清楚地表达了。如果您需要整个集合成功或失败，则将其包装在事务中。经典示例是从支票帐户转账到储蓄帐户的资金转移-如果存款失败，则我希望取款也失败。如果由于开发人员的错误导致数据不同步是可以接受的，则不要使用事务。 - paparazzo

事务必须是原子性的（它是一个工作单元，不依赖于前面和后面的事务），一致性的（数据要么提交或回滚，没有“介于”更新和未更新之间的情况），隔离的（没有事务能看到当前事务的中间结果），持久的（如果数据已提交，即使系统在崩溃后仍然存在）。 - NoWar

4个回答

4

这篇文章是在搜索引擎中排名第一的结果。总体上来说，共识是：除非某些情况下，否则始终使用事务。实际上，答案恰恰相反。明确的事务，无论是由于误用、设计不良还是简单的运气不佳，都可能使数据库乃至整个服务器甚至整个基础架构崩溃。我见过这种情况。

我们有几种情况：

a) 事务根本没有起到帮助作用。根据一些答案和链接的博客文章，我们应该只在必要时使用它。为什么？如果没有帮助你的东西，那就把它扔掉。所以不要“以防万一”地加上事务。

b) 事务可能会有一些用处，你有一些疑问。检查你的代码和数据，这个事务的价值是什么？你的工作就是了解。如果因为不确定而放弃事务，意味着你需要更多地提高你的SQL技能以及解决问题的能力。

c) 我需要这个事务。真的，我是认真的。现在的问题是，我能取消它吗？怎样最小化其影响？如果你需要进行琐碎的插入/更新/删除操作，请使用MERGE。MERGE是一个原子操作，不需要明确的事务。

假设我们有任何需要是原子性的任务。我们有一段执行这个任务的代码。只在这段代码中添加Begin/Commit/Rollback事务就足够了吗？

我让你自己选择。但答案是否定的。需要是原子性的是操作的结果。而不是我们手头恰好有的一段代码的执行。我们需要审查代码。评估它。并大部分时间重写它。必须放入事务的操作是需要原子性的操作。而不是创建临时表或读取XML文件。

也许我们根本不需要原子性操作。某些关键表中是否有一个RowStatus列？如果有，我们可以将我们的行标记为RowStatus='Pending' or 'In Process' or 'Step 14'。那么您想要添加事务的过程是否幂等？如果不是，您必须更改它（带有或不带有事务）。如果没有事务，必须能够安全地连续运行n次，并且每次运行都必须继续上一次的工作或者如果工作已经完成，则不执行任何操作。如果真正需要原子性，则无需显式事务的幂等过程是最好的选择。我们可以像下面这样处理一切：

DO THIS WHERE RowStatus = Something.

最后，在确认一切正常之后（可能使用事务），我们进行最终的更新：

Update Table1 SET RowStatus ='Done' WHERE RowStatus ='Almost Done';
Update Table2 SET RowStatus ='Done' WHERE RowStatus ='Almost Done'
etc.

最终情况：您需要在不同的表中做很多事情，还要保证整个过程是原子性的。

选项1）使用触发器。是的，就是触发器。只是不要在其中放置显式事务。触发器有一个不好的声誉，它们所拥有的公平份额是触发器在事务内执行（这就是为什么您可以在触发器中说回滚事务而不必启动任何事务）。

选项2）重写您的存储过程/脚本。将所有远程捕获/读取、计算和繁重的工作都放在临时表、变量或实际表中（在您的数据库中创建一个Temp、Staging、Whatever模式来达到这个目的）。没有事务。如果您需要复制整个数据库，那就这样做。添加一些Try/Catch和检查以确保一切正常。最后，Begin Try Begin transaction，执行您的原子操作（此时，将进行所有微不足道的操作），并相应地回滚/提交。示例：

如果您有以下内容：

BEGIN TRY
    BEGIN TRANSACTION;
    --All my stuff
    IF @MyCheck = 'Error'
        THROW 51000, 'MyError', 1;
    IF @@TranCount > 0 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    --Other stuff
    IF @@TranCount > 0 ROLLBACK TRANSACTION;
END CATCH;

你需要重构为以下代码：

DECLARE @SomeThing int
DROP TABLE IF EXISTS #MyTable
CREATE TABLE #MyTable(MyColumn int);

TRUNCATE TABLE Temp.MyOtherTable
DELETE TABLE  Temp.MyOtherTable2 WHERE x IS NULL;
/*My Heavy stuff*/
--Bring data from linked server to #MyTable
--Bla bla etc

BEGIN TRY
    BEGIN TRANSACTION;
    MERGE MyRealTable T USING #MyTable... 
    MERGE MyRealTable2 T USING Temp.MyOtherTable... 
    IF @MyCheck = 'Error' THROW 51000, 'MyError', 1;
    IF @@TranCount > 0 COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    --My catch stuff
    IF @@TranCount > 0  ROLLBACK TRANSACTION;
END CATCH;

注意：永远不要在事务（或触发器）中调用存储过程。或者要非常小心。对此过程的进一步修改可能会破坏性能或带来重大问题。

- renegm

4

如果您在单个操作/函数中有多个更新或插入查询，请使用事务。

- silvedo

4

常见的答案是事务可以使数据库操作具有原子性。混淆在于这意味着什么。它与涉及到的特定操作无关，无论是SELECT、UPDATE、DELETE等。它与数据本身的语义含义有关。从操作的角度来看，从下往上，我们称它们为一个整体，原子性。但是，从抽象层次来看，从上往下看，我们说我们拥有信息保持一致性。

一个简单的例子是，如果你有两个账户，并且不想在它们之间转移资金时创造或破坏资金。另外一个更微妙的例子是，如果你有一组需要作为一个整体进行创建或删除的数据。换句话说，部分信息没有意义。我想一个例子可能是，如果你有一个用户，并希望始终保证他们有名和姓。而不是部分名称。

话虽如此，人们提出了短语和经验法则来表达原子性的含义，例如“所有操作都需要成功或失败”。此外，人们倾向于注意模式，例如SELECT不需要事务。

- qmckinsey

如果我在事务中使用单个选择语句和不使用事务有什么区别吗？我的意思是，为什么我需要使用事务来读取数据？ - Abdul Mohsin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Oded · Accepted Answer

当你进行一系列数据库操作时，需要确保这些操作是原子性的，即它们要么全部成功，要么全部失败，没有中间状态。这时需要使用事务。

使用事务可以确保数据库始终处于一致的状态。

通常情况下，除非有好的理由不使用它们（例如长时间运行的进程），否则应该使用事务。详情请参阅此博客文章。

Try/Catch块与事务无关——它们用于异常处理。这两个概念不相关，也不能相互替代。