Transaction-Based Programming

The programmers at Lyon Technologies write transaction-based code.

When dealing with multiple users affecting records on a database, it is important that all items be handled at a transaction level. A transaction is an inseparable list of database operations that must be executed either in its entirety or not at all.

Transactions either end with a commit or a rollback statement. If a transaction ends with a commit statement, then all the changes made to the database are made permanent. If the transaction fails or ends with a rollback, then none of the statements takes effect. If the database were to crash, rollback of all statements automatically occurs. The entire sequence of actions must be either completed or aborted.

By constructing our code in this way, Lyon Technologies maintains the data integrity of your system and guarantees that your database will always be in a consistent state.

Let's say, for example, that you are building a help desk application. You have a rule that whenever a Customer_Problem is closed, the record for it is moved to a Problem_Archive table. In this kind of scenario, two database actions actually take place: 1) the DELETE from the Customer_Problem table and 2) the INSERT into the Problem_Archive table.

In a perfect world where databases never fail, both actions occur without any difficulty. However, if the database happens to crash or if the network fails, then only part of the actions required to perform the function might occur. For example, the database might fail after the DELETE has occurred but before the INSERT is carried out. In a case such as this, without the use of transactions, the database will be left incomplete because the Customer_Problem record has been DELETED.

By combining the DELETE and INSERT actions into one transaction, this kind of loss is prevented: If the database should fail after the DELETE has occurred, then the database will have a record of the partial transaction. When the system is restored, a rollback will occur, which places the Customer_Problem record back where it was when the operation was previously started.

print this explanation | close this window