HP 3000 Manuals HP 3000 Manuals
BEGIN WORK
The BEGIN WORK statement begins a transaction and, optionally, sets one
or more transaction attributes.
Scope
ISQL, Application Programs, or Procedures
SQL Syntax
[RR]
BEGIN WORK [Priority] [CS] [LABEL {'LabelString'}] [[PARALLEL] FILL]
[RC] [ {:HostVariable}] [[NO ] ]
[RU]
Parameters
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.
Description
* 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 the "Using ALLBASE/SQL" chapter, "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 the "Using ALLBASE/SQL" chapter,
"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.
Authorization
You do not need authorization to use the BEGIN WORK statement.
Examples
1. 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
2. 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;