> PostgreSQL takes FOR UPDATE on rows before a DELETE, or before an UPDATE that modifies a column that is part of a unique index that neither contains expressions nor is partial

This also happens when you UPDATE the same row twice within the same transaction.

https://goncalo.mendescabrita.com/blog/cross-table-lock/

tl;dr "Unless you plan to delete a row or modify a key column, always use SELECT FOR NO KEY UPDATE".