SQL Server 中的并发执行

表架构 (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 INDEXInterestBuffer 表中的 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

For Branch 0002, 0003 just change the @BranchCode variable value to 0002 &0003 and Run them simultaneously.

您可能会遇到潜在的死锁问题，因为您正在对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.03CalculatedInterest of 5.0.
• 事务2读取数据，想要在5.0的基础上加0.02。
• 交易1更新CalculatedInterest至 5.03。
• 事务 2 的更新将事务 1 的值覆盖为 5.03（而不是添加到它并得出 5.05）。

也许你不需要这个，如果你的sure该事务永远不会接触相同的记录，但如果是这样，则读取提交将不会让事务 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

这不应该死锁......但是你可能会看到“不必要的”阻塞，因为锁升级 https://technet.microsoft.com/en-us/library/ms184286%28v=sql.100%29.aspx，特别是当您要更新大量行时。一旦表上的锁超过 5000 个，它将升级为表。在该交易完成之前，其他交易将无法继续。这不一定是坏事......您只是想确保您的事务尽可能短，以免锁定其他事务太久。如果锁升级给您带来了问题，有您可以采取一些措施来缓解这种情况 https://support.microsoft.com/en-us/kb/323630例如：

• 将事务分解为更小的工作块，从而创建更少的锁。
• 确保您有一个高效的查询计划。
• 明智地使用锁定提示。

检查您的查询计划并查看是否有任何表扫描InterestBuffer在任何陈述中......特别是对于您的初始人口#tempInterestCalc因为你没有展示你是如何构建它的。

如果您绝对不会同时更新一个分支中的帐户，那么您可能会考虑保持主键相同，但将聚集索引更改为Branch, Account number（顺序很重要）。这将使同一分支的所有记录物理上彼此相邻，并减少您的计划执行表扫描或锁定其他事务可能需要的页面的机会。然后您还可以使用PAGLOCK提示，这将鼓励 SQL Server 按页而不是按行锁定，并防止达到触发锁定升级的阈值。为此，请修改您的代码UPDATE 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 中的一些记录可能会共享同一页。

如果您确实需要分离分支，您可以考虑使用分区表或索引 https://msdn.microsoft.com/en-us/library/ms190787%28v=sql.100%29.aspx。我对它们了解不多，但听起来它可能对你想做的事情有用，但它也可能伴随着它自己的一系列复杂性。