SQL/MX 2.x Reference Manual (H06.04+)

Introduction
HP NonStop SQL/MX Reference Manual—540440-003
1-12
Transaction Management
•
A process can hold a lock over the duration of one (or more) transactions, or the
process can release the lock before the transaction completes. A process releases
the locks it holds by issuing statements that affect the locks.
Stopping or abnormal termination of a process frees any locks the process holds on
nonaudited tables.
Transaction Management
A transaction (a set of database changes that must be completed as a group) is the
basic recoverable unit in case of a failure or transaction interruption. Transactions can
be defined during an MXCI session or in a host program. The typical order of events is:
1. Transaction is started.
2. Database changes are made.
3. Transaction is committed.
If, however, the changes cannot be made or if you do not want to complete the
transaction, you can abort the transaction so that the database is rolled back to its
original state.
All SQL/MX tables must be audited, and although SQL/MP tables can be nonaudited,
HP recommends that they be audited. Transactions are managed by the HP
NonStop Transaction Management Facility (TMF). This product simplifies the task of
maintaining data consistency for databases being updated by concurrent transactions.
For more information on TMF, see the Transaction Management Facility (TMF)
Introduction.
Any transaction is subject to TMF's two-hour limit on audit trails. TMF will automatically
abort a query that runs longer than two hours. You can change this limit to a maximum
of 5965 hours (about 8 months) or set it to zero. In that case, TMF will never perform
an AUTOABORT. This limit can help protect your application from runaway queries or
transactions.
In spite of the AUTOABORT setting, TMF still aborts any transaction or query that pins
the oldest MAT (master audit file) if the file is pinned because of currently active
transactions, and if audit information is filling 45% or more of the MAT's capacity.
Choose this setting with care. Increasing TMF limits degrades system performance
and increases disk space usage for the audit trail.
If you are running a business intelligence system, base the setting on how long you
expect your longest query to run. If you are running an online transaction environment,
base the setting on the longest running update transaction that you plan to have. It is
preferable to have short running transactions or batch updates with frequent COMMIT
WORK statements.
This subsection discusses these considerations for transaction management:
•
Statement Atomicity on page 1-13
•
User-Defined and System-Defined Transactions on page 1-14