Pessimistic locking is something to avoid since it results in the record
being locked for the duration of the Edit / Update. Since the record is
locked for the entire duration, a further round trip is needed to the server
to obtain the lock when Edit is executed. In addition, you need to be
careful about the code that occurs between Edit and Update - if you show a
message box and the user goes out to lunch, the record (page, or table)
The problems that are inherent with pessimistic locking may be the reasons
why this topic comes up so rarely - by far most people use optimistic
locking where the record is locked for the duration of the Update phase
only. See also TimeStamp data type.
So, my suggestion is the obvious one - use optimistic locking and use it
correctly. Your application will have better performance, SQL Server will be
better able to server a higher number of concurrent users, and overall
coding & debugging will be easier with a reduced cost of maintenance.
If you have special requirements for locking the record, then perhaps post
back and describe those requirements. Recently another OP needed to
impliment a Check Out style function - as an example. Failing that, search
MSDN for SetLockingMode.