CP Synch. optimization

Business Central/NAV Developers Forum
1

Hi,

I am having some issues with deadlocks when using Commerce Portal. The “SynchMgt. Message Queue” is getting deadlocked because of the table lock done in CU 6205. Could I get some input on this code change:

Change table 6224 - SynchMgt. Message Queue - No. to BigInteger and AutoIncrement = Yes and then the change below to CU 6205:
GetNextSynchMessageNo(TableName : Text[250];Command : Code[11])
//GetNextSynchMessageNo

//Temp Start
//SynchMessageQueue.LOCKTABLE;
//IF SynchMessageQueue.FIND(’+’) THEN;
//SynchMessageNo := SynchMessageQueue.“No.” + 1;
//Temp End

Conv.PutSynchMessageValue(
SynchMessageQueue,SynchMessageNo,‘TABLE’,TableName);
//Temp Start
SynchMessageNo := SynchMessageQueue.“No.”;
//Temp End
Conv.PutSynchMessageValue(
SynchMessageQueue,SynchMessageNo,‘COMMAND’,Command);

Input would be appreciated. Thanks.

/Bruno

2

Well - You remove the lock, by commenting out the code that locks it.

And since your later processing is dependant on that walue, you MUST lock the queue.

3

Henrik,

Note that this is SQL and an INSERT in SQL does not normally lead to the entire table being locked.

The standard code:

SynchMessageQueue.LOCKTABLE;
IF SynchMessageQueue.FIND(’+’) THEN;

Is the Navision way of simulating a table lock in SQL (locking the last record), if I am not mistaken (which I could be [:)].

Therefore the code change I purpose would not lock the entire table, it would only put an exclusive lock on the inserted records and not the whole table, which should lead to better performance, and allow multiple transactions against the table at the same time.

If this was the Native Navision DB, this wouldn’t matter since it always does a table lock (no support for record level locking).

Again, further input would be great. Thanks.

4

I am having a similar problem to the on described above. I’m not sure if it is a table lock, or an error in the Navision Commerce Portal Synchronization service. Normally, when there is a problem, the synchronization does not produce an event log error.

Today, the service failed with the following event:

Faulting application procdriver.exe, version 3.70.2.19516, faulting module mscsups.dll, version 4.5.3810.0, fault address 0x00018894.

We are running Commerce Portal 3.70.B with SQL 2000 on a Windows 2003 Server.

Any insight would be appreciated.

5

This is an issue with the synch service on the Commerce Server box, correct?

This could be a Commerce Server issue:

http://support.microsoft.com/?kbid=838466

Also make sure that you have the latest 3.70B version of the Navision Commerce Portal Synchronization.

6

Hi Bruno,
I think find(’+’) initiates a complete table scan when you are on a SQL db.
If you are on 4SP1 or higher, you should use FINDLAST instead.

Not sure if it makes a difference in this situation though, but if it SQL makes a complete tablescan, perhaps it locks longer than necessary.

/Daniel