Join me for another Explain it like I’m 5 video. This time, about database transactions in AL. Check the video if you’re new to AL and confused about transactions:
In this video, Erik breaks down database transactions in AL and SQL for Business Central — explaining how they differ from traditional database programming, when they start and end, and why you usually don’t need to worry about them. He demonstrates the concepts with live code examples and covers the important topic of when (and when not) to use Commit.
Transactions in Traditional Programming vs. Business Central
If you’re coming from other technologies, you’re probably used to explicitly managing database transactions. In most languages and SQL environments, the pattern looks something like this in pseudo code:
BEGIN TRANSACTION
-- Write invoice
-- Do other database work
IF error THEN
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
ENDYou explicitly tell the database: “We’re starting now,” “We’re done now,” and “If something went wrong, undo everything.” You encapsulate the start and end of your transaction, and you handle rollbacks yourself.
In Business Central’s AL language, none of this exists. There is no BEGIN TRANSACTION command, no explicit END TRANSACTION, and no manual ROLLBACK. The platform handles all of this for you — which is both a blessing and a source of confusion for newcomers.
When Does a Transaction Start in AL?
A transaction in AL doesn’t begin when you declare variables or assign field values. It begins the moment you actually interact with the database. Consider this code:
pageextension 50100 CustomerListExt extends "Customer List"
{
trigger OnOpenPage();
var
i: INteger;
SH: Record "Sales Header";
begin
Clear(SH);
SH."Document Type" := SH."Document Type"::Quote;
SH.Insert(true);
end;
trigger OnPageBackgroundTaskCompleted(TaskId: Integer; Results: Dictionary of [Text, Text])
}In this example, declaring the SH record variable and assigning the "Document Type" field value does nothing to the database. The transaction only begins at SH.Insert(true) — the moment you actually write to the database.
Proving the Transaction Exists
Erik demonstrates this by adding a Page.Run call after the insert. Certain operations — like opening a page modally — are not allowed while a write transaction is active. If you try to run a page after an insert without committing first, you’ll get an error like:
“An error occurred and the transaction is stopped. Contact your administrator or partner for further assistance.”
This error message has been reworded many times over the decades, but it ultimately means: you’re trying to do something (like open a page) that isn’t allowed inside an active write transaction.
However, if you insert a Commit between the database write and the Page.Run, the transaction ends, and the page opens successfully.
Tip: If you’re seeing cryptic “try function” errors in your debugger, go into your launch.json and change "breakOnError" from "All" to "ExcludeTry" to filter out the noise.
When Does a Transaction End?
If you don’t explicitly call Commit, the transaction ends when the scope of the current trigger completes. Business Central automatically issues a commit when a trigger goes out of scope. So when the OnOpenPage trigger finishes executing, the platform commits whatever database changes were made during that trigger.
Once the trigger is done and the user is looking at the page with a blinking cursor, you are typically not inside a transaction.
Rollbacks: Errors Undo Everything
If an error occurs during a transaction — whether it’s an unhandled runtime error or an explicit Error call — the platform automatically rolls back all database changes made during that transaction. You don’t need to write rollback code yourself.
Erik demonstrates this by inserting two sales quotes and then triggering a division-by-zero error:
Clear(SH);
SH."Document Type" := SH."Document Type"::Quote;
SH.Insert(true);
Clear(SH);
SH."Document Type" := SH."Document Type"::Quote;
SH.Insert(true);
i := i / i; // i is 0, so this causes "Attempt to divide by zero"
Clear(SH);
SH."Document Type" := SH."Document Type"::Quote;
SH.Insert(true);The first two inserts never make it to the database because the error triggers a rollback. And of course, anything after the error statement never executes at all.
Calling Error('something') explicitly has the same rollback effect. This is one of the elegant aspects of AL’s transaction model — you don’t have to write IF error THEN ROLLBACK logic.
The Commit Paradox: When Should You Use It?
Erik shares a classic joke about Commit usage that rings surprisingly true:
- The beginner: Never uses
Commit - The intermediate developer: Tries to control everything with
Commit - The guru: Never uses
Commit
In most cases, you simply don’t need Commit. Business Central handles transactions for you. But there are valid scenarios where it’s necessary:
1. When You Need to Open a Page or Run a Report
If your code needs to open a page or run a report after writing to the database, you must commit first — these UI operations aren’t allowed inside a write transaction. That said, this can sometimes be an opportunity to refactor your code so the commit isn’t needed.
2. When Synchronizing with External Systems
This is perhaps the most compelling use case. Erik shares an example from a SharePoint connector app where code creates a folder on SharePoint and then writes a mapping record to Business Central. If a subsequent error rolls back the mapping record, the folder on SharePoint still exists — the rollback doesn’t “un-create” the external resource.
In these situations, you commit after the external call and the corresponding local record write, ensuring your Business Central data accurately reflects the state of the external system. In more sophisticated scenarios, you might set a status flag on the record so you can handle cleanup if something goes wrong later.
3. When Processing Large Volumes of Data
If you’re looping through a massive dataset — say, creating thousands or millions of records — building up one enormous transaction can lead to significant slowdowns on the server side. In cases where individual records don’t need to be all-or-nothing, you can commit periodically (for example, every 1,000 records) to keep the transaction size manageable.
Read-Only Contexts and Background Tasks
Not all AL code runs in a write-capable context. Page background tasks, for example, are read-only. You cannot write to the database inside a background task. If you need to perform a write operation based on background task results, you do it in the OnPageBackgroundTaskCompleted trigger, which runs in a normal transaction context:
trigger OnPageBackgroundTaskCompleted(TaskId: Integer; Results: Dictionary of [Text, Text])There are also certain system events that run outside a transactional context, and attempting to write to the database in those triggers will result in an error.
Checking Transaction State
AL provides the InWriteTransaction function, which returns whether you’re currently inside a write transaction. This is particularly useful when your code is triggered by event subscriptions — where you may not know or control the calling context. You might want to behave differently depending on whether a transaction is already active.
Summary
The transaction model in Business Central’s AL is designed to keep things simple for developers:
- No explicit begin/end: Transactions start automatically when you first write to the database and end when the trigger scope completes.
- Automatic rollback: Any error — runtime or explicit — rolls back all uncommitted changes.
- Automatic commit: The platform commits when a trigger finishes successfully.
- Use
Commitsparingly: Only when you need to synchronize with external systems, perform UI operations mid-code, or manage large transaction sizes. - Be aware of read-only contexts: Page background tasks and certain system events don’t allow database writes.
In the vast majority of cases, you write your AL code and let Business Central handle the transaction lifecycle. It’s one of the platform’s most developer-friendly design decisions — once you understand it.
