As some of your might already know, our VFP 8.0 application is being upgraded to use Cursor Adapters to connect to Sql Server 2000. I'm now in a situation where I'm trying to AVOID designing our own customized locking schema. Previously we would simple issue an RLOCK() when editing a record in a free table, then UNLOCK() when user was done editing. Now things get a bit more complicated.
Because we're still not sure of how to best implement sql server locking with a VFP front end, my boss has suggested a customzied locking design. His idea is that VFP would write to a central "lock" table when a user decides to modify a record. Then if another user comes in and tries to modify the same record, his session would check the contents of the central "lock" table to see if the first user is already modifying the same record.
Of course, this design is NOT desirable. However, if I don't implement something soon, I'll have to go with his design.
I do understand the different sql server lock modes (S/U/X,etc) and transaction isolation levels (read commited/uncommited, etc) and locking "hints". In light of that, can someone give me a suggestion on how to implement a pessimistic locking scheme? We definitely want to implement the pessismistic method (i.e. user edits a record and holds the exclusive lock until he's done).
I'm on the fringe of implementing a solution, but I want it to be solid.
Thanks again for all of your past advice.