0

This is an odd one: We have a Web API endpoint that periodically starts throwing this error for every post submitted to any POST controller method of any controller, even though none of the code called by those controllers touches qrySoaHeader_Net:

"View or function 'dbo.qrySoaHeader_Net' is not updatable because the modification affects multiple base tables."

I completely understand what that message means normally (you're trying to write to a view that doesn't resolve uniquely to a single table), but that's definitely not what's happening in this case.

qrySoaHeader_Net is a SQL Server view used by an EF entity class (SoaHeader) to retrieve data for a report. None of the POST methods on any of our controllers are touching SoaHeader directly and none of the other objects they call are linked to it in EF (because it's a view, so has no relationships in the DB).

If I copy the database back locally I do not get this issue, and if I restart the production IIS the issue goes away and then reappears after a week or so.

Calls to GET methods on controllers continue to work fine, it's just the POSTs that seem to have the issue - the GET methods are all calling code that only retrieves data.

To me that suggests that this must be an issue with something getting 'stuck' on the IIS or the SQL Server? I've checked for open trans with DBCC OPENTRAN but there aren't any.

Production server is running Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Express Edition (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor).

My machine is running Microsoft SQL Server 2017 (RTM-GDR) (KB4293803) - 14.0.2002.14 (X64) Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 17134: )

Can anyone think of what could cause this error to be thrown regardless of which endpoint is posted to?

tomRedox
  • 28,092
  • 24
  • 117
  • 154
  • Have you/your team authored any middleware in the asp.net pipeline? If so, I'd look for threading/caching issues within those first. – Damien_The_Unbeliever Aug 30 '18 at 10:06
  • @Damien_The_Unbeliever is that the code triggered in Global.asax.cs Application_Start? – tomRedox Aug 30 '18 at 10:09
  • 1
    Yes, I think so. I've been more looking at core stuff recently where it `ConfigureServices` and `Configure`, but yes, I think for what you're doing it hangs off of `Application_Start` and/or the classes lurking under `Start` (or `Startup`) – Damien_The_Unbeliever Aug 30 '18 at 10:19
  • Double check that you don't have triggers that access the view (that would be weird). Also look at your DI. This could be caused by leaking/sharing DbContext instances. If the POST controller gets a DbContext instance with a tracked, altered SoaHeader object, it would try to save it when you call SaveChanges. Otherwise you can use SQL Trace or Extended Events to capture the errors and the queries that cause it. – David Browne - Microsoft Aug 30 '18 at 14:29
  • @DavidBrowne-Microsoft, the DI sounds like a definite possibility. Looking at the `SoaHeader` I can see we have additional `NotMapped` properties that we set values on when retrieving the `SoaHeader`. Would that be enough to make the DbContext consider the record altered? If I set the entity to `AsNoTracking` (like here https://stackoverflow.com/a/35213910/1879019) would that be likely to fix it? – tomRedox Aug 30 '18 at 15:03
  • AsNoTracking may fix this, but if you are sharing DbContext objects across requests, you _will_ have further issues. You should just fix that, and scope your DbContext isntances either to the HTTP request or locally in each controller method. – David Browne - Microsoft Aug 30 '18 at 15:33
  • @DavidBrowne-Microsoft you're right, the core issue here is a misunderstanding on my part about how the DbContext should be scoped, I need to read up on what the right way to do that is – tomRedox Aug 30 '18 at 16:36
  • @DavidBrowne-Microsoft, switching our DBContext binding from `InSingletonScope` to `InRequestScope` has fixed the issue and has also prompted us to do a lot of long overdue reading around DI lifetimes. Thank you so very much for your help. Do post it up as an answer and I will accept it. – tomRedox Aug 30 '18 at 17:19

1 Answers1

1

Look at your DI. This could be caused by leaking/sharing DbContext instances. If the POST controller gets a DbContext instance with a tracked, altered SoaHeader object, it would try to save it when you call SaveChanges.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Just as an addendum, in our case switching our DBContext binding from `InSingletonScope` to `InRequestScope` fixed the issue - this was with ninject, but the concept holds true for any DI framework (including .net Core's built in one) – tomRedox Aug 30 '18 at 17:43