Locking delle tabelle in MySQL

Il procedimento detto Locking ("blocco" o anche "uso esclusivo") delle tabelle o dei record di un database rappresenta un soluzione grazie alla quale è possibile risolvere il problema della "concorrenza" (o "collo di bottiglia"), cioè quel fenomeno che si verifica quando più di un processo cerca di manipolare i dati contenuti all'interno di uno stesso archivio.

Il database manager MySQL mette a disposizione due tipologie differenti per il locking:

• Locking a carico delle tabelle, in cui intere tabelle vengono riservate per l'esecuzione di determinati processi;
• Locking a carico dei records, in cui soltanto i records specificati sono riservati per determinati processi.

Nello stesso modo, il DBMS è in grado di gestire due diverse procedure di locking:

• Internal Locking: può essere effettuato direttamente tramite MySQL per la gestione delle priorità durante i processi simultanei; viene definito "Locking interno" perché essendo completamente gestito dal database server stesso non necessita di programmi esterni per il controllo della concorrenza.
• External locking: viene effettuato sinergicamente e in modo coordinato dal database server e da un programma esterno (da cui la definizione "Locking esterno"), entrambi partecipano nello stabilire quali applicazioni possono accedere ai dati e in quale momento.

Il locking rappresenta una soluzione ottimale sia per una gestione razionale dei processi che per il risparmio delle risorse messe a disposizione da un sistema, è bene però stabilire in quali casi esso dovrebbe essere applicato e in quali risulterebbe invece una procedura superflua o addirittura controproducente.

Tipologie di uso esclusivo delle tabelle

Il locking si basa su specifiche tipologie di variabili chiamate Locks e dotate di particolari proprietà, un Database server MySQL con impostazioni predefinite presenta di default una variabile Lock associata ad ogni tabella.; nel momento in cui un utilizzatore impiega una variabile Lock per una specifica tabella, nessun altro utente potrà effettuare operazioni su di essa.

Per ogni tabella saranno possibili due tipologie di Lock dette anche locktypes:

• READ Locks (o "Locking in lettura"), che permettono l'accesso alle tabelle in sola lettura;
• WRITE Locs (o "Locking in scrittura"), che permettono l'accesso alle tabelle in lettura e scrittura.

Nel primo caso, a tutti gli utenti di MySQL sarà accordato il permesso di leggere il contenuto di una determinata tabella, ma nessuno di essi, compreso l'utilizzatore che ha impostato il Lock, avrà il permesso di apportare modifiche a carico della struttura dei dati; non sarà possibile definire Lock di tipo READ quando su una tabella sussiste già un blocco di tipo WRITE.

Nel secondo caso, locking in scrittura, soltanto l'utilizzatore che ha impostato il Lock potrà accedere alla tabella bloccata in lettura, mentre gli altri utenti si vedranno negato anche il permesso di lettura. Non è possibile impostare blocchi di tipo WRITE su tabelle in cui già sussiste un vincolo di tipo READ.

Alle due tipologie appena elencate corrispondono due ulteriori procedure di locking meno vincolanti rispetto a quelle in lettura e scrittura:

• READ LOCAL Locks: sono molto simili ai Locks di tipo READ ma consentono le esecuzioni di istruzioni basate su INSERT quando queste non alterano i records preesistenti;
• LOW PRIORITY WRITE: funzionano in modo molto simile ai blocchi di tipo WRITE, ma a seconda dei casi gli altri utilizzatori saranno vincolati ad un nuovo Lock in lettura durante i tempi di attesa al termine dei Locks di tipo READ o WRITE definiti.

Le variabili Lock nascono per soddisfare una semplice ma fondamentale esigenza: evitare che due utilizzatori apportino contemporaneamente delle modifiche sugli stessi dati.

Quando procedere al locking delle tabelle

Innanzitutto è importante sottolineare il fatto che le applicazioni che prevedono semplici operazioni di inserimento, cancellazione o modifica di un record e che non utilizzano risultati prodotti da precedenti queries di interrogazione non necessitano di procedure di locking a supporto.

In generale è possibile dire che il locking è richiesto soltanto nei casi in cui un'applicazione è stata concepita in modo da operare una lettura dei dati da una tabella per poi scrivere questi valori sul database. Il locking di una tabella è per esempio necessario quando vengono lanciate istruzioni tra di loro interdipendenti, una situazione in cui modifiche sugli stessi dati effettuate da altri utilizzatori pregiudicherebbero l'integrità dei risultati (concorrenza), come nel caso in cui un'istruzione SELECT precede un comando di aggiornamento o di cancellazione.

Un esempio classico di concorrenza nell'uso delle tabelle da parte di utenze multiple è quello delle applicazioni utilizzate a supporto dei sistemi di prenotazione: due agenzie di viaggio o due portali dedicati al booking online non dovrebbero consentire a due utenti differenti di prenotare la stessa stanza d'albergo nello stesso momento.

Le procedure di locking sono quindi fondamentali nelle operazioni di prevenzione della concorrenza. Istruzioni che prevedono l'utilizzo di comandi come SELECT, UPDATE, INSERT o DELETE possono essere impedite grazie al ricorso alle variabili Lock; se su una tabella sussiste un vincolo in scrittura questa non risponderà a interrogazioni, non consentirà cancellazioni, aggiornamenti o inserimenti nè permetterà la definizione di ulteriori blocchi.

In generale, è possibile elencare quattro regole fondamentali alla base dell'utilizzo del locking a carico delle tabelle:

1. un utilizzatore può rilasciare tutti i blocchi definiti al termine di una transazione;
2. nel caso in cui un utilizzatore necessiti di definire un blocco, questo dovrà essere impostato su tutte le tabelle coinvolte in una transazione;
3. nel caso in cui un utilizzatore desideri scrivere dati all'interno di una tabella e questa sia coinvolta in una transazione a rischio di concorrenza, dovrà essere impostato un blocco in scrittura;
4. nel caso in cui un utilizzatore desideri accedere ad una tabella per la lettura dei dati contenuti in essa e questa sia coinvolta in una transazione a rischio di concorrenza, dovrà essere impostato un blocco in lettura.

In pratica le regole elencate spiegano che, quando si desidera scrivere su una tabella, è buona norma che nessun altro utilizzatore possa accedere ai dati prima che questi vengano modificati; nello stesso modo, se si desidera leggere il contenuto di una tabella è bene che nessuno apporti nello stesso tempo delle modifiche.

Nel caso in cui si desideri effettuare delle operazioni di inserimento e di selezione su una determinata tabella in una situazione in cui, grazie all'utilizzo del locking, non siano accettabili istruzioni concorrenti, sarà possibile ricorrere all'impiego delle tabelle temporanee come nell'esempio seguente:

mysql> LOCK TABLES tabella WRITE, tabella_temporanea WRITE;
mysql> INSERT INTO tabella SELECT * FROM tabella_temporanea;
mysql> DELETE FROM tabella_temporanea;
mysql> UNLOCK TABLES;

I passi da seguire sono quindi molto semplici:

1. impostare il blocco tramite il comando LOCK seguito dall'argomento WRITE per entrambe le tabelle;
2. effettuare l'istruzione d'inserimento nella tabella "fisica" recuperando i dati dalla tabella temporanea tramite un'istruzione SELECT;
3. cancellare la tabella temporanea ormai inutile;
4. sbloccare la tabella rimuovendo i vincoli con il comando UNLOCK TABLES.

Casi di utilizzo dell'External Locking

L'External Locking è una procedura che viene utilizzata quando non può essere utilizzato un singolo processo (ad esempio quello prodotto da MySQL) che richieda l'accesso alle tabelle; fondamentalmente si hanno due casi in cui può essere conveniente ricorrere all'External Locking:

• la dove si utilizzano simultaneamente più server per l'accesso alla stessa database directory; ciascun server dovrà aver abilitata una procedura di External Locking, si tratta però di una configurazione non consigliata;
• quando si utilizza il comando "myisamchk"per le operazioni di manutenzione delle tabelle di tipo MyISAM.

Quando è in funzione un locking esterno, ad ogni processo che richiede l'accesso ai dati è associato un blocco a livello di filesystem relativamente ai file della tabella interessata; nel caso in cui non siano disponibili tutti i blocchi necessari, l'accesso verrà impedito fino a quando la disponibilità dei Lock non sarà completa.

Le procedure di External Locking hanno lo svantaggio di ridurre in alcuni casi le prestazioni generali de DBMS in quanto questo è spesso vincolato all'attesa dell'esecuzione di altri processi prima di poter accedere alle tabelle.

In generale è possibile affermare che l'External Locking non è necessario nel caso in cui sia in esecuzione un singolo server per l'accesso ai dati e nel caso in cui non vi siano altre applicazioni che necessitano di apportare modifiche alle tabelle mentre il DBMS risponde alle chiamate degli utilizzatori. Nello stesso modo non è necessario l'External Locking per la lettura dei records in tabella tramite l'utilizzo di programmi esterni.

L'uso dell'External Locking può essere controllato al memento dell'avvio del Database Server utilizzando gli argomenti --external-locking o --skip-external-locking option disponibili a partire dalla versione 4.0.3 di MySQL.

Conclusioni

Il locking delle tabelle è uno strumento messo a disposizione da MySQL per la soluzione delle problematiche che possono derivare dall'utilizzo concorrente da parte di più processi della stessa struttura di dati. In questa breve trattazione sono state descritte le diverse procedure disponibili per il locking delle tabelle ponendo particolare attenzione ai casi in cui esse devono essere impiegate e a quelli in cui esse sono inutili o addirittura svantaggiose. Per maggiori informazioni, è possibile consultare la sezione dedicata alle procedure di locking delle tabelle pubblicata sul manuale ufficiale di MySQL.