November 19, 2012 at 6:17 pm
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?
November 19, 2012 at 6:57 pm
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 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
November 19, 2012 at 7:13 pm
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 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
November 19, 2012 at 9:24 pm
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!
November 19, 2012 at 9:38 pm
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 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
November 19, 2012 at 9:43 pm
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