ID Number: Q75722
1.10 1.11 4.20
OS/2
Summary:
Locking in SQL Server helps ensure consistency when reading and
writing to the database. While this is desirable, it does affect
performance.
More Information:
The following methods can be used to reduce lock contention and thus
increase overall throughput:
- Avoid situations in which many processes are attempting to perform
updates or inserts on the same data page. For example, this can
occur in a non-indexed table, because all additions are applied to
the last page in the chain. Creating separate history tables that
are accessed by various groups can minimize the delay in this
situation. In addition, creating a clustered index to distribute
data will also help.
- 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.
- Use HOLDLOCK only when necessary, because updates can be delayed by
waiting for shared locks to be released.
- 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.
- Using the SQL Server 4.2 cursor extensions to DB-LIBRARY (db-lib),
optimistic concurrency control can be specified, using the
CCUR_OPTCC setting in dbcursoropen(). This is ideal for applications
that require a high degree of user interaction combined with high
concurrency. This option ensures that update locks are obtained only
when a user wants to commit a transaction.
Additional reference words: 1.10 1.11 4.20 dblib