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)
.