Using DMAX for Sequential Numbering, and MultiUser Concurrency

  • Thread starter Thread starter Andrew Shriner
  • Start date Start date
A

Andrew Shriner

There was a thread about this a few days ago... this is just a little more
complex than that, though - this takes it to the next step (multiuser
concurrently).

I created a database over a year ago that I haven't touched since... until
now. Suddenly, multiple users need to be able to add new records
concurrently (in a couple of forms I have created).

When I created the forms, I used the DMAX domain aggregate function in the
way that Larry Linson suggested in a post in an earlier thread - quoted
below (it retrieves the current DMAX & increments it, and uses that as the
new number to use). The problem is, if one person has opened the form, it
fetches the current DMAX & increments it, but it does not save the record
until that user has completed the form in the way I've decided. Thus, if
another user opens the same form, the exact same DMAX number is returned and
incremented, and then the two users may overwrite each other's data... or
worse.

Do you have any suggestions on how I should fix the problem? Would it be
best just to have the record automatically saved after the DMAX number is
incremented, before the fields in that record are populated? Is there
another solution, or is there a reason this idea should not be used, or will
not work?

Thanks for your help!

Andrew Shriner
 
Hi Andrew.

This is an old chestnut. The best idea is to create another table to hold
the highest number assigned so far. Then in the BeforeUpdate event of the
Form, after you have run any code that involves a user response:
- lock the counter table,
- increment it,
- grab the new number,
- assign it to your primary key field,
- unlock the counter table.

The choice of Form_BeforeUpdate leaves this to the last possible moment. The
process of locking until you get the new number prevents multiple users from
getting the same number. The error handling of this routine will need a
fixed number of retries with a random wait between them. Naturally this code
applies only to new records, i.e.:
If Me.NewRecord Then

Every table that requires a custom ID will need its own counter table:
trying to use one counter table to store the values for multple tables would
result in too many locking conflicts on the counter table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
 
Andrew Shriner said:
There was a thread about this a few days ago... this is just a little more
complex than that, though - this takes it to the next step (multiuser
concurrently).

I created a database over a year ago that I haven't touched since... until
now. Suddenly, multiple users need to be able to add new records
concurrently (in a couple of forms I have created).

When I created the forms, I used the DMAX domain aggregate function in the
way that Larry Linson suggested in a post in an earlier thread - quoted
below (it retrieves the current DMAX & increments it, and uses that as the
new number to use). The problem is, if one person has opened the form, it
fetches the current DMAX & increments it, but it does not save the record
until that user has completed the form in the way I've decided. Thus, if
another user opens the same form, the exact same DMAX number is returned and
incremented, and then the two users may overwrite each other's data... or
worse.

Do you have any suggestions on how I should fix the problem? Would it be
best just to have the record automatically saved after the DMAX number is
incremented, before the fields in that record are populated? Is there
another solution, or is there a reason this idea should not be used, or will
not work?
Click to expand...

What event are you using to grab the next number? Sounds like you're using
BeforeInsert which has the problem you describe. If you use BeforeUpdate
instead the value isn't assigned until a split second prior to the record
being saved so concurrency issues (while not eliminated) are greatly
reduced.

The only added issue with BeforeUpdate is that it can fire multiple times
in the life of a record so you need an If-Then block that causes a number
assignment only if the record doesn't already have one (or you can test for
NewRecord).
 
Andrew,

I ran into this problem too. Not saying what I did is right but it worked
with 4 concurrent users

My data entry form has a hidden txtBox control. It is txtPKIdentifier.

I have a button that saves my record cmdSave. When clicked it fires the
form_beforeUpdate() event. Each of my validation points in this even end
with "exit sub". By time the execution gets to the bottom of beforeUpdate
everything has been validated and the last statement is a dmax statement

me.txtPKIdentifier = dmax("[field]","tablename")+1

then the berforeUpdate event is finished and it goes right back to finish
the save. I have yet to have a problem. I tested with everyone logged in and
I said ready NOW. It didn't mis a beat save one. In an unrelated field I
inadvertantly set a field index to yes no duplicates and in one of the
fields two user used the dummy data "a".

Good luck.
 
Back
Top