何时在 SQL Server 中使用事务 [英] When to use Transactions in SQL Server

问题描述

关于如何使用事务有很多很多问题.我想知道的是何时?在什么情况下?什么类型的查询?Try-Catch 块可以代替吗?等等...

There are lots and lots of questions on HOW to use Transactions. What I want to know is WHEN? Under what circumstances? What types of queries? Can Try-Catch blocks suffice instead? Etc...

我设计了一个包含约 20 个表和约 20 个存储过程的数据库.目前我的 SP 都没有使用事务，但是整个过程中有许多 Try-Catch 块.原因是每次我尝试将它们包装在事务中时，SP 都会停止运行，最终我会丢失数据，并且比使用 Trans 时情况更糟.

I've designed a database with ~20 tables and ~20 stored procedures. Currently none of my SPs use a transaction, but there are numerous Try-Catch blocks throughout. The reason is because every time I tried to wrap them in a transaction the SP would cease to function and I would end up with missing data and worse off than had I used Trans.

又是这样...

1. 何时是使用交易的合适时机?
2. 作为一个后续问题，如果我使用它们，我该如何使用它们以仅防止其他 SP 同时访问相同的数据以防止损坏而不是导致我的 SP根本不起作用?

这是我为重命名产品而编写的一个小示例 SP:

Here's a little sample SP I wrote for renaming a product:

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

现在如果两个人同时使用它会怎样?我真的不想，也没有时间(不幸的是)去幻想.吻.在这种情况下是最好的.:)

Now what if two people were using this at the exact same time? I really don't want to, nor do I have time (unfortunately), to get to fancy. K.I.S.S. is best in this case. :)

推荐答案

当您进行的一组数据库操作需要原子时，您可以使用事务.

You use transactions when the set of database operations you are making needs to be atomic.

也就是说 - 他们都需要成功或失败.中间没有.

That is - they all need to succeed or fail. Nothing in between.

事务用于确保数据库始终处于一致状态.

Transactions are to be used to ensure that the database is always in a consistent state.

一般来说，除非有充分的理由不使用它们(例如长时间运行的进程)，否则请使用它们.请参阅这篇博文 了解详情.

In general, unless there is a good reason not to use them (long running process for instance), use them. See this blog post for details.

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

Try/Catch blocks have nothing to do with transactions - they are used for exception handling. The two concepts are not related and are not replacements for each other.

这篇关于何时在 SQL Server 中使用事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！