1. Use appropriate locking level
you can override how SQL Server performs locking on a table by using the SP_INDEXOPTION command. Below is an example of code you can run to tell SQL Server to use page locking, not row locks, for a specific table:
SP_INDEXOPTION 'table_name', 'AllowRowLocks', FALSE
SP_INDEXOPTION 'table_name', 'AllowPageLocks', FALSE
This code turns off both row and page locking for the table, thus only table locking is available.
2. Keep all Transact-SQL transactions as short as possible.
3. An often overlooked cause of locking is an I/O bottleneck.
4. To help reduce the amount of time tables are locked, which hurts concurrency and performance, avoid interleaving reads and database changes within the same transaction.
5. Any conditional logic, variable assignment, and other related preliminary setup should be done outside of transactions
6. Encapsulate all transactions within stored procedures
7. If you have a client application that needs to "check-out" data
for more information in detail please review: