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!