Deadlocks occur more often in a two-tier architecture or in a three-tier architecture? And why?

Question

I am working with Microsoft Navision 2009 and a lot of times, if for example you make a new order, and change something later on the records, then often occurred that you will get a message:

Another user has changed the records and you cant do anything to change the records.

So we now investigate if it is better to go for another version of Navision. For example:

Micrososft Navision 2013. Because 2013 make use of a tree-tier architecture. And 2009 make use of a two-tier architecture.

So my question is:

Deadlocks occur more often in a two-tier architecture or in a three-tier architecture? And why?

Answer 1

This is a revision control issue. Perhaps Navision is not suited for your needs. Try another Revision Control System.

Answer 2

The error suggests something related to concurrency control rather than SQL like deadlocks (you start doing something on a record, another user edits the same record and you try to save your changes over the modified ones).

This has nothing to do with 2 vs 3-tier architecture, but the way concurrency control is dealt with:

• first to save changes wins (optimistic, rare conflicts assumption) OR
• first to enter editing mode blocks access for other changers (pessimistic, quite often conflicts assumption)

More details about concurrency control in Navision can be found here (point 7). It looks like optimistic concurrency control is used.

Answer 3

As it been said in other answers this is no locking problem, this is concurrency issue. In this case upgrade to Nav 2013 will not have any effect. Not to mention that Nav 2009 was the first version to introduce 3-tier capability. So you can use RTC in and service tier right now.

But then again, if this problem showed up recently one could assume that you are using customized for your needs version of Nav application rather than pure Cronus. In this case you're probably have some bug with something that frequently updates your orders, something like periodic job to update odrer status. Job like this may be executed every minute and while it takes five minutes for user to make changes to order, the order may be updated five times by periodic job. So make a closer look at your modifications and make sure they're not the problem.

Answer 4

As @alexei said: This has nothing to do with 2-tier or 3-tier architectures. And it is not a dead lock at all.

The mechanism is called optimistic locking - which is really no locking. A program should be designed using optimistic locking for entities that are not likely to be changed by more than one person at the same time. When 2 persons change it at the same time optimistic locking prevents the second person from overwriting the first persons change without knowing the change. So this is a good thing. It prevents merge conflicts. The bad thing is, that the second person has to reload the data, see the change and has to do the own change again - or decides not to change it now.

Pessimistic locking on the other side is real locking of resources. A person sets a lock for the entity that is about to be changed. The person changes the entity and releases the lock. In the meantime no other person is able to edit the locked entity. The advantage here is that the second person never has to do the work again because save will never fail. But it also has disadvantages: The second person has to wait. Users forget to unlock their resources when they go to lunch or even worse when they go to holiday. So other users must be able to break these locks or the program must break them after some time.

No locking is also a strategy. If you do building something from scratch - without some kind of framework this is the default. Both persons can edit an entity at the same time like they do with optimistic locking. Then the first one saves it. Then the second one saves it and overwrites the first ones changes without any knowing. This can also be a strategy but in most business cases not a good one.

It is a question of your app design which locking mechanism to use. Or if your constraint is to use one of them you have to design your app so that it works best with it.

Answer 5

As Alexey and pommes already said, switching from 2-tier to 3-tier architecture changes nothing in terms of SQL blocks/deadlocks.

But, if we are more specifically talking about migrating from NAV 2009 to NAV 2013, there are several other changes besides 3-tier architecture, that were directly focused at the SQL blocking issue.

One of them is the redesign of sales and purchase posting routines to decrease significantly the period when G/L Entry table is locked: https://blogs.msdn.microsoft.com/nav/2012/10/17/gl-entry-table-locking-redesign-in-microsoft-dynamics-nav-2013/

Another important change is switch of the isolation level used for pessimistic concurrency (LOCKTABLE, etc.) from SERIALIZABLE to REPEATABLE READ. Though it is possible to make this change for NAV 2009 as well, in NAV 2013 it is the default option. This change directly reduces the probability of blocks/deadlocks. You may read more about this change here: https://blogs.msdn.microsoft.com/nav/2011/05/12/microsoft-dynamics-nav-changes-by-version/

Besides that, the whole data access stack was rewritten and all native-db-compatible code was thrown out. That allowed to optimize for SQL server (as opposed to native DB architecture), introduce more effective queries and data caching. While it doesn't directly influence blocks, it means faster data manipulation and, as a result, locks are held for less amount of time. https://msdn.microsoft.com/en-us/library/hh169480(v=nav.70).aspx

Together with some other features of background posting, these changes could result in NAV 2013 being more effective in terms of SQL locks as compared to NAV 2009.