These locking guidelines can help reduce lock contention and speed performance:
Use the lowest level of locking required by each application. Use isolation level 2 or 3 only when necessary.
Updates by other transactions may be delayed until a transaction using isolation level 3 releases any of its shared locks at the end of the transaction.
Use isolation level 3 only when nonrepeatable reads or phantoms may interfere with results.
If only a few queries require isolation level 3, use the holdlock keyword or the at isolation serializing clause in those queries rather than using set transaction isolation level 3 for the entire transaction.
If most queries in the transaction require isolation level 3, use set transaction isolation level 3, but use noholdlock or at isolation read committed in the queries that can execute at isolation level 1.
To perform mass insertions, updates, or deletions on active tables, reduce blocking by performing the operation inside a stored procedure using a cursor, with frequent commits.
If the application must return a row, wait for user interaction, and then update the row, consider using timestamps and the tsequal function rather than holdlock.
If you use third-party software, check the locking model in applications carefully for concurrency problems.
Other tuning efforts can also help reduce lock contention. For example, if a process holds locks on a page, and must perform a physical I/O to read an additional page, the process holds the lock much longer than it would if the additional page were already in cache. In this case, better cache utilization or the use of large I/O can reduce lock contention. You can also reduce lock contention by improving indexing and distributing physical I/O evenly across disks.
