Concurrency in Business Central: Parallel processes without deadlocks and timeouts

Overview

The session explains how the database engine decides whether a resource can be accessed: lock types, isolation levels, the compatibility matrix, and the intent lock sequence from table to page to record. It then covers lock escalation (about 5,000 locks from one statement), the obsolete LOCKTABLE statement, circular deadlocks and conversion deadlocks. Demos on an on-premise SQL Server use a custom page that reads lock state from the SQL Server lock DMV.

The second half compares ways to update and insert under load. Modify in a loop avoids escalation but took about 2.5 to 3 seconds versus 30 to 50 ms for modify all on 15,000 records. A cinema ticket booking case compares a SEMAPHORE-style lock on the last entry, number sequences, auto increment, locking by entity code, a segmented primary key and a dedicated allocation table. The Q&A covers IsEmpty checks, sequential GUIDs, buffered insert, scale-out caching, optimistic locking and commits.

Key points

• Default lock timeout is 10 seconds on-premise and 35 seconds in SaaS; a session that waits longer is dropped with an error.
• Lock escalation to a table-level lock happens at roughly 5,000 locks from a single SQL statement. The number is approximate because SQL Server counts all lock types.
• SaaS has read committed snapshot isolation on by default, on-premise does not. Concurrency behavior differs, so test in both.
• Modify in a loop avoids escalation but was much slower (2.5 to 3 s vs 30 to 50 ms for 15,000 records). Chunking below the escalation threshold is the proposed compromise and needs tuning.
• Deadlocks are circular (different records, checked by the lock monitor about every 5 seconds) or conversion deadlocks (two repeatable read sessions converting shared locks to exclusive on one record).
• Number sequences do not lock, but adding availability validation on top allowed duplicate bookings. Auto increment turns off insert buffering.
• Locking by entity code can cause table scans that block other entities. A segmented primary key with the entity code first, or a dedicated allocation table, was shown as the fix.

Features

AL objects mentioned

As heard in the captions. A name that matches one object page by exact type and name links to it; the others stay as named.

• table “tr_locks” at 26:09
• table “abstract table” at 26:57
• other “modify all” at 39:28
• other “find set” at 45:23
• other “find first” at 45:23
• other “delete all” at 44:56
• other “modify” at 46:35
• table “entry table” at 56:38
• codeunit “posting routine” at 57:09
• other “check availability function” at 1:05:56
• codeunit “entry” at 1:05:56
• table “cinema allocation” at 1:18:31
• table “cinema layout” at 1:18:31

Not found in BC28-30: table “tr_locks”, table “abstract table”, table “entry table”, codeunit “posting routine”, codeunit “entry”, table “cinema allocation”, table “cinema layout”.

Quotes

• 6:21 “the first session that’s locking a table and holding it can hold it well indefinitely long not indefinitely because it’s limited only by the”
• 7:14 “10 seconds by default on on premise environments and 35 seconds in SAS, right? As the log timeout settings in business central.”
• 9:59 “when we run our code on premise and that’s uh quite an important differentiation on premises with a default uh configuration of SQL server”
• 12:58 “this is the key to understanding the concurrency uh how database engine decides uh if record can be accessed or any uh resource can”
• 13:36 “when the database engine decides if this access concurrent access can be granted uh it basically does something like this”

Disclaimers in the video

• 9:59 other: on premise and that’s uh quite an important differentiation on premises with a default uh configuration of SQL server uh …
• 32:45 other: I’m running this on on on a local uh on premise server with a default configuration the SQL development developer …
• 33:05 other: for the sake of the experiment, enable it
• 34:37 other: It has its own like cave and side effects notably right skew maybe I will show that as well
• 1:02:51 other: first just to one disclaimer about auto increment. We probably already noticed my comments here, right? What’s the downside of of using auto increment?
• 1:13:15 other: it’s not a good idea either and it’s kind of leads to problems that are quite difficult to demonstrate and …
• 1:15:16 other: although it looks like now this solution worked in fact it’s something that in production will have many more problems. …
• 1:27:31 subject-to-change: in BC28 that they changed it on one place uh by not using that anymore if not insert
• 1:35:27 subject-to-change: from version 25 or 26 it will be recommitted in older previous versions will be uh for update update lock. …

Presenters (as heard): Alexander.