Microsoft Dynamics NAV - C/SIDE Solution Development -

Microsoft Dynamics NAV – C/SIDE Solution Development

Locus IT ServicesMicrosoft Dynamics NAV – C/SIDE Solution Development

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.

Locking

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.

Blocking

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.

Deadlocks

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.

Avoid Deadlocks

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.

 

Locus IT Project Management Office
What’s it?