Developer forum

Hi Shawn,
Sorry but your video is not avaible, could you upload it somewhere else and share a link?
What amount of oders/order lines are you importing and with what frequency?
Maybe you can run the task during late hours so it shouldn't block lots of users.
BR, Dmitrij

Hi Dmitriy, 

Thanks for the reply.  My video is shared below again, does that download for you?  I also emailed to you directly.

We are running in off hours as a workaround for now but a higher frequency of order syncing is something our customers expect versus a nightly update.  The workaround helps for now but hoping we can break the lock on the db while the scheduled task is running. 

Thanks, 

Shawn

Hi Shawn,

We have tried making a fix for this problem, and the fix is available in the newest release of our Dynamicweb.Ecommerce package on NuGet.org https://www.nuget.org/packages/Dynamicweb.Ecommerce/1.10.70.

Let us know if you still experience the problem.

Best regards,

Steffen

Hi Steffen, 

Thanks for this fix.  We have tested on 2 sites and all is looking to have corrected the issue. 

What is the Work Item ID is for this development?  It looks like it’s in Dynamicweb.Ecommerce and not per provider, so it probably benefits all providers.

Thanks, 
Shawn

Hi Shawn,
the item no is 2674.
BR, Dmitrij

Thanks Dmitrij!

Shawn

Hi Dmitrij, 

Checked out that Item No and it is for the caching.  Do you have the Item No for the locking fix?

Thanks, 

Shawn

Hi Shawn,
Sorry. Yes, it is now edited/updated, so read my previous post again :)
BR, Dmitrij

Thanks again then :) !

Shawn

We are experiencing similar issues with CustomerImport. We get frequent timeouts when the customerimport is runing. Both in frontend and backend. 

Was this fix only for the Order provider?

Regards / Aki

This fix was related to orders, yes.

sql timeout or script timeout?

Have an exception?

Sorry, a bit vague perhaps. :) 

SQL TImeouts. Attaching an example. 

Regards / Aki

Thanks for clarifying :-)

What destination provider are you using?

User Destination.

We have a number of those and I can see that some of them have the same time to run. Thats propably not good....

Regards / Aki

Hi Aki,
do you have a buge amount of users importing when the timeout occurs?
Can you measure how many records are you trying to import?
BR, Dmitrij

I have also seen the same timeout error when importing users and they need to be placed in several different user groups. When using Sql Server Profiler, I have seen a number of major updates being made to the AccessUser table. Each update contains 1000 users and can therefore occur many times if there are many thousands of users and many user groups. This seems to be the main cause of the timout.

update AccessUser set AccessUserGroups = IsNull (AccessUserGroups, '') + '@ 336005 @' where AccessUserUserName IN ('XXX', 'YYY', 'ZZZ' etc. 997 users ....) and (not AccessUserGroups like '% @ 336005 @% 'or AccessUserGroups is null);

Best regards
Niels Foldager

The error and stack that AKI has posted, I think we have a possible solution for following the same approach we made with the similar problem with orders.

It is a change to how data is queried in our userrepository in Dynamicweb.Security.dll. I have made the change, will commit it, and then it needs to go through QA.

@Aki: You can get a pirate copy to try out if you want.

BR Nicolai

@Nicolai  That sound good, yes please. Worth trying. 

Regards / Aki

Hi,

@Nicolai is there any ETA on the fix in the Dynamicweb.Security.dll? 

Regards / Aki

Hi,

Sorry about the nagging but is there any progress in this @Nicolai, we are really having trouble with this one.

Regards / Aki 

Find the assembly attached.

The nuget package will also get updated some time next week. QA is partially OOO because of vacation, so there is a little waiting time.

BR Nicolai

Excellent! Thanks. 

Regards / Aki

Hello,

This issue has come up again for the User provider when we upgraded to 9.14.9. 

Source Provider = XML
Destination Provider = User

When the data integration job is running in the backend, the frontend of the site locks. In particular, even the homepage doesn't work. I assume that we are doing something with the user, even showing a 'Welcome {name}'. 

Has something changed or reverted with the user provider? While it wasn't ideal, the solution in the past was to have WITH (NOLOCK) on the database calls to ensure that it doesn't lock the site during imports. 

To repro, you can do a large import of users. While it's importing, test the site on the frontend and it should spin and wait for the data integration job to complete. This happens whether it's called from the regular scheduled task, or if you run it manually. 

Scott

Hi Scott,
There were no significant changes in the UserProvider from the last time. The problem is that I could not get it reproduced, tried on old Rapido and Swift with latest 9.14 (I had just one AccessUser table in the mapping.). Do you have a solution url where it occurs? Could you run the sql profiler or sql query for getting a list of locks (https://stackoverflow.com/a/57358842/725497) on the tables during the job is run?
Maybe it could show where it stucks.
BR, Dmitrij

Hi Scott,

Based on your observations, do you think this is exclusively related to the User provider? I'm asking because a couple of weeks ago I had an incident, where a product import (using the Dynamicweb provider) that normally once each night and runs for 20-25 minutes went completely bananas and ran every 2-3 minutes. It ate up all available memory on the server so everything was inaccessible. After resetting the IIS and running the job manually things appeared to be back to normal.

Did you notice anything strange in the integration log?

Br.

Lars

Thanks Dmitriy for trying to reproduce this and for looking into it. Since you say that you can't reproduce it and nothing has changed in the user provider, I'll spend more time on my end narrowing it down. I have a consistent repro that I can work with so I'll narrow it down further. While it's important to me, I may not get an hour or two to focus on it for the next couple days, but I'll reply back as soon as I have further info.

Lars, it's possible that it's not the user provider, but I know that it happens when running a schedueld task that called a data integration job with the user provider as the destination provider. When it happens, CPU and memory aren't working hard, but it follows the pattern from the past where the frontend is locked.

That said, I'll follow yours and Dmitriy's suggestion to check the integration log and narrow it down further. Maybe it isn't specifically due to the user provider. I'll reply back.

Scott 

Ok, I've figured out the issue (not the solution).

Dmitriy, it's not the same locking issue, although it is with the user provider job which I believe is because it clears the cache.

Lars, it does appear to be most commonly with user provider, but because it does more cache busting then other providers (I haven't tested to find out which provider does what, but I know that some other providers run without any impact to the frontend).

It appears to be an assortment issue, causing the 18 second load whenver the data integration job runs. We have 360,285 EcomAssortmentItems, and each time the user provider job runs, it appears to clear the cache and re-load the entire EcomAssortmentItems table (SELECT * FROM EcomAssortmentItems WITH (NOLOCK)). That takes 18 seconds to load on the frontend while the user waits.

I don't remember this being a problem with prior versions so it may be an update to assortments that is causing this to come up now.

Here's a video showing the impact and confirmation what's happening: https://app.screencast.com/InKB35jGNXU8N 

I realize this is not integration related anymore, but are there any ideas what we can do about this assortments impact?

Thanks,
Scott

Great investigation, Scott! I'll run it by the dev team :-)

If this is urgent, my suggestion would be to use the DW provider to populate the raw tables and build the index in the end. I know, you can't associate users and groups using native ERP keys, but if you do an initial import to a staging table, you can join that with AccessUser in a view to look-up AccessUserId or whatever keys you need, and then use the view in your source provider.

Br.
Lars

Hi Lar,

Thanks for both: running it by the dev team, and the short term solution suggestion. That makes sense and we'll do that as a workaround for now. We use ExternalId as the primary key  which maps between the ERP and DW, and that works with the Dynamicweb provider, so we may not have too many things to consider (maybe the auto generated password or something, but we'll figure it out). 

Thanks!

Scott

Hi Scott,
the problem in the UserProvider was fixed and you can get a new 3.0.10 version of it from Nuget.
BR, Dmitrij

Thanks Dmitrij! Great timing since we're just getting ready to go live and I wasn't looking forward to redoing those with a different provider. This is just what we need.

Thanks,

Scott