UPDATE vs REPLACE record locking

UPDATE vs REPLACE record locking

• HIX

  Thanks Tamar,

  I am more concerned with Table locking that I am about speed. The conflicting statements in the Help file is the problem and is obiviously in error.

  I would think a REPLACE (one record) would do record locking where a REPLACE ALL FOR and UPDATE would do Table locking. Which is what the Help file says if you look at the The "Locking Data" page.

  I guess the only way to find out which statements are correct in the Help file is for Microsoft to respond.

  Thanks for you comments,
  Dennis

  
  

• pangeli70

  Tamar,

  It's not really true. See VFP 9 docs:

  REPLACE	Current record and all records from aliased fields
  REPLACE NEXT 1	Current record and all records from aliased fields
  REPLACE RECORD n	Record n and all records from aliased fields
  REPLACE of more than one record	Entire table and all files from aliased fields

  UPDATE	Entire table
  UPDATE – SQL	Entire table

  
  
  

• swtjen01

  >>As Andy implied, Microsoft isn't going to tell us

  Not quite accurate - I only said that it would be quicker to do your own testing.

  Aleksey from the VFP Development Team HAS responded - it's just that it's been more than a month since you asked the question. :-)

  
  
  

• Masthan

  I'm reasonably sure of my ground here (because, among other things, I've tested the relative speed of UPDATE and REPLACE). I am talking only about commands affecting multiple records and about replacements in a single table. (I think issuing a REPLACE that affects records in multiple tables at once is a bad idea.)
  
  No reason to put REPLACE in a SCAN loop unless you need to do a bunch of computation. You can do with REPLACE FOR what you do with UPDATE WHERE.
  
  Tamar
  
  

• inpacem

  UPDATE uses record locking. REPLACE locks the entire table.
  
  Tamar
  
  

• aabes

  Thanks for you input Dave but you have restated the obvious. Locking a table or opening it exclusive are the fastest ways to do updates to a table, no question there. Any form of group updates (REPLACE ALL...FOR, UPDATE...WHERE), to a table have the potential to lock the table sounds logical, no question there either.

  There are two things to be resolved here, one is the fact that the Help file has conflicting statements concerning locking for the REPLACE and UPDATE commands and the other is which statement is correct. As Andy implied, Microsoft isn't going to tell us so the only way is to create my own test, well I don't have the time now.

  You have pointed out why one would use a REPLACE in a SCAN loop, simply because it seems to be the only way to force record locking without locking the table. Now I don't know if table locking is going to cause a problem but it does have the potential to cause problems in a shared table. This is why I am asking the question in the first place.

  Unless someone has the time and resources to test and report back I am going to use the SCAN FOR...REPLACE loop where I want to prevent any table locking potential.

  Thanks,
  Dennis

  
  

• Micael Karlsson

  Tamar,

   Is you statement one of fact or are you repeating what one section of the Help file states As I have pointed out the Help file is conflicting.

  My question would be related to mutiple record changes in a multiuser environment. The REPLACE command would be in a SCAN FOR loop Replacing one record at a time vs the UPDATE -SQL command.

  Thanks,
  Dennis 

  
  

• Quammy _OldProfile_

  Hi Dennis
  
  Another way would be to do your own testing - it'll certainly be quicker than waiting for Microsoft to respond....:)
  
  
  
  

• Game Lover Jon

  Hi Dennis,

  I believe, REPLACE locks table when it is about to modify several records and locks record when it is about to modify one record, UPDATE-SQL locks each affected record one at a time.

  Thanks,

  Aleksey Tsingauz

  Visual FoxPro Dev Team

  
  

• Venom

  Generally both use record locking….

  But please understand that for speed purposes a table lock is much faster, so foxpro (and most good database SOMETIMES use a table lock depending on the current situation.

  For example a REPLEACE ALL will use a table lock because foxpro thinks “I need to lock every record and way so…” if you don’t want a table lock on a replace all consider the following:
  Do while !eof()
  *** error catching code here
  repl x with y
  Skip
  Enddo

  Same with an update statement… and update with no “where” may take a table lock.

  
  
  

• KentDy

  Thanks for your reply Aleksey
  
• UPDATE vs REPLACE record locking