In the SQL context, a transaction consists of one or more SQL statements that operate as a single unit. Each SQL statement in such a unit is dependent on the others, and the unit as a whole is indivisible. If one statement in the unit does not complete successfully, the entire unit will be rolled back and all the affected data will be returned to the state it was in before the transaction was started. Thus, a transaction is said to be successful only if all the individual statements within it are executed successfully.
You might find it hard to think of situations where this all-for-one and one-for-all approach would be useful. In reality, transactions abound all around us—in bank transfers, stock trades, web-based shopping carts, inventory control—the list goes on and on. In all these cases, the success of the transaction depends on a number of interdependent actions executing successfully and in harmony with each other. A failure in any of them must cancel the transaction and return the system back to its earlier, pre-transaction state.
The best way to understand this is with a simple example. Consider a stock trade on any stock exchange (Figure 1), in which Trader A sells 400 shares in ACME Corp. to Trader B.
FIGURE 1 A stock exchange transaction
Somewhere behind the hullabaloo of the trading ring is a complex database system tracking all such deals. In this system, a trade such as the previous one is deemed complete only when Trader A’s account is debited by 400 ACME Corp. shares and Trader B’s account is simultaneously credited with those shares. If either of the previous two steps fail, the exchange would have the unenviable situation of 400 ACME Corp. shares floating around the system with no owner…not very pleasant, I’m sure you’d agree.
Thus, the transfer of 400 ACME Corp. shares from Trader A to Trader B in the previous example can be considered a transaction—a single unit of work that internally encompasses several SQL statements (delete 400 shares from Trader A’s account records, add 400 shares to Trader B’s records, perform commission calculations for both traders, and save the changes). In keeping with the previous transaction definition, all of these statements should execute successfully. If any one of them fails, the transaction should be reversed so the system goes back to its earlier, stable state. Or, to put it another way, at no point in time should the ownership of the 400 shares be ambiguous.
Let’s take another example, this one from our example database: adding a new flight (Figure 2). When adding a flight, the airline has to perform three steps: define the flight’s source, destination and aircraft; define the flight’s departure days and times; and define the number of classes and seats available in each class. At the database level, these operations require three different tables to be modified. If any of these three steps were to fail, the system should cancel all the changes made to avoid an inconsistent or incomplete flight record.
FIGURE 2 An airline fight addition
The three previous tasks constitute a single transaction. A failure in any one of them should cause the entire transaction to be cancelled and the system returned to its previous state.
Leave a Reply