How to handle concurrency 2

[Germancho]

VFP 8.0 SP1

Windows XP Professional

Buffering set to 5

MULTILOCKS set to ON

I have a parent table (Invoices, PK: prefix + number) and a child table (Products, Normal Key: (prefix + number) with a one-to-many relation based on these common fields. The rules in the RI are set to Cascade (Update), Cascade (Delete) and Ignore (Insert).

In addition, I have a table (Consecutive) to keep the last number of the last invoice saved.

As several users can be using the invoice form simultaneously, I have a function to assign the invoice’s number:


FUNCTION fConsecutive (lcPrefix)
LOCAL lcArea, lcNumber

lcArea = SELECT()

SELECT Consecutive
SEEK (lcPrefix)
IF FOUND()
lcNumber = TRANS(VAL(MAX(Number, CURVAL('Number', 'Consecutive'))) + 1, "@L 99999999")
ELSE
lcNumber = '00000001'
ENDIF

REPLACE Number WITH lcNumber

SELECT (lcArea)
RETURN lcNumber
ENDFUNC


Both the server and the client show me the same number. What am I doing wrong? What's the best way to handle concurrency?

Thanks in advance for your help.


Germán Restrepo
Bogotá, Colombia

 

[Mike Lewis]

Germancho,

Your problem is that you have set the Consecutive table's buffer mode to 5, which gives you optimistic locking. This means that you can't prevent two people grabbing the same invoice number.

It would be better to switch off buffering for that table. Instead, use FLOCK() to lock it just before you do the seek, and UNLOCK it after the replace. If you set REPROCESS reasonably high, you will prevent simulaltaneous updates without the users being aware of it.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Germancho]

Thanks a lot Mike Yearwood and Mike Lewis. Your answers really help me. A star for both of you.

Germán Restrepo
Bogotá, Colombia

 

[Mike Lewis]

Germancho,

Glad to be of help.

Actually, I just re-read my post. It occurred to me that it might be more efficient for you to use RLOCK() rather than FLOCK(), and to do that before the REPLACE, rather than before the SEEK. The other details would be the same as in my previous post.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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