A long time ago (in a galaxy far away) I published some blog posts with a deeper overview of the Business Central's ReadIsolation property and its relation to SQL Server transaction isolation levels. I left a few open ends at that time, promising to return to this matter later, but never had a chance to write on this topic again. The main open question left unanswered is the one that I highlighted when writing about the Read Committed Snapshot isolation and the Write Skew anomaly: how can we be sure that Business Central application is not susceptible to this anomaly and what design features allow BC to avoid this trap. I finally decided that this post is long due, but when I started writing, I found that before delving into these design details, I need to clarify a few underlying SQL concepts. These concepts are lock timeouts, deadlocks, and serializable transactions.
So here comes the next post in the "SQL Server for Business Central" series, exploring the topic of lock timeouts and deadlocks.
I also updated my "locking demo tool" which I used in previous posts to illustrate the processes. Now this tool shows more information from the locking sessions and implements two deadlock scenarios described below, so give it a try.
I find it important to pay more attention to deadlocks because, despite being a scary buzzword, it is often misunderstood, and we need to differentiate different types of locking issues. Sometimes I hear developers and consultants saying that a session was deadlocked when they see the message probably we all know so well "We can't save your changes right now, because a record in table 'abc' is being updated in a transaction done by another session."
This message is an example of a timed out lock request and usually this is not a deadlocked transaction (unless the lock timeout in your environment is set below two seconds). What is the difference? Let's start from defining a lock timeout.
If you are reading this blog, most likely you know that SQL Server uses the concept of a lock to ensure data consistency during read and write operations. Transaction locking and row versioning guide is a comprehensive deep dive into SQL Server's transaction architecture. And a short definition of a lock can be fond in another Microsoft Learn article. To put it simply, a lock is a flag that tells other concurrent transactions that a session is performing some operations on the locked object. Locks can be compatible, meaning that multiple transactions can acquire locks on the same object simultaneously, or incompatible. If a session requests a lock on an object which already has an incompatible lock placed on it, the lock request has to wait until the active transaction releases its lock. Some locks are held until the end of the transaction, whilst others have much shorter life span.
Lock timeout happens if the wait time of a lock request exceeds the configured timeout threshold.
Lock timeout can be anything between zero (and then no lock can happen - any lock request will timeout immediately) and infinity (in this case the transaction requesting a lock can wait indefinitely). It is important to note that, even if the locking transaction is stuck in an infinite loop and the second lock request is doomed to wait forever with no lock timeout defined, this situation still does not qualify as a deadlock, because from the external observer's point of view (halting problem aside), transaction B is waiting for transaction A to release the lock, and theoretically, sooner or later this request can be granted.
What distinguishes a deadlock situation is that this lock can never be resolved without interrupting one of the locking transactions, no matter how long we wait. This lock is impossible to resolve in principle. One classic example of a deadlock is two transactions attempting to lock two records, but in reverse order.
In this scenario, Transaction A holds an update lock on Record 1, while Transaction B holds an update lock on Record 2. If Transaction A requests a lock on Record 2, it has to wait for Transaction B to release its lock. If at the same time, Transaction B attempts to lock record 1, a deadlock situation occurs, because both transactions would wait forever for each other to release their locks.
From AL perspective, this could mean two sessions calling LockTable on the same set of tables, but locking the tables in different order.
Session 1 could lock a record in the Customer table, followed by the Item table.
Customer.LockTable();
Customer.Get('10000');
...
Item.LockTable();
Item.Get('1000');Whilst the other session retrieves the same records with locks in reverse order:
Item.LockTable();
Item.Get('1000');
...
Customer.LockTable();
Customer.Get('10000');Instead of LockTable, this can be any other type of locking query - FindSet(true) or ReadIsolation::UpdLock. In all of these cases, the SQL query is issued with the UPDLOCK hint and the following scenario takes place.
Transaction A places an update lock on record 1.
Transaction B places an update lock on record 2.
Transaction A requests an update lock on record 2. Since two update locks are incompatible, it has to wait for the active lock to be released.
Transaction B requests an update lock on record 1. Similar to the Transaction A, this lock request cannot be granted, and both transactions end up in a deadlock waiting for each other to complete.
The first of the two scenarios added to the lock demo tool demonstrates this type of deadlock collision. Session 1 here cannot be completed:
The message we see clearly informs the user that the transaction was caught in a deadlock:
The activity was deadlocked with another user who was modifying the Locking Test table. Please retry the activity.
After the SQL Server lock monitor drops the first session and releases its locked resources, the second session can continue and place the requested lock.
General recommendation to prevent this kind of locking issue is well known: the locking order for all resources should be well defined and consistent throughout the application. If both sessions follow the same order and request the locks on, let's say, Customer table first, with Item coming second, no deadlock can occur in this scenario.
But this is not the only case of a potentially deadlocking code. Deadlock can happen even on a single record if two transactions place a shared lock on a record and later both attempt to modify this record converting the lock from shared to exclusive.
Respective AL code that can cause this type of deadlock reads a record with RepeatableRead isolation level and later attempts to modify (or delete) this record.
Customer.ReadIsolation := Customer.ReadIsolation::RepeatableRead;
Customer.Get('10000');
// Do some processing here...
Customer.Name := 'New customer name';
Customer.Modify();If both transactions A and B run the same code above simultaneously, there is a chance these transactions can deadlock. Let's see what leads to the deadlock in this scenario.
SQL query executed with the repeatable read isolation level places shared locks on all retrieved data for the duration of the transaction, whilst the update operation needs an exclusive lock to modify the record. So the following sequence takes place:
Transaction A reads the customer record placing a shared lock.
Transaction B reads the same customer record placing another shared lock. Shared locks are compatible, so multiple transactions can hold this type of lock on one record at the same time.
Transaction A attempts to modify the record and requests an exclusive lock to complete the update. X lock is incompatible with the S lock held by Transaction B, so the lock request is not granted and has to wait.
Transaction B, in its turn, attempts to modify the same customer record, requesting an exclusive lock, and similarly, has to wait for the Transaction A to release its shared lock.
Now both transactions are waiting for each other to release their S locks to be able to covert their own S lock to X lock and update. Deadlock.
The locking demo tool scenario looks as follows.
Session 1 in this case was completed successfully, reading and modifying a table record.
But this time it is the session 2 that was selected to be the deadlock victim and killed by lock monitor.
The solution in this situation is to use another isolation level from the start of the transaction if the data is intended to be modified.
Without diving too deep into different isolation levels (although, you can read about basic concepts in my earlier posts here and here), if you have doubts which isolation level to use, it may be a good idea to start from exploring the tri-state locking feature of Business Central.