Reduce Lock Contention in SQL Server

it is from http://support.microsoft.com/kb/75722.

Locking in SQL Server helps ensure consistency when reading and writing to the database. There is always a tradeoff in any relational database system between concurrency and consistency. It is always important to maintain consistency, or accuracy, of the data. However, the highest levels of consistency can result in less concurrency, or worse performance, if the proper steps are not taken.

Sometime, database performance tuning is the art of choosing the right tradeoffs.

The following methods can be used to reduce lock contention and increase overall throughput: 

1. Avoid situations in which many processes are attempting to perform updates or inserts on the same data page. For example, in version 6.x and earlier, if there is no clustered index on a table, or if the clustered index consists of a nonrandom value, such as an ever-increasing key value, all inserts will go on the last page of a table. This particular hotspot situation can be avoided by creating a clustered index on a value that will insure each user and/or process is inserting to a different page in the table.

2. Avoid transactions that include user interaction. Because locks are held for the duration of the transaction, a single user can degrade the entire systems performance.

3. Keep transactions that modify data as short as possible. The longer the transaction, the longer the exclusive or update locks are held. This blocks other activity and can lead to an increased number of deadlock situations.

4. Keep transactions in one batch. Unanticipated network problems may delay transactions from completing and thus releasing locks.

5. Avoid pessimistic locking hints such as holdlock whenever possible. They can cause processes to wait even on shared locks.

6. optimistic concurrency control can be specified in read-only environment

7. avoid expensive calculations while locks were hold.

8. design the code to have different phases so that to reduce the locking in shortest period on the shared resources. this is extremely useful in ETL's staging concept.