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:

 

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.

Thomas Maul
• VP of Strategy, 4D Product Line •When 4D's German subsidiary was created in 1988, Thomas joined the company as a Technical Director, helping to build the 4D developer community in both Germany and Austria. After many years supporting customers with technical problems and being increasingly involved in sales and management issues, he was promoted to Managing Director for 4D Germany in 1999.As a member of the executive board since 2005, he became part of worldwide strategy of the company, leading to his current position as Vice President of Strategy, 4D Product Line, responsible for defining and executing the overall strategy for the 4D product line in relation to the Program, R&D, Sales and Marketing teams.