Multiuser website - Possiblity of two users clicking submit at sametime

Discussion in 'ASP .Net' started by Mel, Aug 27, 2009.

  1. Mel

    Mel Guest

    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?
     
    Mel, Aug 27, 2009
    #1
    1. Advertising

  2. Mel

    MLightsOut Guest

    Re: Multiuser website - Possiblity of two users clicking submit atsame time

    On Aug 27, 2:33 pm, "Mark Rae [MVP]" <> wrote:
    > "Mel" <> wrote in message
    >
    > news:...
    >
    > > 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.

    >
    > 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?
    >
    > > Is there a chance that two users could click Submit at the same time

    >
    > Of course there is! The likelihood increases with the number of concurrent
    > users...
    >
    > > and get the same quote number and write it to the database?

    >
    > Impossible to tell without knowing your database schema...
    >
    > > How would I avoid this?

    >
    > http://support.microsoft.com/kb/232144
    >
    > But avoid Jet for web applications if you possibly can...
    >
    > --
    > Mark Rae
    > ASP.NET MVPhttp://www.markrae.net


    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, Aug 27, 2009
    #2
    1. Advertising

  3. Mel

    Paul Shapiro Guest

    Re: Multiuser website - Possiblity of two users clicking submit at same time

    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.

    "MLightsOut" <> wrote in message
    news:...
    On Aug 27, 2:33 pm, "Mark Rae [MVP]" <> wrote:
    > "Mel" <> wrote in message
    >
    > news:...
    >
    > > 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.

    >
    > 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?
    >
    > > Is there a chance that two users could click Submit at the same time

    >
    > Of course there is! The likelihood increases with the number of concurrent
    > users...
    >
    > > and get the same quote number and write it to the database?

    >
    > Impossible to tell without knowing your database schema...
    >
    > > How would I avoid this?

    >
    > http://support.microsoft.com/kb/232144
    >
    > But avoid Jet for web applications if you possibly can...
    >
    > --
    > Mark Rae
    > ASP.NET MVPhttp://www.markrae.net


    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, Aug 27, 2009
    #3
  4. Mel

    MLightsOut Guest

    Re: Multiuser website - Possiblity of two users clicking submit atsame time

    On Aug 27, 3:31 pm, "Mark Rae [MVP]" <> wrote:
    > "Paul Shapiro" <> wrote in message
    >
    > news:...
    >
    > [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...
    >
    > --
    > Mark Rae
    > ASP.NET MVPhttp://www.markrae.net


    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, Aug 27, 2009
    #4
  5. Mel

    MLightsOut Guest

    Re: Multiuser website - Possiblity of two users clicking submit atsame time

    On Aug 27, 3:31 pm, "Mark Rae [MVP]" <> wrote:
    > "Paul Shapiro" <> wrote in message
    >
    > news:...
    >
    > [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...
    >
    > --
    > Mark Rae
    > ASP.NET MVPhttp://www.markrae.net


    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...
     
    MLightsOut, Aug 27, 2009
    #5
  6. Mel

    Paul Shapiro Guest

    Re: Multiuser website - Possiblity of two users clicking submit at same time

    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.

    "Mark Rae [MVP]" <> wrote in message
    news:%...
    > "MLightsOut" <> wrote in message
    > news:...
    >
    >> I like the idea of locking the table

    >
    > Do not UNDER ANY CIRCUMSTANCES consider locking a table in a Jet database
    > in a multiuser environment, especially with web-based clients...
    >
    >
    > --
    > Mark Rae
    > ASP.NET MVP
    > http://www.markrae.net
     
    Paul Shapiro, Aug 28, 2009
    #6
  7. Mel

    MLightsOut Guest

    Re: Multiuser website - Possiblity of two users clicking submit atsame time

    On Aug 27, 7:15 pm, "Paul Shapiro" <>
    wrote:
    > 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.
    >
    > "Mark Rae [MVP]" <> wrote in messagenews:%...
    >
    >
    >
    > > "MLightsOut" <> wrote in message
    > >news:....

    >
    > >>  I like the idea of locking the table

    >
    > > Do not UNDER ANY CIRCUMSTANCES consider locking a table in a Jet database
    > > in a multiuser environment, especially with web-based clients...

    >
    > > --
    > > Mark Rae
    > > ASP.NET MVP
    > >http://www.markrae.net- Hide quoted text -

    >
    > - 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...
     
    MLightsOut, Aug 28, 2009
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. bigbinc
    Replies:
    3
    Views:
    474
    bigbinc
    Jun 24, 2004
  2. Network-Man
    Replies:
    5
    Views:
    78,851
    gaul1
    Jul 7, 2012
  3. MrThingy
    Replies:
    2
    Views:
    369
    Samee Zahur
    Apr 30, 2005
  4. ljlolel
    Replies:
    2
    Views:
    519
  5. Good Guy
    Replies:
    0
    Views:
    165
    Good Guy
    Jun 28, 2013
Loading...

Share This Page