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?

Database advice

Hi,

I recently contacted my hosting company's customer support about my databases not working - saying that I use sql express (which they support).

The guy recommended:

"I would suggest you to upgrade the db's to use mssql 2005."

"Thisis because, sql express is built for development environment. When youare in development environment, you are accessing everything withadministrator permission. However, in live hosting environment (whenthere are differnet kind of permission restrictions), sql express oftenfailed on attaching database."

Does anyone have any opinion on that? Would it be best to change db's to use mssql 2005? How complicated/time consuming will it be to upgrade?

Thanks!

Jon

Hi jbear123

Here is a link from microsoft showing you the difference between all of the SQL Server Products.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

The only major difference express has over the other versions is that it is limited to 4 GB is size and will only use one process if you are running it on a multiprocessor machine. Other than that they are the same, and permissions are set up the same.

This is my understanding.

Hope this helps

Regards

ScottyB