主页 / dba / 问题 / 334280
Accepted
The amateur programmer
Asked: 2023-12-22 19:03:07 +0800 CST

了解 MS SQL TSQL 事务中的表锁定

  • 772

我想让旧的后续查询在并行运行时免受数据竞争的影响。该查询使用特定条件检查表中是否存在一行，如果不存在该行，则会插入包含新数据的新行。旧查询粘贴如下：

BEGIN
    DECLARE @txtPer VARCHAR(MAX) = @nro;
    DECLARE @txtCmin VARCHAR(MAX) = @min;
    DECLARE @txtCmax VARCHAR(MAX) = @max;
    IF NOT EXISTS (SELECT 1 FROM SEND WHERE MSG LIKE (@txtPer + '%') AND STATE < 2 AND ID = 1)
    BEGIN
        ----Time of check is not time of use
        ----Someone could possibly do another insert before this == data race
        INSERT INTO SEND (SNDID, ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
        SELECT MAX(SNDID)+10,1,1,(@txtPer + @txtCmin + '           ' + @txtCmax),0,0,' ',getdate(),getdate() FROM SEND
    END
END

我想出了一个稍微干净一点的新版本，并使用独占表锁：

BEGIN TRANSACTION;
DECLARE @txtPer VARCHAR(MAX) = @nro;
DECLARE @txtCmin VARCHAR(MAX) = @min;
DECLARE @txtCmax VARCHAR(MAX) = @max;

DECLARE @IdMax INT
--Get MAX and simultaneously acquire lock for the table to prevent modifications during this transaction?
SELECT @IdMax=MAX(SNDID) FROM SEND WITH(TABLOCKX)

IF NOT EXISTS(SELECT 1 FROM SEND WHERE MSG LIKE (@txtPer + '%') AND STATE < 2 AND ID = 1)
BEGIN
    INSERT INTO SEND (SNDID, ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
    VALUES((@IdMax + 10),1,1,(@txtPer + @txtCmin + '           ' + @txtCmax),0,0,' ',getdate(),getdate())
END
COMMIT; --writes potential change and releases table lock?

我的理解是否正确，即在操作期间获取的表锁SELECT MAX()（可能还有同一事务中的其他表锁）一直保留到整个事务完成该COMMIT语句？DB 是旧的 MS SQL Server 2005。

t-sql

2 个回答

  1. Best Answer

    我的理解是否正确：在 SELECT MAX() 操作（可能还有同一事务中的其他操作）期间获取的表锁一直保持到整个事务使用 COMMIT 语句完成为止？

    是的，只要它是TABLOCKX并且不是TABLOCK。仅当您使用悲观锁定而不是乐观快照锁定时，这才有效，如 @DavidBrowne 所提到的。

    然而，这是极其低效的。

    • 它将完全锁定整个表的读取或写入。而是使用UPDLOCKand SERIALIZABLE（或HOLDLOCK） 提示。
      • SERIALIZABLE即使数据不存在（即范围锁），提示也是必要的，以确保其他人无法插入。
      • UPDLOCK为了在表上放置 U 锁，该提示是必要的，这可以防止其他任何请求 U 锁的人继续进行，同时仍然允许只读查询。这往往可以防止可序列化隔离级别上的死锁。
    • ID, MSG, STATE确保您的表在列或 上建立索引ID, STATE, MSG，以便NOT EXISTS提高效率。
    • 它还会在整个表中搜索最大值SNDID，您应该使用IDENTITY列。
    • 用于XACT_ABORT ON确保在发生错误时正确回滚。
    SET XACT_ABORT ON;
BEGIN TRAN;

IF NOT EXISTS (SELECT 1
    FROM SEND WITH (UPDLOCK, SERIALIZABLE)
    WHERE MSG LIKE CONCAT(@nro, '%')
      AND STATE < 2
      AND ID = 1
)
BEGIN
        INSERT INTO SEND (ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
        VALUES (1, 1, CONCAT(@nro, @min, '           ', @max), 0, 0, ' ', getdate(), getdate());
END;

COMMIT;
    • 2

  2. 我的理解是否正确：在 SELECT MAX() 操作（可能还有同一事务中的其他操作）期间获取的表锁一直保持到整个事务使用 COMMIT 语句完成为止？

    是的。但是，如果数据库配置了 READ COMMITTED SNAPSHOT，则该锁不会阻止另一个会话读取该表。

    序列化这些的正确方法是在选择上使用锁定提示来决定该行是否存在，如下所示：

    SELECT 1 FROM SEND WITH(UPDLOCK,HOLDLOCK)...

    • 1

相关问题