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.
Read Application Locks (or Mutexes) in SQL Server 2005