sql - SQL Server 中的并发执行

Question

表架构 (SQL Server 2012)

Create Table InterestBuffer
(
    AccountNo CHAR(17) PRIMARY KEY,
    CalculatedInterest MONEY,
    ProvisionedInterest MONEY,
    AccomodatedInterest MONEY,
)

Create Table #tempInterestCalc
(
    AccountNo CHAR(17) PRIMARY KEY,
    CalculatedInterest MONEY
)

我正在做一个upsert。更新那些存在的行并插入其他行。

UPDATE A
SET A.CalculatedInterest = A.CalculatedInterest + B.CalculatedInterest
FROM InterestBuffer A
INNER JOIN #tempInterestCalc B ON A.AccountNo = B.AccountNo

INSERT INTO InterestBuffer
SELECT A.AccountNo, A.CalculatedInterest, 0, 0
FROM #tempInterestCalc A
LEFT JOIN InterestBuffer B ON A.AccountNo = B.AccountNo
WHERE B.AccountNo IS NULL

一切正常。并发执行期间出现问题。我正在将数据插入 #tempInterestCalc通过连接其他各种表，包括与 InterestBuffer 的左连接表和不同的数据集插入 #tempInterestCalc对于每个并发执行。

我的问题是有时执行会被另一个执行锁定，直到我连续提交它们。

我的问题是，当我提供不同的数据集时，行锁对其他并发操作不应该有任何影响。任何建议将不胜感激。

更新 1:我用过 SP_LOCK用于 InterestBuffer 表。它说 IndId = 1, Type = KEY, Mode = X, Status = GRANT .

我认为更新和插入会阻止其他事务进行幻读。

更新 2:对不起!之前我说过更新没问题。但是现在我意识到第一个事务写入正在阻止第二个事务写入。在第一笔交易中，我运行更新并插入。在第二个事务中，在#tempInterestCalc 表中插入数据后，我只执行以下操作，并且它运行良好。

--INSERT DATA INTO #tempInterestCalc 

SELECT * FROM #tempInterestCalc 
RETURN

--UPDATE InterestBuffer

--INSERT InterestBuffer

更新 3:我认为我的问题是在更新期间从 InterestBuffer 读取数据并插入到 InterestBuffer 中。

更新 4:如果我 REBUILD INDEX，我下面的答案有时会起作用InterestBuffer 表中的 BranchCode。批量插入/更新是否有任何原因导致索引出现问题？？？

更新 5:我已经读过，如果需要锁定页面的最大行数以进行批量更新，那么 SQL Server 可能会锁定该页面。有没有办法查看哪一行包含哪一页或哪一页在执行过程中要锁定和释放？

更新 6:我正在提供我的场景。

CREATE TABLE [dbo].[Account](
        [AccountNo] [char](17) NOT NULL,
        [BranchCode] [char](4) NOT NULL,
     CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
    (
        [AccountNo] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE TABLE [dbo].[InterestBuffer](
    [AccountNo] [char](17) NOT NULL,
    [BranchCode] [char](4) NOT NULL,
    [CalculatedInterest] [money] NOT NULL,
 CONSTRAINT [PK_Buffer] PRIMARY KEY CLUSTERED 
(
    [AccountNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

查询分支 0001:

BEGIN TRAN

Declare @BranchCode AS Char(4) = '0001'
Declare @CalculatedInterestNew MONEY = 10

CREATE TABLE #tempInterestCalc
(
    AccountNo Char(17),
    BranchCode Char(4),
    CalculatedInterestNew MONEY,
    CalculatedInterestOld MONEY
)

INSERT INTO #tempInterestCalc
SELECT A.AccountNo, A.BranchCode, ISNULL(B.CalculatedInterest, 0), B.CalculatedInterest
FROM Account A 
LEFT JOIN InterestBuffer B ON A.AccountNo = B.AccountNo AND A.BranchCode = B.BranchCode
WHERE A.BranchCode = @BranchCode

UPDATE A
SET A.CalculatedInterest = B.CalculatedInterestNew + @CalculatedInterestNew
FROM InterestBuffer A
INNER JOIN #tempInterestCalc B ON A.AccountNo = B.AccountNo AND A.BranchCode = B.BranchCode
WHERE A.BranchCode = @BranchCode

INSERT INTO InterestBuffer
SELECT A.AccountNo, A.BranchCode, A.CalculatedInterestNew + @CalculatedInterestNew
FROM #tempInterestCalc A
WHERE A.CalculatedInterestOld IS NULL

DROP TABLE #tempInterestCalc
--ROLLBACK
--COMMIT TRAN

对于 Branch 0002, 0003，只需将 @BranchCode 变量值更改为 0002 &0003 并同时运行它们。

Answer 1

您可能会遇到潜在的死锁问题，因为您正在对 InterestBuffer 进行另一次读取。写入后的表。如果另一个事务阻塞了 InterestBuffer 的一部分，则事务可能会死锁。用于更新的表，并且您的事务正在尝试再次从中读取以进行插入所需的选择。

你说你已经离开了 InterestBuffer在计算您的 #tempInterestCalc 时表...为什么不使用它来缓存来自 InterestBuffer 所需的一些数据所以你不必再读一遍？

将您的临时表更改为:

Create Table #tempInterestCalc
(
    AccountNo CHAR(17) PRIMARY KEY,
    CalculatedInterestNew MONEY,
    CalculatedInterestOld MONEY
)

您可能希望在开始事务之前设置可重复读取隔离级别:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

这是更严格的锁定，但会阻止其他事务尝试同时处理相同的记录，您可能需要这样做，因为您正在组合旧值和新值。考虑这个场景:

事务 1 读取数据并希望将 0.03 添加到现有CalculatedInterest 5.0。

事务 2 读取数据，想在 5.0.02 上加 0.02。

交易 1 更新 CalculatedInterest到 5.03。

事务 2 的更新将事务 1 中的值覆盖到
5.03(而不是添加到它并提出 5.05)。

如果您的 可能不需要这个确定 事务永远不会触及相同的记录，但如果这样读提交不会让事务 2 读取值，直到事务 1 完成它。

然后首先将您的事务分为不同的读取阶段，然后是写入阶段:

--insert data into #tempInterestCalc and include the previous interest value
insert into #tempInterestCalc
select AccountNo, 
    Query.CalculatedInterest CalculatedInterestNew, 
    InterestBuffer.CalculatedInterest CalculatedInterestOLD
from 
    (
    ...
    ) Query
left join InterestBuffer
on Query.AccountNo = InterestBuffer.AccountNo

UPDATE A
SET A.CalculatedInterest = B.CalculatedInterestNew + B.CalculatedInterestOld
FROM InterestBuffer A
INNER JOIN #tempInterestCalc B ON A.AccountNo = B.AccountNo

INSERT INTO InterestBuffer
SELECT A.AccountNo, A.CalculatedInterestNew, 0, 0
FROM #tempInterestCalc A
--no join here needed now to read from InterestBuffer
WHERE CalculatedInterestOld is null

这不应该是死锁……但是您可能会看到由于 Lock Escalation 导致的“不必要的”阻塞。 ，尤其是当您要更新大量行时。一旦表上有超过 5000 个锁，它就会升级到一个表。在事务完成之前，没有其他事务将能够继续。这不一定是坏事……您只是想确保您的交易尽可能短，以免锁定其他交易太久。如果锁升级导致您出现问题，则有 some things you can do to mitigate this如:

将您的事务分解为更小的工作块，从而创建更少的锁。

确保您有一个有效的查询计划。

明智地使用锁定提示。

检查您的查询计划，看看是否有 InterestBuffer 的任何表扫描。在任何声明中...尤其是您的初始人口 #tempInterestCalc因为你没有展示你是如何构建它的。

如果您绝对不会同时更新一个分支中的帐户，那么您可以考虑保持主键不变，但将聚集索引更改为 Branch, Account number (顺序很重要)。这将使您在同一分支的所有记录物理上彼此相邻，并减少您的计划执行表扫描或锁定其他事务可能需要的页面的机会。然后您也可以使用 PAGLOCK提示，这将鼓励 SQL Server 按页而不是按行锁定，并防止达到触发锁升级的阈值。为此，请修改您的代码 更新 6 在你的问题中看起来像这样:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN

Declare @BranchCode AS Char(4) = '0001'
Declare @CalculatedInterestNew MONEY = 10

CREATE TABLE #tempInterestCalc
(
    AccountNo Char(17),
    BranchCode Char(4),
    CalculatedInterestNew MONEY,
    CalculatedInterestOld MONEY
)

INSERT INTO #tempInterestCalc
SELECT A.AccountNo, A.BranchCode, ISNULL(B.CalculatedInterest, 0), B.CalculatedInterest
FROM Account A
LEFT JOIN InterestBuffer B
ON A.AccountNo = B.AccountNo AND A.BranchCode = B.BranchCode
WHERE A.BranchCode = @BranchCode

UPDATE A WITH (PAGLOCK)
SET A.CalculatedInterest = B.CalculatedInterestNew + @CalculatedInterestNew
FROM InterestBuffer A
INNER JOIN #tempInterestCalc B ON A.AccountNo = B.AccountNo AND A.BranchCode = B.BranchCode
WHERE A.BranchCode = @BranchCode

INSERT INTO InterestBuffer WITH (PAGLOCK)
SELECT A.AccountNo, A.BranchCode, A.CalculatedInterestNew + @CalculatedInterestNew
FROM #tempInterestCalc A
WHERE A.CalculatedInterestOld IS NULL

DROP TABLE #tempInterestCalc
--ROLLBACK
--COMMIT TRAN

因为记录是物理排序在一起的，所以这应该只锁定几页......即使在更新数千条记录时也是如此。然后，您可以在 0001 的同时运行分支 0003 的事务，而不会出现任何阻塞问题。但是，如果您尝试同时执行相邻分支(例如 0002)，则可能会遇到阻塞问题。这是因为分支 0001 和 0002 中的某些记录可能会共享同一页面。

如果你真的需要分离你的分支，你可以考虑使用 Partitioned Table or Index .我对它们了解不多，但听起来它可能对您尝试做的事情有用，但它也可能伴随着它自己的一系列并发症。