Multiuser website - Possiblity of two users clicking submit at sametime

[Mel]

I have an asp.net/vb website that multiple users will access. When
they click Submit the code behind will query the MS Access database
for the next quote number and inserts a new record to a table. Is
there a chance that two users could click Submit at the same time and
get the same quote number and write it to the database? How would I
avoid this?

 

[MLightsOut]

Firstly, Jet databases (which some people call Access databases:http://en..wikipedia.org/wiki/Microsoft_Jet_Database_Engine) are a really bad
choice for websites, especially heavily used ones, for many reasons - is
there a reason that you're not using something more suitable?


Of course there is! The likelihood increases with the number of concurrent
users...


Impossible to tell without knowing your database schema...


http://support.microsoft.com/kb/232144

But avoid Jet for web applications if you possibly can...

I can't avoid it. The records must be written there. I would love to
use the Identity field but whoever created this "awesome" table back
in the stone age didn't create any primary keys or autoincrementing
numbers. Is there another way?

Before i can write the new record I need to figure out what the next
quote number is so I can write the new record. Quote numbers start
with today's date like MMDDYY and a an incremening number like 01, for
example quotes done today are 08270901, 08270902, 08270903, and so
on. So when the user clicks submit I query the quote numbers in the
table and determine they go up to # 3 so my new record will be quote
number 08270904.

 

[Paul Shapiro]

Without a primary key on the quote table, you can't really guarantee that
the quote numbers remain unique. Why not make the quote number the PK, so
you at least get that much protection against corrupt data?

Access can't guarantee transactional integrity between the time you read the
current value and the time you enter a new row with your newly-computed
value. So I think you would need to include some kind of locking feature in
your web application to ensure that only one user at a time can be acquiring
a new quote number. If you really care that the quote numbers remain
sequential, it would seem that the time to assign the new quote numbers is
as late as possible, when you are entering the new row into the db.
Otherwise, if you assign a user a new quote number but they don't complete
the process and actually create that quote in the db, you'll have a gap in
your sequence numbers.

You might be able to have your sql insert statement compute and assign the
next quote number while it is adding the row to the db, but Jet sql is more
limited than SQL Server in that regard.

Firstly, Jet databases (which some people call Access
databases:http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine) are
a really bad
choice for websites, especially heavily used ones, for many reasons - is
there a reason that you're not using something more suitable?


Of course there is! The likelihood increases with the number of concurrent
users...


Impossible to tell without knowing your database schema...


http://support.microsoft.com/kb/232144

But avoid Jet for web applications if you possibly can...

I can't avoid it. The records must be written there. I would love to
use the Identity field but whoever created this "awesome" table back
in the stone age didn't create any primary keys or autoincrementing
numbers. Is there another way?

Before i can write the new record I need to figure out what the next
quote number is so I can write the new record. Quote numbers start
with today's date like MMDDYY and a an incremening number like 01, for
example quotes done today are 08270901, 08270902, 08270903, and so
on. So when the user clicks submit I query the quote numbers in the
table and determine they go up to # 3 so my new record will be quote
number 08270904.

 

[MLightsOut]

[please don't top-post]

Why not make the quote number the PK

Because the table doesn't have a primary key...

The OP is unable to modify the Jet database - if he was, none of this would
be a problem...

Paul said
<Access can't guarantee transactional integrity between the time you
read the
current value and the time you enter a new row with your newly-
computed
value. >

Exactly my point! You hit the nail on the head. That's the issue and
that is why I fear the duplicate quote number for a multi user website
because two users could potentially query the table at the same time
and return the same quote number. I can write a record from my
website just fine, I just don't want there to be a duplicate quote
number if I can help it. Although I suppose since I don't have a
primary key MS Access (sorry I should say Jet database) probably won't
generate an error anyway...hmmm. I like the idea of locking the table
while I am quering the quote number, then write the record and then
unlock the table but I don't know how to do it in Asp.net and the
other fly in that ointment is I will have MS Access users that may
have a form with a record open and are modifing data in that same
table so I don't think I can even get a lock on it.

 

[MLightsOut]

[please don't top-post]

Why not make the quote number the PK

Because the table doesn't have a primary key...

The OP is unable to modify the Jet database - if he was, none of this would
be a problem...

And yes to clarify, I can modify the Jet database. The quote number
is unique but it is definitely not incrementing. Even if I did have a
primary key I still need to query the database to find the next quote
number for today and write the new record and do that from a multi-
user web application.

Today's quotes (aug. 27th)
08270901, 08270902, 08270903...

Tomorrow's quotes (aug. 28th)
08280901, 08280902, 08280903, 08280904...

Next Tuesday's quotes (sept. 1st)
09010901, 09010902...

 

[Paul Shapiro]

Agreed- you can't lock the Jet table. Hopefully you don't have too many
simultaneous web users, or the jet solution really wouldn't be feasible.
Instead you might be able to mplement some kind of locking scheme in your
asp.net application to ensure single-user access to the code creating a new
quote. It reduces concurrency, but if there aren't usually many users it's
probably better than corrupted data.

But in another post you mentioned non-web users working simultaneously, in
which case that approach wouldn't work. The only remaining option I can see
is using an insert sql statement that assigns the quote number based on the
current data, rather than a 2-step procedure where you retrieve the next
quote number and then separately insert the new quote. That still doesn't
eliminate the concurrency corruption problem, but it would minimize it as
much as you can with a jet db by shortening the time between computation and
assignment. Two users could still enter the same quote number, unless you
make the quote number the table's primary key. Then you can trap the error
that would occur in the asp.net code when the 2nd "almost-simultaneous" user
tries to enter the duplicate quote number row.

 

[MLightsOut]

Agreed- you can't lock the Jet table. Hopefully you don't have too many
simultaneous web users, or the jet solution really wouldn't be feasible.
Instead you might be able to mplement some kind of locking scheme in your
asp.net application to ensure single-user access to the code creating a new
quote. It reduces concurrency, but  if there aren't usually many users it's
probably better than corrupted data.

But in another post you mentioned non-web users working simultaneously, in
which case that approach wouldn't work. The only remaining option I can see
is using an insert sql statement that assigns the quote number based on the
current data, rather than a 2-step procedure where you retrieve the next
quote number and then separately insert the new quote. That still doesn't
eliminate the concurrency corruption problem, but it would minimize it as
much as you can with a jet db by shortening the time between computation and
assignment. Two users could still enter the same quote number, unless you
make the quote number the table's primary key. Then you can trap the error
that would occur in the asp.net code when the 2nd "almost-simultaneous" user
tries to enter the duplicate quote number row.





- Show quoted text -

Good advice, thank you Paul. I was able to make the Quote # a primary
key so that will help if I want to trap that error. I am not sure how
I would implement the locking scheme in my asp.net application to
ensure single-user access to the code but I like that idea and I think
that would be sufficient for my needs here. I will investigate it
more...