Transazioni e lock

Una delle classiche problematiche che un DBMS deve gestire è l’accesso simultaneo ai dati da parte di diversi utenti, sia in lettura che in aggiornamento.

Una situazione tipica di questo genere è il caso in cui, ad esempio, due utenti leggono lo stesso dato con l’intenzione di aggiornarlo: evidentemente uno dei due lo farà per primo, e a quel punto il secondo utente, quando tenterà a sua volta un aggiornamento, troverà una situazione variata rispetto al momento in cui aveva letto i dati, col rischio di creare situazioni incongruenti.

Un’altra classica situazione che pone dei problemi è quella in cui un’applicazione deve effettuare più aggiornamenti logicamente collegati fra loro, tanto da richiedere che tutti gli aggiornamenti siano annullati qualora uno solo di essi dovesse fallire.

Le soluzioni per questi problemi sono, nella forma più semplice, i lock sulle tabelle, e in quella più avanzata le transazioni. Queste ultime non sono disponibili sullo storage engine tradizionale di MySQL, il MyISAM, ma solo su tabelle InnoDB e BDB.

Cominciamo con l’analisi dei lock, che possiamo considerare dei vincoli di “uso esclusivo” che un utente può ottenere su determinate tabelle per il tempo necessario a svolgere le operazioni che gli sono necessarie. Con i lock si possono simulare (parzialmente) transazioni, o in alcuni casi semplicemente velocizzare le operazioni di scrittura, qualora vi siano grosse moli di dati da inserire. L’uso dei lock è consigliato solo con le tabelle di tipo MyISAM, che non supportano le transazioni.

Un lock può essere richiesto in lettura o in scrittura: nel primo caso l’utente ha la garanzia che nessuno farà aggiornamenti sulla tabella bloccata fino a quando non sarà rilasciato il lock, ma agli altri utenti viene comunque lasciata la possibilità di leggere sulla stessa tabella. In questo caso però nemmeno l’utente che ha ottenuto il lock può fare aggiornamenti.

Il lock in scrittura invece impedisce agli altri utenti qualsiasi tipo di accesso alla tabella, e consente all’utente che l’ha ottenuto operazioni di lettura e scrittura.

Vediamo la sintassi delle operazioni di lock, ricordando che esse richiedono il privilegio LOCK TABLES nonché quello di SELECT sulle tabelle interessate:

LOCK TABLES
    tabella [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tabella [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...

Innanzitutto notiamo che è possibile effettuare il lock su più tabelle con un’unica istruzione LOCK TABLES; in realtà, più che di una possibilità si tratta di un obbligo.

Infatti ogni istruzione LOCK TABLES causa il rilascio dei lock ottenuti in precedenza: di conseguenza, se avete bisogno di ottenere lock su più tabelle, siete obbligati a farlo con un’unica istruzione.

Ad ogni tabella di cui chiediamo il lock è possibile associare un alias, esattamente come nelle query: anche in questo caso siamo vincolati ad usare questo sistema qualora le query che ci accingiamo ad effettuare utilizzino gli alias. In pratica, dopo avere ottenuto un lock, le nostre query possono utilizzare solo le tabelle su cui abbiamo i lock: non è possibile quindi, in presenza di lock attivi, accedere ad altre tabelle; inoltre, a queste tabelle dovremo accedere utilizzando gli stessi alias definiti in fase di lock. Qualora una tabella sia presente più volte in una query, avremo evidentemente bisogno di più di un alias: di conseguenza dovremo ottenere un lock per ogni alias, sebbene la tabella sia la stessa.

La clausola LOCAL associata ad un READ lock consente ad altri utenti di effettuare inserimenti che non vadano in conflitto con le nostre letture. La clausola LOW_PRIORITY associata ad un WRITE lock fa sì che la richiesta dia la precedenza alle richieste di lock in lettura (normalmente invece un lock in scrittura ha priorità più alta).

I lock ottenuti vengono rilasciati con l’istruzione:

UNLOCK TABLES

In realtà abbiamo già visto che anche una nuova richiesta di lock causa il rilascio dei precedenti; inoltre i lock vengono rilasciati automaticamente alla chiusura della connessione, qualora non sia stato fatto esplicitamente.

Le transazioni

Passiamo ora alle transazioni, con particolare riferimento alle tabelle InnoDB.

L’uso delle transazioni permette di “consolidare” le modifiche alla base dati solo in un momento ben preciso: dal momento in cui avviamo una transazione, gli aggiornamenti rimangono sospesi (e invisibili ad altri utenti) fino a quando non li confermiamo (commit); in alternativa alla conferma è possibile annullarli (rollback).

Innanzitutto va segnalato che MySQL gira di default in autocommit mode: questo significa che tutti gli aggiornamenti vengono automaticamente consolidati nel momento in cui sono eseguiti. Se siamo in autocommit, per iniziare una transazione dobbiamo usare l’istruzione START TRANSACTION; da questo punto in poi tutti gli aggiornamenti rimarranno sospesi. La transazione può essere chiusa con l’istruzione COMMIT, che consolida le modifiche, oppure con ROLLBACK, che annulla tutti gli aggiornamenti effettuati nel corso della transazione. Possiamo utilizzare anche COMMIT AND CHAIN o ROLLBACK AND CHAIN, che provocano l’immediata apertura di una nuova transazione, oppure COMMIT RELEASE o ROLLBACK RELEASE, che oltre a chiudere la transazione chiudono anche la connessione al server.

Con l’istruzione SET AUTOCOMMIT=0 possiamo disattivare l’autocommit: in questo caso non è più necessario avviare le transazioni con START TRANSACTION, e tutti gli aggiornamenti rimarranno sospesi fino all’uso di COMMIT o ROLLBACK.
All’interno di una transazione è anche possibile stabilire dei savepoint, cioè degli stati intermedi ai quali possiamo ritornare con una ROLLBACK, invece di annullare interamente la transazione.

Vediamo un esempio:

START TRANSACTION
istruzioni di aggiornamento (1)
SAVEPOINT sp1;
istruzioni di aggiornamento (2)
ROLLBACK TO SAVEPOINT sp1;
istruzioni di aggiornamento (3)
COMMIT

In questo caso, dopo avere avviato la transazione abbiamo eseguito un primo blocco di aggiornamenti, seguito dalla creazione del savepoint col nome ‘sp1′; in seguito abbiamo eseguito un secondo blocco di aggiornamenti; l’istruzione ROLLBACK TO SAVEPOINT sp1 fa sì che “ritorniamo” alla situazione esistente quando abbiamo creato il savepoint: in pratica solo il secondo blocco di aggiornamenti viene annullato, e la transazione rimane aperta; una semplice ROLLBACK invece avrebbe annullato tutto e chiuso la transazione.

La COMMIT effettuata dopo il terzo blocco fa sì che vengano consolidati gli aggiornamenti effettuati nel primo e nel terzo blocco.

È bene ricordare che un utilizzo corretto delle transazioni è possibile solo utilizzando lo stesso tipo di tabelle all’interno di ogni transazione. L’uso simultaneo di tabelle InnoDB e BDB può infatti generare qualche problema nel momento del COMMIT. È altamente sconsigliato ovviamente l’utilizzo di tabelle MyISAM nelle transazioni, in quanto su di esse non è possibile effettuare il ROLLBACK e gli aggiornamenti relativi sono immediatamente effettivi: in caso di ROLLBACK quindi si genererebbero proprio quelle inconsistenze che l’uso delle transazioni mira ad evitare.

Ricordiamo anche che alcuni tipi di operazioni non sono annullabili: in generale tutte quelle che creano, eliminano, o alterano la struttura di database e tabelle. È bene quindi evitare di includere in una transazione tali operazioni, che fra l’altro, nella maggior parte dei casi, causano una implicita COMMIT.

In alcuni casi è utile utilizzare due clausole particolari quando si effettua una SELECT:

SELECT ………. FOR UPDATE;
SELECT ………. LOCK IN SHARE MODE;

La clausola FOR UPDATE stabilisce un lock su tutte le righe lette che impedirà ad altri utenti di leggere le stesse righe fino al termine della nostra transazione; evidentemente si utilizza quando leggiamo un dato con l’intenzione di aggiornarlo. La clausola LOCK IN SHARE MODE invece stabilisce un lock che impedisce solo gli aggiornamenti, garantendoci che il contenuto della riga rimarrà invariato per la durata della nostra transazione.

Isolation level

Un aspetto importante relativamente alle transazioni è il livello di isolamento al quale vengono effettuate. I livelli possibili sono quattro, e li elenchiamo in ordine crescente:

  • READ UNCOMMITTED: a questo livello sono visibili gli aggiornamenti effettuati da altri utenti anche se non consolidati: è un comportamento non propriamente transazionale, che può dare ovviamente seri problemi di consistenza dei dati; va utilizzato solo quando non abbiamo preoccupazioni di questo tipo e abbiamo bisogno di velocizzare le letture
  • READ COMMITTED: a questo livello gli aggiornamenti diventano visibili solo dopo il consolidamento
  • REPETEABLE READ: in questo caso perchè un aggiornamento diventi visibile deve essere non solo consolidato, ma anche la transazione che legge deve essere terminata; in pratica, la stessa lettura ripetuta all’interno di una transazione darà sempre lo stesso risultato; è la modalità di default
  • SERIALIZABLE: come nel caso precedente, ma in più, la semplice lettura di un dato provoca il blocco degli aggiornamenti fino al termine della transazione; in sostanza è come se ogni SELECT venisse effettuata con la clausola LOCK IN SHARE MODE

Il livello di isolamento utilizzato può essere determinato dall’opzione di avvio del server –transaction-isolation (vedere lez. 4, facendo attenzione alla diversa sintassi delle opzioni); per sapere qual è il livello in uso possiamo usare l’istruzione SELECT @@tx_isolation; inoltre possiamo modificarlo con la seguente istruzione:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Se omettiamo le clausole GLOBAL e SESSION la modifica è valida solo per la transazione successiva; con SESSION impostiamo il valore per l’intera connessione, mentre con GLOBAL modifichiamo il valore per il server: tale valore verrà quindi adottato su tutte le connessioni aperte successivamente (non su quelle già aperte); in quest’ultimo caso è necessario il privilegio SUPER.

Per concludere, abbiamo già detto che l’uso di LOCK TABLES è consigliato solo su storage engine non transazionali. Ricordiamo anche che se lo usiamo su storage engine transazionali un’istruzione LOCK TABLES causerà una implicita COMMIT di una eventuale transazione aperta. All’opposto, avviare una transazione provoca l’implicita esecuzione di una UNLOCK TABLES.

Both comments and pings are currently closed.

Comments are closed.