Module 1: DATA AND PROCESS MODEL
Module 2: MASTER TABLES AND PAGES
Module 3: DOCUMENTS
Module 4: POSTING
Module 5: FEATURE INTEGRATION
Module 6: REPORTING
Module 7: STATISTICS
Module 8: DIMENSIONS
Module 9: ROLE TAILORING
Module 10: INTERFACES
Module 11: WEB SERVICES
Module 12: TESTING AND DEBUGGING
Module 13: SQL SERVER OPTIMIZATION
- Lesson 1: SQL Server for Microsoft Dynamics NAV
- Lesson 2: Representation of NAV Tables and Indexes in SQL Server
- Lesson 3: Collation Options
- Lesson 4: SQL Server Query Optimizer
- Lesson 5: SQL Server Query Optimizer
- Lesson 6: Data Access Redesign
- Lesson 7: C/AL Database Functions and Performance on SQL Server
- Lesson 8: Bulk Inserts
- Lesson 9: Locking, Blocking, and Deadlocks
- Lesson 10: SIFT Data Storage in SQL Server
- Lesson 11: SQL Server Profiler
Lesson 9: Locking, Blocking, and Deadlocks
Locking, Blocking, and Deadlocks
Locking, Blocking, and Deadlocks : When data is read from the database, Microsoft Dynamics NAV uses the READUNCOMMITTED isolation level. This means that any user can modify the records that are currently being read, until the table is either changed by a write operation, or locked with the Record.LOCKTABLE function. From this point until the end of the transaction, all read operations on the table are performed with both REPEATABLE READ and UPDLOCK locking. This is frequently known as pessimistic concurrency.
Records can be read with different types of locks such as UPDLOCK. At this level, records that are read are locked. This means that no other user can modify the record.
When other users try to lock data that is currently locked, they are blocked and have to wait. If they wait longer than the defined time out, they receive the following Microsoft Dynamics NAV error: “The XYZ table cannot be locked or changed because it is already locked by the user. “If it is necessary, change the default time-out by selecting File > Database> Alter. On the Advanced tab, select Lock Timeout and Timeout duration (sec) value.
Refer to the previous example, where two users try to modify the same record. The data that will be changed can be locked. This prevents other users from doing the same.
There is a potential situation when blocking cannot be resolved by SQL server. The situation arises when two or more users manage to lock data. Then it is blocked when they try to lock data that is already locked by one of the other users. SQL server resolves the issue by ending the transaction that has done the least amount of work.
For example, consider a case in which two users are working at the same time and try to retrieve one another’s blocked records, as shown in the following pseudo code. SQL Server supports record level locking. So, there may be a situation where these two activities bypass one another without any problem, such as with this pseudo code. Be aware that User A is retrieving the last record compared to the situation that was discussed earlier.
There are also situations where a block on index update may produce the conflict, and situations where updating SIFT tables can cause a deadlock. These situations can be complex and difficult to avoid. However, the transaction that is selected to fail is rolled back to the beginning, so there should be no major issue. However, if the process is written with several partial commits, then there might be dirty data in the database as a side-product of those deadlocks. That can become a major issue for the customer.
Many deadlocks could lead to major customer dissatisfaction, but deadlocks cannot be avoided completely. To reduce the number of deadlocks, do the following:
- Process tables in the same sequence.
- Process records in the same order.
- Keep the transaction length to a minimum.
If this is not possible because of the complexity of the processes, revert to serializing the code by making sure that conflicting processes cannot execute in parallel.