Concurrent database updates

[Jill Graham]

Hi,

What is the best way to resolve following problem in a multi-user
environment ?

I have a table called TOTAL_SALES.
The table has 2 columns : "CarId" and "TotalSales"

I need to access a given car in the TOTAL_SALES table using the "CarId",
read the "TotalSales" value and increment this value with 1 unit.
Then, the "TotalSales" value will be used in other calculations. Another
user who accesses the TOTAL_SALES table will use the NEW "TotalSales" value.

However, there is a concurrency problem. When 2 users access the
TOTAL_SALES table at the same time. Both can read and use the same
"TotalSales" value before the other one incremented the value. Whta is the
best way to solve this in ADO.NET ?

Thanks

Jill

[Guadala Harry]

Tell us what database you are using. Your options are significantly
different depending on which database you are using.

GH

"Jill Graham" <ji**********@yahoo.com.au> wrote in message
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

What is the best way to resolve following problem in a multi-user
environment ?

I have a table called TOTAL_SALES.
The table has 2 columns : "CarId" and "TotalSales"

I need to access a given car in the TOTAL_SALES table using the "CarId",
read the "TotalSales" value and increment this value with 1 unit.
Then, the "TotalSales" value will be used in other calculations. Another
user who accesses the TOTAL_SALES table will use the NEW "TotalSales" value.
However, there is a concurrency problem. When 2 users access the
TOTAL_SALES table at the same time. Both can read and use the same
"TotalSales" value before the other one incremented the value. Whta is the best way to solve this in ADO.NET ?

Thanks

Jill

[Alphonse Giambrone]

Actually, your database should be structured differently.
Values that can be calculated should not be stored in a table, but
calculated when needed.
You should have a 'transactions' table that would have columns for the carid
and whatever other info you need on a sale.
There would be one record for each sale.
Then whenever you need the total sales count you can obtain it by getting
the count of records in the transactions table for that carid. It may seem
simple to just add to a stored value, but ultimately the methodology is
prone to inaccuracy.

--

Alphonse Giambrone
Email: a-giam at customdatasolutions dot us
"Jill Graham" <ji**********@yahoo.com.au> wrote in message
news:Of*************@TK2MSFTNGP10.phx.gbl...

Hi,

What is the best way to resolve following problem in a multi-user
environment ?

I have a table called TOTAL_SALES.
The table has 2 columns : "CarId" and "TotalSales"

I need to access a given car in the TOTAL_SALES table using the "CarId",
read the "TotalSales" value and increment this value with 1 unit.
Then, the "TotalSales" value will be used in other calculations. Another
user who accesses the TOTAL_SALES table will use the NEW "TotalSales" value.
However, there is a concurrency problem. When 2 users access the
TOTAL_SALES table at the same time. Both can read and use the same
"TotalSales" value before the other one incremented the value. Whta is the best way to solve this in ADO.NET ?

Thanks

Jill

[Trevor Benedict R]

What I would do is (Explained with SQL Server)

Begin Transaction
UPDATE TOTAL_SALES SET TotalSales = TotalSales + 1
WHERE CarId = 101
SELECT TotalSales FROM TOTAL_SALES WITH (NOLOCK)
WHERE CarId = 101
COMMIT TRANSACTION

In ADO.NET make sure that you set the Transaction IsolationLevel to
ReadCommitted.

Regards,

Trevor Benedict R
MCSD

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dave Vanwonterghem]

Hi,

Thanks for your answer. But could you explain me what happens between the
UPDATE and the SELECT statement ? Is the record locked ?

What happens when another user changes the TotalSales value before the
server executes the SELECT statement ?

Jill

"Trevor Benedict R" <tr********@yahoo.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...

What I would do is (Explained with SQL Server)

Begin Transaction
UPDATE TOTAL_SALES SET TotalSales = TotalSales + 1
WHERE CarId = 101
SELECT TotalSales FROM TOTAL_SALES WITH (NOLOCK)
WHERE CarId = 101
COMMIT TRANSACTION

In ADO.NET make sure that you set the Transaction IsolationLevel to
ReadCommitted.

Regards,

Trevor Benedict R
MCSD

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Trevor Benedict R]

Since you are within a Transaction, the record will be locked for
Update/Delete/Select till you either Rollback or Commit the Transaction.

Using SQL Server as an example, there is a method to perform a dirty
read, meaning not to wait for the lock but to read the state of the
value as is in the database, in our case the previous value. So when you
need accurate values, in the sense that users should always see the
latest value, then we go by the lock. So we wait for one user to release
the lock, then take that value and start working from there. This will
be streamlined process. Only glitch is the waiting time which should be
nano seconds, provided you keep the Transaction as small as possible.

If another user is in the process of updating/incrementing the counter,
then the next call will wait for the previous call to finish.

We do an update first, so as to get an exclusive lock on the Record.
Remember that SQL Server goes by Page level locking and for high volume
updates inside a smaller table, for those queries that read from the
table, you could specify the NOLOCK clause so as to aviod waiting for
locks to be released.

Let me know if you need anymore information.

Regards

Trevor Benedict R
MCSD

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!