REPLACE ALL does a full lock? 2

[herbstgy]

Hi All,

there's a rather big (224000+ records) DBF table on a network drive. It is used in shared mode between multiple workstations.
Sometimes I need to do a rather lengthy database maintenace which is mainly consists a REPLACE ALL ... FOR ... command. Due to the speed of the network, this part runs for about an hour. During this time, the rest of the workstations can use this table for read, which is good enough for me.

However, if I try to start a new instance on another workstation during the update, it runs to an error. I'd like to understand why is this. Probably REPLACE does a full lock on the table for the time of update, which is understandable. But it seems during open the USE statement also tries to lock the table even if SET EXCLUSIVE is off and there's a SHARED or NOUPDATE parameter on the USE statement.

Is any way to avoid this?

 

[GriffMG]

I suspect this will be opportunistic file locking (again).

You don't say which OS your server uses, or the workstations, or if its a NAS drive, but it is mostly likely that the
w/s running the REPLACE FOR is getting an Op Lock on the file and doesn't release it until it's finished - and presumably
lets go of the file altogether.

If you are running a windows server, it would probably be more efficient to run your replace on there, rather than across the
LAN so to speak.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

I disagree that this is connected with oplocks. It is exactly the behaviour I would expect from within VFP. In fact, I have just reproduced your scenario, and am seeing the same error. This happens despite the fact that you are only opening the the second instance of the table, not necessarily writing to it. (I tried adding NOUPDATE to the USE, but that made no difference.)

I can only suggest that you find a different workflow for your maintenance. Can you do it overnight, or at some other time when no other users will be accessing the table? What about making a copy of the table, doing the maintenance on the copy, and then copying it back (provided other users are prevented from updating the original table, which is the situation you are in anyway)? Or, the other way round: creating a read-only version of the table before the update, and letting the other users access that copy rather than the original?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi,

You also may want to have a look at the SQL UPDATE command. It might be as slow/fast as REPLACE, but it only locks records whereas REPLACE locks the entire table.

hth

Mark

 

[GriffMG]

Good to have a bit of healthy discussion!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Yes, indeed. Healthy discussions are one of the reasons I like Tek Tips.

If Mark is right about SQL UPDATE not locking the entire table (and I've no reason to doubt it), that would look like a good solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

Hi

From the help file

NoteTip
When updating multiple records in a table opened for shared access, SQL UPDATE uses [highlight #FCE94F]record locking, unlike the REPLACE command.[/highlight] This reduces record contention in multiuser situations but might reduce performance. For maximum performance, open the table for exclusive use or use the FLOCK( ) function to lock the table. For more information, see FLOCK( ) Function.

Mark

 

[mjcmkrsr]

Hi Herbstgy,

You may also want to check your network (NAS?) speed.

Under the following setup

NAS (4TB disks - RAID 1) - 1Gb Ethernet connection - Table of 185000 records - EXCLUSIVE USE : both the REPLACE cField with "bbb" and the UPDATE MyTable SET cField = "ccc" took less than 2s

hth

Mark

 

[Chris Miller]

USE does a table header check, see SET TABLEVALIDATE in the help. To turn the checks off, you could set it to 0, but AFAIR there are other flags not insisting on a successful lock during this validation.

On the other hand, how about doing this on the file server itself? Or is it a Linux/NAS system? A neighbor node of the database server would also already be faster, ie in case of a NAS, the server which has that NAS attached to it.


Chriss

 

[herbstgy]

Thanks, guys, I was afraid at first that I have to rewrite the REPLACE parts with SQL UPDATE, but SET TABLEVALIDATE TO 7 did the trick.