3 ms·

This sounds like an implementation specific detail (no doubt for mysql) that for all you know has been fixed in later versions but gets passed around as if it i

This sounds like an implementation specific detail (no doubt for mysql) that for all you know has been fixed in later versions but gets passed around as if it is a permeant truth that applies to all database servers.

barrkel 7y ago
FK consistency can't be guaranteed without ensuring the referenced rows don't disappear before the transaction has been committed. Think about it.
- jeltz 7y ago
  Yes, but it can be done without locking out reads. The only thing you need to lock against is someone changing the primary key of the referenced row or deleting the row. PostgreSQL has implemented this minimum level of necessary locking for quite many years now.
  - barrkel 7y ago
    I never said anything about locking out reads. Did I? FWIW, I just tested in Postgres. Locks like I said it does: A: create table parent(id int, value int, unique(id)); A: create table child(id int, parent_id int references parent(id)); A: insert into parent values (1, 10); A: begin; A: insert into child values (1, 1); B: begin; B: select 1 from parent where id = 1 for update; B: (blocks) The situation in MySQL is worse because it'll block updates on any field, not just row locks. I have specific experience of this due to use of database locks at the application level to avoid deadlocks (different lock orders) and inconsistent updates (updates based on reads across multiple tables that may have separate racing updates) by locking rows up front. For understandable schema reasons, what is logically a parent entity is the natural thing to lock, but for understandable performance reasons, FKs to the parent entity are distributed through some fairly large tables.
    - Volundr 7y ago
      Ok, now think this the rest of the way through. Without FKs, how do you, at the application level, ensure that A and B don't commit separate changes (A to child, B to parent) that break consistency? If A tries to insert a child for 1, and B changes the id to 2.... OOPS! And from both's perspective it looks perfectly safe.

barrkel 7y ago

FK consistency can't be guaranteed without ensuring the referenced rows don't disappear before the transaction has been committed. Think about it.

jeltz 7y ago
Yes, but it can be done without locking out reads. The only thing you need to lock against is someone changing the primary key of the referenced row or deleting the row. PostgreSQL has implemented this minimum level of necessary locking for quite many years now.
- barrkel 7y ago
  I never said anything about locking out reads. Did I? FWIW, I just tested in Postgres. Locks like I said it does: A: create table parent(id int, value int, unique(id)); A: create table child(id int, parent_id int references parent(id)); A: insert into parent values (1, 10); A: begin; A: insert into child values (1, 1); B: begin; B: select 1 from parent where id = 1 for update; B: (blocks) The situation in MySQL is worse because it'll block updates on any field, not just row locks. I have specific experience of this due to use of database locks at the application level to avoid deadlocks (different lock orders) and inconsistent updates (updates based on reads across multiple tables that may have separate racing updates) by locking rows up front. For understandable schema reasons, what is logically a parent entity is the natural thing to lock, but for understandable performance reasons, FKs to the parent entity are distributed through some fairly large tables.
  - Volundr 7y ago
    Ok, now think this the rest of the way through. Without FKs, how do you, at the application level, ensure that A and B don't commit separate changes (A to child, B to parent) that break consistency? If A tries to insert a child for 1, and B changes the id to 2.... OOPS! And from both's perspective it looks perfectly safe.