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.