What are tablelocks?
Tablelocks = locking tables...
...are nothing bad to begin with. Every database uses them to ensure the integrity of data.
An example: User A has a customer master record on the screen and now changes the street. This process is now saved back in the database so that the new street is permanently saved. User B is currently editing the same data record and wants to change the telephone number. Both actions can actually be carried out independently of each other.
However, since the change made by user A always writes back the entire data record and the telephone number change made by user B also writes back the entire customer record (incl. name, address, street, new telephone number, credit limit...), the following can happen in this case: User A and user B receive the same record with the old street address and the old telephone number on the screen.
User A now only changes the street (no telephone number) saves this record. User B has not noticed this. He still has the unchanged data record with the old street on the screen and now changes the telephone number. Now he also saves the data.
What is the consequence? Its change now transfers the new phone number.... together with the old street to the database. The correction of A is lost.
How can you prevent such an everyday situation? You work with table locks: User A receives the data record on the screen. At this moment, Navision or Business Central locks the data record for changes. It is "locked". Not to be confused with an access log, then it is "logged" (from the term "Logbook").
User B cannot display this data record at all. Only when A is finished, e.g. with viewing or changing the street, does B receive the record on the screen, which has now been released again. Now the game starts all over again.
What causes tablelocks?
Fortunately, Business Central and Navision are much smarter than many database systems, which actually do this as described above. Navision uses optimistic concurrency: in the vast majority of cases, a data record is only displayed and not changed.
And what if an address is changed? Statistically, it is very unlikely that the same data record will be changed on another computer is changed at the same time on another computer. For this reason, Navision does not delete this data record under any circumstances, but uses the time stamp to check whether the data record in the database is still the same version as the data record that has just been saved for modification.
This is done via the invisible Timestamp field in each record of a Business Central & Navision database. Therefore, you cannot create a field called TimeStamp yourself. "In the past" this was still possible, a popular "gray hair" error when transferring a native database to SQL.
If Navision does not make any locks at all, what is the problem? The above example is one of hundreds in which the developers of Navision have relieved the user of such work. But there are also real cases where the database must be sure that it is in a consistent state.
Writing hurts...
A new example: a user books an order. This can take 1-2 seconds. During this time, G/L items are written, posted delivery bills and posted invoices, open customer items...
At the same time, another user could want to delete this order or even this customer. If this were permitted, delivery bills, open items and invoices would end up existing for a customer that no longer existed at that time. Depending on the time shift, there would not even have been any items on the customer that would have prohibited deletion!
Business Central and Navision put a lot more effort into avoiding this, but it's enough for our example.
Navision would now block ("lock") the order and the customer when posting in order to prevent exactly this situation. This is done differently in real life, but that is not so important here.
So now there are some tables that are locked ("open for writing") for a short time: Orders, order lines, posted delivery note headers, posted delivery note lines... and a few more.
If not so many people are working on the system, this is not a problem at all: after all, these tables are free again just one or two seconds later. (Not for you? Does it take you many seconds or even minutes to post an order? That's bad! This can certainly be accelerated!)
So still no problem? Exactly. Unless... such transactions take too long, or lock tables in different sequences. This is exactly why deadlocks occur.
Deadlocks... the view of the gates of hell
Deadlocks: Suppose a program wants to block customers in order to enter orders for them afterwards. First BUSINESS CENTRAL or NAVISION blocks the customers, then... there is a moment's delay. During this time, another program starts, which blocks the orders and, if necessary, makes data changes in the customer.
Now the first program comes to the point where it also wants to block the orders for its customers. This is not possible because the second program has already blocked the orders for itself. So the first program simply waits for it to be able to block the orders as well. It really waits! The second program now comes to the point where it would like to change the customers, i.e. block them too. It requests the customer block from the database... but they are already blocked!
Now there is a deadlock: Both programs are waiting for the other table locked by the other program. The lock is - in principle - infinite, as both programs are waiting for the other table from the first program to be released. This situation is known as a "deadlock": it is never released by itself.
The SQL server recognizes this and terminates one of the two queries so that the other can continue working.
Rule of thumb: The worse the program code, the longer the program processing and the more likely deadlocks are. Simply because the write locks are active for too long. Performance optimization is therefore also always deadlock avoidance!
Finding tablelocks/deadlocks
In Navision or Business Central, a resolved deadlock is only displayed with a simple error message:
"The process could not be completed because a data record in the "xxx" table was locked by another user" is displayed. Execute the action again. This message always appears on the computer that has lost the deadlock.
Occasionally, however, deadlocks are distributed across several programs so that even the (SQL) server itself can no longer determine that it is a deadlock. This is where the maximum SQL execution time comes into play, for example.
Until Navision 2009R2, you could simply look in the session table to see which process was blocking the other. With a quick glance at the right screen, you could quickly localize the problem... or start an extensive search.
Since Navision & Business Central 2013 RTC, however, Microsoft's policy of making everything more complicated and more difficult wherever possible has taken effect. Here you have to work directly on the SQL server. Here are a few methods for analyzing:
It always starts in the SQL server in the current database with a query
Listing of blocking users
exec sp_who2
SQL lists all sessions. A blocked session shows in the "Blkby" column whether/by which session it is blocked.
As a rule, you will find the same session ID in several sessions that are all waiting for the same blocking session.
If desired, you can go one level deeper here with the objects that are blocked. The resulting list is much clearer, but perhaps not sufficient. Especially if you have taken great care to only write to temporary tables before a Page.runmodal, and a nasty error message still points to a locked table, you will have a hard time finding out which table(s) are now locked? This often helps you to get on the right track.
Microsoft Dynamics 365 Business Central
Content of the error message: The C/AL functions listed below are restricted during write transactions because at least one table is locked. Form.RunModal is not allowed in write transactions. Codeunit.Run is only permitted in write transactions if the return value is not used. OK := Codeunit.Run() is not permitted, for example. Report.RunModal is only permitted in write transactions if RequestForm = FALSE. Report.RunModal(...,FALSE) is permissible, for example. XmlPort.RunModal is only permitted in write transactions if RequestForm = FALSE. XmlPort.RunModal(...,FALSE) is permissible, for example. Use the COMMIT function to save the changes before the call, or structure the code differently.
-- List all Locks of the Current Database Source: https://stackoverflow.com/questions/694581/how-to-check-which-locks-are-held-on-a-table
SELECT TL.resource_type AS ResType
,TL.resource_description AS ResDescr
,TL.request_mode AS ReqMode
,TL.request_type AS ReqType
,TL.request_status AS ReqStatus
,TL.request_owner_type AS ReqOwnerType
,TAT.[name] AS TransName
,TAT.transaction_begin_time AS TransBegin
,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura
,ES.session_id AS S_Id
,ES.login_name AS LoginName
,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName
,PARIDX.name AS IndexName
,ES.host_name AS HostName
,ES.program_name AS ProgramName
FROM sys.dm_tran_locks AS TL
INNER JOIN sys.dm_exec_sessions AS ES
ON TL.request_session_id = ES.session_id
LEFT JOIN sys.dm_tran_active_transactions AS TAT
ON TL.request_owner_id = TAT.transaction_id
AND TL.request_owner_type = 'TRANSACTION'
LEFT JOIN sys.objects AS OBJ
ON TL.resource_associated_entity_id = OBJ.object_id
AND TL.resource_type = 'OBJECT'
LEFT JOIN sys.partitions AS PAR
ON TL.resource_associated_entity_id = PAR.hobt_id
AND TL.resource_type IN ('PAGE', 'KEY', 'RID', 'HOBT')
LEFT JOIN sys.objects AS PAROBJ
ON PAR.object_id = PAROBJ.object_id
LEFT JOIN sys.indexes AS PARIDX
ON PAR.object_id = PARIDX.object_id
AND PAR.index_id = PARIDX.index_id
WHERE TL.resource_database_id = DB_ID()
AND ES.session_id <> @@Spid -- Exclude "my" session
-- optional filter
AND TL.request_mode <> 'S' -- Exclude simple shared locks
ORDER BY TL.resource_type
,TL.request_mode
,TL.request_type
,TL.request_status
,ObjectName
,ES.login_name;
--TSQL commands
SELECT
db_name(rsc_dbid) AS 'DATABASE_NAME',
case rsc_type when 1 then 'null'
when 2 then 'DATABASE'
WHEN 3 THEN 'FILE'
WHEN 4 THEN 'INDEX'
WHEN 5 THEN 'TABLE'
WHEN 6 THEN 'PAGE'
WHEN 7 THEN 'KEY'
WHEN 8 THEN 'EXTEND'
WHEN 9 THEN 'RID ( ROW ID)'
WHEN 10 THEN 'APPLICATION' end AS 'REQUEST_TYPE',
CASE req_ownertype WHEN 1 THEN 'TRANSACTION'
WHEN 2 THEN 'CURSOR'
WHEN 3 THEN 'SESSION'
WHEN 4 THEN 'ExSESSION' END AS 'REQUEST_OWNERTYPE',
OBJECT_NAME(rsc_objid ,rsc_dbid) AS 'OBJECT_NAME',
PROCESS.HOSTNAME ,
PROCESS.program_name ,
PROCESS.nt_domain ,
PROCESS.nt_username ,
PROCESS.program_name ,
SQLTEXT.text
FROM sys.syslockinfo LOCK JOIN
sys.sysprocesses PROCESS
ON LOCK.req_spid = PROCESS.spid
CROSS APPLY sys.dm_exec_sql_text(PROCESS.SQL_HANDLE) SQLTEXT
where 1=1
and db_name(rsc_dbid) = db_name()
--Lock on a specific object
SELECT *
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
AND resource_associated_entity_id = object_id('Specific Table');Database load
Since deadlocks practically always go hand in hand with excessively long program runtimes, and these are almost always caused by ill-considered database queries, it is always useful to take a look at the database load:
select spid,cmd,waittime,lastwaittype,cpu,physical_io,login_time,last_batch,status,hostname,program_name,nt_username, nt_domain
from master.dbo.sysprocesses where dbid = db_id(‚desireddatabase‘)
or
SELECT DISTINCT
name AS database_name,
session_id,
host_name,
login_time,
login_name,
reads,
writes
FROM sys.dm_exec_sessions
LEFT OUTER JOIN sys.dm_tran_locks ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE resource_type <> ‚DATABASE‘
–AND request_mode LIKE ‚%X%‘
–AND name =’TheInterestingDatabase‘
ORDER BY name
While reads and writes in the 5-digit range are usually not worthy of attention, you should take a look at 6-digit accesses, and the alarm bells should ring at 7-digit accesses... depending on the previous runtime of the respective process, of course.
1.5 million write accesses for an EDIfact Connector that has been online for 12 days is a different number than 500,000 read accesses for a user who logged in 5 minutes ago. Performance Troubleshooting takes a lot of experience and patience.
In Business Central & Navision Debugger from 2017
Locking monitoring has already been built into the debugger here! However, Navision or Business Central requires changes in the SQL server so that it also releases the necessary data.
If Business Central or Navision itself has installed the server, these settings have already been made!
Make the adjustments in SQL_Server, if not already done:
In MSSMS Server/Property -> Authorizations, for the login under which the Navision service is running:
Change any event session: Grant.
Display server status: Grant.
Assign this user to the desired database as owner via Server/Security/Login.
Activate deadlock monitoring in the Business Central and Navision settings:
The activity monitor can also be helpful for performance troubleshooting. It can be accessed via the MSSMS:
English 
Deutsch 
Español