Database concurrency

[The Crow]

we are developing an ASP.NET application with SqlServer at backend..

there are an supplier and about 3000 customer, and each customer has about
3-4 users. they are selling mobile phone counters.

while a sale occurs, we are selecting customers stock amount, if it is
bigger then sale amount, we are updating its stock, and we increment
suppliers stock. The query is like that :

DECLARE @StockAmount int
SELECT @StockAmount = Amont FROM Stocks WHERE CustomerId = @BuyerCustomerId
IF @StockAmount > @SaleAmount
UPDATE Stocks SET amount = @StockAmount - @SaleAmount WHERE CustomerId =
@BuyerCustomerId

SELECT @SuppliersStockAmount = Amont FROM Stocks WHERE CustomerId =
@SellerCustomerId
UPDATE Stocks SET amount = @StockAmount + @SaleAmount WHERE CustomerId =
@SellerCustomerId

i know, each customer has got not so many users and a concurrency problem
seems to be not a big possibility. but there is just a 1 supplier record and
i think conlicts are possible. how can i alter this problem. after a
research, i found
SELECT @SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHERE
CustomerId = @SellerCustomerId

seems to work fine for us, but it will block the row untill the transaction
finishes. any approaches are appreciated. thanks.

 

[Uri Dimant]

Try
SELECT @SuppliersStockAmount = Amont FROM Stocks WITH (UPDLOCK) WHERE
CustomerId = @SellerCustomerId

Some amount of blocks is unavoidable.

This lock hint won't block others for reading. It will assure that the data
has not changed since you last read it

 

[Guest]

You can use locking hint "UPDLOCK" or you can modify the statement and use
something like:

UPDATE Stocks
SET amount = amount - @SaleAmount
WHERE CustomerId = @BuyerCustomerId and amount > @SaleAmount
....


AMB

 

[The Crow]

This is the SQL Server Books Online explanations :

UPDATE LOCK :
Used on resources that can be updated. Prevents a common form of deadlock
that occurs when multiple sessions are reading, locking, and potentially
updating resources later.

UPDLOCK :
Takes update locks instead of shared locks. Cannot be used with NOLOCK or
XLOCK.

so, shared lock as u may know doesnt prevent reading data but modyfying,
which is not the case.

 

[The Crow]

UPDATE Stocks
SET amount = amount - @SaleAmount
WHERE CustomerId = @BuyerCustomerId and amount > @SaleAmount

this statement "SELECT"s the suitable row aquiring update lock which behaves
same as shared lock, and then converts it exclusive lock prior to doing
actual update. isnt it?