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.
Fedora 24 beta entra nella fase di freeze
Il ciclo di sviluppo di Fedora 24 procede in modo rapido e senza intoppi e il team della distribuzione, tramite un annuncio pubblicato da Dennis Gilmore nella mailinglist del pr...
Promoted by Prime Real Time
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 sono disponibili su tabelle InnoDB – lo Storage Engine di default a partire dalla versione 5.5 del DBMS – oltre che su NDB, un meccanismo dedicato ai Cluster che vedremo in seguito. Al contrario, non possono essere utilizzate su tabelle MyISAM.
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.
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.
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. È 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.