Navision legacy in AL: RecordLevelLocking

RecordLevelLocking function in AL is a heritage of the C/SIDE times and the Navision native database. In those old days when Navision server offered its own database management system, RecordLevelLocking function used to indicate the database format in the current connection. Since Navision DB was only capable of table-level locks and could not manage more granular locks on data, this function returned true for a SQL Server database and false for Navision DB.

Much time has passed since C/SIDE, and the Navision database format is long gone. RecordlevelLocking function is now considered a relic of old days, not really usable anymore.

But on the other hand, the documentation still states that this function "determines whether the table supports record-level locking." And record-level locking, as well as page-level locking can be disabled in SQL Server for table indexes, including the clustered index.

Both row locks and page locks are always on by default, but these options can be disabled by rebuilding the index with allow_row_locks = off and allow_page_locks = off respectively.

In the NCL library, RecordLevelLocking is a property which is always initialized to true for all database tables, so it doesn't look likely that it respects actual index properties. But out of curiosity, why not run a small test?

For the test, I rebuild the clustered index PK on a custom test table disabling row locks first.

alter index
    [CRONUS International Ltd_$Locking Test$868c690a-88a7-4e8e-b404-bb877afee589$PK] 
on
    [dbo].[CRONUS International Ltd_$Locking Test$868c690a-88a7-4e8e-b404-bb877afee589]
rebuild with (allow_row_locks = off)

In order to test the locking, I run an update query on a single row and capture locks from the sys.dm_tran_locks management view.

update
    [dbo].[CRONUS International Ltd_$Locking Test$868c690a-88a7-4e8e-b404-bb877afee589]
set Description = '12345' where [Entry No_] = 2

With the default locking options, this query would place intent exclusive locks on the table and the page containing the row being modified, and an exclusive lock on the row itself. With allow_row_locks set to off, row rock is skipped and the exclusive lock is acquired on the page instead.

Now I run the RecordLevelLocking function on the AL side.

Message(
    'Record level locking is allowed: ' +
    Format(LockingTest.RecordLevelLocking()));

And it still tells me that record locks are allowed despite the disabled row lock option on the clustered index.

If I go even further and disable page-level locks on the clustered index, the same query will now place an X lock directly on the table without any lower level locks - just like the Navision DB would do.

Still, RecordLevelLocking returns true, even though any locking query will now place locks on the table level.

Function value does not change either if the table is declared as temporary - it is still "true", even though it doesn't make much sense for temporary tables.

This isn't a very practical discovery, I must admit. Row locks and page locks are very unlikely to be disabled, and it's not even possible in SaaS BC instances. Still it confirms the fact which we already knew: RecordLevelLocking is a legacy function that always returns true without considering actual table locking options.