John I agree that his solution, like others in the article and posted here seem to be missing Lock Hints in all the SQL statements.

When dealing with a shared transactual database with many differant user connections executing the same code that accesses the same data there is a hint that needs to be in every select and/or update statement.

WITH (NOLOCK) or WITH(ROWLOCK) or another locking hit that matches the requirements better.

This is my production 2 million transaction a day database. The connections stay at about 100-400 at all times and every connection creates a session object that is ID numbered using something similar to what Danny submited. I have never captured any locking or blocking in this DB.

I also have another DB app writen by a third party that was originaly writen for Oracle DB and refactored to work on MS SQL. It locks all the time becuase of the lack of locking hints.