增加Transaction保护，加上独占锁会造成潜在性能问题吗？

Question

系统原因的存储过程类似CREATE TABLE TT(A INT NOT NULL PRIMARY KEY，B INT)
GO
...
IF NOT EXISTS ( SELECT 1 FROM TT WHERE A = 1 )
BEGIN
INSERT INTO TT (A， B) VALUES (1, 2)
END
ELSE
BEGIN
UPDATE TT SET B = 3 WHERE A = 1
END
...在某些情况下，或者高并发是会产生主键冲突，原因是多个调用者访问相同数据，同时进入INSERT...
BEGIN TRAN
IF NOT EXISTS ( SELECT 1 FROM TT WITH(UPDLOCK) WHERE A = 1 )
BEGIN
INSERT INTO TT (A, B) VALUES (1, 2)
END
ELSE
BEGIN
UPDATE TT SET B = 3 WHERE A = 1
END
COMMIT
GO经测试(开两个查询窗口，一个使用WAITFOR DELAY '00:00:05')，可以避免主键冲突，而且我个人认为不存在任何潜在的性能问题。但有人质疑会增加死锁检测等等额外开销，所以特发此贴，希望能有比较权威的说明。

Answer 1

默认隔离级别 READ-COMMITTED。下面是摘自我邮件的内容
although the lock is upgraded from shared lock to exclusive lock, I don’t think it will bring any additional overheads, and, on application server side, no exception will be raised, thus, it will save a certain amount of overheads to build exceptions and call stack.我认为在正常情况下，A是主键，所以应该使用行锁，在WITH(UPDLOCK)的时候只是锁升级，目的是阻塞其他访问同一个资源的进程，让其处于等待状态，以保证多进程访问同一资源是后是按照先得到独占锁的进程先执行。而对于绝大多数情况，多线程/进程访问不同的数据的时候，应该跟没有改动时没有任何区别。因此我认为本身这个改动不会造成IO问题，也不会造成性能问题。另外一个是隐形事务和显性事务，我不觉得这里存在一个性能问题。大家多讨论。

Answer 2

这个问题本质是事务隔离级别的问题,而非讨论是否增加额外开销..默认的事务隔离级别(read committed)下,此例有可能产生主键冲突,
若要想避免,则提高事务隔离级别到最高级(serializable)即可.理论就不解释了,详见Google.

Answer 3

至于隐形事务和显性事务,是另外一回事了,
SQL Server默认是显性事务,即自动提交.

Answer 4

我认为：
SQL Server默认是隐性事务,即自动提交及一条SQL一个事务，你不必显示的声明BEGIN TRAN，当然这个不在讨论范围。提高到repeatable read / serializable不一定解决问题，反而有可能有deal-lock，我无法解释原因，但是你可以简单测试一下。用显示事务+UPDLOCK后则没有，这个你可以开2个查询窗口测试，第一个窗口delay几秒先跑，第二个接着立刻执行，最终结果会看到两个查询都成功，而且最终更新的值是后一个结束的事务。当然解决方法不再讨论范围，有兴趣的同学可以自行测试。我的问题是用顶楼的方法解决了问题之后，是否存在潜在的性能问题，究其原因是如何分析得知的。

Answer 5

IF NOT EXISTS ( SELECT 1 FROM TT WITH(UPDLOCK) WHERE A = 1 )
BEGIN  --多个线程同时并发，这里会不会有问题？
    INSERT INTO TT (A, B) VALUES (1, 2)
...

Answer 6

BEGIN TRAN
IF NOT EXISTS ( SELECT 1 FROM TT WITH(UPDLOCK) WHERE A = @p_A )
用@p_A输入参数更清楚一些这里是经过测试的，多线程多进程如果使用相同的@p_A，则会阻塞(Block)在这里，即Process 1先拿到UPDLOCK，Process 2就在这等，而不会进入BEGIN后的语句。Process 1提交或回滚之后，Process 2才会得到锁，才能继续执行。当然不同的@p_A则不会有Block以上是我的认知。

Answer 7

DECLARE @p_A INT
SET @p_A = 1000  --一个表中以前不存在的值BEGIN TRAN 
IF NOT EXISTS ( SELECT 1 FROM TT WITH(UPDLOCK) WHERE A = @p_A ) 
BEGIN 
WAITFOR DELAY '0:0:3'INSERT INTO TT (A, B) 
VALUES (@p_A, 2) 
END 
ELSE BEGIN 
  UPDATE TT SET B = 3 WHERE A = @p_A 
END 
COMMIT 
GO

Answer 8

没错，大致是楼上的代码。Process 1:
DECLARE @p_A INT
SET @p_A = 1000  --一个表中以前不存在的值BEGIN TRAN 
IF NOT EXISTS ( SELECT 1 FROM TT WITH(UPDLOCK) WHERE A = @p_A ) 
BEGIN 
  WAITFOR DELAY '0:0:3' --为了模拟阻塞
  
  INSERT INTO TT (A, B) 
  VALUES (@p_A, 2) 
END 
ELSE BEGIN 
  UPDATE TT SET B = 3 WHERE A = @p_A 
END 
COMMIT 
GOProcess 2:
DECLARE @p_A INT
SET @p_A = 1000  --一个表中以前不存在的值BEGIN TRAN 
IF NOT EXISTS ( SELECT 1 FROM TT WITH(UPDLOCK) WHERE A = @p_A ) 
BEGIN 
  INSERT INTO TT (A, B) 
  VALUES (@p_A, 2) 
END 
ELSE BEGIN 
  UPDATE TT SET B = 3 WHERE A = @p_A 
END 
COMMIT 
GO先跑Process 1的代码，然后立刻跑Process 2的代码，可以见到#2一直在等，最终B的Value=3，两个Process都没有出错，都正常退出。所以在多线程多进程环境中，@p_A的值不断变化（偶尔可能重复，而且不存在TT中），以上代码存在性能问题的证明是什么。我是觉得没有。

Answer 9

测试过了，就是这样WITH(UPDLOCK, HOLDLOCK)之前可能用SET TRANSACTION ISOLATION LEVEL改来改去更改了默认的隔离级别

Answer 10

不需要，都测试过第三个查询窗口，是立刻返回的
DECLARE @p_A INT
SET @p_A = 1001  --一个表中以前不存在的值，不同于1000BEGIN TRAN 
IF NOT EXISTS ( SELECT 1 FROM TT WITH(UPDLOCK, HOLDLOCK) WHERE A = @p_A ) 
BEGIN 
  INSERT INTO TT (A, B) 
  VALUES (@p_A, 2) 
END 
ELSE BEGIN 
  UPDATE TT SET B = 3 WHERE A = @p_A 
END 
SELECT * FROM TT;
COMMIT 
GO

Answer 11

OK，不管怎样，要求是可以实现了，而且只阻塞相同PK的访问。我剩下关系的相对于无显示BEGIN TRAN和UPDLOCK, HOLDLOCK的，有没有任何影响性能的地方？如果读写都频繁是否有潜在问题？如果我允许脏读呢（NOLOCK)？还会有问题？

Answer 12

如果删除WITH(UPDLOCK, HOLDLOCK)，只增加SET TRANSACTION ISOLATION LEVEL SERIALIZABLE则Process 1成功
Process 2失败
Msg 1205, Level 13, State 48, Line 8
Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Process 3等待WITH(UPDLOCK, HOLDLOCK)和SET TRANSACTION ISOLATION LEVEL SERIALIZABLE并不完全一样，一个是Transaction Level的，一个Syntax Level的，SERIALIZABLE则导致系统失去并发。

Answer 13

Statement Level，不是Syntax Level，写错 :p