Transactions

The transaction is the key tool used to deal with both the issue of data integrity and to address the problems
created by multiple simultaneous requests for the same data. Atransaction is defined as a unit of work. Within
a transaction, all of the SQL activity aimed at a database is guaranteed to be completed, and at the same time.
You can begin a transaction either explicitly, with the BEGIN TRANSACTION syntax, or implicitly, by issuing an
SQL statement. You end a transaction by either writing the changes to the database and making them visible
to other users, with the keyword COMMIT, or by erasing all the work in the transaction, with the keyword
ROLLBACK. The code for a simple transaction is shown in the following example, with the optional keywords BEGIN TRANSACTION included:
BEGIN TRANSACTION;
UPDATE USER_ACCT SET BALANCE = BALANCE + :nAmount WHERE ACCT_ID = :nDestAcct;
UPDATE USER_ACCT SET BALANCE = BALANCE - :nAmount WHERE ACCT_ID = :nSourceAcct;

COMMIT;

This code illustrates the classic use of a transaction. The transaction is taking money from one account and
moving it to another account. You would not want one part of this transaction to succeed and the other
part to fail. Although the SQL that adds an amount to the destination account actually comes before the
SQL that subtracts the same amount from a source account, the order of statements in the transaction
doesn’t matter. In a transaction, either the results of all the statements are applied permanently to the
database or all the transactions are rolled back with the syntax ROLLBACK.
Within transactions, a ROLLBACK rolls back all the changes made by the transaction. There may be times
when you do not want to roll back all the changes, just some of them. To handle these situations, you can
use the SAVEPOINT syntax. A ROLLBACK either rolls back all the work for the entire transaction or just to
the point of the last checkpoint. In the following example, the first piece of SQL shown is adding a line to
an overall order. The application allows a user to delete just that line of an order without destroying the
entire order. The SAVEPOINT statement sets a point for the end of a rollback in this scenario.
.
.
INSERT INTO ORDER_HEADERS (CUSTOMER_NAME, ORDER_HEADER_ID) VALUES (:sCustomerName,
:sOrderHeaderID);
SAVEPOINT first_insert;
INSERT INTO ORDER_LINES (ORDER_HEADER_ID, ITEM_ID, QUANTITY) VALUES
(:sOrderHeaderID, :sItemID, :nQuantity);
IF ORDER_LINE_DELETED
ROLLBACK TO first_insert;
ENDIF;
.
.
The transaction provides not only the mechanism to ensure logical data integrity but also the container
for implementing user isolation. Until a transaction is committed, the changes made by the transaction
are not visible to any other users in the database. But multiple users, accessing the same sets of data, can
create problems of data integrity.