SQL Server: Best way to Update row if exists, Insert if not

This question pops up a lot everywhere and it's a common business requirement and until SQL Server 2008 doesn't come out with it's MERGE statement that will do that in one go we're stuck with 2 ways of achieving this. The biggest problem with every update/insert (upsert for those who haven't heard the term yet) is locking.