Weird Azure Sql Data Caching Issue - NOT

Question

Sorry folks. This was a wild goose chase. Devices were doing something wrong and server code was trying to make up for it. This post could be deleted but that's not an option I have.

I am encountering what looks like a weird Azure Sql Data Caching issue.

1. Azure Web App running 5 instances.
2. A specific service url with the same query parameters makes the same Azure Sql query.
3. The web app logs the integer result of the query.
4. When a mobile device makes the service call, the logged result is different then when a browser makes the service call.
5. Running the query in SSMS made by the service call matches the browser result.

I've been seeing occasional log entries indicating this for at least a couple years but it was intermittent, posed no real issues, and I assumed it was a minor issue with the devices. Today, working with the device developer, we have confirmed that the same exact query returns different results depending on who is making the call.

The Azure Sql database is a Standard S4. It isn't using replication or elastic pools or anything like that. The web app only has access to that one database. The web app makes database calls using thin wrappers of System.Data and System.Data.SqlClient objects. A specific url always executes a query like this:

select count(*) from dbo.tblJobs j join dbo.tblHandsets h on j.HandsetId = h.Id where JobSent is null and ToBeRemoved = 0 and h.HandsetNumber = @hn

That query is executed with IDbCommand.ExecuteScalar and the result is logged to the database by the web app before returning the result to the caller. In the query above, tblHandsets.Id is the PK uniqueidentifer and tblHandsets.HandsetNumber has a unique index. We have confirmed that calls from the mobile device with a given HandsetNumber logs different results than the same url in a browser or query in SSMS and has been doing this for at least a couple hours.

Sample url parmeters from device:

[website]/jobs.ashx/?ac=1&ver=4.0&hn=DEC0DEDDE43943E0C7144D480DB6700000009981

web app logs query result = 8 before returning result 8 to device

Sample url parameters tested in browser:

[website]/jobs.ashx/?ac=1&ver=4.0&hn=DEC0DEDDE43943E0C7144D480DB6700000009981

web app logs query result = 0 to before returning result 0 to browser

Sample query in SSMS:

select count(*) from dbo.tblJobs j join dbo.tblHandsets h on j.HandsetId = h.Id where JobSent is null and ToBeRemoved = 0 and h.HandsetNumber = 'DEC0DEDDE43943E0C7144D480DB6700000009981'

SSMS query result is 0

I really am stumped on this one and have no clue why mobile device initiated calls would be getting a different result. I presume it's possible Azure might route mobile devices to a different web app instance than browsers but really have no clue about that. And that still wouldn't explain anything to me because there's only one database. Any ideas?

Additional Info: It was also confirmed that another call from the mobile device retrieved data different than the same query in SSMS. For example, using a SqlDataReader to read a result set with CommandText

Select * from dbo.vwJobsToSend Where HandsetNumber = @hn

returned different results depending on whether it was initiated by the mobile device or by SSMS. It really was as if the mobile device was reading data from an earlier instance of the database but updates to the data initiated by the device were going to the current instance read by browsers or SSMS.

Answer 1

Please do not use the view for mobile connections instead use a Table-Valued function or the query itself as you use it on SSMS. Here you will find one reason to not use the view.