ALLBASE/SQL Reference Manual (36216-90216)

84 Chapter2
Using ALLBASE/SQL
Managing Transactions
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.