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 Chapter 5 “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	EX 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 “Transaction Attribute Scope”. 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. Chapter 10 “SQL Statements A - D” and Chapters 11 and 12 contain 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 “Transaction Attribute Scope” 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

Statement	Attributes	When Effective	Duration of Attribute Setting	Begins a Transaction if None Already Begun
SET SESSION^[1]	isolation level priority label constraint checking mode DML atomicity level fill option	for the next transaction	until the session ends	no
SET TRANSACTION	isolation level priority label constraint checking mode DML atomicity level	for the next or current transaction	until the transaction ends	no
SET CONSTRAINTS	constraint checking mode	for the current transaction	until the transaction ends	yes
SET DML ATOMICITY	DML atomicity level	for the current transaction	until the transaction ends	yes
BEGIN WORK	isolation level priority label fill option	when the transaction begins	until the transaction ends	yes
^[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 Chapter 5 “Concurrency Control through Locks and Isolation Levels” 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.

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. The SQLUtil SHOWDBE command displays the current, default, and maximum values of the timeout parameter in the DBECon file.

Monitoring Transactions

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:

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 on-line 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.