Tri-State Locking and Isolation Levels: Reducing Locking in the Runtime

Locking is essential for preventing conflicts, but too much of it can drag performance down. This post dives into how Microsoft is tackling this challenge with tri-state locking and isolation levels to reduce unnecessary locking in the runtime.

Why Does Locking Matter?

If you’ve ever worked with databases, you know locks are like traffic signals—they maintain order. In business applications, locks ensure data consistency and prevent messy write collisions.

However, too many locks—or poorly managed ones—can cause traffic jams. Nobody likes waiting, whether in rush hour or during a system timeout. 😁

Some examples when table will be locked on SQL level:

LockTable

But this one is not really what it says it is since it’s not locking the table. It specifies that all reads against that table in the remainder of the transaction will happen with UPDLOCK hints.

Lock on a different variable

LockTable is not connected to the variable. It is active on a table until transaction end.

Read after write

If there is a write operation on a table any read will lock records.

Count after write

Findset

If no rows are returned the entire table is locked for inserts until the transaction ends.

Catching errors on insert

Even if the insert fails we will still be in a write transaction.

FlowFields

Every day, as per Microsoft information, the system experiences roughly 50,000 lock timeouts across over 6,000 tenants. Most of these happen when applications use locking excessively, sometimes unintentionally. The heavier the traffic, the bigger the problem. So, finding ways to make things “flow” more smoothly is a big deal.

Understanding Locking and Isolation Levels

The Basics of Locking

In the AL runtime, locks help maintain concurrency control and isolation between sessions. Normally, when multiple processes or users access the same data, locks prevent conflicts.

But not all locks are equal. For instance:

• Update locks: Used when a process intends to modify data. These locks prevent others from making changes until the initial process is done.
• Shared locks: Allow other processes to read but not write to the data.
• Exclusive locks: Block all access until the lock is released.

Although helpful, locks can get in the way when overused. Imagine blocking off entire blocks of a city when you just need to do renovations on a single house. It’s overkill, and that’s where optimization comes in.

Two-State Locking: The Old Default

Before tri-state locking, two-state locking was the standard in the AL runtime. Here’s how it worked:

• Stage 1: As long as no writes or lock table calls were made, reads used the “read uncommitted” isolation level. This allowed fast reads with no locks applied. However, it came with risks—like dirty reads, where data from uncommitted changes could be accessed.
• Stage 2: Once a write or lock table was triggered, all subsequent reads used an update lock. This ensured data integrity but often caused unnecessary blocking between processes.

Two-state locking worked, but it leaned toward pessimism. Locking reads too aggressively could bring down performance in high-traffic environments, especially on shared tables. It will be still possible to activate it until BC26.

Tri-State Locking

Tri-state locking changes the game by adding another layer of flexibility. It’s like going from black-and-white thinking (lock or no lock) to shades of gray.

The new approach splits the second state into two parts:

• Stage 1: Reads continue to use the read-uncommitted isolation level as long as no writes or lock table calls are made.
• Stage 2: If writes happen, reads upgrade to “read committed” instead of jumping straight to update locks. This allows safer reads without blocking other processes unnecessarily.
• Stage 3: For scenarios requiring strict consistency, explicit lock table calls still apply update locks. No change here for those who rely on the old behavior.

This added flexibility means fewer blocks and smoother operations, especially in high-traffic environments.

Why Read Committed?

Read committed is like keeping things clean without going overboard. It prevents dirty reads (data from uncommitted changes isn’t accessible) but doesn’t block every interaction. It uses shared locks, which allow others to read but not modify data until the transaction is done. This allows more processes to run without stepping on each other.

One key consideration: whether your SQL Server uses read-committed snapshot isolation (RCSI). If enabled, the system relies on row versioning instead of locks, which can further minimize conflicts.

Two sessions trying to access the same data rows at the same time:

How Tri-State Locking Reduces Lock Timeouts

Microsoft’s data showed that about half of lock-related issues came from reads, not writes. This surprised many, as people assume locking issues mainly stem from updating or inserting data.

By adopting tri-state locking, reads with the default “read committed” level become compatible with each other. That means two people can read the same data without blocking one another—a major win for system concurrency.

For example:

• Under two-state locking, if someone reads with an update lock and another person tries to read the same data, the second process is blocked.
• With tri-state locking, two reads using read committed can now run side-by-side.

The result? Higher concurrency, fewer lock timeouts, and better system performance.

How to Enable Tri-State Locking

Tri-state locking is rolling out in phases:

• New tenants (v23 or later): Enabled by default.
• Existing tenants: Must opt-in through Feature Management starting in v25.
• Mandatory adoption: From v26 onward, tri-state locking will be the standard, and older methods will no longer be available.

To enable it now, go to Feature Management and search for “Enable Tri-State Locking in AL.” Turn it on for all users and test it in a sandbox first. This lets you see how it impacts your setup before full adoption.

Additional Tools: Read Isolation

Alongside tri-state locking, Microsoft introduced read isolation in version 22. This lets developers control isolation levels at a more granular level—per record instance, not just per table.

For example, you can temporarily increase or decrease the isolation level for specific reads. Need more consistency? Use update locks. Want fewer locks? Use read uncommitted or committed. The flexibility helps fine-tune performance without impacting the entire table.

Isolation Levels

More about it on Microsoft Learn:

Record instance isolation level – Business Central | Microsoft Learn

Practical Tip: Temporary Locking Patterns

A practical way to use read isolation effectively is what Microsoft calls “temporary heightening.” Instead of using lock table broadly, you can apply stricter isolation (like update locks) only where necessary. Once the critical read is done, the isolation level returns to normal. This pattern allows you to balance performance and data integrity.

Key Takeaways

Tri-state locking and read isolation are powerful tools for reducing unnecessary locking in the AL runtime. Here’s what you should remember:

1. Tri-state locking adds a new middle state, allowing reads under write scenarios to use read committed.
2. This change reduces blocking, improves concurrency, and shortens lock durations.
3. Read isolation gives you more control, letting you pick the right isolation level for specific scenarios.
4. Plan now—tri-state locking becomes mandatory in version 26.

Try enabling these features in your sandbox environment. Test it thoroughly, analyze the impact, and prepare for the transition.

Conclusion

Tri-state locking and isolation levels offer a smarter way to handle database locks. By balancing performance and consistency, they reduce bottlenecks without compromising data integrity. With this change coming to all tenants soon, now’s the time to experiment, test, and optimize your workflows. Ready to unlock better performance? Give it a try today.

More resources:

Tri-state locking – BC Internals

Tri-state locking in database – Business Central | Microsoft Learn

Record instance isolation level – Business Central | Microsoft Learn

Tech Days 2024 Microsoft video about Tri-state locking: