OK, an interesting problem to troubleshoot, requiring a specific set of circumstances:
An upload comes in with a chase or a S2S of a summit that was once valid, but is no longer valid.
This would pass verification because of a missing check when determining summit points - it would verify the date against the table of points for the summit, but not verify if the summit itself was still valid at that time.
A similar style of bug then occurred when submitting the upload:
All checks would pass, so a transaction would be created.
During that transaction, the table that tracks uploads would be locked for writing by the transaction.
When adding the Chaser or S2S entry, the missing check would cause a panic, which would trigger a 500 HTTP response code.
Critically, this would forcibly terminate the connection and bypass the error handling and rollback of the transaction, leaving the uploads table locked until the transaction would timeout or the deadlock prevention code in the database would kick in.
During this time, no other upload or edit of an upload would proceed until the timeout or deadlock trigger would fire, as it could not obtain an exclusive write lock on the uploads table.
In the interim, users would retry the failed upload, exacerbating the problem.
While this wouldn’t ordinarily pose much of a problem - people don’t generally activate invalid or retired SOTA summits - there were three unique no-longer-valid summits activated in the past week that have triggered the problem. As each activation had many chasers and S2S, there were a lot of opportunities for the bug to trigger. I don’t know why there’d suddenly be three different no-longer-valid summits activated all of a sudden, and not in the same associations.
The fix for this was threefold: firstly, trying to enter an invalid summit that was once valid will now throw an invalid summit warning during the verify stage, and highlight the summit as an issue, so it won’t be able to move to submission stage. For those not using SD3 to submit logs, attempting to upload an unverified log should either result in an error or 0 points being allocated for the summit (as a chase). This required fixes in the Chaser and S2S upload code portions.
Since fixing this, there have been no further 500-error uploads entered and I expect the issue to be resolved at this point.
What a puzzle.
Presumably if an Alert or Spot is attempted it fails tidily so neither an activation nor chaser log would be attempted. Also, what would RBN produce?
73,
Rod
I just tried to spot and alert for Raw Head G/SP-016 which has been deleted for many years. Neither the spot or alert went through. This was using SOTAwatch but as that and other spot/alert system use the same API they should all be stopped from being accepted.
Very strange… I cannot edit your alert even with SuperAdmin power. But I can impersonate you and edit the alert. Wierd. I’ll look in the logs later. Back to winding toroids for now.
Diagnosing it took some time and it kept me, Josh and Andrew busy. There was lots of monitoring the DB trying to find what caused it to go AWOL. We could see from the monitor console lots of waiting processes but what was triggering this state was harder to find. The only constant was there would always be one INSERT locked and that was the hint it had to be the convergence of several events.
Once Andrew did the first fix the change was really obvious to what I had been observing on the SSMS Activty console. Just doing simple read-only requests showed the performance had come back. The bug fix was need in a few places.
This may have caused moments of the system appearing to be broken in the past but as soon as anybody looked, everything would be working normally. It’s just weird as Andrew says for all of a sudden there to be so many loggings of no longer valid summits.
Fair play to find that bug. Having once worked for a very large database company, I can tell you, that looks harder to find than chasing a greased otter through the Scottish clag on Rannoch Mor at 3am with a dying torch and only one boot on.
Well once we could see there was always at least one INSERT that was pagelocked it was a case of looking for ways the code could leave the tables locked and your man Andrew came up trumps quickly once he knew where to start looking in depth.
(Or maybe 
)
