Kevin,

What you are describing is a semaphore lock. i.e. setting a flag of some kind. This is simple and works well. It's agood scheme for locking FoxPro tables and has beena round for years. The alternative is to create a lock table and place rows in the table that are locked.

Both versions come with maintenance issues.

The other method you describe can be problematic in a large production environment as SQL Server may choose to perform deletes and inserts instead of inplace updates. As well it looks like you will have open transactions on the server. Is this really what you want? Transactions should be short and sweet - not held at the whim of what's going on at the workstation.

- Val


>To
>All
>Hi, all.
>
>I've been trying for weeks to come up with a row-record locking scheme for a VFP app that uses SQL Server 2000.
>
>As a simple example, we have a customer maintenance form for CUSTMAST. If one user goes into a record and starts to EDIT, we want to be able to lock the record in SQL Server, so that if a 2nd user comes along 10 seconds later and also tries to edit, the 'RLOCK' attempt would fail (even though there's no RLOCK in T-SQL).
>
>I posed the solution that we could have a LOCKSTATUS column in CUSTMAST, that a value of 1 would be locked and 0 would be unlocked.
>
>However, another developer suggested the following...
>
>1- Use SQLSETPROP to set TRANSACTIONS to 2, and ASYNCHRONOUS to TRUE.
>
>2- Attempt a simple update on CUSTMAST, where you set any field to the current value...for instance: nResult = SQLEXEC(nMyConn,'UPDATE CUSTMAST SET ADDRESS = ADDRESS')
>
>3) If the 'mock update' succeeds, you know you have a LOCK. If nResult returns a 0 or -1, then you know it failed.
>
>4) if it succeeded, go about your business, let the user edit, and then do your real UPDATE, and then finish with either an SQLCOMMIT or CANCEL/ROLLBACK to unlock the record.
>
>Admittely, this first sounded a bit kludgy, but I can't think of any specific reason to discount it in a production environment.
>
>Comments/thoughts?
>Thanks,
>Kevin