帮忙看看这个存储过程, 关于事务处理,多进程调用存储过程的问题

[我看你有戏]

USE [mybak]
GO
/****** Object:  StoredProcedure [dbo].[GetLeague]    Script Date: 2020/11/5 8:00:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[GetLeague]
AS

--根据对比TimeIns的时间来确定是否需要抓取
DECLARE @LastTime DATETIME
SELECT TOP 1 @LastTime=TimeIns FROM [TbLeague] ORDER BY TimeIns DESC


DECLARE @sLeague NVARCHAR(50)
SET @sLeague='-1'

BEGIN   TRAN
SELECT TOP 1 @sLeague=sLeague  FROM [dbo].[TbLeague]  WHERE  [Status]=0 
  AND sLeague IN (SELECT sLeague FROM [dbo].[TbLeagueFav] WHERE flag=1) 
  AND DATEDIFF(MINUTE,TimeCatch,GETDATE())>5  
  AND DATEDIFF(MINUTE,TimeIns,@LastTime)<5
  ORDER BY [sLeague] ASC

  IF @sLeague<>'-1'
  BEGIN
  	UPDATE [dbo].[TbLeague] SET [Status]=1 WHERE sLeague=@sLeague
  END
COMMIT TRAN


 SELECT * FROM [TbLeague] WHERE sLeague=@sLeague

基本需求描述:
1.sLeague 字段在TbLeague 表中的值是唯一的
2.多个进程会定时调用GetLeague存储过程,希望每个进程获取到不一样的sLeague值
3.获取数据后 TbLeague 中的 [Status] 列设置为1, 进程处理完后有会把Status设置为0,为了方便下一次筛选读取

问题:
多进程调用这个存储过程的时候发现,偶发行的两次获取到的sLeague值是一样的,而且读取数据的时间也是几乎是一样的

是什么问题导致的,请教各位高手,谢谢

[lao_yunger]

DECLARE @sLeague NVARCHAR(50) SET @sLeague='-1' BEGIN TRAN SELECT TOP 1 @sLeague=sLeague FROM [dbo].[TbLeague] WHERE [Status]=0 AND sLeague IN (SELECT sLeague FROM [dbo].[TbLeagueFav] WHERE flag=1) AND DATEDIFF(MINUTE,TimeCatch,GETDATE())>5 AND DATEDIFF(MINUTE,TimeIns,@LastTime)<5 ORDER BY [sLeague] ASC IF @sLeague<>'-1' BEGIN UPDATE [dbo].[TbLeague] SET [Status]=1 WHERE sLeague=@sLeague if @error=0 then commite else begin rollback commit return 0 end END ---if @error=0 then commite else begin rollback commit return 0 'end --- IF @sLeague<>'-1' end TRAN

[ダ雨夹雪リ]

 


--1.你把select取变量,改成update取,update会加排他锁,这样不会重复取到值
--2.你可以用WAITFOR TIME指定一个时间,多开几个查询窗口测试一把
--需要排序的话
DECLARE @sLeague VARCHAR(50)
;
WITH ct
AS
(
	SELECT sLeague,[Status],ROW_NUMBER() OVER(ORDER BY [sLeague]) AS id    FROM [dbo].[TbLeague]  WHERE  [Status]=0 
		AND sLeague IN (SELECT sLeague FROM [dbo].[TbLeagueFav] WHERE flag=1) 
)
UPDATE ct set @sLeague=sLeague ,[Status] = 1
WHERE id = 1
SELECT @sLeague 

--不需要排序的话
UPDATE TOP (1) TbLeague set @sLeague=sLeague ,[Status] = 1 
WHERE  
[Status]=0 AND sLeague IN (SELECT sLeague FROM TbLeagueFav WHERE flag=1) 
SELECT @sLeague