1. Classic Database Server
2. The SQL Server Option
Both Server Options
Locking
In the beginning of a transaction, the data that you read is not locked. This means that reading data does not conflict with transactions that modify the same data. If you want to ensure that you are reading the latest data from a table, you must lock the table before you read it.
Locking Single Records
Usually, you must not lock a record before you read it even though you may want to modify or delete it afterward. When you try to modify or delete the record, you get an error message if another transaction has modified or deleted the record. You receive this error message because C/SIDE checks the timestamp that it keeps on all the records in a database and detects that the timestamp on the copy you have read is different from the timestamp on the modified record in the database.
Locking Record Sets
Usually, you lock a table before reading a set of records in that table if you want to read these records again to modify or delete them. You must lock the table to ensure that another transaction does not modify these records.
You will not receive an error message if you do not lock the table even though the records have been modified as a result of other transactions being carried out while you were reading them.
Minimizing Deadlocks
To minimize the amount of deadlocks that occur, you must lock records and tables in the same order for all transactions. You can also try to locate areas where you lock more records and tables than you actually need, and then diminish the extent of these locks or remove them completely. This can prevent conflicts from occurring that involve these records and tables.
If this does not prevent deadlocks, you can, lock more records and tables to prevent transactions from running concurrently.
If you cannot prevent the occurrence of deadlocks by programming, you must run the deadlocking transactions separately.
Locking in Microsoft SQL Server
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).
Locking in Classic Database Server
Data that is not locked is read from the same snapshot (version) of the database. If you call a modifying function (for example, INSERT, MODIFY, or DELETE), on a table, the whole table is locked.
Locking Record Sets
Usually you lock a table before reading a set of records in that table if you want to read these records again and modify or delete them. With Classic Database Server, you can choose to lock the table with LOCKTABLE(TRUE,TRUE) after reading the records for the first time instead of locking with LOCKTABLE before reading the records for the first time.
When you try to modify or delete the records, you receive an error message if another transaction has modified the records.
You also receive an error message if another transaction has inserted a record into the record set. However, if another transaction has deleted a record from the record set, you will not notice this change. The purpose of locking with LOCKTABLE(TRUE,TRUE) after reading the records for the first time is to improve concurrency by postponing the table lock that Classic Database Server puts on the table.
1 comment:
Its a helpful information for Microsoft Dynamics NAV and Navision System. Thanks for sharing.
Post a Comment