understanding locktable/commit

Hello; I recently tried write-locking a table to prevent other users from changing it and did not observe the behaviour I expected. Even though a part of the program was protected by a locktable/commit pair, another instance of Navision was able to modify the table in question. I expected any call to modify(), after a locktable(True) call has been made, to block in other sessions. Perhaps this only occurs when the owner of the lock has a write in progress. After a lockTable() call has been placed, will all other writers be blocked? Brian Alexander.

The locktable, do stop other users from modifying / Inserting records in the table. However, once you do either a INSERT or MODIFY, you automaticly unlock the table. This also goes for running an object. Once the object is closed, you commit (and Unlock). i.e. If you run a report that do a locktable. the table will be unlocked once the report is completely run, and closes the window. //Henrik Helgesen -: KISS::Keep it Simple, Stupid :-

By the way! I once learned that “You only use LOCKTABLE, if you are Inserting a record, that are dependant on what you read”. That goes for the General Ledger Entries. Standard Navision does a GLEntry.LOCKTABLE, finds the last one, and uses that entry number to increase new G/L Entries Entry Number. //Henrik Helgesen -: KISS::Keep it Simple, Stupid :-

Hi Henrik, As you describe the next statement of INSERT/MODIFY by another user will unlock the table and allow a transaction to take place. Hence, how should we solve this? Thx in advance.

A bit of clarification of the behavior of locking when doing INSERT or MODIFY (or RENAME or DELETE or MODIFYALL or …): Even if you do not issue an explicit LOCKTABLE before an INSERT/MODIFY/… statement, an implicit LOCKTABLE will automatically be send immediately before the modification command. So the table will NOT be unlocked following any of these statements, as Henrik wrote. Once tables are locked by a transaction, they will remain locked until either a COMMIT or an error statement (a rollback). If there are no explicit COMMIT statement in the code, an implicit COMMIT will be send when the execution of C/AL code completes and control is returned to the user interface (there is an exception for the CONFIRM statement). There is another issue which may affect Brian: On Navision Server, a LOCKTABLE will indeed immediately lock the entire table and thus prevent other users from either locking or updating the table. So the scenario Brian describe is not possible on Navision Server. But: On SQL Server, a LOCKTABLE will NOT lock the table (!). Instead, it will cause a change in the way tables are accessed: After the LOCKTABLE all records that are read will be locked. When placing locks on records, the SQL Server may decide to lock larger portions of the table than a record, eg. a page, or it may decide to lock the ranges between records. So the scenario Brian describes is possible only on SQL Server. Rgds - Jens

Thanks for your great explaination, Jens. Well done! If the scenario is happening in SQL environment, is there any solution to it?

Thanks for your great explaination, Jens. Well done! If the scenario is happening in SQL environment, is there any solution to it?

Jens Rainer says that when loctable is used, SQL normally only locks the records that are read before the next commit, but in some instances may lock pages or ranges of records. Is there any way of getting SQL (via Navision) to only lock a single record per read/‘find()’ and to stop it from locking ranges or pages?