Nice article.

Note that there's more than one 'MySQL FOREIGN KEY' implementation. MySQL Ndb Cluster also supports foreign keys with some differences wrt the InnoDB implementation :

- NDB, therefore not limited to a single MySQL Server, shard etc - Not limited to references between tables in a single database - Supports NoAction deferred constraint checks - Cascaded changes Binlogged independently as part of RBR (Nice side effect of reducing replica apply time work) ...

https://dev.mysql.com/blog-archive/foreign-keys-in-mysql-clu...

Some of the issues described wrt DDL limitations are shared.

Many schemas seem to overuse foreign keys perhaps under the assumption that they are required for or accelerate joins?

innodb supports cross-schema foreign keys, there’s no limitation to tables in a single database

can be terrible in mysql 8 though due to metadata locks now extending across foreign key boundaries. this means alter in one schema can block things in other schema if foreign key across databases

speaking of, am surprised that blog post author doesnt discuss the new mysql 8 metadata locking behavior, is new major problem with mysql foreign keys!

You are right about cross schema foreign keys being supported, my mistake.

Note that the 2PC in Ndb is not quite the same as that used for the normal '2PC is blocking' arguments :

  1.  The transaction coordinator (TC) role is 
      automatically taken over in case of the 
      failure of the coordinator during commit.  
      The new coordinator uses the surviving 
      participants' states to quickly decide 
      on Commit or Abort so that locked 
      resources are released.

  2.  As the participants are part of an integrated 
      system, failure of both the coordinator and 
      e.g. the only participant to be aware of the 
      commit decision can be handled correctly.  
      In this case the transaction is quickly 
      aborted by the new coordinator and on recovery 
      the old participant will 'undo' its local
      committed-state before rejoining the cluster.

  3.  Participants' local commit decisions are not 
      coupled directly to lock release / visibility 
      of those decisions.  Locks are released only 
      when the participant-failure-durability of 
      the commit decision is assured (e.g. all 
      replicas have committed).

  4.  Ndb has a third phase (The complete phase) 
      which releases locks on replicas.  This is 
      not the same as the algorithm referred to 
      as three phase commit, so we don't call it 
      three phase commit.  Also, the third Complete 
      phase is not part of the critical commit 
      path - the commit acknowledgement is sent 
      to the application prior to the start of 
      the third phase, so it does not affect 
      the user's experienced commit latency.

Not all '2PC' implementations are created equal.