What are transactions and how do I use them?

Imagine transferring €200,000 from one bank account to another. You withdraw the amount from the source account, and then you deposit it in the destination account. So far everything is normal and in a perfect world the operation will succeed. Unfortunately, here in the real world things can go wrong. Something happens and the money is lost. That’s very bad.

Well, transactions are here to ensure that this doesn’t happen with your applications! In this blog post explore in detail the use and importance of transactions, as well as several scenarios showing how they can save your business.

What are transactions?

According to the documentation:

Transactions are a series of related data modifications made to a database within a process. A transaction is not saved to a database permanently until the transaction is validated. If a transaction is not completed, either because it is canceled or because of some outside event, the modifications are not saved.

It’s important to note:

When you use nested transactions, the result of each subtransaction depends on the validation or cancellation of the higher-level transaction. If the higher-level transaction is validated, the results of the subtransactions are confirmed (validation or cancellation). On the other hand, if the higher-level transaction is cancelled, all the subtransactions are cancelled, regardless of their respective results.

Be sure to check out the documentation for more in-depth information about transactions.

using Transactions

It’s difficult to imagine business applications that don’t use transactions, yet from time to time I’ve seen them! When discussing this lack of transactions with the application developers, I’ve heard phrases like “oh, it’s worked so far”, or even “we’ve always been lucky”. In my opinion, luck shouldn’t be a design concept for business applications.

A classic example of using transactions is accounting. Financial statements provide a record of two categories: assets and liabilities. Both need to be calculated and saved (completely, never partially), or not touched at all.

Another example is a shipping system, where the creation of an order decreases the inventory in the warehouse. Rather than relying on luck, we need to ensure that both the order and the inventory records are created or modified together. Whatever happens, either both operations are completed or canceled.

You can think of a transaction as a kind of big bracket:

• You open it (START TRANSACTION command),
• perform operations in the database (create/modify/delete records – from one or several tables),
• then close it by either validating (VALIDATE TRANSACTION command) or canceling (CANCEL TRANSACTION command) ALL of the operations.

 

Besides low-level database operations, another good case for making use of transactions is a user interface. Imagine an invoice input form, using a list box (or sub form) with product items (stored in another table) … or customers and contacts … or contacts and phone numbers. The list goes on and on ….

Your user wants to modify an invoice. They might change one or several items, then finally decide to click the cancel button and assume that all invoice item modifications are reversed (not saved). You could handle this with complex arrays or simply by using a transaction.

Hopefully, you’re already using transactions.

using Nested Transactions

Scenario one: a Contact application

Let’s say you have a simple structure with 2 tables: customers and contacts. You have a customer input form including a list box of contact records. Double-clicking on a record opens a contact input form with more details for the contact.

Here’s what might happen:

• An end user opens a customer record and modifies the street name.
• Then they open contact ‘A’, modify the birthdate, and close the contact form by clicking an OK button.
• They follow this up by opening contact ‘B’, modifying something, then deciding to cancel their changes.
• Now back to the customer form, the end user clicks an OK button to complete their activities.

 

The user expects that their modification of contact ‘A’ is saved, while the modification for contact ‘B’ is not. The changes to the customer record itself should, of course, also be saved.

With nested transactions, this is easily handled. Simply use one transaction in the customer form, and another transaction in the contact form. No sweat!

Scenario two: automatic batch processing

A completely different scenario – but with the same concept – is automatic batch processing (calculations, importing, etc.):

• In the middle of a complex batch job, you need to stop the process for some reason (e.g., encountered bad data or a user request to cancel/quit).

 

With a single level transaction, you can only cancel all operations – or stop in the middle, resulting in an unclear situation. You may be thinking “I wouldn’t stop in the middle, I’d finish the operation before stopping”. That’s great, but how would you handle power outages, crashes, hardware failures, and other unexpected errors?

Since you can’t … and counting on luck isn’t dependable … the system should be designed to ensure reliable data.

keep in mind…

Transactions give you control over these kinds of situations. Use individual transactions for every operation (importing data, performing calculations, etc.) nested within a top-level transaction (to allow canceling or validating all operations).

If a transaction fails to complete for whatever reason (crash, power outage, etc.), have no fear! Every operation already performed is undone because nothing has been validated by the top-level transaction yet.