tableupdate not updating in time? 3

[foxup]

Hi,

I have this simple code in a push-button:

** CREATE NEXT AVAILABLE UID **
IF USED("NEXTLOCALNUM")
SELECT NEXTLOCALNUM
ELSE
USE NEXTLOCALNUM IN 0
ENDIF
XX=''
XX=NEXTLOCALNUM.NEXTLOCALN+1
SET MULTILOCKS ON
=CURSORSETPROP('Buffering', 5, 'NEXTLOCALNUM')
REPLACE NEXTLOCALNUM.NEXTLOCALN WITH XX
=TABLEUPDATE(.T.)
L_ORDNUM="L"+ALLTRIM(STR(XX))
THISFORM.TEXT1.VALUE=L_ORDNUM
THISFORM.REFRESH
** CREATE NEXT AVAILABLE UID END**

It's a multi-user environment & basically, it provides an ongoing unique sequential ORDER NUMBER (for each order created). Simple enough, YET, the problem I'm seeing is that if 1 user pushes that button and then another user pushes that button, the 1st and 2nd user sometimes (not all the time) get the same order number (which is weird because I'm TABLEUPDATEing, so the value should be different, correct?

I hope you understand what I'm saying.

Please help in fixing this bug because I don't know why it's happening.


Thanks,
FOXUP!

 

[Mike Lewis]

The problem is occuring because you are passing .T. to TABLEUPDATE(). This tells VFP to force the update, even if there is a multi-user conflict.

What you should be doing is passing .F., then checking the reply from TABLEUPDATE(). If this is itself .F., then that indicates an error, which might (or might not) be a multi-user conflict. If it is, you need to take action to deal with it.

That said, this is not really the best way to go about this.

If you have VFP 9.0, it is very much easier to use an Integer - AutoInc as the data type for your primary key. That way, everything is done for you behind the scenes.

With earlier versions, you need code similar to what you have shown, but it is easier to issue an explicit file lock on your key table (with FLOCK()) rather than rely on buffering.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

My mistake. Integer Autoinc came in with VFP 8.0, not 9.0. If you have either 8.0 or 9.0, then you will be doing yourself a big favour by using that feature.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[foxup]

Hi Mike,

OK, I get what you're saying. I am passing .T. to TABLEUPDATE(). This tells VFP to force the update which is exactly what I want. I want the table to update that record so that the next time somebody uses it, it's already updated and they gert to use the next available ORDER#. I mean, what are the odds that the 2 or more people click on that button on the exact same time. It's happening very often that I'm getting doubles and I find it almost impossible that there are that many. There are only 4 people using this module and I'm getting some that have 2 of the same ORDER # (which means that 2/4 people clicked on the button at the same time which is extremely unlikely). I can see it happening once or twice but every 3rd or 4th order is almost impossible.

Any other help please?

Thanks,
FOXUP!

 

[foxup]

I just figured why it's happening by doing some tests now I just need help on how to fix it please.

It's happening when they have 2 sessions opened of the same EXE on the same computer at the same time.

How can fix it so that it updates the DBF() when the other session is still opened.

You know what I mean?


Thanks,
FOXUP

 

[JRB-Bldr]

Is there a reason that you are not approaching the problem by preventing them from having 2 sessions opened of the same EXE on the same computer at the same time ?

Do they really need to be able to do that?

Good Luck,
JRB-Bldr

 

[foxup]

Actually JRBBLDR, that's exactly what I do not want. I do not want them to have 2 sessions opened of the same EXE on the same computer at the same time.

Any ideas on what is the best solution for that?

I'm gonna keep going thru the threads to see what I can do but do you have a preference on how to do that?

Thanks,
FOXUP!

 

[foxup]

OK, I found what suits my needs. Nice and simple code bny Ramani:

It's faq184-839


Thanks to JRBBLDR & MIKE.


Stars!


Thanks,
FOXUP!

 

[foxup]

Hi All,

I have fixed the problem by using faq184-839. But, for my personal learning, can anybody please tell me WHY when I had 2 sessions opened of the same EXE on the same computer at the same time and even though the TABLEUPDATE() would execute, the second session did not have the correct data?

Simply curious and want to know why.


Thanks,
FOXUP!

 

[Olaf Doschke]

Let's take a look at the first and last step:

First step: reuse or open the table

IF USED("NEXTLOCALNUM")
   SELECT NEXTLOCALNUM
ELSE
   USE NEXTLOCALNUM IN 0
ENDIF
[/codes]

Besides the first run of this code, the table is already open.

Last step: save

[code]
=CURSORSETPROP('Buffering', 5, 'NEXTLOCALNUM')
REPLACE NEXTLOCALNUM.NEXTLOCALN WITH XX
=TABLEUPDATE(.T.)
[/codes]

You do a tableupdate, to be able to tableupdate you need buffering. And this buffering stays.

Once you write a value back, it's written back. And whatever other users write back to the counter table, you don't reread (!)

That's the point where you fail:
[code]
XX=NEXTLOCALNUM.NEXTLOCALN+1

You count up 1 from the value you have in the buffer. You don't care to reread the current value.

I assume you will still have the problem, even though you avoid two instances on the same computer.

What you do is letting each client have it's own counter. They all store there next count value back, but nobody reads from there despite the first run.

Don't use buffering in this case, just open the table in every run, count +1, save and close the dbf. This way you always read the fresh value from the file and update it.

To prevent concurrent access, FLOCK the counter dbf before incrementing it, closing the dbf then unlocks and saves very reliable.

Bye, Olaf.

 

[foxup]

OK, I'll keep checking. Thanks.

 

[Olaf Doschke]

If you don't get the doubles anymore, the only reason I could think of is, you close the table in between calls of your counter code somewhere else or you turn off buffering at least. Only taking into account the code you posted this is making a seperate local counter starting with the counter value of the first run, to make a central counter of it, the least thing to do is change to

XX=CurVal("NEXTLOCALN","NEXTLOCALNUM")+1

Bye, Olaf.

PS: sorry to mess up the TGML code above.

 

[Olaf Doschke]

Besides you could simply turn to autinc, if you at least use VFP9.

I made a testdrive with your code, and it works, but I needed to do a slight change to make it work:

Set Exclusive Off
Close Tables All

* Initialising counter:
Cd GetEnv("TEMP")
Erase "sessioncounter*.dbf"

If File("NEXTLOCALNUM.DBF")
   Use NEXTLOCALNUM
   Replace NEXTLOCALN With 0
Else
   Create Table NEXTLOCALNUM (NEXTLOCALN I)
   Insert Into NEXTLOCALNUM Values (0)
EndIf
Use

* Initialising 4 datasessions permanently creating counter values
Rand(-1) && make rand more reandom by init with a negative seed.
loSession1=Createobject("countingsession")
loSession2=Createobject("countingsession")
loSession3=Createobject("countingsession")
loSession4=Createobject("countingsession")

* wait a bit
Wait '' Timeout 10

* end the sesssions (this also closes all sessioncounter dbfs
loSession1 = .Null.
loSession2 = .Null.
loSession3 = .Null.
loSession4 = .Null.

* now reopen to look into the tables each session generated
For lnCount = 1 To ADir(laDBFs,"sessioncounter*.dbf")
  Select 0
  Use laDBFs[lnCount,1]
  Browse Nowait
Endfor

* your central counter code
Function NextNum()
   ** CREATE NEXT AVAILABLE UID **
   If Used("NEXTLOCALNUM")
      Select NEXTLOCALNUM
   Else
      * slight change here:
      Select 0
      Use NEXTLOCALNUM
   Endif
   XX=NEXTLOCALNUM.NEXTLOCALN+1
   Set Multilocks On
   =CursorSetProp('Buffering', 5, 'NEXTLOCALNUM')
   Replace NEXTLOCALNUM.NEXTLOCALN With XX
   =Tableupdate(.T.)
   Return XX
Endfunc

Define Class countingsession as Session
   Procedure Init()
      AddProperty(This,"Timer1",CreateObject("countertimer"))
   EndProc
EndDefine

Define Class countertimer as Timer
   Interval = Rand()*5 && set timer to an interval between 0 and 5 ms
   Datasession = 2
   
   Procedure Init()
      AddProperty(This,"cDBF","sessioncounter"+Sys(2015)+".dbf")
      Create Table (This.cDBF) (iCounter I)
      Use
   EndProc
      
   Procedure Timer()
      Insert Into  (This.cDBF) Values (NextNum())
   EndProc
EndDefine

In case your table is not open, you do

USE NEXTLOCALNUM IN 0

This in itself is no error, but this doesn't make NEXTLOCALNUM the currently selected workarea!

Whatever is the currently active workarea is updated via your TableUpdate(.T.), but it's not the NEXTLOCALNUM table.

So your code only updates NEXTLOCALNUM in each secondary run you do SELECT NEXTLOCALNUM.

But changed the way I did it works, also without CurVal(). Maybe putting the file onto network share changes the timings, maybe the double numbers really jsut were because any first tableupdate() you did in your first calls did adress a different table.

Bye, Olaf.

 

[foxup]

Hi Olaf,

No worries about the TGML code above. I really appreciate all the work you did. I am still getting the doubles though, correct. I really thought that:

Select 0
Use NEXTLOCALNUM

was equivalent to USE NEXTLOCALNUM IN 0

According to the HELP File:
Including 0 opens a table in the lowest available work area.

which I would think means the same as:

Select 0
Use NEXTLOCALNUM

or am I missing something?

OK, so I changed it to the two lines of code as you mentionned. I hope this will solve the problem.

Should I change the other line you mentioned as well to:

XX=CurVal("NEXTLOCALN","NEXTLOCALNUM")+1

Let me know know.


Thanks,
FOXUP!

 

[Olaf Doschke]

Both codes open the table in the same workarea, the difference is, USE sometable in 0 does not select that workarea, instead the current workarea stays the same.

Use browser.dbf
? Alias() && BROWSER
Use browser.dbf in 0 Again Alias browser2
? Alias() && still BROWSER, not BROWSER2

Indeed you can also ensure a table is opened AND selected this way:

IF NOT USED("NEXTLOCALNUM")
   USE NEXTLOCALNUM IN 0
ENDIF
SELECT NEXTLOCALNUM

SELECT NEXTLOCALNUM is done in any case and you're sure that workarea is selected.

You can also simply add the alias you want to update into the TABLEUPDATE() call: TABLEUPDATE(.T.,"NEXTLOCALNUM"). So this also is another case of the rule to use alias clauses in any case to prevent any flaw in the code hardly spotted.

Instead of USE, USE IN SELECT("Alias")
Instead of REPLACE field WITH value, REPLACE field with value IN Alias
Instead of ZAP, ZAP in Alias
Instead of GO TOP, GO TOP in Alias
Instead of TABLEUPDATE(), TABLEUPDATE(...,...,"Alias")
Instead of SEEK value, SEEK(value,"Alias","Tag")

Should I continue?

Being verbose in programming pays. I'm not saying this the first time.

Bye, Olaf.

 

[foxup]

Oh, OK, I understand the difference. Wow! Thank you so much.

Again, I really appreciate all the time you're helping me with this one.

I've learned & I will change me code accordingly.

Thank You,
FOXUP!

 

[Olaf Doschke]

Well, I also learn a bit each time. This time I learned the buffering works a bit different, than I thought of, no Curval() needed.

Makes sense actually: After a buffered value is written back by Tableupdate, Getfldstate reports the new value as now old, unmodified, because it's in sync with the dbf now. Curval() only would be needed to peek into the dbf, if the field has a bufferd state.

Bye, Olaf.

 

[foxup]

Hi,

I'm sorry to bust y'all but I'm still getting the dupes. Any recommendations on how to figure out this darn tiny bug?

Please advise.


Thanks,
FOXUP

 

[foxup]

OK, so I've changed my code to this instead:

IF USED("NEXTLOCALNUM")
SELECT NEXTLOCALNUM
LOCK('1', 'NEXTLOCALNUM')
ELSE
SELECT 0
USE NEXTLOCALNUM
LOCK('1', 'NEXTLOCALNUM')
ENDIF

XX=''
XX=NEXTLOCALNUM.NEXTLOCALN+1
SET MULTILOCKS ON
SET REPROCESS TO 10
REPLACE NEXTLOCALNUM.NEXTLOCALN WITH XX
UNLOCK IN NEXTLOCALNUM

Any input would be greatly appreciated on if this will work. I'm gonna try it & recompile & redistribute but let me know if you guys see any problems with that.


Thanks,
FOXUP!

 

[TamarGranor]

The most obvious problem I see if that you don't check the return value from LOCK() and you just keep going. What if you couldn't lock it?

Tamar