Gestire lock di tabelle in MySQL

Una delle problematiche tipiche che un DBMS deve saper gestire è la concorrenza di diversi utenti in operazioni simultanee di lettura ed aggiornamento dei dati.

Al fine di descrivere la problematica, si pensi a due utenti che, simultaneamente, leggano il medesimo dato per effettuarne l’aggiornamento. E’ evidente che uno dei due utenti riuscirà per primo nell’operazione, con la conseguenza che per l’aggiornamento effettuato dal secondo utente la situazione risulterà variata rispetto al momento iniziale, con il rischio che ciò generi eventuali incongruenze nei dati.

Un’altra ipotesi di scuola è quella relativa ad una sequenza di passaggi logici sui dati, strettamente collegati tra loro, che debbano essere eseguiti in sequenza pena l’invalidità dell’intera operazione. In casi come questi è necessario che l’intera procedura si blocchi in caso si verifichi un intoppo in uno dei passaggi logici che la compongono.

Al fine di gestire correttamente situazioni come queste, MySQL ha introdotto i lock e le transazioni.

I Lock

Attraverso un lock è possibile vincolare l’utilizzo di un’intera tabella o di una singola riga ad uno specifico utente per il tempo necessario allo svolgimento di una determinata operazione. In questo modo si esclude la possibilità che due o più utenti possiano svolgere operazioni concorrenti e simultanee ingenerando conflitti ed incongruenza sui dati.

Si parla, quindi, di table lock quando il vincolo riguarda un’intera tabella e di row lock quando riguarda una singola riga. E’ bene precisare che MyISAM supporta unicamente i lock a livello di tabella, quindi se volete utilizzare i lock a livello row dovete utilizzare lo storage engine InnoDB.

Ancora, i lock possono essere di due tipi:

• in lettura: l’utente che ottiene un lock in lettura ha la garanzia che nessuno (nemmeno lui) potrà compiere operazioni di aggiornamento sulla tabella sino a quando il lock non verrà rilasciato: nessuno può modificare i dati, ma tutti hanno facoltà di accedervi in lettura;
• in scrittura: l’utente che ottiene un lock in scrittura ha la possibilità di leggere e modificare i dati in esclusiva; sino a quando il lock non verrà rilasciato, infatti, nessun altro potrà accedere a quei dati ne in inettura ne in scrittura.

Impostare un table lock in MySQL

Per impostare un lock a livello di tabella si fa ricorso al comando LOCK TABLES seguito dal nome della tabella e dal tipo di vincolo che si vuole creare (READ o WRITE).

Ad esempio:

LOCK TABLES acquisti READ;

Se per finalità applicative è necessario applicare un lock a più tabelle, è necessario farlo all’interno del medesimo comando (separando i nomi delle diverse tabelle con una virgola). E’ importante ricordare, infatti, che ogni volta che utilizziamo LOCK TABLES i lock precedenti vengono rilasciati. Vediamo un esempio di lock su più tabelle:

LOCK TABLES acquisti READ, guadagni WRITE;

Nel momento in cui viene attivato un lock è possibile lavorare solo sulle tabelle cui esso è associato e non su altre tabelle! Nel nostro esempio, quindi, le query potranno riguardare solo le tabelle "acquisti" e "guadagni".

Lock e Alias

Quando si imposta un lock è possibile specificare degli alias per le tabelle coinvolte. In questo caso dovremo accedere alle tabelle utilizzando gli stessi alias definiti in fase di assegnazione del lock.

Qualora una tabella sia presente più volte all’interno di una query, avremo necessariamente bisogno di avere più di un alias: di conseguenza dovremo ottenere un lock per ciascun alias utilizzato, sebbene la tabella coinvolta sia sempre la stessa.

Le clausole LOCAL e LOW_PRIORITY

La clausola LOCAL può essere applicata ad un LOCK … READ: in questo caso gli altri utenti avranno la facoltà di modificare i dati nella tabella qualora non vadano in conflitto con la lettura che beneficia del lock.

La clausola LOW_PRIORITY può essere applicata ad un LOCK … WRITE: in questo caso verrà data precedenza ad eventuali lock in lettura (che, normalmente, sarebbero subordinati a quello in scrittura).

Rilasciare i lock

Una volta completate le operazioni sulle tabelle coinvolte è possibile rilasciare esplicitamente il lock mediante il comando:

UNLOCK TABLES;

E’ bene ricordare che, anche in mancanza di un unlock, i lock vengono rilasciati automaticamente nei seguenti casi:

• quando viene impostato un nuovo lock;
• alla chiusura della connessione col database.

Un esempio completo di table lock in MySQL

Bisogna ricordare che i lock sono uno strumento potente ed utile ma non bisogna abusarne. L’utilizzo dei lock, infatti, dovrebbe essere limitato ai casi di effettivo bisogno, cioè quei casi nei quale l’effettiva concorrenza di operazioni simultanee potrebbe causare una anomalia. Facciamo un esempio pratico:

LOCK TABLES acquisti WRITE, affiliati WRITE;
INSERT INTO acquisti (giorno,oggetto,euro,id_affiliato) VALUES (CURDATE(),'Apple iPhone',550,123);
SELECT SUM(euro) INTO @tot FROM acquisti WHERE id_affiliato = 123;
UPDATE affiliati SET guadagno = @tot WHERE id = 123;
UNLOCK TABLES;

Nel nostro caso abbiamo lockato le due tabelle "acquisti" e "affiliati" al fine di registrare un nuovo acquisto all’interno del nostro ipotetico negozio on-line e, contestualmente, aggiornare i guadagni dei nostri affiliati senza che eventuali acquisti simultanei, generati con lo stesso "id_affiliato", possano compromettere l’integrità del conteggio.

Impostare un lock a livello di riga in MySQL

Lo storage engine InnoDB, a differenza di MyISAM, supporta il cosidetto multiple granularity locking (MGL), in quanto consente di impostare dei lock con differenti livelli di granularità: a livello di tabella (come MyISAM) o anche a livello di singola riga (o addirittura a livello di singola cella).

Poter impostare dei lock a livello di riga comporta che una molteplicità di transazioni potranno leggere e/o scrivere simultaneamente all’interno della medesima tabella. Questa peculiarità rende evidente come InnoDB sia la scelta consigliata se si sviluppano applicazioni con un numero considerevole di INSERT e UPDATE: in un simile scenario, a differenza di quanto accadrebbe con MyISAM, non si corre il richio che un numero elevato di SELECT restino bloccate in attesa del rilascio di LOCK impostati a livello di tabella.

Nella prossima lezione vedremo, appunto, come gestire le transazioni nelle tabelle InnoDB e come applicare lock a livello di riga.