I've written an article here on SQL Team about Application Locks in SQL Server 2005
Application locks aren't a well known area of locking in SQL Server, but they can be very useful for special scenarios. They work in a analogous way to the
lock() construct in .Net and are basicaly user defined mutexes in SQL Server.
It's a second part in the locking series articles:
Part 2: Application Locks (or Mutexes) in SQL Server 2005
Part 1: Introduction to locking in SQL Server 2005