A transaction is a sequence of one or more SQL statements that together form a logical unit of work. The SQL statements that form the transaction are typically closely related and perform interdependent actions. Each statement in the transaction performs some part of a task, but all of them are required to complete the task. Grouping the statements as a single transaction tells the DBMS that the entire statement sequence should be executed atomically—all of the statements must be completed for the database to be in a consistent state.
Here are some examples of typical transactions for the sample database, along with the SQL statement sequence that comprises each transaction:
- Add -an-order. To accept a customer’s order, the order-entry program should (a) query the PRODUCTS table to ensure that the product is in stock, (b) insert the order into the ORDERS table, (c) update the PRODUCTS table, subtracting the quantity ordered from the quantity-on-hand of the product, (d) update the SALESREPS table, adding the order amount to the total sales of the salesperson who took the order, and (e) update the OFFICES table, adding the order amount to the total sales of the office where the salesperson works.
- Cancel-an-order. To cancel a customer’s order, the program should (a) delete the order from the ORDERS table, (b) update the PRODUCTS table, adjusting the quantity-on-hand total for the product, (c) update the SALESREPS table, subtracting the order amount from the salesperson’s total sales, and (d) update the OFFICES table, subtracting the order amount from the office’s total sales.
- Reassign-a-customer. When a customer is reassigned from one salesperson to another, the program should (a) update the CUSTOMERS table to reflect the change, (b) update the ORDERS table to show the new salesperson for all orders placed by the customer, (c) update the SALESREPS table, reducing the quota for the salesperson losing the customer, and (d) update the SALESREPS table, raising the quota for the salesperson gaining the customer.
In each of these cases, a sequence of four or five actions, where each action consists of a separate SQL statement, is required to handle the single logical transaction.
The transaction concept is critical for programs that update a database because it ensures the integrity of the database. A SQL-based DBMS makes this commitment about the statements in a transaction:
The statements in a transaction will be executed as an atomic unit of work in the database. Either all of the statements will be executed successfully, or none of the statements will be executed.
The DBMS is responsible for keeping this commitment even if the application program aborts or a hardware failure occurs in the middle of the transaction, as shown in Figure 12-1. In each case, the DBMS must make sure that when failure recovery is complete, the database never reflects a partial transaction.
1. Commit and rollback
SQL supports database transactions through two SQL transaction-processing statements, shown in Figure 12-2:
- COMMIT. The COMMIT statement signals the successful end of a transaction. It tells the DBMS that the transaction is now complete; all of the statements that comprise the transaction have been executed, and the database is self-consistent.
- ROLLBACK. The ROLLBACK statement signals the unsuccessful end of a transaction. It tells the DBMS that the user does not want to complete the transaction; instead, the DBMS should back out any changes made to the database during the transaction. In effect, the DBMS restores the database to its state before the transaction began.
The COMMIT and ROLLBACK statements are executable SQL statements, just like SELECT, INSERT, and UPDATE. Here is an example of a successful update transaction that changes the quantity and amount of an order and adjusts the totals for the product, salesperson, and office associated with the order. A change like this would typically be handled by a forms-based change order program, which would use programmatic SQL to execute the statements shown here.
Change the quantity on order number 113051 from 4 to 10, which raises its amount from $1458 to $3550. The order is for QSA-XK47 reducers and was placed with Larry Fitch (employee number 108) who works in Los Angeles (office number 21).
UPDATE ORDERS
SET QTY = 10, AMOUNT = 3550.00
WHERE ORDER_NR = 113051
UPDATE SALESREPS
SET SALES = SALES – 1458.00 + 3550.00
WHERE EMPL_NUM = 108
UPDATE OFFICES
SET SALES = SALES – 1458.00 + 3550.00
WHERE OFFICE = 21
UPDATE PRODUCTS
SET QTY_ON_HAND = QTY_ON_HAND + 4 – 10
WHERE MFR_ID = ‘QSA’
AND PRODUCT_ID = ‘XK47’
. . . confirm the change one last time with the customer . . .
COMMIT WORK
Here is the same transaction, but this time assume that the user makes an error entering the product number. To correct the error, the transaction is rolled back, so that it can be reentered correctly:
Change the quantity on order number 113051 from 4 to 10, which raises its amount from $1458 to $3550. The order is for QAS-XK47 reducers and was placed with Larry Fitch (employee number 108), who works in Los Angeles (office number 21).
UPDATE ORDERS
SET QTY = 10, AMOUNT = 3550.00
WHERE ORDER_NR = 113051
UPDATE SALESREPS
SET SALES = SALES – 1458.00 + 3550.00
WHERE EMPL NUM = 108
UPDATE OFFICES
SET SALES = SALES – 1458.00 + 3550.00
WHERE OFFICE = 21
UPDATE PRODUCTS
SET QTY_ON_HAND = QTY_ON_HAND + 4 – 10
WHERE MFR_ID = ‘QAS’
AND PRODUCT_ID = ‘XK47’
. . . oops! the manufacturer is “QSA,” not “QAS” . . .
ROLLBACK WORK
2. The ANSI/ISO Transaction Model
The ANSI/ISO SQL standard defines a SQL transaction model and the roles of the COMMIT and ROLLBACK statements. Most, but not all, commercial SQL products use this transaction model, which is based on the transaction support in the early releases of DB2. The standard specifies that a SQL transaction automatically begins with the first SQL statement executed by a user or a program. The transaction continues through subsequent SQL statements until it ends in one of four ways:
- COMMIT. A COMMIT statement ends the transaction successfully, making its database changes permanent. A new transaction begins immediately after the COMMIT statement.
- ROLLBACK. A ROLLBACK statement aborts the transaction, backing out its database changes. A new transaction begins immediately after the ROLLBACK statement.
- Successful program termination. For programmatic SQL, successful program termination also ends the transaction successfully, just as if a COMMIT statement had been executed. Because the program is finished, there is no new transaction to begin.
- Abnormal program termination. For programmatic SQL, abnormal program termination also aborts the transaction, just as if a ROLLBACK statement had been executed. Because the program is finished, there is no new transaction to begin.
Figure 12-3 shows typical transactions that illustrate these four conditions. Note that the user or program is always in a transaction under the ANSI/ISO transaction model. No explicit action is required to begin a transaction; it begins automatically with the first SQL statement or immediately after the preceding transaction ends.
Recall that the ANSI/ISO SQL standard is primarily focused on a programmatic SQL language for use in application programs. Transactions play an important role in programmatic SQL, because even a simple application program often needs to carry out a sequence of two or three SQL statements to accomplish its task. Because users can change their minds and other conditions can occur (such as being out of stock on a product that a customer wants to order), an application program must be able to proceed partway through a transaction and then choose to abort or continue. The COMMIT and ROLLBACK statements provide precisely this capability.
The COMMIT and ROLLBACK statements can also be used in interactive SQL, but in practice, they are rarely seen in this context. Interactive SQL is generally used for database queries; updates are less common, and multistatement updates are almost never performed by typing the statements into an interactive SQL facility. As a result, transactions are typically a minor concern in interactive SQL. In fact, many interactive SQL products default to an autocommit mode, where a COMMIT statement is automatically executed after each SQL statement typed by the user. This effectively makes each interactive SQL statement its own transaction.
3. Other Transaction Models
A few commercial SQL products depart from the ANSI/ISO transaction model to provide additional transaction-processing capability for their users. The Sybase DBMS, which is designed for online transaction-processing applications, is one example. SQL Server, which was derived from the Sybase product, also uses the Sybase transaction model.
The Transact-SQL dialect used by Sybase includes four transaction-processing statements:
- BEGIN TRANSACTION. The BEGIN TRANSACTION statement signals the beginning of a transaction. Unlike the ANSI/ISO transaction model, which implicitly begins a new transaction when the previous one ends, Sybase requires an explicit statement to start a transaction.
- COMMIT TRANSACTION. Signals the successful end of a transaction. As in the ANSI/ISO model, all changes made to the database during the transaction become permanent. However, a new transaction is not automatically started.
- SAVE TRANSACTION. Establishes a savepoint in the middle of a transaction. Sybase saves the state of the database at the current point in the transaction and assigns the saved state a savepoint name, specified in the statement.
- ROLLBACK TRANSACTION. Has two roles. If a savepoint is named in the ROLLBACK statement, Sybase backs out the database changes made since the savepoint, effectively rolling the transaction back to the point where the SAVE TRANSACTION statement was executed. If no savepoint is named, the ROLLBACK statement backs out all database changes made since the BEGIN TRANSACTION
The Sybase savepoint mechanism is especially useful in complex transactions involving many statements, as shown in Figure 12-4. The application program in the figure periodically saves its status as the transaction progresses, establishing two named savepoints. If problems develop later during the transaction, the application program does not have to abort the entire transaction. Instead, it can roll the transaction back to any of its savepoints and proceed from there. All of the statements executed before the savepoint remain in effect; those executed since the savepoint are backed out by the rollback operation.
Note that the entire transaction is still the logical unit of work for Sybase, as it is for the ANSI/ISO model. If a system or hardware failure occurs in the middle of a transaction, for example, the entire transaction is backed out of the database. Thus, savepoints are a convenience for the application program, but not a fundamental change to the ANSI/ISO transaction model.
The explicit use of a BEGIN TRANSACTION statement is, however, a significant departure from the ANSI/ISO model. SQL statements that are executed outside a transaction (that is, statements that do not appear between a BEGIN/COMMIT or a BEGIN/ROLLBACK statement pair) are effectively handled in autocommit mode. Each statement is committed as it is executed; there is no way to roll back the statement once it has succeeded.
Some DBMS brands that use a Sybase-style transaction model prohibit statements that alter the structure of a database or its security from occurring within a transaction (such as CREATE TABLE, ALTER TABLE, and DROP TABLE, discussed in Chapter 13, and GRANT and REVOKE, discussed in Chapter 15). These statements must be executed outside a transaction. This restriction makes the transaction model easier to implement, because it ensures that the structure of the database cannot change during a transaction.
In contrast, in a complete implementation of the ANSI/ISO-style transaction model, the structure of a database can change significantly during a transaction. (Tables can be dropped, created, and populated, for example.) The DBMS must be able to undo all of these alterations if the user later decides to roll back the transaction, including the structural changes. In practice, this can be difficult to implement, and many popular DBMS products introduce simplifying restrictions. A common restriction is that database structure changes cannot be intermixed with database access operations within a transaction. Another common restriction is that a transaction that alters the database structure may contain one and only one SQL statement (such as a CREATE TABLE or a DROP TABLE statement).
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.