Debug MySQL for stuck sessions and transctions

debug.sql

# Run this first
USE INFORMATION_SCHEMA;

# Check the transactions section in:
SHOW ENGINE INNODB STATUS;

# To check about all the locks transactions are waiting for:
SELECT * FROM INNODB_LOCK_WAITS;

# A list of blocking transactions:
SELECT INNODB_LOCKS.* 
FROM INNODB_LOCKS
JOIN INNODB_LOCK_WAITS
  ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID);

# A list of transactions waiting for locks:
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX
WHERE TRX_STATE = 'LOCK WAIT';

# Kill a process in RDS
CALL mysql.rds_kill(<process id>)