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