CP Synch. optimization

bruno77

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

Soft_Tod

Hi Bruno.

I've done a couple of Commerce Portals but I never experienced synchronisation deadlocks. However do the NAS usually lock the table 6224 for quite a long time as the synchronisation data can be rather voluminous, and as long as NAS locks no user can change or add data to the system tables which are to be synchronised.

My workaround is to create a copy of the table 6224 and let NAS shovel the data from table 6224 into the copy, unlock the table 6224 and synchronise from the copy.

I'm not sure this is what you're looking for, but it is a common problem so I thought I post it...

Best Regards

bruno77

Good idea, I will try that if the above code change does not solve the problem. I did not consider the issue with the synch holding the lock for too long, I thought it was caused by multiple transactions hitting the table(request NAS and NAV users).

I have added a few additional tables to the synch (document lines) which causes a lot more transactions, so that is probably why I have issues.

Again, this is SQL so what I am trying to accomplish is to not do a table lock on insert, just a record level lock, and if I am not mistaken SQL will only lock the specific records that are synched (deleted), when synching (record level, or page level), however these are not touched by any other process so it "should" not matter how long they are locked in SQL.

Standard Navision does a table lock though (Locktable and Find('+')) which would have an impact and could cause conflicts. I think I can eliminate that using the code above though. The code change kind of simulates the change log, which uses the auto increment, instead of getting the last records entry number and increasing it.

Thanks for the input.

/Bruno

iceborg

So Bruno, what was the result of your test?
Did it help?

Im using almost standard Commerce Portal backed in Navision(Synch and to receive requests) and a completly other webshop in the Front End (I'm not that happy about Commerce Server 2000 or 2002).

The shop receives around 400 orders per day, salespeople might register max 100 orders in a day, and there is quite alot of change activity on items, but price updates etc. are done in evenings, so i don't think the volume should be a issue, but still deadlocks from time to time.

btaylor

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.