Perché una query sulla mia istanza database MySQL di Amazon RDS è stata bloccata se non c'erano altre sessioni attive?

Breve descrizione

Le query bloccate possono verificarsi perché una transazione in InnoDB è in attesa che un'altra transazione rilasci un blocco. Le query possono essere bloccate anche a causa di transazioni non effettuate. Queste transazioni possono apparire come NULL. Segui questi passaggi per identificare la query o la sessione che potrebbe bloccare la tua query.

Soluzione

Identificare le transazioni non effettuate

1.    Visualizza le transazioni attualmente in esecuzione eseguendo questa query sulla tabella INNODB_TRX:

select * from information_schema.innodb_trx\G

2.    Esegui questa query per vedere quali transazioni sono in attesa e quali transazioni le stanno bloccando.

Per MySQL 5.7 e versioni precedenti:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

Per MySQL 8.0:

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

Nota: La transazione bloccata non può procedere finché l'altra transazione non viene confermata o ripristinata.

Quando si identificano le transazioni di blocco, viene riportato un valore NULL per la query di blocco se la sessione che ha emesso la query è diventata inattiva. In questo caso, utilizza la query nel passaggio 2 per trovare la l'ID della processlist blocking_thread.

3.    Per MySQL 5.7 o versioni successive, esegui questa query per determinare il THREAD_ID della transazione di blocco tramite l'ID della processlist blocking_thread sostitutivo:

SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = blocking_thread;

4.    Utilizzare THREAD_ID per interrogare la tabella dello schema delle prestazioni events_statements_current. Questo determina l'ultima query eseguita dal thread.

Nota: Assicurati di sostituire THREAD_ID con il valore restituito nel passaggio 3.

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID = THREAD_ID;

5.    Dopo aver identificato la sessione di blocco o l'ID thread, interrompi la transazione eseguendo questa procedura:

Nota: Prima di eseguire questa procedura, valuta se è necessaria la transazione o se è sicuro interromperla.

CALL mysql.rds_kill(thread-ID);

Nota: L'interruzione o il ripristino di un'operazione di lunga durata può richiedere molto tempo e un traffico I/O intenso.

Informazioni correlate

Terminare una sessione o una query

Opzioni per le istanze database MySQL

La tabella INFORMATION_SCHEMA_INNODB_TRX sul sito web di MySQL

Identifying blocking transactions sul sito web di MySQL 

Attività di log DBA comuni per istanze database MySQL