Record locking

Jan 17, 2013 at 8:31 PM

First, BRAVO! Great piece of work!

Been playing around for a few hours and have been able read and write and now looking at how to deal with record locking and how to tell if anybody has modifed the data from VFP after my dataset was opened.  Currently just using a browse window in vfp and issuing a =LOCK() statement in the command box to test.

Any sugestions or hints :)

Many thanks in advance

Regards,

Mike

Coordinator
Jan 18, 2013 at 12:25 PM

Check out Optimistic Concurrency Patterns for some ideas on how to deal with concurrency.

Jan 18, 2013 at 2:20 PM

Thanks Tom,

I read that article while searching for how to deal with an Optimistic platform yesterday, just still not seeing which command will replace data while the row is locked.  Given the "Database wins" design in the example

   do
    {
        saveFailed = false;
        try
        {
            context.SaveChanges();
        }
        catch (DbUpdateConcurrencyException ex)
        {
            saveFailed = true;
            ex.Entries.Single().Reload();
        }
    } while (saveFailed);


If SaveChanges fails due to another lock, and the catch occurs, the Reload() will loop and get "newer" data.
Problem is while looping just because the SaveChanges passes, doesnt mean the Reload() caught the last version?

In the legacy world we live in, it was common to have "Serialno" fileds for uniq identifers - there was no AutoInc fields back in the day, and Recno() wasnt a good plan.  Being able to open a table and lock and inc record is key (example)

Function GetSerialNo
SELECT Serials
STORE RLOCK() TO locked
DO WHILE .NOT. locked 
  WAIT WINDOW "Record in use, Please Wait..." TIMEOUT 1
  STORE RLOCK() TO locked
ENDDO
REPLACE serialno WITH IIF(serialno=0 OR serialno=9999999999,1000000000,serialno+1)
UNLOCK
RETURN STR(sysdata->serialno,10) TO mserno

RLOCK() and UNLOCK are always wrapped around all database updates.

Are there any commands in this new optimistic world that guarantee you sole operation to a row? Seems all examples I find are backend triggers and hacks?

Regards,

Mike

Jan 18, 2013 at 8:45 PM

After more research I've found the "Concurency Mode" in the edmx model and have set it to Fixed for all the fields
Now it's raising errors in the SaveChanges if the value have changed, priblem now is im getting "SQL: Statement too long" on other tables with lots of fields

I'm reading now about 2k and other say 4k limits on VFPOLEDB sql commands, prior to EF framework it was suggested to break up your UPDATE commands.
Any Workarounds for EF?

Thanks again,
Mike

Coordinator
Jan 18, 2013 at 9:15 PM

Unfortunately there isn't anything that I can do to avoid the "SQL: Statement too long" error. I've actually come across this issue a few times and reworked the provider to ensure that the generated sql statements are as short as possible. Adding concurrency on every field most likely doubled the length of the update statement resulting in it being too long for VFP to handle. Supposedly you can use SYS(3055) to request that VFP handle longer strings but I found that it just returned a different error after changing that setting.

In your case you might want to see if you could split the table up into several smaller entities or maybe reduce the concurrency fields down to just a couple (a last modified datetime and last modified user would be good).

Jan 18, 2013 at 10:20 PM

Bummer, is there any way within your code to split the replace up into multiple replaces possibly around a transaction?

Ill look into your other sugestions - Thanks

Regards,

Mike