Concurrent access to duplicate records

  • Hi all,

    Got a situation I haven't had to deal with before. Basically, I have a table which stores products. This table contains several unimportant fields for this question, and a field called Quantity, which indicates the number of the product in stock:

    CREATE TABLE [dbo].[Product](

    [p_ID] [int] IDENTITY(1,1) NOT NULL,

    [p_SystemID] [smallint] NULL,

    [p_Name] [varchar](500) NOT NULL,

    [p_Location] [varchar](500) NULL,

    [p_Quantity] [smallint] NULL,

    [p_Dynamic] AS (case when [p_Quantity] IS NULL then (0) else (1) end) PERSISTED NOT NULL,

    [p_Notes] [varchar](500) NULL,

    [p_Active] [bit] NOT NULL,

    [p_DateUpdated] [datetime] NULL,

    [p_DateCreated] [datetime] NOT NULL,

    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

    (

    [p_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    I also have a table called ProductUser, which indicates whether users have a particular product on loan:

    CREATE TABLE [dbo].[ProductUser](

    [pu_ID] [int] IDENTITY(1,1) NOT NULL,

    [pu_ProductID] [int] NOT NULL,

    [pu_UserID] [smallint] NOT NULL,

    [pu_Active] [bit] NOT NULL,

    [pu_DateUpdated] [datetime] NULL,

    [pu_DateCreated] [datetime] NOT NULL,

    CONSTRAINT [PK_ProductUser] PRIMARY KEY CLUSTERED

    (

    [pu_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    The way the system should work is that, when a user wants to take out a product, the system first checks the value in the product table, field Quantity. This field indicates the maximum available of the product. The system then checks the ProductUser table, for all instances of that particular product which have pu_Active = 1. If it finds that the count in the ProductUser is less than the Quantity, it allows the loan. Simple enough. The loan takes place by inserting a new record into the ProductUser table, with the ProductID and UserID in question. When the user wants to return the product, it first checks to ensure that there actually are products on loan. If so, it updates the ProductUser table, and sets the pu_Active field to 0, indicating that instance of a loan has been returned.

    This all works perfectly fine in a single-user environment, because it doesn't matter which particular ProductUser record is returned - what I would do is simply return the oldest record for that user and product.

    The problem that I'm trying to deal with ahead of time is what will happen in a situation in which multiple people are using the application simultaneously.

    The following situations could occur:

    1) Two people attempt to take a product out at the same(ish) time, and there is only one instance of the product available. In this case, I need to ensure that one of the two attempts to withdraw the product fails.

    2) Two people attempt to return a product at the same(ish) time. In this case, I need to ensure that they both don't update the same record.

    I have a feeling that all of this can be done with transactions, I just don't know exactly how I would ensure that the correct information is processed. I was thinking of maybe first beginning a transaction, establishing a row lock on the Product record, and then doing either the insert or the update, depending on the operation in question, then releasing the lock and ending the transaction. Would this work fine? What would happen in the two cases, would one of the instances just wait until the other one was finished, and then begin processing? Also, how exactly do I establish an exclusive lock on a row?

  • Interesting problem, but you may have missed something. Shouldn't your PU table also include the quantity of product a particular user has on loan?

    The code below should handle your case #1 OK by using the UPDLOCK hint within a transaction to ensure that the first user grabs the associated product record and holds it until the PU record is inserted. Note that if you need to know whether a loan was rejected or not, grab the @@ROWCOUNT right after the INSERT - if 0 it was a reject (otherwise it should always be 1).

    CREATE TABLE #Product(

    [p_ID] [int] IDENTITY(1,1) NOT NULL,

    [p_Quantity] [smallint] NULL,

    CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED

    (

    [p_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    CREATE TABLE #ProductUser(

    [pu_ID] [int] IDENTITY(1,1) NOT NULL,

    [pu_ProductID] [int] NOT NULL,

    [pu_UserID] [smallint] NOT NULL,

    [pu_Active] [bit] NOT NULL,

    [pu_Quantity] [int] NOT NULL,

    CONSTRAINT [PK_ProductUser] PRIMARY KEY CLUSTERED

    (

    [pu_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    INSERT INTO #Product

    SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 25

    DECLARE @User SMALLINT = 5

    ,@ProductID INT = 3

    ,@Quantity INT = 50

    -- User 5 wants 50 of product 3 but quantity is only 25 - reject

    BEGIN TRAN T1;

    ;WITH QtyRemaining AS (

    SELECT Quantity=SUM(p_quantity)

    FROM (

    SELECT p_quantity

    FROM #Product WITH(UPDLOCK)

    WHERE p_ID = @ProductID

    UNION ALL

    SELECT -pu_Quantity

    FROM #ProductUser

    WHERE @ProductID = pu_ProductID AND pu_active = 1) a)

    INSERT INTO #ProductUser

    SELECT @ProductID, @User, 1, @Quantity

    FROM QtyRemaining

    WHERE @Quantity <= Quantity

    COMMIT TRAN T1;

    SELECT * FROM #ProductUser

    -- User 5 wants 50 of product 1 and available quantity is 100 - allowed

    SELECT @ProductID = 1

    BEGIN TRAN T1;

    ;WITH QtyRemaining AS (

    SELECT Quantity=SUM(p_quantity)

    FROM (

    SELECT p_quantity

    FROM #Product WITH(UPDLOCK)

    WHERE p_ID = @ProductID

    UNION ALL

    SELECT -pu_Quantity

    FROM #ProductUser

    WHERE @ProductID = pu_ProductID AND pu_active = 1) a)

    INSERT INTO #ProductUser

    SELECT @ProductID, @User, 1, @Quantity

    FROM QtyRemaining

    WHERE @Quantity <= Quantity

    COMMIT TRAN T1;

    SELECT * FROM #ProductUser

    -- User 3 wants to return 30 of product 1

    SELECT @ProductID = 1

    ,@Quantity = 30

    BEGIN TRAN T1;

    UPDATE pu

    SET pu_Active = CASE WHEN @Quantity < pu_Quantity THEN 1 ELSE 0 END

    ,pu_Quantity = CASE WHEN @Quantity < pu_Quantity THEN pu_Quantity - @Quantity

    ELSE 0 END

    FROM #ProductUser pu

    WHERE pu_UserID = @User AND pu_ProductID = @ProductID

    COMMIT TRAN T1;

    SELECT * FROM #ProductUser

    DROP TABLE #Product, #ProductUser

    The issue here is the case of returns. The code I wrote handles returns OK unless the same user has taken out more than one active instance of the same product. This will be a bit of a challenge - to split the return quantity, if it doesn't exactly match one of the PU records, across multiple PU records. The reason I didn't finish out this case is you'll need to decide: 1) do you return the products in the order they were taken out (you could use the created_date column I took out of this table for this or 2) do you want to do a fancy matching, so that if the user took out 50 and returned 50 it matches first on a PU record that is exactly 50, otherwise it spreads it out. Both may be doable by what is known as a "Quirky Update."

    The transaction around the 3rd case should still be what you need to prevent case #2 from being an issue.

    The code is designed to get you thinking about what you'll need to do. Not be the final solution.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • BTW I forgot to mention this.

    Kudos to you for trying to think ahead to the concurrency scenarios. Many people don't and that causes untold problems that are only seen after the code is in Prod for awhile and transaction counts grow.

    You should probably also consider the use of TRY/CATCH blocks around the actual INSERTs and UPDATEs, to ensure that if concurrency is high deadlocks are handled properly.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • They will not be taking out multiple items at a time - each time a user wants to withdraw an item, it occurs as one withdrawl for one item. Thats the reason I don't have a count in the ProductUser table - every record in that table is one instance of a withdrawl, and all the records where pu_Active = 1 represent the currently withdrawn items.

    So taking what you've suggested as a starting point, would this work?

    Withdraw Product:

    CREATE PROCEDURE [dbo].[usp_WithdrawProduct]

    (

    @ProductID INT,

    @user-id SMALLINT

    )

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    DECLARE @Success BIT

    BEGIN TRAN

    DECLARE @ProductCountTotal INT

    DECLARE @ProductCountActive INT

    DECLARE @DoInsert BIT = 0

    -- ESTABLISH A LOCK ON THE PRODUCT RECORD BEFORE DOING ANYTHING

    SELECT @ProductCountTotal = p_Quantity

    FROM [Product]

    WITH (UPDLOCK)

    WHERE p_ID = @ProductID

    SELECT @ProductCountActive = COUNT(*)

    FROM [ProductUser]

    WHEREpu_ProductID = @ProductID

    AND pu_UserID = @user-id

    IF @ProductCountActive IS NULL

    BEGIN

    IF @ProductCountActive = 0

    SET @DoInsert = 1

    END

    ELSE

    BEGIN

    IF @ProductCountActive < @ProductCountTotal

    SET @DoInsert = 1

    END

    IF @DoInsert = 1

    BEGIN

    INSERT INTO [ProductUser] (pu_ProductID, pu_UserID)

    VALUES (@ProductID, @user-id)

    SET @Success = 1

    END

    ELSE

    BEGIN

    SET @Success = 0

    END

    COMMIT TRAN

    RETURN @Success

    END

    Return Product:

    CREATE PROCEDURE [dbo].[usp_ReturnProduct]

    (

    @ProductID INT,

    @user-id SMALLINT

    )

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    -- Insert statements for procedure here

    BEGIN TRAN

    -- ESTABLISH A LOCK ON THE PRODUCT RECORD BEFORE DOING ANYTHING

    SELECT p_ID

    FROM [Product]

    WITH (UPDLOCK)

    WHERE p_ID = @ProductID

    ;

    WITH cte AS

    (

    SELECT

    pu_ID,

    pu_Active,

    pu_DateUpdated,

    ROW_NUMBER() OVER (PARTITION BY pu_ProductID, pu_UserID ORDER BY pu_DateCreated) AS rowNum

    FROM ProductUser

    WHEREpu_ProductID = @ProductID

    AND pu_UserID = @user-id

    )

    UPDATE cte

    SET pu_Active = 0,

    pu_DateUpdated = GETDATE()

    WHERE rowNum = 1

    COMMIT TRAN

    END

    Basically, if I understand the way the UPDLOCK works, whichever request goes in first will establish a semi-exclusive lock on the row in the Product table. It will then keep the row locked until the transaction is complete - so if another process tries to withdraw or return an instance of the same product, it will simply wait until the lock is returned.

    A few questions:

    1) What's the difference between using an UPDLOCK and using a higher-level lock? Should I also be using ROWLOCK and XLOCK, or is that overkill?

    2) How long will the SELECT statement wait for the lock to be released? Is there a way I can set a predefined timeout, so that the lock doesn't get held forever?

    3) What happens if the query is interrupted from the client-side application before the lock is returned? Does the server simply kill the query off, or will it continue waiting for the lock to be acquired?

    4) The documentation on Books Online for UPDLOCK specifies that the lock is acquired and released only when the transaction is complete. Is the "transaction" in my case the entire BEGIN TRAN-END TRAN block, or is it referring only to the SELECT statement?

    This is actually all pretty cool information to be getting, since I'm studying for the 70-433 exam, and one of my weaker areas was transactions, so I'm killing two birds with one stone 😛

    Thanks for all the help btw, and for the kudos!

  • kramaswamy (11/19/2012)


    A few questions:

    1) What's the difference between using an UPDLOCK and using a higher-level lock? Should I also be using ROWLOCK and XLOCK, or is that overkill?

    2) How long will the SELECT statement wait for the lock to be released? Is there a way I can set a predefined timeout, so that the lock doesn't get held forever?

    3) What happens if the query is interrupted from the client-side application before the lock is returned? Does the server simply kill the query off, or will it continue waiting for the lock to be acquired?

    4) The documentation on Books Online for UPDLOCK specifies that the lock is acquired and released only when the transaction is complete. Is the "transaction" in my case the entire BEGIN TRAN-END TRAN block, or is it referring only to the SELECT statement?

    This is actually all pretty cool information to be getting, since I'm studying for the 70-433 exam, and one of my weaker areas was transactions, so I'm killing two birds with one stone 😛

    Thanks for all the help btw, and for the kudos!

    The return product is probably OK.

    For the withdraw product, you can probably do it the way you're doing it but you're introducing unnecessary latency by all the separate SQL statements. Do it as a single SQL statement (similar to my suggestion) by counting active withdrawals against product quantity in a CTE and doing the 1 (or 0) record insert based on it. This reduces the overall time the transaction needs to be active.

    As to your questions:

    1. Read BOL on UPDLOCK and the others. I think it should be sufficient for your case. I've used it in similar cases and it works fine.

    2. The UPDLOCK should occur on the Products table for a very short period of time if you use the approach I've suggested. There should be no need to specify a timeout and in any event I don't think you can (for the transaction) I believe there's a global setting which I wouldn't want to touch.

    3. I don't know how the client app could interrupt the transaction. Once it executes the SP, the SP is off and running.

    4. The entire BEGIN TRAN/END TRAN block.

    Once again, I suggest you consider putting TRY/CATCH blocks around the INSERT and UPDATE statements to make the code bulletproof in case of deadlocks.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Excellent. Alright, I think that just about covers everything then. Thanks for all the help!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply