What are the considerations for deciding on transaction boundaries?
Joe Sam Shirah Transaction processing should always deal with more than one statement and a transaction is often described as a Logical Unit of Work ( LUW ). The rationale for transactions is that you want to know definitively that all or none of the LUW completed successfully. Note that this automatically gives you restart capability. Typically, there are two conditions under which you would want to use transactions:
- Multiple statements involving a single file - An example would be inserting all of a group of rows or all price updates for a given date. You want all of these to take effect at the same time; inserting or changing some subset is not acceptable.
- Multiple statements involving multiple files - The classic example is transferring money from one account to another or double entry accounting; you don't want the debit to succeed and the credit to fail because money or important records will be lost. Another example is a master/detail relationship, where, say, the master contains a total column. If the entire LUW, writing the detail row and updating the master row, is not completed successfully, you A) want to know that the transaction was unsuccessful and B) that a portion of the transaction was not lost or dangling.