SELECT FOR UPDATE locks other tables due to Foreign Keys

[ctapobep]

Most of the time your innocent-looking select ... for update can block the modifications to OTHER tables:

tx1: select * from A for update
tx2: insert into B ... -- <-- BLOCKED!

Why? Foreign Keys…

When running FOR UPDATE queries, you tell the DBMS that you’re going to update this row later. But it doesn’t yet know which columns you’ll touch. What if you update the Primary Key? And what if the parallel INSERT into the related table wants to reference this row now?

If both transactions proceed, then we end up referencing the non-existing row. So to prevent this, during INSERT/UPDATE we must lock all the connected rows from other tables. This is where it starts to conflict with SELECT FOR UPDATE. Or well, with the actual UPDATE/DELETE in the connected tables.

But updating PKs is such an exotic use case, do we really want these extra locks for such rare edge cases? Postgres comes with a solution:

select … for no key update

With NO KEY you promise the DBMS that you won’t be updating the PK. And this is what you should (almost) always use in Postgres instead of the ordinary FOR UPDATE.

Other databases

• MySQL doesn’t have a solution to this, so you can’t escape these locks
• Oracle always uses the “NO KEY”, no need to do anything explicitly. You won’t experience such locks unless you actually UPDATE/DELETE the key.
• SQL Server - can someone check? I’m too lazy :)

---

To get notifications about new posts, Watch this repository.