3 ms·
And that updates on tables with FKs block 'for update' locks on the referenced tables. (Let's not forget, amid all this Postgres-specific chatter, that the art
by barrkel 7y ago
And that updates on tables with FKs block 'for update' locks on the referenced tables.
(Let's not forget, amid all this Postgres-specific chatter, that the article is about GitHub, who use MySQL.)
- wbolster 7y agonot sure about mysql, but in postgresql an update on a table with a foreign key will take 'FOR KEY SHARE' locks on the referenced table, which is a weaker type of lock. updates on the referenced table that do not update (primary) key columns (changing a pk is very uncommon anyway) will suffice with a 'FOR NO KEY UPDATE' lock, which does not get blocked by 'FOR KEY SHARE' locks. in fact, the main reason postgresql has these weaker 'FOR KEY SHARE' and 'FOR NO KEY UPDATE' lock types is for handing of foreign keys.
- Volundr 7y agoAlso worth noting here, if you ARE updating the primary key on the referenced table, then locks MUST be taken to ensure data consistency. If that PK isn't locked, than by the very problem definition you have open transactions relying on the original PK value. Those locks would be very challenging to accomplish at the application level.
- jeltz 7y agoIn which case you can run FOR NO KEY UPDATE.