解决同步登录主键冲突问题

DROP PROCEDURE FetchNewSegment
GO
CREATE PROC FetchNewSegment
@SegmentID INT OUT,
@CurID INT OUT,
@MaxID INT OUT,
@KeepSeconds INT
AS
BEGIN
DECLARE @MinID INT
DECLARE @RetryTimes INT
DECLARE @UpdateCount INT
DECLARE @CSegmentCount INT
SET @CSegmentCount = ISNULL((SELECT COUNT(MT_0.SegmentID) AS MT_0_SegmentID_COUNT FROM Segment AS MT_0 ), 0) 
SET @RetryTimes = 100

WHILE (@RetryTimes > 0)
BEGIN
SET @SegmentID = (FLOOR((RAND() * ((@CSegmentCount - 1000)))) + 1000);
IF EXISTS(SELECT TOP 1 MT_0.SegmentID AS MT_0_SegmentID_EXIST FROM Segment AS MT_0 WHERE (MT_0.SegmentID = @SegmentID)) 
BEGIN
UPDATE Segment SET
[OverDueTime] = DATEADD(SECOND, @KeepSeconds, GETDATE())
WHERE (((Segment.SegmentID = @SegmentID)) AND (NOT Segment.IsUseout = 1 AND (((Segment.OverDueTime IS NULL) OR (Segment.OverDueTime < GETDATE())) AND NOT EXISTS(SELECT TOP 1 MT_0.SegmentID AS MT_0_SegmentID_EXIST FROM LoginInfo AS MT_0 WHERE (MT_0.SegmentID = Segment.SegmentID)) )));
SET @UpdateCount = @@ROWCOUNT;
IF (@UpdateCount > 0)
BEGIN
SELECT @CurID = Segment.CurID,@MaxID = Segment.MaxID
FROM Segment
WHERE (Segment.SegmentID = @SegmentID);
BREAK
END
END
ELSE
BEGIN TRY
SET @MinID = ((((@SegmentID - 1)) * 10000) + 1)
SET @CurID = @MinID
SET @MaxID = (@SegmentID * 10000)

INSERT INTO Segment
(SegmentID, MinID, MaxID, CurID, IsUseout, OverDueTime)
SELECT @SegmentID AS SegmentID, @MinID AS MinID, @MaxID AS MaxID, @CurID AS CurID, CAST(0 AS BIT) AS IsUseOut, DATEADD(SECOND, @KeepSeconds, GETDATE()) AS OverDueTime

SET @UpdateCount = 1
BREAK
END TRY
BEGIN CATCH
SET @UpdateCount = 0
END CATCH;
SET @RetryTimes = (@RetryTimes - 1)
END

IF (@UpdateCount = 0)
BEGIN
SET @SegmentID = Null;
SET @CurID = Null;
SET @MaxID = Null
END
END
GO

 

转载于:https://www.cnblogs.com/fanzf/p/5584145.html