Un ottimo articolo http://www.sql-server-performance.com/2004/advanced-sql-locking.