Auto-Increment value duplicated in multi-user environment

[betty]

This is two problems so maybe I need to put it in two posts ... I have not been able to reproduce this - but then again - it is pretty difficult to run two copies of the database at the same time and push the buttons at exactly the same time ..

This is how the application works ..
1. The customer creates a quote by choosing existing data in the database. This is stored in a "Quote" table.
2. When the quote is accepted, they dispatch the quote to a driver and the quote is copied to a "Load" table with additional information about the load (ie. driver, scheduled date, gallons, etc.)
3. When the driver delivers the load he brings back information about the load and they "Complete" the load (date delivered, actual gallons, etc.)

The first issue:
Two people were dispatching two different loads at the same time. One of them was correct. The 2nd load had the driver and gallons of the 1st load ..iow;

Person #1 entered "Joe" and "3000 gallons" on the xdialog form. Person #2 entered "Bob" and "2000 gallons" on the xdialog form.

Person #1 ended up with a record for Load #A-101 with a driver whose name was Joe who delivered 3000 gallons.
Person #2 ended up with a record for Load #A-102 with a driver whose name was Joe who delivered 3000 gallons.


The 2nd issue:
Today they tell me they tried it again and it created two records with the same load number.

I am using an auto-increment value for the load number which is assigned when a new record is created - and an XDialog form to capture information from the user.

Because the load number is an auto-increment value, I can't make it unique (I actually thought that an auto-increment value is SUPPOSED to be unique!)

Attached is the dispatch code. What do I have wrong here?

[Garry Flanigan]

Re: Auto-Increment value duplicated in multi-user environment

You may want to look at this http://msgboard.alphasoftware.com/al...=autoincrement

[martinwcole]

Re: Auto-Increment value duplicated in multi-user environment

Another scheme, which goes back to 5-4.0 is to have a table named "counters." The table contains a field for every table where you need a unique key (auto increment type.)

I have one app running since 2003 (V5-5) that has 75 tables that are all maintained from one single counter table. Have never had any problems with it.

One reason I don't use auto increment fields is that appending from one table to another messes them up, and I frequently archive records to keep the main files as small as possible, and then copy back when necessary from the archives.

In the cansaverecord event in field rules, it opens the counter table in exclusive mode, increments the respective field, closes it, and assigns that number to the unique field for that table - allowing up to 10 trys with a 1/3 second wait for each try. That particular app is run from a max of 17 W/S's.

if a_deleting_record
 end
end if 
t=table.get("invoices")
if t.mode_get()=2 'enter
 if t.company="" 
  cancel()
  end
 end if 
 dim trys as n
 trys=0
 on error goto retry
 t=table.open("counters",FILE_RW_EXCLUSIVE)
 on error goto errors
 t.change_begin()
  t.invoicenumber=t.invoicenumber + 1
 t.change_end(.t.)
 n=t.invoicenumber
 t.close()
 t=table.get("invoices")
 t.invoicenumber=substr(alltrim(str(year(date()))),3,2) + padl(alltrim(str(n)),7,"0")
 end
end if 
end
retry:
if trys > 10
 cancel() 
 ui_msg_box("Error","The system is currently busy - try again in a minute!",16)
 sys_send_keys("{esc}")
 end
end if 
trys=trys + 1
sleep(1/3)
resume 0
end
errors:
err_msg = error_text_get(error_code_get())
line = error_line_number_get()
script = error_script_get()
ui_msg_box("Error", err_msg+" Error occurred at line "+alltrim(str(line,4,0))+ " in script: "+script)
end

[Finian Lennon]

Re: Auto-Increment value duplicated in multi-user environment

This may not be the cause of your problem but it's worth commenting on.

You enter the load record then close the table. Then reopen the table and do a fetch_last to get the last record and then populate a variable with the load number value in that record. In your scenario, depending on the amount of data entry being done etc, there is no guarantee that the value you get there will, in fact, be the load number that was created by the autoincrement action.

I suggest that you populate that variable immediately after the table.enter_end() using vc_Loadnum = tbl.Loadnum which will give you the correct value from the already open table pointer.

[EJR]

Re: Auto-Increment value duplicated in multi-user environment

Martin, I like your scheme and plan to do likewise where I need unique ID numbers. I assume you've used it with many flavors of Windows? Have you ever seen any counters disappear (i.e., something happened after grabbing the next counter and before it was saved in the caller's application), or have you even felt the need to tackle such a situation?

Also, I assume you have only one record in "counters" with a separate field for each counter needed - correct?

Thanks.

[CALocklin]

Re: Auto-Increment value duplicated in multi-user environment

EJR - IMPORTANT: See the comment I made in this link about using 2 records even though only one has data. (That happens to be a child link from the one Gary listed above.)

The issue only applies if exclusive access is used for the "increment" table. BUT, if you don't use exclusive access then you risk problems with two people trying to change the value at the same time.

Disclaimer: This was definitely an issue in earlier versions of Alpha. I haven't tested it in newer versions but it just seems like it's the safe thing do to - (1) I don't think it was a bug in the first place and (2) "fixes" have been known to become "unfixed".

[martinwcole]

Re: Auto-Increment value duplicated in multi-user environment

I've been using it since 5-4.0 circa 1998.
I have used it in all my apps since then.
Yes, it is a single record file - with canenter and candelete properties set to false in field rules.

I have had 1 regular auto imcrement field fail in 12 years, and 1 using this routine in 12 years. (I have never - knock on wood - had a memo field corruption.)

I have an index rebuild routine, that also contains a routine to reset the counters.

With the first client I converted to Alpha, 1998, when I moved from one language to Alpha, I used auto increment - it failed and it took me 2 days to unravel the records, and at that time there was a lot of chatter on the board from other developers that were having issues with autoincrement fields, and I think it was Dr. Wayne who came out with this idea back then. I have not heard anything about them failing anymore in more recent versions.

With my busiest client, the one where the one table manages about 75 files, about 17 concurrent users max - typically about 7 - I set the value to "temp" when they enter a new record, and then to an actual value when they save it. They have an "Emergency Maintenance" button that deals with certain problems - say a w/s crashes or there is a network blip, and a record gets saved, the emergency button looks for and removes all records with "temp" in the key field and resets the counters table.

[CALocklin]

Re: Auto-Increment value duplicated in multi-user environment

Martin, it sounds like your single record file has never resulted in any known issues with two people changing it at one time - correct? I also assume that you are not requiring exclusive access in the incrementing function? (Using exclusive access was what caused my lockup when the second person attempted to access the same record.)

I've wondered about the need for exclusive access. My concern was that two people would be attempting to change the record at the same time. BUT, I've also wondered what the chances of that happening are when an xbasic routine runs it. In xbasic you open the table and immediately put the record into Change mode which should lock out anyone else trying to change that record. If A5 waits even a few milliseconds for the record to unlock so the second person can access it, that should be enough time for the original routine to complete.

If it has been working for you with that many users and you confirm the questions above then I guess that requirement can be removed from my scripts.

[martinwcole]

Re: Auto-Increment value duplicated in multi-user environment

Cal, I do require exclusive read/write access to the counter table

like
on error goto retry
t=table.open("counters",read_wrte_exclusive)
on error goto errors

I totally do see what you mean, though, about 1 person opening it, the system crashing, and it left in a locked mode. But so far it has never happened, and I'm using it in all my multiuser apps. I have access to all my clients, remotely, and can restart their servers if necessary. Except one - where an app runs 24/7 in a small hospital chain. And in that app, after the great flood in Cedar Rapids, IA, several years ago, when their servers were under water, along with generators, etc., they moved all servers to Des Moine. And we immediately started having problems. Initially, we would get locked files, and would have to have their 24/7 IT guys unlock the files (not the counters table, though.) Since then server glitches have been almost completely fixed. We also had a major drop in performance, too. We had to get several vice presidents to jointly hammer IT to investigate. They put their head honcho using a very sophisticated monitor on it, and determined that it was primarily their virus/firewall. They completely exempted us at both the server and W/S and that brought us back to a tolerable perfomance level. The turn around for the server before the flood was 1 milisecond, and after 4 miliseconds. As he pointed out, thats not much, but when you create a record or save it or even enter data into it, thousands of events occur, and they add up.

[CALocklin]

Re: Auto-Increment value duplicated in multi-user environment

Thanks Martin. I have to admit that the lockups I experienced happened during development. However, having seen the issue, I opted to add the second (blank) record which eliminated the issue completely. I guess I'll leave it as-is since the second record doesn't hurt anything and might help.

BTW, I also have the ON ERROR check and allow it to loop 3 or 4 times - about 1/2 second total. Anything longer than that is almost sure to be a "permanent" problem that won't correct itself.

[Peter.Greulich]

Re: Auto-Increment value duplicated in multi-user environment

...

I suggest that you populate that variable immediately after the table.enter_end() using vc_Loadnum = tbl.Loadnum which will give you the correct value from the already open table pointer.

I think Mr. Fin is onto something (as usual).

[martinwcole]

Re: Auto-Increment value duplicated in multi-user environment

Yes, Peter, I agree.

Interesting: I just now realized that the hosptial is running a WAN - i.e., the app is shadowed, but to a server in a different city, and it is actually running!!! Wonder what kind of broadband they have - must be really bad#$@!!!!

I had always heard to never try to use a shadowed app over a WAN.

[Peter.Greulich]

Re: Auto-Increment value duplicated in multi-user environment

the app is shadowed, but to a server in a different city, and it is actually running!!!

Of course it works! After all, you built it. ;)

[Finian Lennon]

Re: Auto-Increment value duplicated in multi-user environment

Hi Peter, Martin et al:

The auto-increment subject is always a good topic to get the juices flowing. I've always used the built-in field rule auto-increment and have, all other things being equal, found it to be no more susceptible to failure than any other core part of the A5 engine. In the "all other things being equal" category I include a simplified data entry design as the single most important factor.

I don't think that Betty (the original poster) has an auto-increment issue at all and haven't yet seen evidence that it actually failed on her end. Initially I was surprised, because, on first glance, her method looked foolproof. However as I pointed out, the rest of her code is primed to propagate a false value to other tables so that what looked like an auto-increment problem is, I believe, a coding problem.

[EJR]

Re: Auto-Increment value duplicated in multi-user environment

Cal, thanks for the info. Great discussion.

Does anyone know EXACTLY what scheme is under the covers for A5's auto-increment field rule?

About the only issue I can see that might be a concern in some situations is the extra I/O imposed to read then write a value to another file during an enter operation. Also, the strategy here (which is good and stable) does not force a flush before closing the file, so the next counter is not stored on the disk until the OS decides to write it. This is a potential weakness (in the event of a server crash).