Multi-user environment without locking entire table 3

[Paco75]

Hi,

Actually we got applications that use free tables. Up to now they were used by only one people at time and in rare cases by two at same time (and in that case the second waited for the first to finish). But it happens more often that two users want to use the same program at same time. So im looking for a good way to manage multi-user environment.

The ideal would be to be able to lock each records being used. This way allowing another user to use the other records in a table having another record locked rather than locking the entire table as it is actually doing.

any help appreciated!

 

[mjcmkrsr]

Hi Paco,

First step - please have a look at DATASESSION and Programming for Shared Access in the help file.

hth

Mark

 

[Mike Lewis]

Paco,

Yes, I agree with Mark that you should start by reading the Help file. Start with the topic "Programming for Shared Access" and proceed from there.

That said, let me give you a start. First, you've got to decide between optimistic and pessimistic locking.

You'll probably find pessimistic locking easier to deal with. In short, it means that, when one user starts to edit a record, that record will be locked, and no other user will be able to edit it. The first user has to explictly commit or revert the edits in order to release the lock.

Optimistic locking means that the record is locked only at the point that it is being written to disk. It allows you to detect cases where one user overwrites another user's edits. But it doesn't prevent such cases. You would need to develop a strategy for dealing with them.

You second step is to buffer your tables. Read up on buffering, and also on the two key functions: TABLEUPDATE() and TABLEREVERT().

I hope this has given you a start. Come back when you have more detailed questions.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

The really first step is to not SET EXCLUSIVE OFF, but that is a default anyway. So you should check your code, where you explicitly set this ON or if you use tables exclusive. Otherwise shared access of DBFs is the foxpro default anyway.

But no matter if you open DBFs shared with or without buffering, at write foxpro does lock tables. So you will need to handle conflicts anyway, there is no real carefree shared dbf access.

In regard to locking recordwise, yes, that's possible via RLOCK(). This depends on a stable network, I've made bad experinces with manual locking.

Bye, Olaf.

 

[Paco75]

thanks for the answers. I will take a look a the "Programming for Shared Access" and create a program to test it.

 

[JRB-Bldr]

Additionally by doing a Google search for: vfp multiuser you can find a number of other references.

A number of those references will be redundant to what you get from the VFP Help file, but there might also be a few other issues mentioned to consider.

Good Luck,
JRB-Bldr

 

[Paco75]

I created a simple program with a sigle form and a small table. Im using private datasessions and table buffering with pessimistic row locking.

i open two instances of the application and on the first instance i do a RLOCK() on a record to test then on the second instance i verify if the record is locked with ISRLOCKED() but it's not!

on my form init i do

SET MULTILOCKS ON 
SET REPROCESS TO 0
USE "data\table1" SHARED
CURSORSETPROP("Buffering", 2)
[\code]

DATASESSION form's property is set to 2 - Private Datasession

the button i use to lock contains this:
[code]IF ISRLOCKED() THEN 
	UNLOCK
ELSE
	RLOCK()
ENDIF[\code]

Why does the second datasession's record is not locked?

 

[Dan Freeman]

It sounds like you misunderstand IsRlocked(). It does not tell you if another user (or another instance) has a record locked. It tell you whether *YOU* (or the current instance) has a record locked.

As always, the only way to find out whether a record can be locked is to try locking it:

If Not Rlock()
  * someone else has a lock
Else
  * You just locked it
Endif

 

[Mike Lewis]

I'm not sure why you are calling RLOCK() explicitly. Given that your table is pessimistically buffered, VFP will apply the locks automatically.

The only code you need is to trap the error that's generated when the lock is violated (error 109). When that happens, just advise the user that someone else is editing the record, and ask them to try again later.

You don't need to make any other changes, beyond those connected with buffering.

Also, the datasession issue is a bit of a red herring here. I'm not saying data sessions aren't important (they are), but they don't directly affect this particular issue.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

The answer is in the help file in the ISRLOCKED topic: ISRLOCKED( ) returns a logical true (.T.) if the record is locked by the current application

So ISRLOCKED only checks the lock is achieved by the current process. You also cannot unlock a lock from another process, only the process doing the rlock can unlock.

Besides this using pessimistic row locking you mix automatic locks with manual locks. I don't want to get verbose on that for now, but pessimistic locking means vfp does lock out all other processes than the one, which started editing that record.

Bye, Olaf.

 

[Paco75]

Mike: i call rlock() only to test if i implemented the mechanism correctly... only for tests. Obviously i misunderstood ISRLOCKED().

 

[Paco75]

I do

If Not Rlock()
  * someone else has a lock
  MESSAGEBOX("Someone else is using it")
Else
  * You just locked it
Endif

while im editing a field in another instance Rlock() just wait for the record to be unlocked... it does not return false.

 

[Dan Freeman]

There is a very complex mix of settings starting with SET REPROCESS (see the help file) that determines what happens here. Behavior is different depending whether there's an ON ERROR in place, depending on SET STATUS, etc. -- it's worth studying some.

 

[Paco75]

i think i need an example... i read help but i dont get it. The help show lots of things but i need to glue things together... If you know a good sample or two online maybe it should help.

 

[DSummZZZ]

You have an example:

If Not Rlock()
  * someone else has a lock
  MESSAGEBOX("Someone else is using it")
Else
  * You just locked it
Endif

The only thing missing is the part where you do your update:

If Not Rlock()
  * someone else has a lock
  MESSAGEBOX("Someone else is using it")
Else
  * You just locked it[COLOR=red]
  REPLACE SomeFeilds WITH SomeData
  UNLOCK [/color]
Endif

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Paco75]

but this is not working RLock() always return .T. because if it can't lock the rlock() is waiting endlessly for the record to be unlocked... even if i put SET REPROCESS TO 0 in my init

also as Mike seem to say if im using pessimistic buffering VFP should lock/unlock automatically

 

[Mike Lewis]

also as Mike seem to say if im using pessimistic buffering VFP should lock/unlock automatically

That's right. Have you tried it yet?

Just create a form that edits a table. Set the table's buffer mode to pessimistic. Then run the form in two VFP sessions.

Edit the form in one session. Then try to edit it in the other. After a short delay, you should see an error (error 109). Trap that error in your error-handler. That's all there is to it.

At least, that's how I've always handled it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

REPROCESS and other settings are per datasession settings, it's insufficient to set them at init of an app in the main.prg only.

Quote "SET REPROCESS is scoped to the current data session." in the Remarks section of SET REPROCESS.

Bye, Olaf.

 

[Dan Freeman]

Take out all attempts at locking and let VFP handle it. Optimistic vs. pessimistic doesn't actually matter. ANY time VFP writes to a file it will lock and then unlock.

There are inevitably conflicts, and that's when you need to be handling things manually, but for now just ignore the whole hoohaw and get on with it. Worry about this *IF* it becomes an issue later.

 

[mjcmkrsr]

Hi Paco,

If you give me your email address I'll send you a zipped small sample project (no add no spam no virus).

hth

Mark