时间戳

首页 > 解决方案 > 存储过程的优化

sql-server - 存储过程的优化

问题描述

我不是 SQL Server 专家，我有一个表 MainRBDBalance 和另一个 RBDTransaction：

CREATE TABLE [hybarmoney].[MAINRBDBALANCE]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [USERID] [bigint] NULL,
    [RBD] [decimal](18, 8) NULL,
    [CurrentDollar] [decimal](18, 8) NULL,
    [EquivalentRBD] [decimal](18, 8) NULL,
    [UpdatedRBD] [decimal](18, 8) NULL,
    [PreviousRBDBeforUpdate] [decimal](18, 8) NULL,
    [UPDATERBDFROMONEYEAAR] [decimal](18, 8) NULL,
    [RBDbeforeupdatefromoneyear] [decimal](18, 8) NULL,
    [TOBEADDEDFROM15DAYS] [decimal](18, 8) NULL,
    [RBDBEFOREUPDATFROM15DAYS] [decimal](18, 8) NULL
)

RbdTransaction 将具有生成的 otps 以及要从和向谁转移的金额。

CREATE TABLE RbdTransaction
(
    ID int identity(1,1),
    RBD Decimal (18,8),
    OTP Nvarchar(100),
    FromUserID bigint,
    ToUserID bigint,
    Active tinyint,
    CreatedDateTime Date
)

对于 RBD 从一个表到另一个表的事务，我写了一个存储过程，如下图。当我提交必要的参数时，存储过程会检查 RBD 是否在 MainRBDBALANCE 表的 FromUsersID 帐户中可用，然后需要检查对于相同的 RbdTransaction 表，则需要更新这两个表。

    CREATE PROCEDURE UpdateRBDTransactionMainRBDBalance (
    @OTP NVARCHAR(100)
    ,@FromUserID BIGINT
    ,@ToUserID BIGINT
    ,@RBD DECIMAL(18, 8)
    )
AS
BEGIN
    IF EXISTS (
            SELECT TOP 1 1
            FROM RbdTransaction
            WHERE OTP = @OTP
                AND FromUserID = @FromUserID
                AND ToUserID = @ToUserID
                AND RBD = @RBD
            )
    BEGIN
        IF EXISTS (
                SELECT TOP 1 1
                FROM hybarmoney.MAINRBDBALANCE
                WHERE USERID = @FromUserID
                    AND RBD >= @RBD
                )
        BEGIN
            BEGIN TRY
                BEGIN TRANSACTION

                UPDATE hybarmoney.MAINRBDBALANCE
                SET RBD = RBD - @RBD
                WHERE USERID = @FromUserID

                UPDATE RbdTransaction
                SET Active = 0
                WHERE OTP = @OTP
                    AND FromUserID = @FromUserID
                    AND ToUserID = @ToUserID
                    AND RBD = @RBD

                IF EXISTS (
                        SELECT TOP 1 1
                        FROM hybarmoney.MAINRBDBALANCE
                        WHERE USERID = @ToUserID
                        )
                BEGIN
                    UPDATE hybarmoney.MAINRBDBALANCE
                    SET RBD = RBD - @RBD
                    WHERE USERID = @ToUserID
                END
                ELSE
                BEGIN
                    INSERT INTO hybarmoney.MAINRBDBALANCE (
                        RBD
                        ,USERID
                        )
                    VALUES (
                        @RBD
                        ,@ToUserID
                        )
                END

                COMMIT TRANSACTION
            END TRY

            BEGIN CATCH
                ROLLBACK TRANSACTION
            END CATCH
        END
    END
END

我需要使用事务，因为我需要同时更新所有表。这是出于我的目的在 sql server 中进行事务的正确方法吗？

标签： sql-server

解决方案

您对交易的使用很好，但是我倾向于更改您在此处查看余额的方式：

    IF EXISTS (
            SELECT TOP 1 1
            FROM hybarmoney.MAINRBDBALANCE
            WHERE USERID = @FromUserID
                AND RBD >= @RBD
            )
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION

            UPDATE hybarmoney.MAINRBDBALANCE
            SET RBD = RBD - @RBD
            WHERE USERID = @FromUserID

这为余额在检查和更新之间的变化留下了一个小窗口。相反，我会在更新的同时进行检查：

        UPDATE hybarmoney.MAINRBDBALANCE
        SET RBD = RBD - @RBD
        WHERE USERID = @FromUserID
        AND RBD >= @RBD;

然后，您可以使用@@ROWCOUNT检查这是否更新了任何行，如果更新了，则用户有可用资金，如果@@ROWCOUNT返回 0，则相当于EXISTS检查失败。

您还可以删除EXISTS收件人帐户的支票，并将其替换为MERGE：

MERGE hybarmoney.MAINRBDBALANCE AS t WITH (UPDLOCK, SERIALIZABLE)
USING (VALUES (@ToUserID)) AS s (UserID)
    ON t.userID = s.UserID
WHEN MATCHED THEN 
    UPDATE SET RBD = RBD + @RBD
WHEN NOT MATCHED THEN 
    INSERT (UserID, USERID) VALUES (s.UserID, @RBD);

这再次消除了竞争条件导致重复用户的机会。此处解释了表锁防止竞争条件的必要性 - “UPSERT” Race Condition With MERGE

所以最后的程序最终会是这样的：

CREATE PROCEDURE UpdateRBDTransactionMainRBDBalance (
    @OTP NVARCHAR(100)
    ,@FromUserID BIGINT
    ,@ToUserID BIGINT
    ,@RBD DECIMAL(18, 8)
    )
AS
BEGIN
    IF EXISTS (
            SELECT 1
            FROM RbdTransaction
            WHERE OTP = @OTP
                AND FromUserID = @FromUserID
                AND ToUserID = @ToUserID
                AND RBD = @RBD
            )
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION

                UPDATE hybarmoney.MAINRBDBALANCE
                SET RBD = RBD - @RBD
                WHERE USERID = @FromUserID
                AND RBD >= @RBD;

                IF @@ROWCOUNT > 0
                BEGIN
                    UPDATE RbdTransaction
                    SET Active = 0
                    WHERE OTP = @OTP
                        AND FromUserID = @FromUserID
                        AND ToUserID = @ToUserID
                        AND RBD = @RBD;

                    MERGE hybarmoney.MAINRBDBALANCE AS t WITH (UPDLOCK, SERIALIZABLE)
                    USING (VALUES (@ToUserID)) AS s (UserID)
                        ON t.userID = s.UserID
                    WHEN MATCHED THEN 
                        UPDATE SET RBD = RBD + @RBD
                    WHEN NOT MATCHED THEN 
                        INSERT (UserID, USERID) VALUES (s.UserID, @RBD);

                END

            COMMIT TRANSACTION
        END TRY

        BEGIN CATCH
            ROLLBACK TRANSACTION
        END CATCH
    END
END

推荐阅读