BEGIN WORK

Detailed information about isolation levels is presented in the "Concurrency Control through Locks and Isolation Levels" chapter.

When you use most SQL statements, ISQL or the preprocessor automatically issues the BEGIN WORK statement on your behalf, unless a transaction is already in progress. However, to clearly delimit transaction boundaries and to set attributes for a transaction (isolation level, priority, transaction label, and fill options), you can use explicit BEGIN WORK statements.

The following statements do not force an automatic BEGIN WORK to be processed:

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

See Chapter 2 “Using ALLBASE/SQL” "Scoping of Transaction and Session Attributes" section for information about statements used to set transaction attributes.

Within a given transaction, the isolation level, priority, and label can be changed by issuing a SET TRANSACTION statement. Attributes specified in a SET TRANSACTION statement within a transaction override any attributes set by a BEGIN WORK statement for the same transaction.

An application or ISQL can have one or more active transactions at a time. Refer to the SET MULTITRANSACTION statement syntax in this chapter.

The following sequences of statements must be in the same transaction in a program:

   PREPARE and EXECUTE
 
   PREPARE, DESCRIBE, OPEN, FETCH USING DESCRIPTOR, EXECUTE, and CLOSE
 
   OPEN, FETCH, DELETE WHERE CURRENT, UPDATE WHERE CURRENT, and CLOSE (unless KEEP CURSOR is used)

To end your transaction, you must issue a COMMIT WORK or ROLLBACK WORK statement. Otherwise, locks set by your transaction are held until a STOP DBE, DISCONNECT, RELEASE, or TERMINATE USER statement is processed.

If the maximum number of concurrent DBEnvironment transactions has been reached, the application is placed on a wait queue. If the application times out while waiting, an error occurs. Default and maximum timeout values are specified at the DBEnvironment level. To set a timeout for a session or transaction, use the SET USER TIMEOUT statement. Refer to Chapter 2 “Using ALLBASE/SQL” "Setting Timeout Values" section for further information.

To avoid lock contention in a given DBEnvironment, do not allow simultaneous transactions when performing data definition operations.

When using RC or RU, you should verify the existence of a row before you issue an UPDATE statement. In application programs that employ cursors, you can use the REFETCH statement prior to updating. REFETCH is not available in ISQL. Therefore, you should use caution in employing RC and RU in ISQL if you are doing updates.

If the FILL or PARALLEL FILL option has already been set for the session with a SET SESSION statement, and you do not want either of these options in effect for a given transaction, specify NO FILL in the transaction's BEGIN WORK statement.