Using DMAX for Sequential Numbering, and MultiUser Concurrency

[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

 

[Allen Browne]

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

 

[Rick Brandt]

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?

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).