Record Lock problem

[Wanderson]

Hi, I have many problems with duplicated records, how the best way to block a record in network.

I use this to control a field name CODIGO, but in lan enviroment I have problem with records with the same CODIGO

oArqUlt:GoTop()

While ! oArqUlt:RecLock()
MsgBeep()
MsgStop("Registro bloqueado, tente novamente","Informação")
Loop
Enddo

...

oArqUlt:Load()
oArqCto:CONHEC := Strzero(oArqUlt:PROXCTO,6,0)
oArqUlt:PROXCTO := oArqUlt:PROXCTO + 1
oArqUlt:Save()
oArqUlt:Commit()
oArqUlt:RecUnlock()

Thanks in advance.

[James Bott]

Wanderson,

I don't understand exactly what you are saying. Is CODIGO the primary key field? How are you setting it (your sample code does not show the field CODIGO)?

I have found that you cannot use the database to keep track of the last used ID as you will get duplicates due to index problems (corruption, not the current index, etc). I use a separate database to keep track of the last used ID.

James

[Wanderson]

Wanderson,

I don't understand exactly what you are saying. Is CODIGO the primary key field? How are you setting it (your sample code does not show the field CODIGO)?

I have found that you cannot use the database to keep track of the last used ID as you will get duplicates due to index problems (corruption, not the current index, etc). I use a separate database to keep track of the last used ID.

James

Hi Jame, thanks for your answer. I explain

I have a customer table with many fields, the first field is name CODIGO Type Numeric and lenght 6.
I have another table with one field named NEXTCUS Type Numeric and lenght 6, i use this field to control the inserts. All the time that i insert one registry in customer table i lock this field and add +1 to NEXTCUS, save and unlock. I use this because dbf do not have a auto increment field. But in some cases i see duplicated datas in table customer with equal CODIGO. My question is how the best way (i use tdatabase) to lock and unlock my table NEXTCUS in network situation.
Sorry about my terrible english.

Thanks in advance.

[avista]

HI You must detect next valye for CODIGO in moment of writing data

try this example

Code: Select all  Expand view  RUN

SELECT customer
APPEND BLANK
REPLACE CODIGO WITH NextCODIGO() ,;
        Field2 WITH ???          ,;
        Field3 WITH ???
        ...

FUNCTION NextGODOGO()

Local SelectedFile  := SELECT()
Local SelectedIndex := INDEXORD()

Local nGODIGO := 0

SELECT anothertable
GO BOTTOM
IF EOF()
   nGODIGO := 1
 ELSE
   nGODIGO := GODIGO + 1
ENDIF

APPEND BLANK
REPLACE GODIGO WITH nGODIGO

SELECT( SelectedFile )
SET ORDER TO SelectedIndex

RETURN nGODIGO


[James Bott]

Wanderson,

I have a customer table with many fields, the first field is name CODIGO Type Numeric and lenght 6.

I have another table with one field named NEXTCUS Type Numeric and lenght 6, i use this field to control the inserts. All the time that i insert one registry in customer table i lock this field and add +1 to NEXTCUS, save and unlock.

OK, remember that locking a record does not prevent reading. So, when you read the record, you need to make sure it is not locked before reading. If it is locked, then you need to keep checking until it is unlocked before reading it.

Note that there is a fairly new FWH function isLocked(). We didn't have this in Clipper.

James

[James Bott]

Avista,

Using GO BOTTOM is risky since records may get deleted (unless the app doesn't allow it). If there were records created with that ID (invoices printed, etc.) then that record is deleted, it would get reused for a new customer. Not good.

I have also had a corrupted file cause a problem with this method. GO BOTTOM did not actually go to the bottom but to the middle and I ended up with lots of duplicates.

I have been using a separate file with just one last used ID field (as Wanderson is doing) for many years without any problems.

James

[Rick Lipkin]

Wanderson

I would write a function to create the next unique number and before I append it to the database .. I would test for its existence in the customer database... if I don't find it I would update the nextcus table and return that number to your CUstomer add routine and

Something like this

Rick Lipkin

Code: Select all  Expand view  RUN

// customer add routine

nID := _GenId()
Select Customer
append blank

customer->Codigo := nId
..
...
dbCOmmit()

//------------------
Static Func _GenId()

Local nNext

Select CUstomer
set order to tag CustomerID // indexed on codigo

Select nextcus
nNext := nextcus->codigo
DO WHile .t.
     
     nNext++
     select customer
     seek nNext
     if .not. found()
        select nextcus
        nextcus->codigo := nNext
        exit
     endif

Enddo

CLose nectcus
select CUstomer
set order to last tag  // set index back

return( nNext )  
 

[Wanderson]

Wanderson,

I have a customer table with many fields, the first field is name CODIGO Type Numeric and lenght 6.

I have another table with one field named NEXTCUS Type Numeric and lenght 6, i use this field to control the inserts. All the time that i insert one registry in customer table i lock this field and add +1 to NEXTCUS, save and unlock.

OK, remember that locking a record does not prevent reading. So, when you read the record, you need to make sure it is not locked before reading. If it is locked, then you need to keep checking until it is unlocked before reading it.

Note that there is a fairly new FWH function isLocked(). We didn't have this in Clipper.

James

Hi James I understand but in my case I dont read the record before a

oArqUlt:GoTop()
While ! oArqUlt:RecLock()
MsgBeep()
MsgStop("Registro bloqueado, tente novamente","Informação")
Loop
Enddo

In this case if I locked the record first other user see the message "Record Lock"

Thanks.

[Wanderson]

HI You must detect next valye for CODIGO in moment of writing data

try this example

Code: Select all  Expand view  RUN

SELECT customer
APPEND BLANK
REPLACE CODIGO WITH NextCODIGO() ,;
        Field2 WITH ???          ,;
        Field3 WITH ???
        ...

FUNCTION NextGODOGO()

Local SelectedFile  := SELECT()
Local SelectedIndex := INDEXORD()

Local nGODIGO := 0

SELECT anothertable
GO BOTTOM
IF EOF()
   nGODIGO := 1
 ELSE
   nGODIGO := GODIGO + 1
ENDIF

APPEND BLANK
REPLACE GODIGO WITH nGODIGO

SELECT( SelectedFile )
SET ORDER TO SelectedIndex

RETURN nGODIGO


Thanks avista but I had many insues with this solutions

[RAMESHBABU]

Hello Friends,

Try this

Code: Select all  Expand view  RUN

#include "fivewin.ch"

STATIC nCodeRef
REQUEST DBFCDX

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

FUNCTION Main()

LOCAL nOldCode, nNewCode

FIELD party_code

IF .NOT. FILE("UNIQUEID.DBF")
   Create_Dbf()
ELSE
   USE UniqueId SHARED NEW
ENDIF
 
INDEX ON party_code TAG party_code TEMPORARY
GO BOTTOM

nOldCode := party_code
nCodeRef := party_code-RECNO()

APPEND BLANK

nNewCode := (UniqueId->(RecCount()) + nCodeRef)

IF RLock()
   REPLACE Party_Code WITH nNewCode
   DbUnLock()
ENDIF

MsgInfo("New Party Code "+LTRIM(STR(Party_Code)), "Old Code : "+LTRIM(STR(nOldCode)))

RETURN nil

**********

FUNCTION Create_Dbf()

LOCAL aStruct := { { "PARTY_CODE","N", 5, 0 } }

DbCreate( "UniqueId", aStruct, "DBFCDX", .T., "UniqueId" )

RETURN nil

**********
 

- RAMESH BABU

[James Bott]

Wanderson,

OK, I have studied your code some more. I was confused, because in your description you said the fieldname of the primary key was CONDIGO, but it seems it is CONHEC (oArq:CONHEC). Also the fieldname you said was NEXTCUS is really PROXTO (oArqUlt:PROXTO).

I am assuming oArqUlt is the database that you keep the NEXT UNUSED primary key in.

If my assumptions are true, then you are doing just what I suggested--locking the record, incrementing the number and saving it, then unlocking. And nobody else can read the next key while the record is locked. I don't see how this can fail.

So I have to assume there is some other code that is causing the problem. What is the missing code "..." doing? Is there code somewhere else in your program that is reading the next key without using this routine?

James

[James Bott]

Wanderson,

May I also suggest writing a small sample program that just adds new records to the customer file. Then run two or more copies of this program across the LAN for a few minutes, then check for duplicates.

James

[Wanderson]

Wanderson,

May I also suggest writing a small sample program that just adds new records to the customer file. Then run two or more copies of this program across the LAN for a few minutes, then check for duplicates.

James

Thanks James. In my pc with 2 copies of my program runs ok, not duplicated but the case occurs in my customer with windows xp and lan mapped unit. I think is some windows xp bug.

[James Bott]

Wanderson,

I think is some windows xp bug.

I do remember some issues with COMMITs because the OS doesn't necessarily flush the buffers when a COMMIT is issued.

In this case I guess the best solution is to check for the existence for a duplicate record before saving a new record. If the same primary key already exists, then go back and generate another key and try saving again.

Another possibility might be to add a slight delay after locking the primary key record, and actually reading it. I.E. lock it, wait, read it using oDBF:load().

Or use both ideas.

You can build all this logic into a subclass of TDatabase. Create a new Append method that automatically increments the primary key field and saves it to the newly appended record, then calls the load() method (to get the primary key into the buffer).

Regards,
James

[Wanderson]

Wanderson,

I think is some windows xp bug.

I do remember some issues with COMMITs because the OS doesn't necessarily flush the buffers when a COMMIT is issued.

In this case I guess the best solution is to check for the existence for a duplicate record before saving a new record. If the same primary key already exists, then go back and generate another key and try saving again.

Another possibility might be to add a slight delay after locking the primary key record, and actually reading it. I.E. lock it, wait, read it using oDBF:load().

Or use both ideas.

You can build all this logic into a subclass of TDatabase. Create a new Append method that automatically increments the primary key field and saves it to the newly appended record, then calls the load() method (to get the primary key into the buffer).

Regards,
James

Thanks James.