troubleshooting Question

VFP 8.0 and Sql Server: implementing pessimistic locking

Avatar of jgroetch
jgroetchFlag for United States of America asked on
FoxPro
15 Comments1 Solution3415 ViewsLast Modified:

Folks,
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.
Sincerey,
John
ASKER CERTIFIED SOLUTION
edwardsearch

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 15 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros