4 ms·
Old best practice for InnoDB performance was actually to never use foreign keys because of these locking issues. Not sure if that's the case in 2021.
by ericbarrett 5y ago
Old best practice for InnoDB performance was actually to never use foreign keys because of these locking issues. Not sure if that's the case in 2021.
- noduerme 5y agoInnoDB uses row-level locking, and foreign keys are (usually) a great feature to ensure data integrity. But using multiple foreign keys from tables `a`,`b` as a composite index for table `x` can cause deadlock if both are being updated in rapid succession, because an update on `a` gets a lock on `x`, which needs a read lock on `b` which is waiting for the lock from `a` to be released. I try to never structure multiple foreign keys as a composite index.
- bt1a 5y agoThis is fascinating, thanks for sharing.