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

Risoluzione

Le query bloccate si verificano perché una transazione in InnoDB è in attesa che un'altra transazione rilasci un blocco. Anche transazioni non effettuate possono bloccare le query. Queste transazioni possono apparire come NULL.

Utilizza la tabella INNODB\ _TRX per identificare le transazioni non effettuate

Per identificare la query o la sessione che blocca la tua query, completa i seguenti passaggi:

1. Per visualizzare le transazioni attualmente in esecuzione, esegui la seguente query sulla tabella INNODB_TRX:

   select * from information_schema.innodb_trx\G

2. Per visualizzare le transazioni in attesa e le transazioni che le bloccano, esegui una delle seguenti query in base alla tua versione di MySQL.
   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;
   

   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 può procedere solo quando l'altra transazione viene confermata o ripristinata. Quando la sessione che ha emesso la query è inattiva, viene riportato un valore NULL per la query bloccante. In questo caso, utilizza la query del passaggio 2 per trovare l'ID del processo (PROCESSLIST_ID) del thread bloccante (blocking_thread).

3. Per MySQL 5.7 o versioni successive, esegui la seguente query per determinare l'ID del processo (PROCESSLIST_ID) della transazione bloccante mediante l'ID del thread (THREAD_ID) bloccante sostitutivo:

   SELECT PROCESSLIST_ID FROM performance_schema.threads where THREAD_ID = blocking;

4. Esegui la seguente query nella tabella events_statements_current con il valore THREAD_ID per determinare l'ultima query eseguita dal thread:

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

   Nota: sostituisci a THREAD_ID il valore che hai ottenuto nel passaggio 3.

5. Esegui il comando seguente per interrompere la transazione:

   CALL mysql.rds_kill(PROCESSLIST_ID);

   Nota: interrompere o annullare una transazione di lunga durata richiede molto tempo e molte operazioni di I/O.

Identifica le transazioni XA bloccanti

Se blocking_thread è 0 e CALL mysql.rds_kill(PROCESSLIST_ID); non funziona, il blocco potrebbe essere dovuto a una transazione XA.

Per identificare la transazione XA che causa il blocco, esegui il seguente comando:

XA RECOVER;

Il comando precedente restituisce un output per le transazioni XA che si trovano nello stato Prepared (Preparato). Per interrompere il blocco, utilizza la colonna dati per eseguire il commit o il ripristino della transazione:

XA RECOVER;

+----------+--------------+--------------+--------------------+
| formatID | gtrid_length | bqual_length | data               |
+----------+--------------+--------------+--------------------+
|   1      |           10 |            0 | RePostTest         |
+----------+--------------+--------------+--------------------+
1 row in set (0.00 sec)

XA COMMIT 'RePostTest';
XA ROLLBACK 'RePostTest';

Se ricevi il seguente errore quando esegui il commit o il ripristino, converti l'XID in formato esadecimale e ripristina la transazione:

"ERROR 1397 (XAE04): XAER_NOTA: Unknown XID"

Per ripristinare la transazione, esegui il seguente comando per recuperare gtrid_length e bqual_length ed estrarre le parti necessarie del campo dati:

mysql> xa recover convert xid;
+----------+--------------+--------------+----------------------------------------+
| formatID | gtrid_length | bqual_length | data                                   |
+----------+--------------+--------------+----------------------------------------+
|   1      |           10 |            0 | 0x5265506F737454657374                 |
+----------+--------------+--------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> XA ROLLBACK X'5265506F737454657374';
Query OK, 0 rows affected (0.01 sec)

Per ulteriori informazioni su come convertire i valori XID, consulta XA transaction SQL statements sul sito web di MySQL.

Informazioni correlate

Terminare una sessione o una query per RDS per MySQL

Opzioni per le istanze database MySQL

Tabella INFORMATION_SCHEMA_INNODB_TRX sul sito web di MySQL

Identifying blocking transactions sul sito web di MySQL

Attività DBA comuni per istanze database MySQL