 |
» |
|
|
|
Non-cursor SELECT involves less overhead than FETCH with a
cursor. Use SELECT or BULK SELECT if you have a choice.
Use the KEEP CURSOR option with the OPEN statement to permit frequent
release of locks during long cursor operations. A combination of Cursor
Stability and KEEP CURSOR can improve concurrency by letting you scan
and update a large table without holding locks for the duration of the
entire scan. (Do not forget to use the COMMIT WORK statement immediately
following an OPEN for a kept cursor.)
Use DML only mode (i.e., DDL Enabled set to NO through
SQLUtil) to improve concurrency on access to system catalog tables.
Try to perform data definition in single-user mode as much as
possible. Data definition involves such activities as CREATE,
DROP, ALTER, UPDATE STATISTICS, GRANT, and REVOKE. It is
advisable to perform these operations outside of a production
application if at all possible, since these operations place
exclusive locks on the system catalog tables. These locks, like all
other exclusive locks, are held for the duration of the transaction,
and will reduce concurrency as other users attempt to access the
system catalog tables. For more information on system catalog
locks, refer to the appendix, "Locks Held on the System Catalog,"
in the ALLBASE/SQL Database Administration Guide.
Using Short Transactions and Savepoints | |
At the end of a transaction, the COMMIT WORK statement makes
changes permanent to disk, which causes I/O. Short transactions
free locks and buffers more frequently, which improves concurrency,
but they also increase log I/O (the increase is slight,
since the use of group commits in logging dilutes the
effect of increased log I/O in this case). Longer transactions minimize I/O,
but they hold locks longer and thus reduce concurrency. In general,
you should keep your transactions as short as possible to improve
the performance of the DBEnvironment.
A savepoint, created in a transaction with a SAVEPOINT statement,
marks a place you can roll back to in the transaction, releasing
locks that were obtained since you issued the statement.
Savepoints can be used to reduce the number of transactions
that must be resubmitted because part of the transaction was
unsuccessful. | | | |  | NOTE:
Whether or not you use savepoints, the entire transaction is
rolled back in the event of a deadlock.
| | | | |
|
