NHibernate Cache AC Gold R11

[artfmv]

We have a strange scenario and are not nhibernate experts so we are hoping someone can tell us exactly how we should handle this scenario. The issue is we did a pricing update in our database (ac_products.price field) by loading a spreadsheet of prices. After a period of time the pricing reverted back to the previous pricing. We were able to profile the database and found the update statements that were doing the update and saw the dynamic nature of these queries and attribute this to nhibernate caching. After restarting IIS on the server the behavior stopped and our new pricing is now in effect and since this was a one-time load and all other pricing updates moving forward will occur in the admin UI, it seems like this will take care of the situation but it's really troublesome not knowing that this is the exact culprit and wondering if it will rear it's ugly head again. Is this behavior nhibernate as we suspect and can we totally disable it to be safe? Thanks in advance. Art

[artfmv]

Quick update to this post, the IIS reset simply masked the issue for a couple of hours and it then repeated so somewhere there is something caching the old pricing and keeps reverting it back.

[Katie]

I'm not sure what could be going on, but after you update the database, can you check a product from the admin and see if the updated price appears here or does it revert back?

Thanks
Katie

[artfmv]

When we go back into the admin site the price update is there, but it will revert after a period of time. We profiled the database and see the update statements occurring but have not yet honed in on the exact frequency. It does appear to be occurring about every hour. The SQL statement we see in the profiler is definitely auto-generated from NHibernate and it is updating two files, Price and RowVersion. So when the reverting occurs any item that was affected does have an incremented RowVersion along with the price going back to the cached version.

[Katie]

All I can say is that AbleCommerce does not cache the product prices. Once the price is updated in the database, it will not change back.

Perhaps you are having some trouble with a customization.

[artfmv]

Katie,

Thank you but please notice this query that we pulled out of our SQL profiler:

exec sp_executesql N'UPDATE ac_Products SET RowVersion = @p0, Price = @p1 WHERE ProductId = @p2 AND RowVersion = @p3',N'@p0 int,@p1 decimal(28,5),@p2 int,@p3 int',@p0=96,@p1=6.99000,@p2=1783,@p3=95

We have no customizations that would be formulating a dynamic query such as this, and when examining this product the last modified date is 2-21-2018

The 6.99 price it is updating is the OLD price that had been applied manually the first time and no matter what we do to apply the correct price, e.g. do it through the UI or do it manually through the database it keeps running this query and setting it back after some period of time. So far that period of time seems to be about every hour and we are trying to isolate this as we speak, but I am confident this is coming from something outside our control.

Art

[jguengerich]

I've never had it revert a price for me (on R5, R12, R12 SR1, or R12 SR2), so I doubt it is something in the standard AbleCommerce code. Are you sure you don't have a recurring Agent job set up in SQL Server or a recurring Windows Task set up in Task Manager (wouldn't even have to be on the SQL Server itself, just any machine that can access it) that is running some code that set your initial prices? Or perhaps a mod in the site's Global.asax that sets up a timer and calls something?

EDIT: This seems unlikely, but is someone using the Batch Edit screen to look at prices before you update them, then you update the prices, then they hit the Save button?