Why We Need Transactions (Magic xpa 4.x)
Every application developer has the same goal of ensuring database integrity. There can be problems if you don’t use transactions to ensure database integrity and this can be illustrated by the following scenario:
A user, called Fred, wants to transfer $50 from his Savings account to his Checking account. In this case, the arithmetic is simple:
|
Savings = Savings – 50
|
|
Checking = Checking + 50
|
What would happen if there were a failure of some sort between the two operations? In this case, the Savings account would be decreased by $50, but the Checking account would not be updated. Poor Fred is out $50 and there is also the question of missing funds. Wait until an audit! We can say that, in this example, data integrity is severely damaged.
If a transaction was used to ensure that the two operations were performed as a single logical unit, then if there was a failure during the execution of this simplistic process, the entire process would have been aborted or rolled back (in the language of SQL) and data integrity is preserved. So the money in the accounts will return to the amounts that were there before initiating the transaction.
Here, I should also add the obvious: In the above scenario in which there seemed to be only two operations, there were actually two more operations:
|
Read Savings amount
|
|
Read Checking amount
|
This is without taking into account the actual changes to the database. As we can see, the seemingly simple process is not as simple as meets the eye.
In short, developing with transactions is a necessary part of data-bound applications.
