This is related to Debugging a hanging session/lock
Every so often (~1-2 times a month) we have our database lock up because there is one query that doesnt finish, and that has requested locks that block all others. This is on a table that is core to our business, so it results in downtime.
The query in question is one of a couple of candidates, all of them are just simple
UPDATEs running on just 1 row. The best lead I have so far (I am open to other avenues) is from RDS performance insights I see that while all other queries are locked on "tuple", there is always 1 query that is locked on "ClientRead". From my research, this appears to be Postgres waiting on the client lib to send it the bound parameters or similar. So my theory is that there is one query that randomly sometimes ends up in this state, and this blocks all others after it. Is this a reasonable evaluation?
If this is the case, how can I go into investigating and resolving it? We use
sequelize 5.9.4 (node-js). We did upgrade it recently, ~1-2 months back, so I am wondering if there might be a bug in this version which might cause a error on the sequelize side when processing a query, is that possible? It could be a bug in
node-postgres as well, this came up in my searching https://github.com/brianc/node-postgres/issues/1952.
I will try to update with a screenshot of RDS performance insights next time it happens, its retention was shorter than anticipated.