Compatibility settings – Nested Transactions (Part 3)

Welcome to our ongoing series about compatibility settings and “hidden” features for improved performance. In the first post, we looked at the QUERY BY FORMULA command and its impact on an application’s behavior. The second post was about the “Use period and comma as placeholders” compatibility option to avoid running into “the numbers are displayed as >>>>>>>>>” hassle.

In this third installment, we’re going to explore Nested Transactions.

If this option isn’t visible on your Compatibility page (because you’ve created your structure with 4D v11 or later) or it’s already enabled, there’s no need to read further. If this isn’t the case, your application is running in a mode emulating 4D v2004. You’re not only missing out on a lot of great features, you’re also in a risky position since this mode is no longer tested by 4D.

When 4D first introduced transactions 25 years ago, it only supported a single transaction level: operations were either in a transaction or not. However, 4D has been supporting multi-level (nested) transactions for more than 10 years now. You should take advantage of them!

If you’ve never used transactions, I advice you to read this blog post on transactions … otherwise keep reading!

Nested Transaction compatibility flag

So, why is there an option at all? Isn’t it better to always use nested transactions?

Yes, it’s much better. However, your existing code may have been written with only single-level transactions, thus the option.

START TRANSACTION
START TRANSACTION // second call by accident, ignored from 4D v2004
VALIDATE TRANSACTION // v2004 was now at the end, no transaction running anymore
   // v11 still has a transaction open…

Why? What could happen? Glad you asked!! Let’s say an end user quits an application with only single-level transactions, which may still have unconfirmed, non-validated transactions open. They’ll be automatically canceled and all that work could be lost!

Before enabling the nested transaction compatibility flag, you should carefully review your code and make sure that all calls to transactions are balanced. Verify that they’re not only opened, but closed as well. For example, a method called inside an IF condition could start a transaction, but could be hard to find if the code is not well structured and organized.

There are two ways to check this:

1. The first is to do a complete code audit, which could be very time consuming. However, there is another option.
2. If your application uses only one process, it could be very simple. In the On Exit event, check to see if there are any transactions still open. If yes, report it (send yourself an email, call admin, etc.) AND validate the transaction (assuming it was a transaction opened by accident).

Since you’ll most likely have more processes, you’ll need to identify them. If you have a generic method you call in any process when you start or finish it, no worries. If not, you’ll need to find these processes and modify each of them. At the end of each process, check the Transaction level.

At this point, don’t do anything else. Before you start using nested transactions, check your code!

suspending Transactions

As soon you’ve enabled nested transactions, you’ll notice another benefit. You can hit the snooze button!

Let’s say you’re inside a transaction and need to increase a counter stored in another table (e.g., an invoice number). As long as the transaction is open, all modified records are locked so other users or processes can’t modify them. While this may be fine for the invoice itself, it’s not so great for the invoice number counter. In the past, developers tried to work around this issue by starting another process and creating processes to handle communication. That’s a lot of code just to run something outside of a transaction. Fortunately, there’s a better way… as soon as nested transactions are enabled, you can pause them at anytime. This lets you increment the counter and resume the transaction.

This is such a fantastic feature that there’s a whole article in the documentation about it!

Memory

Whether or not you’re using nested transactions, you should be aware that transactions increase the required memory because modified data is stored in a temporary buffer. Not only that, but internally 4D also builds temporary indices which allow queries to be run.

As result, the larger your tables (as more fields are indexed and more records are modified), the more memory is required. If your memory is insufficient, you’ll see temp files created beside your .4DD files (to store the temporary buffer). These files, while solving the memory shortage, could decrease your application’s performance.

So even though transactions are great, it’s not the best idea to manipulate millions of records inside a single one!