Christia,

It's more involved with SQL Server, because (to the best of my knowledge), there's no simple RLOCK() that allows one user to grab a row, such that others can read from (but not write to) the row until an UNLOCK is issued.

If you do a search on the SQL server forum for certain keywords (like "record lock", etc.), you'll find some solutions that people have discussed over the years. (I think you need a premium membership to be able to search for past messages).

What I've done in the past (and I got the tip from someone on this forum) is maintain a "lock table" of primary keys and lock status/lock user, for rows that are locked. So if you have a customer record where the primary key is 123, you could have the following:

1) User 'A' tries to edit customer 123. You check your lock table to see if 123 is in there, and if so, is the lock bit turned on.

If it's off (or 123 isn't even in there), user 'A' gets the row. (If 123 wasn't in there, insert a row. And then set the lock status on and any other tracking info you want to set)

2) User 'B' pulls up customer 123 a minute later, and tries to edit. This time, a search into the lock table for key 123 finds that it IS locked by user 'A' (maybe the lock table could even indicate that user 'A' grabbed the record at 11:30 AM). So you can display some type of message to User 'B'.

3) User 'A' saves the record...so you can update the lock table and set the lock status off.

4) Now User 'B' tries to edit...however, the record they have is the 'old' record...so many you need a rowversion entry for each record as well...because the row that user 'B' pulled up is now out of date. So you need to account for that (by pulling down the 'updated' row automatically, or telling the user they need to re-retrieve, or whatever you want to do).

That may not be the 'end-all' for what you need to do, you may have additional workflow requirements that need to be accounted for in your approach - the first thing someone usually says to this is..."what happens when user 'A' starts to edit, then walks away for hours". One client I worked with had a rule that a lock was valid for an hour. So when user 'B' pulls up the row and finds that it was locked an hour ago, the client would let user 'B' have the record. That may not work for you, but just a thought.

But hopefully this might give you a few ideas for starters, and I'm sure others who have come across this will have input. But again, I encourage you to check past messages on the sql server forum - there's a wealth of information there.

Kevin