SQLServer 并发问题 先select再update

[child_1986]

SQLServer 并发问题

例如

并发执行以下两个事务

事务1

begin tran
declare @count int =0
select @count=[Count] from test4 where id=1
select @count as count1
waitfor delay '00:00:30' --等待30秒
update test4 set [Count]=@count+1 where id=1
commit tran

事务2

begin tran
declare @count int =0
select @count=[Count] from test4 where id=1 --holdlock人为加锁
select @count as count2
update test4 set [Count]=@count+1 where id=1
commit tran

要求：事务2的count2是事务1中已经update 加1后的数据

怎么在数据库层面解决

试过锁，总是出现死锁的问题

[Tiger_Zhao]

事务1

begin tran

DECLARE @t TABLE ([Count] int)
declare @count int =0

-- 都在更新、加锁的同时读取旧值
update test4
   set [Count]=[Count]+1
OUTPUT DELETED.[Count]
  INTO @t
 where id=1

waitfor delay '00:00:30'

SELECT @count = [Count] FROM @t
SELECT @count AS count1

commit tran 

SELECT * FROM test4

事务2

begin tran

DECLARE @t TABLE ([Count] int)
declare @count int =0

update test4
   set [Count]=[Count]+1
OUTPUT DELETED.[Count]
  INTO @t
 where id=1

SELECT @count = [Count] FROM @t

SELECT @count AS count2

commit tran 

SELECT * FROM test4

[child_1986]

1楼的亲，我可能没写明白，意思只是，update时会使用select出的数据，不一定是+1这样的操作

[gw6328]

没有看懂题目

[Tiger_Zhao]

你没明白我的意思。
避免并发问题，必须一上来就加更新锁。
我用 UPDATE/OUTPUT 只是为了省略 SELECT。
如果你一定要分开SELECT/UPDATE，如下：

事务1

begin tran 
declare @count int =0
select @count=[Count] from test4 WITH(UPDLOCK,HOLDLOCK) where id=1
select @count as count1
waitfor delay '00:00:30'
update test4 set [Count]=@count+1 where id=1
commit tran 

select * from TEST4

事务2

begin tran
declare @count int =0
select @count=[Count] from test4 WITH(UPDLOCK,HOLDLOCK) where  id=1
select @count as count2
update test4 set [Count]=@count+1 where id=1
commit tran 

select * from TEST4

[child_1986]

嗯嗯，谢谢楼上，我也尝试了一下，只能加锁的方式