Hi, Readers.
Today, I would like to briefly talk about how to view Database Locks in Business Central. I ran into this problem recently when I was testing standard events, so I wanted to share it in the hope that it will help you.
Database locking controls access by multiple users to the same data at the same time. To protect a transaction against other transactions modifying the same data, the first transaction puts a lock on the data. The lock remains until the transaction’s done.
Users may be blocked from completing transactions on the locked data. They’ll typically get a message that indicates the lock condition.
We could not update the data right now.
Please try again later.
Technical details:
A record in table Interaction Log Entry was locked for use in another session. Administrators can see current locks on the Database Locks page or use telemetry. (Snapshot ID: 8c2cede2-7055-4088-a4c6-afcfb280288d)
In Business Central, the following situations generally cause the lock table.
1. Using Record.LockTable([Boolean] [, Boolean]) Method: Starts locking on a table to protect it from write transactions that conflict with each other.
This is frequently used in the base application:
2. The [ForUpdate] parameter in Record.FindSet() Method is set to true.
PS: Record.FindFirst() Method and Record.FindLast() Method cannot lock the table.
3. Two users modify the same record (line) at the same time. (This is very rare)
So if database locks occur, is there any feature to see the details in Cloud (SaaS)?
Yes, there is a new feature in Dynamics 365 Business Central 2020 release wave 1 (BC16).
To view database locks:
Choose the search icon, enter Database Locks, and then choose the related link.
The Database Locks page gives snapshot of all current database locks.
This page shows a snapshot of all database locks. Where possible, it displays details on the AL session that is causing the SQL lock.
More details: Viewing Database Locks
Please note that this feature only allows you to view Database Locks, not to unlock the table. So what should I do when database locks occurs?
1. Just waiting.
Database Locks is usually due to long transaction processing time, and the table will be automatically unlocked when the processing is finished.
And the contextual time-out for a SQL command is set to 30 minutes in the cloud by default, an error will be prompted if it exceeds 30 minutes. So waiting for the table to be unlocked is the most straightforward way.
An unexpected error occurred after a database command was cancelled.
Database limits:
The remaining two methods I actually do not want to recommend, for reference only.
2. Canceling a session in Admin Center
Canceling a session is sometimes the only way to unblock a customer. For example, a long-running report is locking data in a table, preventing warehouse employees from working.
To cancel a session, select it from the list and then select Cancel selected sessions.
More details: Managing Sessions in the Admin Center
3. Restarting environment in Admin Center
In some circumstances, as a troubleshooting step, administrators might have to restart a particular Business Central environment to resume its normal operation.
In conclusion, although there are various other factors that affect performance and cause Database Locks, Database Locks are usually caused by bad code and bad logic, such as not considering using temporary tables when inserting large amounts of data, a long-running report, multi-layer loop, etc. This can be done by optimizing code and processes.
END
Hope this will help.
Thanks for reading.
ZHU
コメント