Hi there,

This post is long, so please bear with me....I have a rather frustrating hard-to-pinpoint problem happening with my application.

ANY help/suggestions are MORE than welcome!!

Thanks for reading,

Joe

**************************************************************

PROBLEM: Experiencing intermittent table locking problems

BACKGROUND: ** Recently migrated application form VFP 5.0 to VFP 7.0
** Database and tables on an NT4.0 file share
** 50+ client workstations (PCs) performing inserts
** Client PCs are mixed bag (Win9, Win98, and Win2K)
** All inserts involve 2 tables... INSTANCE (parent) and RESULTS (child) .... average of 30 inserts will go into the RESULTS table for each INSTANCE record, but there can be as much as 300.
** INSTANCE table has 10 index tags (cdx) and memo (fpt)
** RESULTS table has 4 index tags

INSERT ROUNTINES: ** Have 2 procedures that perform inserts into the INSTANCE and RESULTS tables. One is done real-time on the shop floor from 50+ PCs. The other procedure is performed by only one PC every 10 minutes and involves reading data from multiple text files and inserting that data into the INSTANCE and RESULTS tables.
** Both routines use the same startup code (see below)
** Very likely that multiple inserts are happening at the same time

-------------------------------------

Code in MAIN startup:

Set Ansi On
Set Exact On
Set Near Off

Set Bell Off
Set Century On
Set Console Off
Set Cpdialog Off
Set Deleted On
Set Escape Off

Set Exclusive Off
Set Memowidth To 255
Set Multilocks On
Set Null On
Set Palette Off
Set Refresh To 20,20
Set Reprocess To Automatic
Sys(3052, 1, .T.)
Sys(3052, 2, .T.)
Set Safety Off
Set Sysformats On
Set Talk Off

-------------------------------------

Differences between the following 2 routines that do the inserting:

** Realtime inserting procedure: Set Reprocess to Automatic; Text file inserting procedure: Set Reprocess to 10
** Realtime: nothing; Text file: Re-sissue SYS(3052,1,.T.) & SYS(3052,2,.T.) functions
** Realtime: Have index tags set on tables; Text file: no tags set
** Data sessions may be different for the two procedures.

-------------------------------------

Code for the shop floor real-time inserts:


set reprocess to automatic

FOR lnSave = 1 TO ALEN(OAPP.ASERNUM)

select DATENTRY

IF !EMPTY(oApp.aSerNum(lnSave))

SELECT DATENTRY

*** Update the results, instance
IF lSave

*** Open the instance and results tables if not already open
IF !USED('INSTANCE')
USE IPC!INSTANCE IN 0 SHARED ORDER INST_ID
ENDIF

IF !USED('RESULTS')
USE IPC!RESULTS IN 0 SHARED ORDER INST_ID
ENDIF

*** Now get the next instance sequence
cInst_id = NextId('INST_ID')

SELECT INSTANCE
SEEK CINST_ID
IF FOUND()
DO WHILE FOUND()
cInst_id = NextId('INST_ID')
SEEK CINST_ID
ENDDO
ENDIF

*** Update the instance and results tables

select instance
=cursorsetprop('buffering',5)
select results
=cursorsetprop('buffering',5)

SELECT INSTANCE

*** Update the instance table
INSERT INTO instance (inst_id, dc_grp, review, oper, datetime, serial, inst_comment, signoff) ;
VALUES (cInst_id, M.dc_grp, lDeviant, IIF(empty(GUID),oapp.cuserid, GUID), ldDateTime, IIF(llgridsn,lcgridsn,oApp.aSernum(lnSave)), thisform.inst_comment, '')

SELECT DATENTRY
SCAN
INSERT INTO results (inst_id, reqt_id, var, attr, datetime, disp) ;
VALUES (cInst_id, DATENTRY.reqt_id, DATENTRY.var, DATENTRY.attr, DATENTRY.datetime, cDisp)
ENDSCAN

begin transaction
select instance
llupdate = tableupdate(.t.,.t.,'instance')
select results
llupdate2 = tableupdate(.t.,.t.,'results')
if llupdate and llupdate2
end transaction
else
rollback
tablerevert(.t.,'instance')
tablerevert(.t.,'results')
return .f.
ENDIF
ENDIF
ENDIF
ENDFOR

IF USED('RESULTS')
USE IN RESULTS
ENDIF
IF USED('INSTANCE')
USE IN INSTANCE
ENDIF

IF USED('NEXTID')
USE IN NEXTID
ENDIF



-------------------------------------

Code for the text file inserts:

use ipc!results in 0
use ipc!instance in 0

SET REPROCESS TO 10
SYS(3052, 1, .T.)
SYS(3052, 2, .T.)

select instance
=cursorsetprop('buffering',5)
select results
=cursorsetprop('buffering',5)

select datsave
go top

SCAN
if lcserial <> datsave.serial
lnInst_Id = NextId('INST_ID')
lcserial = datsave.serial
INSERT INTO instance (inst_id, dc_grp, datetime, serial, oper, review, SIGNOFF, inst_comment, inst_disp) ;
VALUES (lninst_id, datsave.dc_grp, datsave.datetime, datsave.serial, datsave.oper, datsave.review, '', datsave.inst_comment, datsave.inst_disp)

endif
DO CASE
CASE REQT.V_A = 'V'
INSERT INTO results (inst_id, reqt_id, attr, VAR, DATETIME, disp) ;
VALUES (lnInst_id, datsave.reqt_id, .NULL., datsave.VAR, .NULL., datsave.disp)
CASE REQT.V_A = 'A'
INSERT INTO results (inst_id, reqt_id, attr, VAR, DATETIME, disp) ;
VALUES (lnInst_id, datsave.reqt_id, datsave.attr, .NULL., .NULL., datsave.disp)
CASE INLIST(REQT.V_A,'D','T')
INSERT INTO results (inst_id, reqt_id, attr, VAR, DATETIME, disp) ;
VALUES (lnInst_id, datsave.reqt_id, .NULL., .NULL., datsave.DT, datsave.disp)
ENDCASE
endscan

begin transaction
select instance
llupdate = tableupdate(.t.,.t.,'instance')
select results
llupdate2 = tableupdate(.t.,.t.,'results')
if llupdate and llupdate2
end transaction
else
rollback
tablerevert(.t.,'instance')
tablerevert(.t.,'results')
llfinish = .f.
endif

use in results
use in instance


-------------------------------------