Help to see the stored procedure, about the transaction, multi-process call a stored procedure-CodePudding

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

- according to the contrast TimeIns time to determine whether to need to grab 
DECLARE @ LastTime DATETIME 
SELECT the TOP 1 @ LastTime=TimeIns FROM [TbLeague] ORDER BY TimeIns DESC 


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

The BEGIN TRAN 
SELECT the 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 ()) & gt; 5 
AND DATEDIFF (MINUTE, TimeIns, @ LastTime) & lt; 5 
The ORDER BY [sLeague] ASC 

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


SELECT * FROM [TbLeague] WHERE sLeague=@ sLeague

Basic requirement description:
1. SLeague fields in TbLeague the values in the table is the only
2. Multiple processes GetLeague regularly call stored procedures, hope every process to obtain the value different sLeague
3. To get the data after TbLeague [Status] column in the set to 1, after handling process after the Status is set to 0, in order to facilitate the next screening read

Question:
While on a multi-process call the stored procedure, occasional lines of two get sLeague value is the same, and read data time is almost the same

What's the problem, consult everybody, thank you

CodePudding user response:

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

The BEGIN TRAN
SELECT the 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 ()) & gt; 5
AND DATEDIFF (MINUTE, TimeIns, @ LastTime) & lt; 5
The ORDER BY [sLeague] ASC

IF @ sLeague<>
'1'The BEGIN
The UPDATE [dbo] [TbLeague] SET [Status]=1 WHERE sLeague=@ sLeague
If @ error=0 then commite
The else
The begin
The rollback commit
Return 0
End
END - if @ error=0 then commite
The else
The begin
The rollback commit
Return 0
'end - IF @ sLeague<>
'1'
End TRAN