Delays in data update in multi-user environment 1

[pennyman]

First, please forgive me if this is a well known issue, I feel sure it should be given the significance of its effects, but I have been unable to find any prior reference to the issue under Tec Tips or otherwise. I am rather hoping that I have I've done something stupid, or that the issue is fixed in subsequent versions of VFP - any thoughts, suggestions, or results from different versions / platforms would be welcomed.

I am running VFP7.0 on an NT4sp6a and XPPro sp2 workstations of various hardware specifications.

I have a form in an application that is designed to update a flag on a file once a process has been completed.
The environment is multi-user, and the flag exists to prevent the process inadvertently occurring more than once by more than one user. I have found that in some circumstances the update of the flag is apparently not visible to the a second user for a period of time (or indefinitely without additional action - see test 3 below) once it has been updated by another.

To test the issue, I have tried to incrementally eliminate possible influences on a table being updated: ie. network issues, buffering issues, operating system issues, & hardware issues.

A 'SelectTest' form was created that runs a SQL SELECT to return a SECONDS() value stored in a local free table. This value is tested within a loop against an old value, and if different, the difference in update time and current time is recorded. This is repeated ten times and the average recorded.

An 'update' form was created to run an SQL UPDATE to update the time field of the table with SECONDS(). The Update button is hit each time the SelectTest form detects a data change.

By running these two forms in separate VFP sessions on the same workstation, I hoped to measure the time taken for an update to be visible to a select.

Results indicate that where the file is immediately closed following the select, the change can be detected almost instantaneously - as I would have expected (Test 1). However, where the file is allowed to remain open after the select, a significant delay of up to 4 seconds or so is experienced (Test 2). Worse still, if the selecttest form's datasession is set to private, and the vfptest1 file is open in the default session, the changes are not detected at all! (Test 3)

Note that Issuing 'FLUSH' following the update, or Closing the file following the update makes little difference to the times recorded.

I have also run this test on a server networked file from two workstations, having first synchronised workstation times (and restarted VFP, as seconds() appears to rely on the system time at startup). This resulted in similar results to the non-networked version.

Regards,

Mike.

RESULTS:
Test 1 - Default data session, close file each select
Detected Applied Difference
45910.964 45910.964 0.000
45911.839 45911.839 0.000
45912.480 45912.479 0.001
45913.011 45913.011 0.000
45913.464 45913.464 0.000
45914.058 45914.057 0.001
45914.527 45914.526 0.001
45915.042 45915.042 0.000
45915.495 45915.495 0.000
45916.042 45916.042 0.000
Tests: 10
Total Delay: 0.003
Average Delay: 0.000

Test 2 - Default data session, file remains open after each select
Detected Applied Difference
45944.558 45942.214 2.344
45949.558 45945.557 4.001
45954.558 45950.042 4.516
45959.558 45957.229 2.329
45964.558 45960.229 4.329
45969.558 45968.057 1.501
45974.558 45970.292 4.266
45979.558 45975.089 4.469
45984.558 45980.636 3.922
45989.558 45986.261 3.297
Tests: 10
Total Delay: 34.974
Average Delay: 3.179


Test 3 - Private data session, close file each select -test file opened in default session
- Total Failure to detect change - loops until interupted by 'esc'

The test file was created as follows:
CREATE TABLE c:\mptest1 free (ID c(1), time n(9,3))
INSERT INTO c:\mptest1 values ('1', SECONDS())

My test forms are as follows:

**************************************************
*-- Form: frmselecttest
*-- ParentClass: form
*-- BaseClass: form
*-- Time Stamp: 04/03/06 12:51:13 PM
*
DEFINE CLASS frmselecttest AS form


Top = 0
Left = 0
Height = 104
Width = 189
DoCreate = .T.
Caption = "Form1"
Name = "frmSelectTest"


ADD OBJECT cmdtest1 AS commandbutton WITH ;
Top = 36, ;
Left = 48, ;
Height = 27, ;
Width = 84, ;
Caption = "Start Test 1", ;
Name = "cmdTest1"


ADD OBJECT lbltestsdone AS label WITH ;
Caption = "Label1", ;
Height = 17, ;
Left = 84, ;
Top = 12, ;
Width = 40, ;
Name = "lblTestsDone"


ADD OBJECT cmdtest2 AS commandbutton WITH ;
Top = 72, ;
Left = 48, ;
Height = 27, ;
Width = 84, ;
Caption = "Start Test 2", ;
Name = "cmdTest2"


PROCEDURE runtest
LPARAMETERS tnTestID

LOCAL ARRAY latemp(1)
LOCAL lnOldTime, lnTotalDelay, lnTotalTests
lnTotalDelay=0
lnTotalTests = 10

thisform.Caption = 'Running test ' + ALLTRIM(STR(tnTestID))
lcRestext = 'Test ' + ALLTRIM(STR(tnTestID)) + ' - Default data session, ' + ;
IIF(tnTestID=1, 'close file each select', 'file remains open after each select') + CHR(13)+CHR(10)
lcRestext = lcRestext + 'Detected Applied Difference' + CHR(13)+CHR(10)

&& get the initial time value from the file:
SELECT time FROM c:\vfptest1;
where id = '1';
INTO ARRAY latemp
lnOldTime = laTemp(1)
USE IN vfptest1

&& Begin tests...
FOR lnTest = 1 TO lnTotalTests
DO WHILE .T.
SELECT time FROM c:\vfptest1;
where id = '1';
INTO ARRAY latemp
lnPostTestTime = SECONDS()

IF latemp(1) != lnOldTime
lnOldTime = latemp(1)
EXIT
ENDIF

&& Small loop delay to prevent cpu from going too high...
&& note that removing this makes little difference to the result on my pc
&&=INKEY(0.01)

&& test 1 - Close the test file within the test loop...
IF tnTestID = 1
USE IN vfptest1
ENDIF

ENDDO

&& fallen out of our loop, so record the discovery time
lcRestext = lcRestext + STR(lnPostTestTime,9,3) + ' ' + ;
STR(laTemp(1),9,3) + ' ' + ;
STR(lnPostTestTime-laTemp(1),9,3)+CHR(13)+CHR(10)
lnTotalDelay = lnTotalDelay + lnPostTestTime-laTemp(1)

&& Show the number of tests completed...
thisform.lblTestsDone.caption = ALLTRIM(STR(lnTest))

ENDFOR

lcRestext = lcRestext + 'Tests: ' + ALLTRIM(STR(lnTotalTests)) + CHR(13)+CHR(10)
lcRestext = lcRestext + 'Total Delay: ' + ALLTRIM(STR(lnTotalDelay,9,3)) + CHR(13)+CHR(10)
lcRestext = lcRestext + 'Average Delay: ' + ALLTRIM(STR(lnTotalDelay/lnTest,9,3))
_cliptext=lcRestext
thisform.release
ENDPROC


PROCEDURE cmdtest1.Click
thisform.runtest(1)
ENDPROC


PROCEDURE cmdtest2.Click
thisform.runtest(2)
ENDPROC


ENDDEFINE
*
*-- EndDefine: frmselecttest
**************************************************


**************************************************
*-- Form: frmupdatetest
*-- ParentClass: form
*-- BaseClass: form
*-- Time Stamp: 04/03/06 12:52:05 PM
*
DEFINE CLASS frmupdatetest AS form


Top = 0
Left = 0
Height = 59
Width = 155
DoCreate = .T.
Caption = "UpdateTest"
Name = "frmUpdateTest"


ADD OBJECT cmdupdate AS commandbutton WITH ;
Top = 12, ;
Left = 36, ;
Height = 27, ;
Width = 84, ;
Caption = "Update", ;
Name = "cmdUpdate"


PROCEDURE cmdupdate.Click
UPDATE c:\vfptest1 set time = SECONDS() WHERE id = '1'
&& flush
&& use in vfptest1
ENDPROC


ENDDEFINE
*
*-- EndDefine: frmupdatetest
**************************************************

 

[Mike Lewis]

Hi Mike,

You say that issuing a FLUSH makes no difference. But are you aware that, in VFP 7, FLUSH only flushes the buffers at the operating system level? It doesn't necessarily physically write them to the disk controller. If the controller enforces delayed writing, that might explain the behaviour you are seeing.

I'm not sure about this, but it might be worth trying the FlushFileBuffers() API function.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[pennyman]

Mike, thanks for the prompt reply.

I'll try FlushFileBuffers IN Win32API once I've figured out how to apply it to an open dbf file.

However, unless I'm missing something major, would you agree that this wouldn't explain how changes only in the selecttest form would give such drastically different results?

Best regards,

Mike.

 

[Mike Lewis]

Mike,

would you agree that this wouldn't explain how changes only in the selecttest form would give such drastically different results?

Yes, that might be true, but I'm not sure.

At first, I thought the reason was that the data was buffered, and the SELECT wasn't seeing the data in the buffer (which is the standard behaviour of SELECT in VFP 7). But I see you tried the test both without and with bufferering. And in any case that wouldn't explain why it worked after a short delay.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[pennyman]

Mike,

I you mention VFP default SELECT behaviour was buffered.

You are right, I thought I had tested unbuffered, as I was assuming that running a select from code within a form whose BufferMode = 0 (none) would result in an unbuffered query. Is it the case that I need to set buffering explicitly for my select statements?

Regards,

Mike.

 

[Mike Lewis]

Mike,

I'm not sure what you mean by an "unbuffered query". In general, buffering affects updates (and inserts and deletes). It's got nothing to do with querying.

What I was referring to in my earlier post was that a SELECT statement will always retrieve data as it exists on disk, regardless of what's in the buffer.

Say you've got a buffered table. It has three records in which the city = "Paris". You change another record so that it too has a city of Paris. Then you run SELECT * FROM MyTable WHERE City = "Paris". Before yo do the TABLEUPDATE(), you'll still get only three records. If you do it again after the TABLEUPDATE(), you'll get all four.

There's no way to change that behaviour in VFP 7.0. However, it only applies to SELECT. If you do the same query with, for example, APPEND FROM ... FOR, you'll always get all the records.

I don't think that will help you much, but it might be worth keeping in mind.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[PRPhx]

Have you given any thought to doing a requery and/or a refresh on your form?

Don't know if it will work the same as in VFP 6, but the help for requery says:

Requery Method

Requeries the row source to which the ListBox or ComboBox control is bound.

Syntax

Control.Requery

Remarks

Use the Requery method to ensure a control contains the most recent data. The Requery method requeries the RowSource property and updates the list with the new values.

You also stae that your data is correct if the file is closed immediately after the update. So why not close it, and if need be, immediately reopen it? At least try this as a test.

 

[pennyman]

Many thanks to all who have replied.

Mike Yearwood - Spot on! sys(1104) does the trick - having issued SYS(1104) immediately prior to my select, the updated data is returned.

I would expect a performance hit of course, but far better that, than to have out of date data returned quickly!

I'd be interested to hear if other versions of VFP suffer in the same way?

Regards,

Mike.