The other day, I was troubleshooting a deadlock, and I wondered if any of the table’s columns were referenced by any foreign keys (fks) from any other tables in the instance.

Well, this is actually very simple with information_schema (I_S):

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME='parent';

Where `parent` is the name of the table you’re searching for.

Note this query does not restrict on the database, or schema, name, but that could easily be added (or any other number of conditions). Here is an example where I only return the most useful columns (which could be useful for determining said conditions):

SELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_SCHEMA,
        TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_SCHEMA,
        REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME='parent';

If there …

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE …