Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concurrent access to duplicate records Expand / Collapse
Author
Message
Posted Monday, November 19, 2012 6:17 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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?
Post #1386648
Posted Monday, November 19, 2012 6:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1386649
Posted Monday, November 19, 2012 7:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1386652
Posted Monday, November 19, 2012 9:24 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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,
@UserID 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]
WHERE pu_ProductID = @ProductID
AND pu_UserID = @UserID

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, @UserID)

SET @Success = 1
END
ELSE
BEGIN
SET @Success = 0
END
COMMIT TRAN

RETURN @Success
END

Return Product:
CREATE PROCEDURE [dbo].[usp_ReturnProduct]
(
@ProductID INT,
@UserID 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
WHERE pu_ProductID = @ProductID
AND pu_UserID = @UserID
)

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

Thanks for all the help btw, and for the kudos!
Post #1386667
Posted Monday, November 19, 2012 9:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 11:12 PM
Points: 3,616, Visits: 5,230
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 :P

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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1386668
Posted Monday, November 19, 2012 9:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
Excellent. Alright, I think that just about covers everything then. Thanks for all the help!
Post #1386671
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse