下面是我写的一个存储过程,可以在高并发的情况下为什么会导致Sql阻塞啊?请教一下如何优化及建立索引,非常感谢!
CREATE PROCEDURE dbo.getNewMsg
@UserID int,
@SUserID int,
@ID int output,
@SNickName varchar(25) output,
@MsgContent varchar(2000) output,
@DateAndTime datetime output
as
set nocount on
Begin Tran
begin
IF @UserID is null set @UserID=0
select top 1 @ID=ID,@SNickName=SNickName,@MsgContent=MsgContent,@DateAndTime=DateAndTime
from dbo.[Message] with (updlock)
where UserID=@UserID and SUserID=@SUserID and status=0
update dbo.[Message] set status=1 where ID=@ID
end
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return
End
GO
CREATE PROCEDURE dbo.getNewMsg
@UserID int,
@SUserID int,
@ID int output,
@SNickName varchar(25) output,
@MsgContent varchar(2000) output,
@DateAndTime datetime output
as
set nocount on
Begin Tran
begin
IF @UserID is null set @UserID=0
select top 1 @ID=ID,@SNickName=SNickName,@MsgContent=MsgContent,@DateAndTime=DateAndTime
from dbo.[Message] with (updlock)
where UserID=@UserID and SUserID=@SUserID and status=0
update dbo.[Message] set status=1 where ID=@ID
end
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return
End
GO
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货