Fork me on GitHub
#off-topic
<
2021-07-15
>
Stuart09:07:17

I'm having database issues with C# calling into SQLT on sql-server. A good number of times a day I will get an error that a query which 100% should return rows, will return 0 rows. For each individual query, it runs each 48 times a day, sometimes a given query x will fail and return 0 rows only 2 or 3 times a day. The data in the db isn't changing, so if it succeeds once, it should succeed every time. I get no errors when this fails like you might expect if it was a concurrency issue, e.g. deadlock errors etc. Just the query returns 0 rows. I can reproduce it occasionally on debug, but its very sporadic, where I will breakpoint in the if condition for no rows, then just rerun the query by dragging breakpoint to the db call, and it works and I get rows... Has anyone seen this?

rakyi10:07:59

maybe your setup has multiple nodes and you sometimes hit a different node/replica with inconsistent data via some kind of load balancer?

Stuart10:07:16

nah, its hitting one single on-prem db. No replication, no load balancing.

indy12:07:01

Perhaps there’s a fault tolerance thing in place where it returns 0 rows if something in particular errors out. We had a similar situation where sometimes the response to the front end would have 0 elements in the array because there was some code facilitated by Hystrix that would return an empty array if a particular call to one of micro services errored out.

Stuart12:07:51

This is in the backend service, and its the http://ADO.net object that shows 0 rows. I'm not even using an ORM.

Stuart12:07:34

I';m right in thinking it can't possibly be our sql server having too many requests at once and jsut returning some as empty result set. That would be crazy right?

indy12:07:57

That would be crazy

Stuart12:07:46

Cause I can literally stick a breakpoint on the line

(if rows.Count == 0)
    // print error.
And then drag it back a couple of lines previous to execute the sql, and it will return rows.

Stuart12:07:37

I dont even know where to start to debug this

Stuart12:07:36

Or maybe as a temp hack fix, I say if rows.Count == 0, then just rerun the query. And if it fails twice then error.

Stuart12:07:59

That seems a terrible solution though.

indy12:07:58

Can you check the DB logs for whether there are queries where it would return 0 rows?

indy12:07:41

Since you can repro in local, there won't be a lot of logs that you'll need to scan through

quoll12:07:31

This sounds so much like what I used to see while talking to MS-SQL Server in the late 90s. I’m sure it’s better by now, but it came down to thread races (in MS code… my code was single-threaded), and by adding a small delay (or reordering some operations) I was able to hide the problem. I’m sure this isn’t your issue, but you reminded me about it when you said ADO.

chucklehead16:07:10

Maybe an obvious question, but you’ve ruled out any sources of non-determinism in the query itself, e.g. no current date/time, user-defined functions, non-deterministic built-ins, etc?

2
Stuart16:07:23

yeah, the query is basically

select foo, bar, quax
inner join ...
left join ...
inner join ...
left join ...
where hotdog = @someparam
WHat I hve noticed is, we have a greatly reduced load going through the database (only around 40k an hour right now, whereas normally its an order of magnitude greater) and a lot of these row counts = 0 have just stopped happening