What happens on multiple connections with SQL

  • Thread starter Thread starter RA
  • Start date Start date
R

RA

Hi

I use Sql 7 for the db.
I have asp.net apps which collocts online transactions.
I have customers, orders, orderdetails.. tables.
I have a DataSet for each table and I change the dataset based on the info
from the user.
Squence: Add to Customers dataset, Add to orders dataset and add to
orderdetails datase. If all is ok then I call each dataset adapter update
method..

In order to fill the order table I need to get the CustomerId - This I am
getting my retreiving the Max(CustomerId) from the Customers table.
My question is that if lets say 200 new customers try to add their
information to the web site, and since for each one of them I use the
Max(CustomerId) to get the new customerId, it is possible that multiple
users will get the same CustomerId, this is because I only updates the
database after the new order and order detail has been processed. What
should I do to avoid this? I don't want to enter a new Customer if for
example the order orderdetails dataset addrow method fails. Thats why I am
wating until I add the rows to all tables.


Thenka,
Ron
 
if you want to use this slow algorithm, you need to use the same connection
for all updates, begin a transaction and take an exclusive table lock when
you do the max. this greatly reduces concurrency, but is the only way.

a better solution it to use a customer id table (with one row), so the
exclusive lock only hurts inserts, but concurrancy is still impacted.

-- bruce (sqlwork.com)
 
I think you should use an Identity column for the CustID. See my article on
Identity issues. www.betav.com\articles.htm.


--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top