I’m curious what the SQL statements you’re using to build the state look like. Perhaps you didn’t explain the full scope of what you’re doing, but if it’s really just checking how many alarms are active at each site, it seems like there should be a way to do it in just one query, meaning that it shouldn’t take much longer over the vpn than locally. And if that were the case, it should be possible to optimize the indexes so that the whole thing was faster.
Also, deadlocks can be triggered by accessing keys in a different order between queries. If you post the queries you’re running, we can compare them to what FSQL runs. Also, post the indexes that are defined for the table.
Triggers are a bit notorious for causing this type of problem. But, I suspect it all just comes down to optimizing the query that builds the state.
In regards to retrying the query, there isn’t really a way right now to get FactorySQL to do it. Not much consolation, I know, but Ignition would… the store and forward system there works differently.
Regards,