I'd recommend reading the DW2 link that cjard posted.
If you're a visual learner this series will be of assistance:
Forms over Data Video Series
Here's an article on handling concurrency issues in .NET. The article is for an ASP.NET application but it does go over some of the drawbacks of pessimistic locking and provides a couple of sample stored procedures that you could build from:
15 Seconds : Handling Concurrency Issues in .NET