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 your desired results.
If only a few queries require level 3, use the holdlock keyword or at isolation serializing clause in those queries instead of using set transaction isolation level 3 for the entire transaction.
If most queries in the transaction require level 3, use set transaction isolation level 3, but use noholdlock or at isolation read committed in the remaining queries that can execute at isolation level 1.
If you need to perform mass inserts, updates, or deletes on active tables, you can reduce blocking by performing the operation inside a stored procedure using a cursor, with frequent commits.
If your application needs to return a row, provide for user interaction, and then update the row, consider using timestamps and the tsequal function rather than holdlock.
If you are using third-party software, check the locking model in applications carefully for concurrency problems.
Also, other tuning efforts can 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, it holds the lock much longer than it would have if the additional page had already been in cache.
Better cache utilization or using large I/O can reduce lock contention in this case. Other tuning efforts that can pay off in reduced lock contention are improved indexing and good distribution of physical I/O across disks.
