|
|
The BEGIN WORK statement begins a transaction and, optionally, sets
one or more transaction attributes.
ISQL, Application Programs, or Procedures
BEGIN WORK [Priority][RR
CS
RC
RU ][LABEL {'LabelString'
:HostVariable}][[PARALLEL
NO ]FILL]
- Priority
is an integer from 0 to 255 specifying the priority of the
transaction. Priority 127 is assigned if you do not specify a priority.
ALLBASE/SQL uses the priority to resolve a deadlock. The transaction with
the largest priority number is aborted to remove the deadlock.
For example, if a priority-0 transaction and a priority-1 transaction are
deadlocked, the priority-1 transaction is aborted. If two transactions
involved in a deadlock have the same priority, the deadlock is resolved
by aborting the newer transaction (the last transaction begun, either
implicitly or with a BEGIN WORK statement).
- RR
Repeatable Read. Means that the transaction uses locking strategies to
guarantee repeatable reads.
RR is the default isolation level.
- CS
Cursor Stability. Means that your transaction uses locking strategies
to assure cursor-level stability only.
- RC
Read Committed. Means that your transaction uses locking strategies to
ensure that you retrieve only rows that have been committed by some
transaction.
- RU
Read Uncommitted. Means that the transaction can read uncommitted
changes from other transactions. Reading data with RU does not place any
locks on the table being read.
- LabelString
is a user defined character string of up to 8 characters. The default
is a blank string. The label is visible in the SYSTEM.TRANSACTION
pseudo-table and also in SQLMON. Transaction labels can be useful
for troubleshooting and performance tuning. Each transaction in an
application program can be marked uniquely, allowing the DBA to easily
identify the transaction being executed by any user at any
moment.
- HostVariable
is a host variable containing the LabelString.
- FILL
is used to optimize I/O performance when loading data and creating
indexes.
- PARALLEL FILL
is used to optimize I/O performance for multiple, concurrent loads to
the same table. The PARALLEL FILL option must be in effect for
each load.
- NO FILL
turns off the FILL or PARALLEL FILL option for the duration of the
transaction. This is the default fill option.
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.
You do not need authorization to use the BEGIN WORK statement.
BEGIN WORK and ROLLBACK WORK
Transaction begins:
BEGIN WORK CS
statement-1
SAVEPOINT :MyVariable
statement-2
statement-3
Work of statements 2 and 3 is undone:
ROLLBACK WORK TO :MyVariable
Work of statement-1 is committed and the transaction ends:
COMMIT WORK
BEGIN WORK and set attributes
Begin the transaction and set priority, isolation level, label name,
and fill option:
BEGIN WORK 32 CS LABEL 'xact1' FILL
.
.
.
Execute SQL statements.
.
.
.
Work is committed and the transaction ends.
COMMIT WORK
Begin another transaction and set priority, isolation level,
and label name. Note that since a fill option is not specified,
the default (NO FILL) is in effect.
BEGIN WORK 64 RC LABEL 'xact2'
.
.
.
Execute SQL statements.
.
.
.
Work is committed and the transaction ends.
COMMIT WORK;
|