Developer forum

Forum » Integration » Scheduled Task Locking db EcomOrders Table and Holding Frontend Hostage

Scheduled Task Locking db EcomOrders Table and Holding Frontend Hostage

Shawn Tehini Dynamicweb Employee
Shawn Tehini
Reply

Hi,

Please see attached video reproducing issue.  

We are seeing the following behavior:

When impersonating a customer and on the frontend and you go to change who you're managing, there are three outcomes to that click. 1) The click is fast and timely. This happens when there are no items in the cart of the customer. 2) The click is a few seconds slower, but still timely. This happens when the customer has items in their cart. 3) The click on the frontend happened to be at the same time as the Import Orders job run and the frontend is held hostage until that job is finished.

The Order Provider seems to be causing a blocking query, causing everything else that depends on EcomOrders (including carts, lookups to addresses on the cart, etc.) to be locked. The scheduled task is utilizing the "Import data with custom request add-in".  

Has anyone run into this issue?  It is causing performance issues across sites.

Thanks, 

Shawn

 

 

 


Replies

 
Dynamicweb Employee
Dmitriy Benyuk
Reply

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

 
Shawn Tehini Dynamicweb Employee
Shawn Tehini
Reply

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

 

 
Steffen Kruse Hansen Dynamicweb Employee
Steffen Kruse Hansen
Reply

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

 
Shawn Meach Dynamicweb Employee
Shawn Meach
Reply

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

 
Dynamicweb Employee
Dmitriy Benyuk
Reply

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

 
Shawn Tehini Dynamicweb Employee
Shawn Tehini
Reply

Thanks Dmitrij!

Shawn

 
Shawn Tehini Dynamicweb Employee
Shawn Tehini
Reply

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

 
Dynamicweb Employee
Dmitriy Benyuk
Reply

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

 
Shawn Tehini Dynamicweb Employee
Shawn Tehini
Reply

Thanks again then :) !

 

Shawn

 
Aki Ruuskanen
Aki Ruuskanen
Reply

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

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

This fix was related to orders, yes.

sql timeout or script timeout?

Have an exception?

 
Aki Ruuskanen
Aki Ruuskanen
Reply

Sorry, a bit vague perhaps. :) 

SQL TImeouts. Attaching an example. 

Regards / Aki

MicrosoftTeams-image.png
 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

Thanks for clarifying :-)

What destination provider are you using?

 
Aki Ruuskanen
Aki Ruuskanen
Reply

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

 
Dynamicweb Employee
Dmitriy Benyuk
Reply

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

 
Niels Foldager
Reply

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

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

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

 
Aki Ruuskanen
Aki Ruuskanen
Reply

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

Regards / Aki

 
Aki Ruuskanen
Aki Ruuskanen
Reply

Hi,

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

Regards / Aki

 
Aki Ruuskanen
Aki Ruuskanen
Reply

Hi,

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

Regards / Aki 

 
Nicolai Pedersen Dynamicweb Employee
Nicolai Pedersen
Reply

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

 
Aki Ruuskanen
Aki Ruuskanen
Reply

Excellent! Thanks. 

Regards / Aki

 

You must be logged in to post in the forum