Spiegazione dettagliata della soluzione di blocco USE DB in Mysql

Quando incontriamo un problema, spesso pensiamo a come risolverlo, piuttosto che riflettere sulla causa fondamentale del problema. Questo risultato ci fa ottenere il pesce, ma perdere la pesca. Oggi, condividiamo un caso di riflessione scaturito da un guasto di blocco USE DB.

Descrizione di故障

Oggi un amico ha incontrato un grave problema di database, l'ambiente di故障 come segue:

MYSQL 5.6.16

Livello di isolamento RR

GITD disattivato

La sua espressione è come segue:

use db non può accedere al database

show table status non può查询到表信息

Il schema.processlist mostra molti Waiting for table metadata lock

In una situazione di emergenza, ha ucciso una serie di thread ma non è stato possibile ripristinare, alla fine ha ucciso una transazione non prontamente commitata per tornare alla normalità. Ha lasciato solo un'immagine come quella qui di seguito:

Estrazione delle informazioni di故障

Torniamo all'immagine precedente, possiamo fare una sintesi dei tipi di istruzioni come segue:

1、CREATE TABLE A AS SELECT B

Il suo stato è sending data

2、DROP TABLE A

Il suo stato è Waiting for table metadata lock

3、SELECT * FROM A

Il suo stato è Waiting for table metadata lock

4、 SHOW TABLE STATUS[like 'A']

Il suo stato è Waiting for table metadata lock

Analisi delle informazioni

Analizzare questo caso non è molto facile perché è una combinazione di MDL LOCK a livello MYSQL e row lock in modalità RR innodb, e dobbiamo essere sensibili allo stato schema.processlist.

Consiglio di leggere i miei seguenti articoli per imparare MDL LOCK:

https://it.oldtoolbag.com/article/131383.htm

Questo capitolo utilizza i seguenti due metodi per la verifica di MDL LOCK:

Metodo uno: ho aggiunto output di log nella funzione di bloccaggio del codice sorgente MDL LOCK, se si desidera analizzare il tipo di MDL LOCK delle varie istruzioni, questo è ancora il modo giusto, poiché MDL LOCK spesso scompare rapidamente e performance_schema.metadata_locks non può osservarlo.

Metodo due: osserva con la versione 5.7 di performance_schema.metadata_locks in stato di blocco.

Apri mdl per monitorare il metodo nel P_S:

1. Analisi di 'CREATE TABLE A AS SELECT B' per la tabella B riguardo a 'sending data'

In realtà, lo stato 'sending data' può rappresentare molti significati, dal mio attuale comprensione, è un termine generico utilizzato da MYSQL superiore per le istruzioni di tipo SELECT in fase di interazione dati tra livello INNODB e livello MYSQL, quindi la sua presenza può includere:

In effetti, è necessario accedere a una quantità di dati particolarmente grande, potrebbe essere necessario ottimizzare.

Poiché la acquisizione di row lock a livello di INNODB richiede una attesa, come il comune SELECT FOR UPDATE che incontriamo spesso.

Allo stesso tempo, dobbiamo anche notare che nel modello RR, il modo di bloccare SELECT B è lo stesso di INSERT...SELECT, non viene riportato di seguito:

Dalla sua reazione, poiché ha ucciso un'operazione transazionale a lungo termine alla fine, quindi è il caso 2. E l'intera frase CREATE TABLE A AS SELECT B non può essere eseguita a causa di alcuni database bloccati sulla tabella B, portando l'intera frase a rimanere nello stato sending data.

Secondo, analisi di show table status[like 'A'] Waiting for table metadata lock.

Questo è il punto più importante di questo caso, uno show table status[like 'A'] è stato bloccato, lo stato è Waiting for table metadata lock e attenzione, questo è table perché ci sono molti tipi di MDL LOCK. Ne ho parlato in un articolo sull'introduzione di MDL, dove ho menzionato che quando si descrive una tabella con desc, viene applicato MDL_SHARED_HIGH_PRIO(SH), e anche quando si esegue uno show table status viene applicato MDL_SHARED_HIGH_PRIO(SH) a questa tabella.

Metodo uno

Metodo due

Entrambi i modi possono osservare l'esistenza di MDL_SHARED_HIGH_PRIO(SH) e ho simulato la situazione di blocco.

Ma MDL_SHARED_HIGH_PRIO(SH) è un tipo di MDL LOCK con una priorità molto alta, che si presenta come segue:

Compatibilità:

Priorità della coda di blocco:

Le condizioni che lo bloccano, oltre al blocco di MDL_EXCLUSIVE(X), non ci sono altre possibilità. Quindi questo è un punto di rottura molto importante.

Terzo, analisi dell'aggiunta di MDL LOCK per CREATE TABLE A AS SELECT B sulla tabella A.

Questo è anche qualcosa che non sapevo prima e che ha richiesto più tempo in questo caso, come già analizzato in precedenza, solo uno show table status[like 'A'] che può bloccarsi su MDL_SHARED_HIGH_PRIO(SH) e MDL LOCK può essere bloccato su MDL LOCK, e l'unica possibilità è che la tabella A abbia MDL_EXCLUSIVE(X).

那么我开始怀疑这个DDL语句在语句结束之前会对A表上MDL_EXCLUSIVE(X) ，然后进行实际测试不出所料确实是这样的如下：

Metodo uno

Metodo due

这里比较遗憾在performance_schema.metadata_locks中并没有显示出MDL_EXCLUSIVE(X)，而显示为MDL_SHARED(S)是我们在我输出的日志中可以看到这里做了升级操作将MDL_SHARED(S) 升级为了MDL_EXCLUSIVE(X)。并且由前面的兼容性列表来看，只有MDL_EXCLUSIVE(X)会堵塞MDL_SHARED_HIGH_PRIO(SH)。所以我们应该能够确认这里确实做了升级操作，否则SHOW TABLE STATUS[like 'A'] 是不会被堵塞的。

四、关于SELECT * FROM A Waiting for table metadata lock的分析

也许大家认为SELECT不会上锁，但是那是在innodb 层次，在MYSQL层会上MDL_SHARED_READ(SR) 如下：

Metodo uno

Metodo due

可以看到确实有MDL_SHARED_READ(SR)的存在，当前处于堵塞状态

其兼容性如下：

显然MDL_SHARED_READ(SR) 和MDL_SHARED_HIGH_PRIO(SH)是不兼容的需要等待。

五、关于DROP TABLE A Waiting for table metadata lock的分析

这一点很好分析因为A表上了X锁而DROP TABLE A必然上MDL_EXCLUSIVE(X)锁它当然和MDL_EXCLUSIVE(X)不兼容。如下：

Metodo uno

Metodo due

其中EXCLUSIVE就是我们说的MDL_EXCLUSIVE(X)它确实存在当前处于堵塞

六、为何use db也会堵塞？

如果使用mysql客户端不使用-A选项(或者 no-auto-rehash)在USE DB的时候至少要做如下事情：

1、对db下每个表上MDL (SH) lock如下（调用MDL_context::acquire_lock 这里给出堵塞时候的信息）

Metodo uno

Metodo due

Si può vedere che USE DB è anche bloccato a causa di MDL_SHARED_HIGH_PRIO(SH).

2, aggiungi ogni tabella al table cache e apri la tabella (chiamata open_table_from_share())

Quindi, questa situazione è identica al blocco di SHOW TABLE STATUS[like 'A'], anch'essa causata da incompatibilità del lock MDL.

Analisi e riassunto

Con l'analisi precedente, possiamo riassumere le cause del guasto come segue:

C'è una DML non sottoposta a lungo termine sulla tabella B
L'istruzione aggiunge un lock row innodb su alcuni dati della tabella B.

Il blocco di CREATE TABLE A AS SELECT B è causato dal passaggio 1
In modalità RR, SELECT B inevitabilmente blocca i dati soddisfacenti sulla tabella B, poiché il blocco è già stato impostato, viene innescato l'attesa, lo stato è sending data.

Il blocco di altre istruzioni è causato dal passaggio 2
Poiché CRATE TABLE A AS SELECT B richiede MDL_EXCLUSIVE(X) prima che la tabella A venga creata, questa lock blocca tutte le altre operazioni relative alla tabella A, inclusi DESC/SHOW TABLE STATUS/USE DB (non-A) che impongono solo MDL_SHARED_HIGH_PRIO(SH) lock. Lo stato è uniformemente Waiting for table metadata lock.

Test di simulazione

Ambiente di test:

5.7.14

GITD disattivato

Livello di isolamento RR

Usare lo script:

I passaggi sono i seguenti:

session1 session2 session3 session4------use test;---use test;begin; delete from b;------------use test;create table a asselect * from b;(a causa del blocco del lock row innodb su b tabella)------------show table status like 'a';(a causa del lock MDL su tabella a)------------use test(a causa del lock MDL su tabella a)

L'ultimo stato di attesa che vediamo è il seguente:

Così possiamo perfettamente simulare lo stato online, se uccidiamo l'oggetto nel session1, naturalmente tutto sarà sbloccato, diamo un'occhiata all'output di performance_schema.metadata_locks:

Possiamo vedere l'output sopra, ma è necessario notare che LOCK_TYPE: SHARED non può bloccare LOCK_TYPE: SHARED_HIGH_PRIO (si può fare riferimento all'appendice o agli articoli di analisi MDL LOCK che ho scritto in precedenza). Come analizzato nell'articolo sopra, qui è stata effettivamente eseguita un'operazione di aggiornamento, aggiornata a MDL_EXCLUSIVE(X).

Conclusione

Nel modello RC, anche se la tabella B non viene applicato alcun INNODB ROW LOCK nel CREATE TABLE A SELECT B, se la tabella B è molto grande, la tabella A sarà protetta da MDL_EXCLUSIVE(X), quindi verrà attivata la situazione di attesa di USE DB\SHOW TABLE STATUS.

Non è possibile utilizzare la sintassi CREATE TABLE A SELECT B se non si può abilitare GTID.

Per i sistemi che utilizzano DML/DDL, è necessario prestare attenzione alla concorrenza, come nel caso in cui, se si nota la situazione ad alta concorrenza, si possono trovare modi per evitarla.

Questo caso dimostra nuovamente che le operazioni non completate a lungo termine possono portare a tragedie, quindi si consiglia di monitorare le operazioni che non si completano entro N secondi.

Appendice

MDL LOCK TYPE

Matrice di compatibilità

Matrice di priorità della coda di attesa

Dichiarazione: il contenuto di questo articolo è stato raccolto da Internet, il copyright spetta ai rispettivi autori, il contenuto è stato caricato autonomamente dagli utenti di Internet, il sito web non detiene i diritti di proprietà, non è stato editato manualmente e non assume alcuna responsabilità legale. Se trovi contenuti sospetti di violazione del copyright, ti preghiamo di inviare una e-mail a: notice#oldtoolbag.com (al momento dell'invio dell'e-mail, sostituisci # con @) per segnalare il problema e fornire prove pertinenti. Una volta verificata, il sito web eliminerà immediatamente i contenuti sospetti di violazione del copyright.