Arda Solutions s.r.l. | The Informal Blog

keep-calm-and-lock-the-database-2

Già da tempo avevo intenzione di scrivere un articolo che avesse un titolo del genere … Questo perché, qualche settimana fa mi è capitato di dover effettuare un analisi su un database in infrastruttura RAC Oracle riguardo a problemi legati a performance e blocchi apparentemente random che poi (naturalmente) non si sono dimostrati tali ma erano il frutto del lavoro di sviluppatori ignari dell’importanza dei lock in un ambiente relazionale specialmente in un ambiente Oracle, specialmente in un ambiente RAC … Questo post, ha la pretesa mira a fare un po’ di chiarezza sull’accaduto e su come non sottovalutare l’importanza dei lock possa salvarti la vita

Partiamo subito da un assunto: I tipi di lock che esistono negli RDBMS (qui parleremo solo di Oracle) non sono stati creati per dar fastidio agli sviluppatori 🙂 ma per garantire la coerenza del dato e l’accesso multiplo ad una risorsa (per esempio una tabella) condivisa.

I lock posso essere anche richiesti manualmente ma il motore relazionale li definisce implicitamente, cioè in modo automatico. I lock DML automatici, sono specifici data lock, che servono (come già detto) a garantire l’integrità del dato. Lock di questo genere sono richiesti a livello di: riga (TX Lock) oppure a livello di tabella (TM Lock) … Ma che cosa fa scatenare uno piuttosto che un altro tipo di lock? In realtà entrambe i lock vengono automaticamente richiesti quando una DML (insert, update etc)  viene eseguita, ma è bene capire il perché. Quando, per esempio, una transazione esegue un update su una riga: la transazione richiede un lock di tabella e un lock su quella riga: il lock di riga viene richiesto per garantire la coerenza del dato aggiornato mentre il lock di tabella viene richiesto per prevenire che una DDL modifichi la struttura (per esempio) della tabella mentre stiamo eseguendo l’update.

Andando nello specifico del problema, l’analisi riguardava una Stored Procedure che mandava in waiting le altre sessioni, bloccandole. Analizzando il codice e verificando i tipi di lock sulla GV$SESSION (ricordo che siamo in ambiente RAC) il tipo di lock ricorrente era:   Lock Type: TM, LMODE: 3 (Cioè un Lock di Tabella in modalità 3,  SX). Questo tipo di modalità di Lock è definito subexclusive table lock e rappresenta un lock “permissivo” che da la possibilità ad altre sessioni di modificare altre righe della medesima tabella, e allora perché una sessione che richiede un lock di questo tipo costringe le altre sessioni ad accodarsi, bloccandole?

In generale, è bene sapere che, avere lock di questo genere (o avere dei wait per questo tipo di lock) non è quasi mai un “buon segno” ed è indicativo di qualche problema a livello applicativo. L’applicativo genera delle DDL mentre altre DML vengono eseguite (molto brutto, ma mi è accaduto anche questo), oppure nei casi più frequenti avviene che i campi aggiornati abbiamo (o siano) delle Foreign Keys senza relativo indice. In Oracle (diversamente da SQL Server, per esempio) la creazione di oggetti collegati tendenzialmente non è automatica cosi, quando creiamo un FK non viene creato il relativo indice, quando il campo viene aggiornato la sessione richiede un lock di tabella per garantire l’integrità referenziale che però ha un tempo di esecuzione molto alto costringendo le altre sessioni ad un enqueue lock che manda in blocco le sessione successive.

Eseguendo questo script è possibile estrapolare le FK che non hanno un indice associato:

SELECT acc.owner<br />
 || '-> '<br />
 || acc.constraint_name<br />
 || '('<br />
 || acc.column_name<br />
 || '['<br />
 || acc.position<br />
 || '])' "Owner, Name, Column, Position"<br />
 FROM all_cons_columns acc, all_constraints ac<br />
 WHERE ac.constraint_name = acc.constraint_name<br />
 AND ac.constraint_type = 'R'<br />
 AND (acc.owner, acc.table_name, acc.column_name, acc.position) IN<br />
 (SELECT acc.owner, acc.table_name, acc.column_name, acc.position<br />
 FROM all_cons_columns acc, all_constraints ac<br />
 WHERE ac.constraint_name = acc.constraint_name<br />
 AND ac.constraint_type = 'R'<br />
 MINUS<br />
 SELECT table_owner, table_name, column_name, column_position<br />
 FROM all_ind_columns)<br />
ORDER BY ACC.owner, ACC.constraint_name, ACC.column_name, ACC.position;

Inutile dire che questa query ha estratto una miriade di FK senza indice che incidevano sulle prestazioni generali dell’istanza e che in più erano responsabili della creazione dei blocchi incriminati. Il Mantra quindi che portiamo dietro conclusa quest’analisi è il seguente (estratto dalla documentazione oracle ufficiale):

The TM Oracle metric also known as TM locks are table locks. Waits for these locks are usually due to application issues, possibly because foreign key constraints have not been indexed.

TM (DML enqueue lock): This is a general table lock. Every time a session wants to lock a table (for an UPDATE, INSERT, or DELETE), a TM enqueue is requested. These locks are normally of very short duration, but they can be held for long periods when updating a table when foreign-key constraints have not been properly indexed.

Carrellata di istruzioni utili per capire il fenomeno

E’ bene evidenziare anche un ulteriore aspetto: non indicizzare le FK richiede un consumo di CPU estremamente intensivo causato dall’accesso in Full Table Scan oppure per calcolare le referenzialità. Infatti, ad ogni esecuzione di questa Stored Procedure e monitorando i processi a livello di sistema operativo, c’era un picco enorme che terminava solo con il kill delle sessioni incriminate. Se vi dovesse capitare, potete verificare i processi oracle che consumano di più (per ogni nodo RAC) utilizzando la seguente istruzione shell:

ps -e -o pcpu,pid,user,tty,args |grep -i oracle|sort -n -k 1 -r|head

Se le sessioni continuano a macinare senza soluzione di continuità potete verificare con questa query quale sessione (o sessioni) blocca le altre:

SELECT s1.username || '@' || s1.machine</p>
<p> || ' ( SID=' || s1.sid || ' ) is blocking '<br />
 || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status<br />
 FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2<br />
 WHERE s1.sid=l1.sid AND s2.sid=l2.sid<br />
 AND l1.BLOCK=1 AND l2.request > 0<br />
 AND l1.id1 = l2.id1<br />
 AND l2.id2 = l2.id2 ;

Con questa query, potete vedere le sessioni che hanno un lock (o richiedono un lock) di tipo TM:

SELECT sid, id1 FROM gv$lock WHERE TYPE='TM';

E, in fine, una volta verificate quali sono le sessioni in lock verificare su quali oggetti questo lock è stato richiesto (è necessario specificare nella where l’ID di sessione):

SELECT object_name FROM dba_objects WHERE object_id=20127

Buon Lavoro!

Leave a Reply