This topic explains how database locking works in Microsoft Dynamics NAV with Microsoft SQL Server.

Locking in SQL Server

When data is read without locking, you get the latest (possibly uncommitted) data from the database. If you call Rec.LOCKTABLE, nothing happens. However, when data is read from the table after LOCKTABLE has been called, the data is locked.

If you call INSERT, MODIFY, or DELETE, the specified record is locked immediately. This means that two transactions, which either insert, modify, or delete separate records in the same table do not conflict. Furthermore, locks are also placed when data is read from the table after the modifying function has been called.

SumIndexFields are maintained when INSERT, MODIFY, or DELETE is called. SQL Server places locks on the records to be updated in the underlying Indexed View. For example, if the application contains a SIFT index on a key consisting of only 'AccountNo' then only one user at the time will be able to modify records on a given AccountNo.

Even though SQL Server initially puts locks on single records, it can also choose to escalate a single record lock to a table lock. This will happen if the overall performance can be improved by not having to set locks on individual records. The improvement in performance must outweigh the loss in concurrency that this excessive locking causes.

If you specify what record to read, for example, by calling Rec.GET, that record is locked. This means that two transactions, which read specific, but separate records in a table does not cause conflicts.

If you browse a record set (read sequentially through a set of records), for example, by calling Rec.FIND('-') or Rec.NEXT, the record set (including the empty space) is locked as you browse through it. This means that two transactions, which just read separate sets of records in a table, will cause a conflict if there are no records between these two record sets. When locks are placed on a record set, other transactions cannot put locks on any record within the set.

Note that C/SIDE determines how many records to retrieve from the server when you ask for the first or the next record within a set. C/SIDE then handles subsequent reads with no additional effort, and fewer calls to the server give better performance. In addition set when you browse.

Microsoft Dynamics NAV with Microsoft SQL Server only supports the default values for the parameters of the LOCKTABLE function – LOCKTABLE(TRUE,FALSE).

See Also