The Curious Case of the Table-Locking UPDATE Query | Heroku

I maintain an internal-facing service at Heroku that does metadata processing. It's not real-time, so there's plenty of slack for when things go wrong. Recently I discovered that the system was getting bogged down to the point where no jobs were being executed at all. After hours of debugging, I found the problem was an UPDATE on a single row on a single table was causing the entire table to lock, which caused a lock queue and ground the whole process to a halt. This post is a story about how the problem was debugged and fixed and why such a seemingly simple query caused so much harm.