Where can I find more detailed MySQL deadlock logs?

Question

We have a MySQL database in AWS RDS that was migrated recently from 5.7 to 8. One of our microservices running in ECS executes bootstrapping code at launch to iterate through a large list of table names and issue a CREATE TABLE IF NOT EXISTS statement on each one.

Since the migration, this bootstrapping code will predictably cause a deadlock during this loop:

*************************** 1. row ***************************
     ERROR_NUMBER: 1213
       ERROR_NAME: ER_LOCK_DEADLOCK
        SQL_STATE: 40001
 SUM_ERROR_RAISED: 30
SUM_ERROR_HANDLED: 0
       FIRST_SEEN: 2024-11-25 15:12:29
        LAST_SEEN: 2024-11-25 21:11:51

The deadlocks do not show up in the InnoDB status output, so I'm assuming this may be a result of metadata locking. I would appreciate if someone could please point me in the direction of where I could find any kind of additional logging about these deadlocks.

Answer 1

You can identify which session holds the metadata lock through "show full processlist". Then you can query the tables "information_schema.INNODB_TRX", "performance_schema.metadata_locks", and "performance_schema.data_locks" to obtain more information.