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

我看你有戏 2020-11-05 04:34:46

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值是一样的,而且读取数据的时间也是几乎是一样的

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









...全文
303 2 打赏 收藏 举报
写回复
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
雨夹雪 2020-11-09


 


--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 

  • 打赏
  • 举报
回复
qq_457565758 2020-11-05
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
  • 打赏
  • 举报
回复
发帖
疑难问题

2.1w+

社区成员

MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
加入社区
帖子事件
创建了帖子
2020-11-05 04:34
社区公告
暂无公告