HP 3000 Manuals

Managing Transactions [ ALLBASE/SQL Reference Manual ] MPE/iX 5.5 Documentation

ALLBASE/SQL Reference Manual

Managing Transactions

A transaction is a logical unit of work that changes the database. All
actions within this logical unit of work must succeed, or all of them
must fail. When a transaction completes successfully, it is said to
commit. Should a transaction fail, none of the changes it generates are
recorded in the database, and the transaction aborts.

A transaction is bounded by the BEGIN WORK and COMMIT WORK statements.
One or more SQL statements, and any number of programming language
statements can be contained within a transaction. An example of a simple
transaction is as follows:

BEGIN WORK

UPDATE PurchDB.Parts
SET PartName = 'Defibrillator'
WHERE PartNumber = '1152-DE-95683'

COMMIT WORK

The SQL statements used in transaction management are as follows:

BEGIN WORK Starts the transaction.
COMMIT WORK Terminates a successful transaction.
ROLLBACK WORK Undoes any changes made by the current transaction.
SAVEPOINT Permits partial rollback of a transaction.

Objectives of Transaction Management

The objectives of transaction management are related to one another.
Data integrity is enforced by proper transaction management, but must be
balanced by the need for high concurrency. The use of transactions
facilitates the recovery of data after a crash, maintaining data
integrity.

Ensuring Logical Data Integrity.

The data in the database must be accurate and consistent. For example,
adding a part to the warehouse inventory entails inserting a row into
three tables: PurchDB.Parts, PurchDB.SupplyPrice, and PurchDB.Inventory.
All three inserts must succeed, or else the database is left in an
inconsistent state. To enforce data integrity, the three inserts are
contained in a single transaction. If any one insert fails, then the
entire transaction fails and none of the other inserts takes effect. The
following example shows how this transaction might be coded:

BEGIN WORK
INSERT INTO PurchDB.Parts ...
If the insert into PurchDB.Parts fails then
ROLLBACK
else
INSERT INTO PurchDB.SupplyPrice ...
If the insert into PurchDB.SupplyPrice fails then
ROLLBACK
else
INSERT INTO PurchDB.Inventory ...
If the insert into PurchDB.Inventory fails then
ROLLBACK
else
COMMIT WORK
endif
endif
endif

Maximizing Concurrency.

Concurrency is the degree to which data can be accessed simultaneously by
multiple users. For example, an application that allows one hundred
users to access a table simultaneously has higher concurrency, and
therefore better performance, than an application that allows only one
user at a time to access the table. Locking regulates the simultaneous
access of data. For example, if one user updates a row, the row is
locked and other users cannot access the row until the first user is
finished. Locking the row enforces data integrity, but reduces
concurrency because other users are forced to wait. The isolation level
specified in a BEGIN WORK statement affects the duration and types of
locks held within a transaction. Isolation levels are fully discussed in
the chapter, "Concurrency Control through Locks and Isolation Levels."
Well managed transactions balance the conflicting requirements of minimal
lock contention and maximum concurrency.

Facilitating Recovery.

When a soft crash occurs, incomplete transactions are automatically
rolled back when the DBEnvironment is restarted. If archive logging is
in effect when a hard crash occurs, committed transactions are applied to
the database during rollforward recovery. In both cases, only those
transactions that were uncommitted when the crash occurred need to be
redone.

Starting Transactions

A transaction is initiated with either an implicit or explicit BEGIN WORK
statement. An implicit BEGIN WORK statement is issued by ALLBASE/SQL
when any SQL statement is executed, except for the following:

ASSIGN BEGIN ARCHIVE BEGIN DECLARE SECTION
BEGIN WORK CHECKPOINT COMMIT ARCHIVE
COMMIT WORK CONNECT DECLARE VARIABLE
DISABLE AUDIT LOGGING ENABLE AUDIT LOGGING END DECLARE SECTION
GOTO IF INCLUDE
PRINT RAISE ERROR RELEASE
RESET RETURN ROLLBACK TO SAVEPOINT
ROLLBACK WORK SET SESSION SET TIMEOUT
SET TRANSACTION START DBE STOP DBE
SQLEXPLAIN TERMINATE USER WHENEVER
WHILE

Explicit BEGIN WORK statements are recommended, for the following
reasons:

* Explicit BEGIN WORK statements make your code easier to read.

* You must use an explicit BEGIN WORK statement to specify a
non-default isolation level or transaction priority.

* You might unintentionally lock out other users by the default
isolation level of an implicit BEGIN WORK.

Since nested transactions are not allowed, an error is generated if a
session with an active transaction issues a BEGIN WORK statement. The
first transaction must end before another transaction can begin.

Ending Transactions

A transaction ends when either a COMMIT WORK or a ROLLBACK WORK statement
is issued. All locks held by the session are released when the
transaction ends, except those held by a kept cursor.

Using COMMIT WORK.

Issue the COMMIT WORK statement when the transaction is successful and
you want the changes made permanent. Unlike the BEGIN WORK and ROLLBACK
WORK statements, the COMMIT WORK statement is never issued automatically
by ALLBASE/SQL. You must issue the COMMIT WORK explicitly for each
transaction. The COMMIT WORK statement causes the contents of the log
buffer to be written to a log file. If rollforward recovery is needed at
a later time, the transactions recorded in the log file are applied to
the database.

Using ROLLBACK WORK.

The ROLLBACK WORK statement ends the transaction and undoes all data
modifications made since the BEGIN WORK statement, unless it references a
savepoint. (See the discussion of savepoints in the following section.)
The ROLLBACK WORK statement is issued automatically by ALLBASE/SQL under
the following conditions:

* A non-archive log file becomes full.

* A RELEASE statement is issued before the end of the transaction.

* A system failure occurs. When the system is up again, and a START
DBE statement is issued, incomplete transactions are rolled back.

* ALLBASE/SQL chooses the transaction as the victim when breaking a
deadlock.

* The session is terminated by a TERMINATE USER command.

The ROLLBACK WORK statement should be issued explicitly to maintain data
integrity. You may want to issue a ROLLBACK WORK in an application
program when any of the following situations arise:

* The transaction contains more than one SQL statement and one of
the statements generates an error. For example, if your
transaction contains three INSERT statements, and the second
INSERT fails, you should rollback the entire transaction.

* An INSERT, UPDATE, or DELETE statement that affects multiple rows
generates an error after some of the rows have been modified. You
should rollback the transaction if the partial changes will leave
your database in an inconsistent state.

* The end user provides input indicating that he or she does not
want to commit the transaction.

Using SAVEPOINT

The SAVEPOINT statement allows you to rollback part of a transaction.
Multiple savepoints are permitted within a transaction anywhere between
the BEGIN WORK and COMMIT WORK statements. Each SAVEPOINT statement
places a unique marker, called a savepoint number, within the
transaction. When a subsequent ROLLBACK references the savepoint number,
only those database changes made after the savepoint are rolled back.
Rolling back to a savepoint does not end the transaction, but it does
release locks obtained after the savepoint was issued.

In the following ISQL example, the number identifying the savepoint
marker is 6. The update performed after the SAVEPOINT statement is
undone by the ROLLBACK statement, but any database changes made before
savepoint 6 are unaffected.

isql=> SAVEPOINT;

Savepoint number is 6. Use this number to do ROLLBACK WORK to 6.

isql=> UPDATE PurchDBParts
> SET SalesPrice = 244.00
> WHERE PartNumber = '1243-MU-01';

isql=> ROLLBACK WORK to 6;

After a rollback to a savepoint has been executed, use the COMMIT WORK
statement to make the changes that were not rolled back permanent. If
you want to rollback the entire transaction, issue the ROLLBACK statement
without a savepoint.

Savepoints are suitable for transactions that perform several operations,
any of which may need to be rolled back. In the following example, a
travel agency is booking tour reservations for 15 people. When the first
attempt to make a hotel reservation fails, only that part of the
transaction is rolled back. The car reservations are unaffected by the
roll back because they were made prior to the savepoint.

BEGIN WORK

Make 15 car reservations.

SAVEPOINT

Savepoint number is 1. An attempt to make 15 hotel reservations fails
because the designated hotel is full.

ROLLBACK WORK TO 1
SAVEPOINT

Savepoint number is 2. Make 15 hotel reservations at another hotel.

COMMIT WORK

Scoping of Transaction and Session Attributes

A set of attributes is associated with each transaction and user session.
This section discusses the statements used to specify the following
transaction and session attributes:

* priority
* isolation level
* label
* fill option
* constraint checking mode
* DML atomicity level

Each attribute can be specified in one or more of the statements listed
in Table 2-1 . You can issue such statements at any point in an
application or ISQL session (with the exception of BEGIN WORK which
cannot be issued within a transaction). However they may not take effect
immediately, and the duration of their effect differs as described in the
following paragraphs. The "SQL Statements" chapter contains complete
syntax for each statement.

When beginning a transaction, attributes specified in a BEGIN WORK
statement take effect immediately and remain in effect until the
transaction ends, unless reset by a SET TRANSACTION, SET CONSTRAINTS, or
SET DML ATOMICITY statement within the transaction.

Within a transaction, the attributes specified in a SET TRANSACTION, SET
CONSTRAINTS, or SET DML ATOMICITY statement take effect immediately and
remain in effect until the transaction ends, unless subsequently reset by
such a statement. A SET SESSION statement issued within a transaction
has no effect on the present transaction, instead it takes effect for the
next transaction and remains in effect for the duration of the session,
unless reset by a subsequent BEGIN WORK, SET TRANSACTION, SET
CONSTRAINTS, SET DML ATOMICITY, or SET SESSION statement.

Outside of a transaction, the attributes specified in a SET TRANSACTION
or SET SESSION statement take effect for the next transaction, unless
subsequently reset by such a statement or by a BEGIN WORK statement. The
SET TRANSACTION, SET CONSTRAINTS, and SET DML ATOMICITY statements remain
in effect for the duration of the transaction, unless subsequently reset.
The SET SESSION statement remains in effect for the duration of the
session, unless subsequently reset.

Table 2-1 shows these statements, the attributes associated with
each, when each statement goes into effect after being issued and the
scope of each statement's attributes if not reset by a subsequent
statement:

Table 2-1. Transaction Attribute Scope

------------------------------------------------------------------------------------------
| |
| 1 Note that SET SESSION issued within a transaction is not savepoint sensitive. |
| |
------------------------------------------------------------------------------------------

For example, you might write an application containing several
transactions. Each transaction contains one or more SELECT statements.
You want to ensure that all data selected has been committed to the
database. You know that the default isolation level for a session is RR,
but RR does not provide the concurrency you need. At the beginning of
the session, you set the isolation level to RC (read committed) for all
transactions in the session, as follows:

:
SET SESSION ISOLATION LEVEL RC
:

Note that each transaction starts implicitly. In this example, there is
no need for any BEGIN WORK statements. However, you might choose to
include BEGIN WORK statements to make your code more readable or to set a
different isolation level for a particular transaction.

SELECT * FROM PurchDB.OrderItems
WHERE VendPartNumber = '2310'
COMMIT WORK
:
SELECT * FROM PurchDB.Vendors
WHERE VendorNumber = 1234
COMMIT WORK
:
SELECT * FROM PurchDB.SupplyPrice
WHERE VendorNumber = 1234 AND VendPartNumber = '2310'
COMMIT WORK
:

For more information on isolation levels, refer to the "Concurrency
Control through Locks and Isolation Levels" chapter in this manual.

Transaction Limits and Timeouts

The maximum number of concurrent transactions is determined by the
MaxTransactions parameter of the DBECon file. Use either the START DBE
statement or the SQLUtil ALTDBE command to set MaxTransactions. The
SQLUtil SHOWDBE command displays the current setting of MaxTransactions
in the DBECon file. If a session attempts to start a transaction, but
the maximum number of concurrent transactions has already been reached,
the new transaction is placed in the throttled wait queue. The
transaction must wait until it reaches the head of the queue and one of
the active transactions terminates. The throttled wait queue is serviced
on a first in, first out basis. The transaction priority parameter of
the BEGIN WORK statement determines which transaction is aborted to break
a deadlock, not the transaction's position on the throttled wait queue.
[REV BEG]

If the transaction is still waiting when its timeout limit is reached,
the transaction is aborted. The timeout action can also be set to abort
the command being processed instead of the entire transaction. Set the
timeout limit for the DBEnvironment with the STARTDBE statement or the
SQLUtil ALTDBE command. To specify a timeout limit for a particular
session, use the SET USER TIMEOUT statement. Both SET SESSION and SET
TRANSACTION have parameters to specify which action the system should
take when a timer expires. The setting of timeout values is also
incorporated into these commands.[REV END] The SQLUtil SHOWDBE command
displays the current, default, and maximum values of the timeout
parameter in the DBECon file.

Monitoring Transactions
[REV BEG]

The SYSTEM.TRANSACTION pseudo-table contains the user identifier,
connection-id, session identifier, transaction identifier, transaction
priority, and isolation level of every current transaction. To view this
information with ISQL, issue the following statement:[REV END]

isql=> SELECT * FROM System.Transaction;

To identify the transactions on the throttle wait queue, query the
SYSTEM.CALL pseudo-table as follows:

isql=> SELECT * FROM System.Call WHERE Status = 'Throttle wait';

For more information on transaction activity, consult Load subsystem in
SQLMON, the ALLBASE/SQL online monitoring tool. SQLMON provides the
following transaction information:

* total number of active and waiting transactions in the
DBEnvironment
* total number of BEGIN WORK, COMMIT WORK, and ROLLBACK WORK
statements executed in the DBEnvironment
* maximum number of transactions configured
* which sessions have active or waiting transactions
* which sessions have executed BEGIN WORK, COMMIT WORK, and ROLLBACK
WORK statements

See the ALLBASE/SQL Performance and Monitoring Guidelines for more
information on SQLMON.

Tips on Transaction Management

Keep transactions short. As the length of a transaction increases, so
does the chance that other transactions are forced to wait for the locks
it holds. In addition to increasing concurrency, short transactions
minimize the amount of data that must be re-entered after a system crash.
When archive logging is in effect, changes made to the database are
written to the log file whenever a COMMIT WORK is issued. If the system
crashes during a long transaction, a large number of uncommitted changes
will be rolled back.

To shorten a transaction, place program statements not essential to the
logical unit of work outside of the transaction. Retrieve all user input
before the start of a transaction, to ensure that locks are not held if
the user walks away from the terminal. Because terminal writes can also
be time consuming, they should not be performed within a transaction.

Careful use of savepoints can decrease the amount of time locks are held,
and reduces the need to resubmit transactions because part of a
transaction was unsuccessful.

Set the maximum number of transactions (MaxTransactions) and timeout
limit parameters correctly. If MaxTransactions is too low, transactions
will wait for no reason. However, the overall throughput of the
DBEnvironment may be reduced if MaxTransactions is too high. If the
timeout limit is too low, transactions will abort, but if set too high,
the session might wait indefinitely for a transaction slot.

MPE/iX 5.5 Documentation