我要多线程从数据库中取一条记录,并且取完后更新记录状态,怎样让多线程不会取到同一条记录,这些线程可能在不同的进程中.我希望通过事务来控制,但好像不行,存储过程如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[up_GetEmailReceiver]
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN
BEGIN TRANSACTION T1
BEGIN TRY
declare @recieveId varchar(200)
set @recieveid = ''
select top 1 @recieveId = RecieveId from RecieveList with(updlock) where Status = 0 order by SendLevel desc
if(@recieveId <> '')
begin
update RecieveList set Status = 1,SendTime = GetDate() where RecieveId= @recieveId and Status = 0
IF @@error<>0 or @@rowcount = 0
BEGIN
ROLLBACK TRANSACTION T1
-- SET ROWCOUNT 0
RETURN 0
END
select * from RecieveList where RecieveId = @recieveId
end COMMIT TRANSACTION T1
-- SET ROWCOUNT 0
RETURN 1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T1
-- SET ROWCOUNT 0
RETURN 0
END CATCH
END
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER PROCEDURE [dbo].[up_GetEmailReceiver]
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN
BEGIN TRANSACTION T1
BEGIN TRY
declare @recieveId varchar(200)
set @recieveid = ''
select top 1 @recieveId = RecieveId from RecieveList with(updlock) where Status = 0 order by SendLevel desc
if(@recieveId <> '')
begin
update RecieveList set Status = 1,SendTime = GetDate() where RecieveId= @recieveId and Status = 0
IF @@error<>0 or @@rowcount = 0
BEGIN
ROLLBACK TRANSACTION T1
-- SET ROWCOUNT 0
RETURN 0
END
select * from RecieveList where RecieveId = @recieveId
end COMMIT TRANSACTION T1
-- SET ROWCOUNT 0
RETURN 1
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION T1
-- SET ROWCOUNT 0
RETURN 0
END CATCH
END
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货