SQL 事务会自动回滚

Question

我正在使用Storageproc，DML语句发生在Transaction中。在事务中，我们将更新多个列，如下所示：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
UPDATE TBLMarket SET [YR] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [YR] = ' ';
UPDATE TBLMarket SET [TYBEG] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [TYBEG] = ' ';
UPDATE TBLMarket SET [TYEND] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [TYEND] = ' ';
UPDATE TBLMarket SET [PSEQ] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [PSEQ] = ' ';
UPDATE TBLMarket SET [CTI] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [CTI] = ' ';
UPDATE TBLMarket SET [GTI] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [GTI] = ' ';
UPDATE TBLMarket SET [PTI] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [PTI] = ' ';
UPDATE TBLMarket SET [TIPR] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [TIPR] = ' ';
UPDATE TBLMarket SET [RAR] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [RAR] = ' ';
UPDATE TBLMarket SET [TMOD_E] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [TMOD_E] = ' ';
UPDATE TBLMarket SET [ATI] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [ATI] = ' ';
UPDATE TBLMarket SET [PERC] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [PERC] = ' ';
UPDATE TBLMarket SET [PPCT] = NULL WHERE ImportTrnId = @pi_ImportTrnId AND [PPCT] = ' ';
.
.
.
.
.
-----120 update statements
.
.
COMMIT TRAN

但是当我并行运行多个事务(我们尝试使用 50 个事务(时，我们收到此错误。

"事务(进程 ID 102(在与另一个进程的锁资源上死锁，并被选为死锁受害者。重新运行事务。

注意：每个事务将具有不同的@pi_ImportTrnId，这意味着并行运行的事务永远不会尝试更新同一组行。

任何帮助/建议将不胜感激。谢谢！

Answer 1

There are no indexes- 这就是问题所在。在ImportTrnId列上放置一个索引， 以单语句方式重写查询，删除READ UNCOMMITTER，它将正常运行。

UPDATE t SET
[YR] = ISNULL(NULLIF(LTRIM(t.[YR]), ''), t.[YR]),
[TYBEG] = ISNULL(NULLIF(LTRIM(t.[TYBEG]), ''), t.[TYBEG]),
...
FROM TBLMarket t
WHERE ImportTrnId = @pi_ImportTrnId