Database concurrency

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.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
"The Crow" <q> wrote in message
news:ODUI3bVVFHA.2984@.tk2msftngp13.phx.gbl...
> 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.
>|||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" wrote:

> 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 = @.BuyerCustomerI
d
> 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 a
nd
> i think conlicts are possible. how can i alter this problem. after a
> research, i found
> SELECT @.SuppliersStockAmount = Amont FROM Stocks WITH (XLOCK ROWLOCK) WHE
RE
> CustomerId = @.SellerCustomerId
> seems to work fine for us, but it will block the row untill the transactio
n
> finishes. any approaches are appreciated. thanks.
>
>|||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.|||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?