ALLBASE/SQL Reference Manual (36216-90216)

Chapter 2 83
Using ALLBASE/SQL
Managing Transactions
The following example illustrates the DELETE statement and its two components:
DELETE FROM PurchDB.Parts --1
WHERE PartNumber = '9999-AJ' --2
Only a single table name or view name can be specified. Only certain views can be used to
delete rows, as described under “Updatability of Queries” in Chapter 3 , “SQL Queries.”
The WHERE clause is optional. You omit it if you want to delete
all the rows in a table or
view. Otherwise, you use it to specify a search condition for which row(s) to delete.
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