Article ID: 75722
Article Last Modified on 12/13/2005
APPLIES TO
- Microsoft SQL Server 4.21a Standard Edition
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
- Microsoft SQL Server 2005 Standard Edition
- Microsoft SQL Server 2005 Developer Edition
- Microsoft SQL Server 2005 Enterprise Edition
- Microsoft SQL Server 2005 Express Edition
- Microsoft SQL Server 2005 Workgroup Edition
This article was previously published under Q75722
SUMMARY
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.
MORE INFORMATION
The following methods can be used to reduce lock contention and increase overall throughput:
- 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.
- 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.
- 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.
- Keep transactions in one batch. Unanticipated network problems may delay transactions from completing and thus releasing locks.
- Avoid pessimistic locking hints such as holdlock whenever possible. They can cause processes to wait even on shared locks.
- In most cases, you should use SQL Server's default isolation level. The isolation level determines at what point the tradeoffs are made between concurrency and consistency. If you have a strong business need for a higher isolation level, make sure that you evaluate all the tradeoffs and perform thorough testing under a high stress load.
- Reduce the fillfactor when creating an index to help diminish the chance of random updates requiring the same page. This is especially useful for small tables that are frequently accessed.
- If you are using DB-Library (DB-Lib), optimistic concurrency control can be specified by using the CCUR_OPTCC setting in dbcursoropen(). This option ensures that update locks are obtained only when a user wants to commit a transaction.
Additional query words: 4.20 dblib Windows NT
Keywords: kbinfo kbprogramming KB75722
