none
Sometime SQL Server insert multiple record in same millisecond RRS feed

  • Question

  • Hi,

    I am using SQL Version "Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763)"

    Here is my insert statement in my procedure,

    Store Procedure:

    ALTER PROCEDURE dbo.CheckLoginUser ( @AgentUsername AS VARCHAR(150), @ClientUsername AS VARCHAR(150), @SecretKey AS UNIQUEIDENTIFIER, @Point AS INT, @IP AS VARCHAR(50), @WebsiteUrl AS NVARCHAR(500), @Share AS dbo.ClientShare READONLY, @Message AS VARCHAR(500) = '' OUTPUT, @Code AS INT = 0 OUTPUT ) AS BEGIN SET NOCOUNT ON; SET @Message = ''; SET @Code = 0; BEGIN TRY IF (dbo.TRIM(@AgentUsername) = '') BEGIN SET @Message = 'Invalid agent user name'; SET @Code = 2; END; ELSE IF (dbo.TRIM(@ClientUsername) = '') BEGIN SET @Message = 'Invalid client user name'; SET @Code = 2; END; ELSE BEGIN DECLARE @AgentUserID INT = 0; DECLARE @ClientUserID INT = 0; DECLARE @TempShare AS TABLE ( [UserID] INT NOT NULL, [parentLevel] [INT] NOT NULL, [userName] [VARCHAR](150) NOT NULL, [share] [DECIMAL](5, 2) NOT NULL ) DECLARE @Counter INT = 1, @DataLength INT = (SELECT COUNT(1) FROM @Share), @UserID INT = 0, @UserName VARCHAR(150) = ''; SET @AgentUserID = ISNULL( ( SELECT UserID FROM dbo.[User] U WITH (NOLOCK) INNER JOIN dbo.Agent A WITH (NOLOCK) ON A.AgentUserID = U.UserID WHERE U.IsDelete = 0 AND A.IsDelete = 0 AND U.IsActive = 1 AND A.IsActive = 1 AND U.Role = 'agent' AND U.Username = dbo.TRIM(LOWER(@AgentUsername)) AND A.SecretKey = @SecretKey ), 0 ); IF (@AgentUserID <> 0) BEGIN DECLARE @Token UNIQUEIDENTIFIER = NEWID(); SET @ClientUserID = ISNULL( ( SELECT U.UserID FROM dbo.[User] U INNER JOIN dbo.Client C ON C.ClientUserID = U.UserID WHERE C.IsDelete = 0 AND C.IsActive = 1 AND U.IsDelete = 0 AND U.IsActive = 1 AND Role = 'client' AND Username = dbo.TRIM(LOWER(@ClientUsername)) AND AgentUserID = @AgentUserID ), 0 ); IF (@ClientUserID <> 0) BEGIN WHILE @DataLength >= @Counter BEGIN SELECT @UserName = userName FROM @Share WHERE parentLevel = @Counter; EXEC dbo.UpdateAgentLoginDetails @AgentUserID, -- int @ClientUserID, -- int @UserName = @UserName, -- varchar(50) @UserID = @UserID OUTPUT, -- int @Message = @Message OUTPUT, -- varchar(500) @Code = @Code OUTPUT; -- int INSERT INTO @TempShare ( UserID, parentLevel, userName, share ) SELECT @UserID, parentLevel, userName, share FROM @Share WHERE parentLevel = @Counter; SET @Counter = @Counter + 1; END; UPDATE dbo.Client WITH (ROWLOCK) SET Points = @Point, Token = @Token, TokenCreatedDateTime = GETUTCDATE(), Share = (SELECT * FROM @TempShare FOR JSON AUTO) WHERE IsDelete = 0 AND IsActive = 1 AND ClientUserID = @ClientUserID; SET @Message = ''; SET @Code = 0; END; ELSE BEGIN DECLARE @TranCount INT; SET @TranCount = @@trancount; IF @TranCount = 0 BEGIN BEGIN TRANSACTION; END; ELSE BEGIN SAVE TRANSACTION AddUpdateTran; END; INSERT INTO dbo.[User] WITH (ROWLOCK) ( Username, Role, IsActive, IsDelete, CreatedDate, CreatedBy ) VALUES (dbo.TRIM(LOWER(@ClientUsername)), 'client', 1, 0, GETDATE(), @AgentUserID); SET @ClientUserID = SCOPE_IDENTITY(); WHILE @DataLength >= @Counter BEGIN SELECT @UserName = userName FROM @Share WHERE parentLevel = @Counter; EXEC dbo.UpdateAgentLoginDetails @AgentUserID, @ClientUserID, @UserName = @UserName, @UserID = @UserID OUTPUT, @Message = @Message OUTPUT, @Code = @Code OUTPUT; INSERT INTO @TempShare ( UserID, parentLevel, userName, share ) SELECT @UserID, parentLevel, userName, share FROM @Share WHERE parentLevel = @Counter; SET @Counter = @Counter + 1; END; INSERT INTO dbo.Client WITH (ROWLOCK) ( AgentUserID, ClientUserID, Commision, Points, Token, TokenCreatedDateTime, IsActive, IsDelete, CreatedDate, CreatedBy, Share ) SELECT TOP 1 @AgentUserID, @ClientUserID, AGM.Commision, @Point, @Token, GETUTCDATE(), 1, --IsActive 0, --IsDelete GETUTCDATE(), @AgentUserID, (SELECT * FROM @TempShare FOR JSON AUTO) FROM dbo.AgentGameMap AGM WITH (NOLOCK) WHERE AGM.IsActive = 1 AND AGM.IsDelete = 0 AND AGM.AgentID = ( SELECT AGM.AgentID FROM dbo.Agent WHERE IsActive = 1 AND IsDelete = 0 AND AgentUserID = @AgentUserID ); SET @Message = 'Client added successfully'; SET @Code = 0; --Commit transaction and exit IF @TranCount = 0 BEGIN COMMIT; END; END; SELECT dbo.TRIM(LOWER(@AgentUsername)) AS AgentUsername, dbo.TRIM(LOWER(@ClientUsername)) AS ClientUsername, @SecretKey AS SecretKey, @Point AS Point, LobbyUrl + 'binary?Token=' + CAST(@Token AS NVARCHAR(50)) AS LobbyUrl, @Token AS Token FROM dbo.Agent WITH (NOLOCK) WHERE IsDelete = 0 AND IsActive = 1 AND AgentUserID = @AgentUserID; END; ELSE BEGIN SET @Message = 'Agent does not exist'; SET @Code = 2; END; END; END TRY BEGIN CATCH SET @Message = 'Error occurred in CheckLoginUser'; SET @Code = 2; END CATCH; END;

    Here, we have simple insert statement as below for inserting record in User table.

     INSERT INTO dbo.[User] WITH (ROWLOCK)
                        (
                            Username,
                            Role,
                            IsActive,
                            IsDelete,
                            CreatedDate,
                            CreatedBy
                        )
                        VALUES
                        (dbo.TRIM(LOWER(@ClientUsername)), 'client', 1, 0, GETDATE(), @AgentUserID);
    

    The issue here is some time, this single insert statement insert multiple time after this procedure run only once. It occurs randomly in 5,6 days once.

    Please refer below records for reference

    Sql Table Select:

    10 playnwin247 Agent 2020-01-16 06:51:51.070 11 PrimeExch Agent 2020-01-16 07:01:27.753 12 brizzosport.com Agent 2020-01-16 07:04:06.063 13 park.bet Agent 2020-01-16 07:07:24.703 14 aura24.bet Agent 2020-01-16 07:34:57.197 15 kt11 client 2020-01-16 12:39:43.567 16 kt11 client 2020-01-16 12:39:43.567 17 kt11 client 2020-01-16 12:39:43.567 18 kt11 client 2020-01-16 12:39:43.567 19 kt11 client 2020-01-16 12:39:43.567 20 kt11 client 2020-01-16 12:39:43.567 21 kt11 client 2020-01-16 12:39:43.567 22 kt11 client 2020-01-16 12:39:43.567 23 kt11 client 2020-01-16 12:39:43.567 24 kt11 client 2020-01-16 12:39:43.567 25 kt11 client 2020-01-16 12:39:43.567 26 kt11 client 2020-01-16 12:39:43.567 27 kt11 client 2020-01-16 12:40:44.927 28 Admin Parent 2020-01-16 12:40:44.943 29 demom Parent 2020-01-16 12:40:44.957 30 demod Parent 2020-01-16 12:40:44.957 31 Mahakalexch Agent 2020-01-16 12:54:54.843 32 kt11 client 2020-01-16 13:01:33.030 33 bose07 client 2020-01-16 15:28:27.733 34 bose07 client 2020-01-16 15:28:27.733 35 bose07 client 2020-01-16 15:28:27.733 36 bose07 client 2020-01-16 15:28:27.733 37 rohithjain client 2020-01-16 16:11:20.517 38 rohithjain client 2020-01-16 16:11:20.517 39 mbabu8989 client 2020-01-16 17:33:49.710 40 jt97 client 2020-01-17 06:33:32.680


    Please help me to resolve this.

    Thanks


    • Edited by Hardik Surani Friday, January 17, 2020 8:27 AM Added more details
    Friday, January 17, 2020 7:26 AM

All replies

  • Hi 

    As GETDATE() keeps changing with respect to the time the record gets inserted, it varies.

    To get the same datetime stamp across the records, declare a variable by initiating its value as GETDATE() and use that variable as part of your insert statement as below.

    If you want to have datetime stamps inserted in a more accurate way, use next level data types like DATETIME2 OR DATETIMEOFFSET.

    Reference for accurate results.

    DECLARE @timestamp DATETIME2 = GETDATE();
    INSERT INTO dbo.[User] WITH (ROWLOCK)
    (
        Username,
        Role,
        IsActive,
        IsDelete,
        CreatedDate,
        CreatedBy
    )
    VALUES (dbo.TRIM(LOWER(@ClientUsername)), 'client', 1, 0, @timestamp, @AgentUserID);

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.



    Friday, January 17, 2020 7:42 AM
  • Please help me to resolve this.

    Not clear what your issue is? If your application fires in high speed several INSERT commands then you can have same timestamps; the data type "datetype" do have a 1/300 second  = 3.333 milli seconds resolution. For a higher resolution use datetime2 data type.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, January 17, 2020 7:44 AM
  • Hi Hardik Surani,

    Thank you for your issue .

    Sorry that I could not understand your requirement . Would you like to get some special result or did you meet some errors? Could you please share us  your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) along with your expected result or error messages ? So that we’ll get a right direction and make some test.

     

    When you post your issue, please refer POSTING TIPS - Code, Images, Hyperlinks, Details.

     

    Best Regards,

    Rachel



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 17, 2020 7:45 AM
  • Perhaps you should add a unique constraint to Username column and maybe also reconsider the usage of transactions and hints?

    Friday, January 17, 2020 9:08 AM
  • Hello,

    You have :

    - begin transaction

    save transaction

    - commit

    I do not see :

    - rollback transaction

    So, you may have a situation when you have saved and unfinished transaction prior you do call your procedure and it may be auto commited.

    Check your error handler for uncommited transaction.

    P.S. Please post the minimal code to reproduce the problem.


    Sincerely, Highly skilled coding monkey.

    Friday, January 17, 2020 9:45 AM
  • I can't add unique constraint to Username because my as business logic diff-diff agent can have same username of client.
    Saturday, January 18, 2020 5:12 AM
  • I have done rollback transaction in my catch block but it didn't work
    Saturday, January 18, 2020 5:13 AM
  • I am using SQL Version "Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763)"

    This is the original release of SQL 2017. The latest CU is CU18. While you don't necessarily should install every CU that is released, you should definitely not be on the RTM version at this stage. Install CU18 or ask your DBA to do this.

            IF (dbo.TRIM(@AgentUsername) = '')

    Rather than making this call all over again, do

    SELECT @AgentUsername = trim(@AgentUserName),
           @ClientUsername = trim(@ClientUsername)

    (There is a built-in function trim(), starting with SQL 2017.)

                SET @AgentUserID = ISNULL(
                                   (
                                       SELECT UserID
                                       FROM dbo.[User] U WITH (NOLOCK)
                                           INNER JOIN dbo.Agent A WITH (NOLOCK)
                                               ON A.AgentUserID = U.UserID
                                       WHERE U.IsDelete = 0
                                             AND A.IsDelete = 0
                                             AND U.IsActive = 1
                                             AND A.IsActive = 1
                                             AND U.Role = 'agent'
                                             AND U.Username = dbo.TRIM(LOWER(@AgentUsername))
                                             AND A.SecretKey = @SecretKey
                                   ),

    Are you really sure that you want NOLOCK here? What if there are uncommited rows that matches the criteria? Do you really want that user ID even if the  uncomitted row would be rolled back?

    Please refer below records for reference

    I can't map that data with any of your tables. Can you give the SELECT statement, so that we know what data we are looking at?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 18, 2020 10:54 AM