Jump to content United States-English
HP.com Home Products and Services Support and Drivers Solutions How to Buy
» Contact HP
More options
HP.com home
 ALLBASE/SQL Performance and Monitoring Guidelines: HP 9000 Computer Systems > Chapter 4 Guidelines on Transaction Design

General Tips on Managing Transactions
» 

Technical documentation

Complete book in PDF
» Feedback
Content starts here

 » Table of Contents

 » Index

spacerspacerspacer
spacer
spacer
  		 
 

  • 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.


Chapter 4 Guidelines on Transaction Design
  		 


Controlling Locking  
Printable version
Privacy statement Using this site means you accept its terms Feedback to webmaster
© Hewlett-Packard Development Company, L.P.