Tip #6 – Find locked records

This Blog Article was brought forward from my old blog

Sometimes, when you investigate performance issues you see users blocking each other on small tables with good indexes and small locks.

In that case it can be very interesting to know exactly which record in the table is locked and which records are available.

That is what this tip is about.

Most hardcore NAV developers are familiar with the statement “IF CODEUNIT.RUN THEN…” and most of you might also know that NAV supports Lock timeout.

These principles are the background for this tip.

To be able to implement this you need at least two codeunits available in your license.

The first codeunit has a table parameter. It is the table you want to investigate.

It should contain this code

The second codeunit is a normal codeunit with a bit more code

And these variables

For this to work, we need to change the settings of the database under file -> database -> alter

Change the Timeout Duration to 0 seconds. If you do this in a production system, then temporary turn of the Lock Timout option.

Now let’s lock some records in the table.

And see here the result:

Hopefully this wil also eliminate to of the oldest myths in the Navision on SQL Server world; locking by neighboring and table locking by the LOCKTABLE command.

BTW: You can also make this smarter by implementing this code:

In a SingleInstance codeunit off course.

Good luck