7. Gestione dell'accesso simultaneo ai dati

Finora abbiamo utilizzato tabelle di cui eravamo gli unici utenti. In pratica, su una macchina multiutente, i dati sono molto spesso condivisi tra diversi utenti. Ciò solleva la domanda: chi può utilizzare una determinata tabella e in quale veste (query, inserimento, cancellazione, aggiunta, ...)?

7.1. Creazione di utenti Firebird

Quando abbiamo lavorato con IB-Expert, abbiamo effettuato l'accesso come utente SYSDBA. Queste informazioni sono disponibili nelle proprietà della connessione aperta al DBMS:

A destra, vediamo che l'utente connesso è [SYSDBA]. Quello che non vediamo è la sua password [masterkey]. [SYSDBA] è un utente speciale di Firebird: ha privilegi completi su tutti gli oggetti gestiti dal DBMS. È possibile creare nuovi utenti in IBExpert utilizzando l'opzione [Strumenti / Gestione utenti] o l'icona seguente:

Si aprirà la finestra di gestione degli utenti:

Il pulsante [Aggiungi] consente di creare nuovi utenti:

Creiamo i seguenti utenti:

nome utente	password
ADMIN1	admin1
ADMIN2	admin2
SELECT1	select1
SELECT2	select2
AGGIORNA1	aggiorna1
AGGIORNA2	aggiornamento 2

7.2. Concessione dei diritti di accesso agli utenti

Un database appartiene all'utente che lo ha creato. I database che abbiamo creato finora appartenevano all'utente [SYSDBA]. Per illustrare il concetto di autorizzazioni, creiamo (Database / Create Database) un nuovo database con l'identità [ADMIN1, admin1]:

e registriamolo con l'alias DBACCES (ADMIN1). L'uso degli alias consente di aprire connessioni allo stesso database con nomi utente diversi, rendendoli più facili da identificare nell'esploratore di database di IBExpert. :

Ora creiamo le seguenti due tabelle, TA e TB:

Tabella TA

Tabella TB

Queste tabelle non sono correlate tra loro.

Utilizzando IB-Expert, creiamo una seconda connessione al database [DBACCES], questa volta con il nome [ADMIN2 / admin2]. Per farlo, useremo l'opzione [Database / Registra database]:

Selezionare DBACCES(ADMIN2) e aprire un editor SQL (Shift + F12):

Avremo la possibilità di utilizzare diverse connessioni allo stesso database [DBACCES]. Per ciascuna di esse, avremo a disposizione un editor SQL. In [1], l'editor SQL visualizza l'alias del database connesso. Utilizza questa informazione per determinare in quale editor SQL ti trovi. Questo è importante perché creeremo connessioni che non hanno gli stessi diritti di accesso agli oggetti del database.

Interroghiamo il contenuto della tabella TA:

Riceviamo il seguente messaggio di errore:

Cosa significa? Il database [DBACCESS] è stato creato dall'utente [ADMIN1] ed è quindi di sua proprietà. Solo lui ha accesso ai vari oggetti presenti in questo database. Può concedere diritti di accesso ad altri utenti utilizzando il comando SQL GRANT. Questo comando ha diverse sintassi. Una di queste è la seguente:

`sintassi`	GRANT privilegio1, privilegio2, ...\| ALL PRIVILEGES ON tabella/vista A utente1, utente2, ...\| PUBBLICO [ CON OPZIONE DI CONCESSIONE ]
`azione`	concede privilegi di accesso specifici o tutti i privilegi (ALL PRIVILEGES) sulla tabella o sulla vista a un utente specifico o a tutti gli utenti (PUBLIC). La clausola WITH GRANT OPTION consente agli utenti a cui sono stati concessi i privilegi di concederli a loro volta ad altri utenti.

Tra i privilegi che possono essere concessi vi sono i seguenti:

`DELETE`	il diritto di utilizzare il comando DELETE sulla tabella o sulla vista.
`INSERT`	il diritto di utilizzare il comando INSERT sulla tabella o sulla vista
`SELECT`	Autorizzazione a utilizzare il comando SELECT sulla tabella o sulla vista
`UPDATE`	Autorizzazione a utilizzare il comando UPDATE sulla tabella o sulla vista. Questa autorizzazione può essere limitata a determinate colonne utilizzando la sintassi: GRANT update (col1, col2, ...) ON tabella/vista TO utente1, utente2, ...\| PUBLIC [WITH GRANT OPTION]

Concediamo all'utente [ADMIN2] l'autorizzazione SELECT sulla tabella TA. Solo il proprietario della tabella può concedere questa autorizzazione, ovvero [ADMIN1] in questo caso. Passa alla connessione DBACCES(ADMIN1) e apri un nuovo editor SQL (Shift+F12):

Da questo punto in poi, passeremo da un editor SQL all'altro. Per spostarsi tra di essi, è possibile utilizzare l'opzione di menu [Windows]:

Sopra vediamo i due editor SQL, ciascuno associato a un utente specifico. Torniamo all'editor SQL (ADMIN1) e inseriamo il seguente comando:

Quindi confermiamo con un COMMIT:

Ora passiamo all'editor dell'utente ADMIN2 per rieseguire l'istruzione SELECT che ha dato errore:

Otteniamo il seguente messaggio di errore:

L'utente [ADMIN2] non dispone ancora dell'autorizzazione per visualizzare la tabella [TA]. Infatti, sembra che le autorizzazioni di un utente vengano caricate al momento dell'accesso. [ADMIN2] avrebbe quindi ancora le stesse autorizzazioni di quando ha effettuato il primo accesso, ovvero nessuna. Verifichiamolo. Disconnettiamo l'utente [ADMIN2]:

selezionare la sua connessione
richiedere la disconnessione facendo clic con il tasto destro del mouse sulla connessione e selezionando l'opzione [Disconnetti dal database] oppure (Shift + Ctrl + D)

Se una finestra di dialogo richiede un [COMMIT], eseguire il [COMMIT]. Quindi ricollegare l'utente [ADMIN2] selezionando l'opzione [Ricollega] sopra. Una volta fatto ciò, tornare all'editor SQL (ADMIN2) ed eseguire nuovamente la query SELECT che ha dato errore:

Otterremo quindi il seguente risultato:

Questa volta, ADMIN2 può visualizzare la tabella TA grazie al privilegio SELECT concesso dal suo proprietario, ADMIN1. Normalmente, questo è l'unico privilegio di cui dispongono. Verifichiamolo. Sempre nell'editor SQL (ADMIN2):

La schermata a destra mostra che ADMIN2 non dispone dell'autorizzazione DELETE sulla tabella TA.

Torniamo all'editor SQL (ADMIN1) per concedere ulteriori diritti all'utente ADMIN2. Eseguiamo i seguenti due comandi in successione:

Il primo comando concede all'utente ADMIN2 i diritti di accesso completi alla tabella [TA], insieme alla possibilità di concedere diritti ad altri (WITH GRANT OPTION)
Il secondo comando convalida quello precedente

Una volta fatto ciò, come in precedenza, aggiorniamo la connessione dell’utente [ADMIN2] (Disconnetti / Riconnetti), quindi nell’editor SQL (ADMIN2) inseriamo i seguenti comandi:

ADMIN2 è riuscito a eliminare tutte le righe dalla tabella TA. Annulliamo questa eliminazione con un ROLLBACK:

Verifichiamo che ADMIN2 possa a sua volta concedere autorizzazioni sulla tabella TA.

Ora apriamo una connessione al database [DBACCES] (Database / Registra database) con il nome [SELECT1 / select1], uno degli utenti creati in precedenza, quindi facciamo doppio clic sul collegamento creato in [Esplora database]:

Passiamo a questa nuova connessione e apriamo un nuovo editor SQL (Shift + F12) per inserire i seguenti comandi:

L'utente SELECT1 dispone effettivamente dei diritti SELECT sulla tabella TA. Può trasferire questo diritto all'utente SELECT2?

L'operazione non è andata a buon fine perché l'utente SELECT1 non ha ricevuto il diritto di concedere il privilegio SELECT che gli era stato assegnato dall'utente ADMIN2. Affinché ciò avvenisse, l'utente ADMIN2 avrebbe dovuto utilizzare la clausola WITH GRANT OPTION nella propria istruzione SQL GRANT. Le regole per la concessione dei privilegi sono semplici:

un utente può concedere solo i privilegi che ha ricevuto e nient’altro
e può trasferirli solo se li ha ricevuti con il privilegio [WITH GRANT OPTION]

Un privilegio concesso può essere revocato utilizzando l'istruzione REVOKE:

`sintassi`	REVOKE privilegio1, privilegio2, ...\| ALL PRIVILEGES ON tabella/vista DA user1, user2, ...\| PUBBLICA
`azione`	revoca i privilegi di accesso (privilegio1) o tutti i privilegi (ALL PRIVILEGES) sulla tabella o sulla vista agli utenti (utente1, utente2, ...) o a tutti gli utenti (PUBLIC).

Proviamo. Torniamo all'editor SQL di ADMIN2 per rimuovere il privilegio SELECT che abbiamo concesso all'utente SELECT1:

Disconnettiamoci e poi riconnettiamoci alla sessione dell'utente SELECT1. Quindi, nell'editor SQL (SELECT1), interroghiamo il contenuto della tabella TA:

L'utente SELECT1 ha effettivamente perso il privilegio SELECT sulla tabella TA. Si noti che è stato ADMIN2 a concedere questo privilegio ed è stato ADMIN2 a revocarlo. Se ADMIN1 tenta di revocarlo, non viene segnalato alcun errore, ma possiamo quindi vedere che SELECT1 ha mantenuto il proprio privilegio SELECT.

È possibile concedere un privilegio a tutti utilizzando la sintassi: GRANT privilege(s) ON table / view TO PUBLIC. Concediamo il privilegio SELECT sulla tabella TA a tutti. Per farlo possiamo utilizzare sia ADMIN1 che ADMIN2. Useremo ADMIN2:

Creiamo una connessione al database utilizzando l'utente USER1 / user1:

Con la connessione DBACCES(USER1), apri un nuovo editor SQL (Shift + F12) e inserisci i seguenti comandi:

L'utente USER1 dispone effettivamente dell'autorizzazione SELECT sulla tabella TA.

7.3. Transazioni

7.3.1. Livelli di isolamento

Passeremo ora dalla questione dei diritti di accesso agli oggetti del database a quella dell'accesso concorrente a tali oggetti. Due utenti con diritti di accesso sufficienti a un oggetto del database — una tabella, ad esempio — vogliono utilizzarlo contemporaneamente. Cosa succede?

Ogni utente opera all'interno di una transazione. Una transazione è una sequenza di istruzioni SQL che viene eseguita in modo "atomico":

o tutte le operazioni vanno a buon fine
oppure una di esse fallisce, nel qual caso tutte quelle precedenti vengono annullate

In definitiva, le operazioni in una transazione vengono applicate tutte con successo oppure nessuna viene applicata. Quando l'utente ha il controllo sulla transazione (come avviene in tutto questo documento), esegue il commit di una transazione con un'istruzione COMMIT o la annulla con un'istruzione ROLLBACK.

Ogni utente opera all'interno di una transazione a lui appartenente. Esistono in genere quattro livelli di isolamento tra utenti diversi:

Lettura non confermata
Lettura confermata
Lettura ripetibile
Serializzabile

Lettura non confermata

Questo livello di isolamento è noto anche come "Lettura sporca". Ecco un esempio di ciò che può accadere in questa modalità:

L'utente U1 avvia una transazione sulla tabella T
L'utente U2 avvia una transazione sulla stessa tabella T
L'utente U1 modifica le righe nella tabella T ma non le ha ancora confermate
L'utente U2 "vede" queste modifiche e prende decisioni in base a ciò che vede
L'utente esegue il rollback della propria transazione utilizzando un ROLLBACK

Possiamo notare che al punto 4, l'utente U2 ha preso una decisione basandosi su dati che in seguito si riveleranno errati.

Lettura con commit

Questo livello di isolamento evita l'insidiosa situazione descritta in precedenza. In questa modalità, l'utente U2 nel passaggio 4 non "vedrà" le modifiche apportate dall'utente U1 alla tabella T. Le vedrà solo dopo che U1 avrà eseguito il commit della propria transazione.

In questa modalità, nota anche come "Lettura non ripetibile", possono verificarsi le seguenti situazioni:

un utente U1 avvia una transazione su una tabella T
L'utente U2 avvia una transazione sulla stessa tabella T
L'utente U2 esegue un SELECT per ottenere la media della colonna C dalle righe di T che soddisfano una determinata condizione
L'utente U1 modifica (UPDATE) determinati valori nella colonna C di T e conferma (COMMIT) le modifiche
L'utente U2 ripete lo stesso SELECT del punto 3. Noterà che la media della colonna C è cambiata a causa delle modifiche apportate da U1.

Ora l'utente U2 vede solo le modifiche "confermate" da U1. Tuttavia, pur rimanendo nella stessa transazione, due operazioni identiche (passaggi 3 e 5) producono risultati diversi. Il termine "lettura non ripetibile" si riferisce proprio a questa situazione. Si tratta di una situazione problematica per chi desidera una visione coerente della tabella T.

Lettura ripetibile

In questo livello di isolamento, a un utente viene garantito di ottenere gli stessi risultati dalle letture del database fintanto che rimane all'interno della stessa transazione. Lavora su un'istantanea che non riflette mai le modifiche apportate da altre transazioni, anche se tali modifiche sono state confermate. Vedrà tali modifiche solo una volta che avrà terminato la propria transazione con un COMMIT o un ROLLBACK.

Tuttavia, questo livello di isolamento non è ancora perfetto. Dopo l'operazione 3 sopra descritta, le righe interrogate dall'utente U2 sono bloccate. Durante l'operazione 4, l'utente U1 non sarà in grado di modificare (UPDATE) i valori nella colonna C di queste righe. Tuttavia, potrà aggiungere nuove righe (INSERT). Se alcune delle righe aggiunte soddisfano la condizione verificata in 3, l'operazione 5 produrrà una media diversa da quella trovata in 3 a causa delle righe aggiunte.

Per risolvere questo nuovo problema, è necessario passare all'isolamento "Serializable".

Serializable

In questa modalità di isolamento, le transazioni sono completamente isolate l'una dall'altra. Ciò garantisce che il risultato di due transazioni eseguite simultaneamente sarà lo stesso che si otterrebbe se fossero eseguite una dopo l'altra. Per ottenere questo risultato, durante l'operazione 4, quando l'utente U1 tenta di aggiungere righe che modificherebbero il risultato del SELECT dell'utente U1, gli verrà impedito di farlo. Un messaggio di errore lo informerà che l'inserimento non è possibile. Diventerà possibile una volta che l'utente U2 avrà eseguito il commit della propria transazione.

I quattro livelli di isolamento delle transazioni SQL non sono disponibili in tutti i DBMS. Firebird fornisce i seguenti livelli di isolamento:

snapshot: modalità di isolamento predefinita. Corrisponde alla modalità "Repeatable Read" dello standard SQL.
committed read: corrisponde alla modalità "committed read" dello standard SQL

Questo livello di isolamento viene impostato dal comando SET TRANSACTION:

sintassi

SET TRANSACTION

[READ WRITE | READ ONLY]

[WAIT|NOWAIT]

LIVELLO DI ISOLAMENTO [SNAPSHOT | READ COMMITTED]

Funzionalità

Le parole chiave sottolineate sono i valori predefiniti

READ WRITE: La transazione può leggere e scrivere

SOLO LETTURA: La transazione può solo leggere

WAIT: in caso di conflitto tra due transazioni, quella che non è riuscita a completare la propria operazione attende fino a quando l'altra transazione non viene confermata. Non può più emettere istruzioni SQL.

NOWAIT: la transazione che non è riuscita a completare la propria operazione non viene bloccata. Riceve un messaggio di errore e può continuare a lavorare.

LIVELLO DI ISOLAMENTO [SNAPSHOT | READ COMMITTED]: livello di isolamento

Proviamo. Nell'editor SQL (ADMIN1), inserisci il seguente comando SQL:

Vediamo che non è stato autorizzato. Non sappiamo perché...

IB-Expert consente di impostare la modalità di isolamento in un altro modo. Fare clic con il tasto destro del mouse sulla connessione DBACCES(ADMIN1) per selezionare l'opzione [Informazioni di registrazione del database]:

La schermata a destra mostra l'opzione [Transazioni]. Questa opzione ci consentirà di impostare il livello di isolamento delle transazioni. Qui lo impostiamo su [snapshot]. Facciamo lo stesso con la connessione DBACCES(ADMIN2).

7.3.2. Modalità snapshot

Esaminiamo il livello di isolamento snapshot, che è la modalità di isolamento predefinita di Firebird. Quando un utente avvia una transazione, viene acquisita un'istantanea del database. L'utente opera quindi su questa istantanea. Ogni utente lavora quindi sulla propria istantanea del database. Se apporta delle modifiche, gli altri utenti non le vedono. Le vedranno solo una volta che l'utente che ha apportato le modifiche le avrà confermate con un COMMIT.

Esistono due possibili scenari:

un utente legge la tabella (SELECT) mentre un altro la sta modificando (INSERT, UPDATE, DELETE)
Entrambi gli utenti vogliono modificare la tabella contemporaneamente

7.3.2.1. Principio della lettura coerente

Consideriamo due utenti, U1 e U2, che lavorano sulla stessa tabella TAB:

      --------+----------+--------+-------+----------------------
              T1a       T2a      T1b     T2b

La transazione dell'utente U1 inizia al momento T1a e termina al momento T1b.

La transazione dell'utente U2 inizia al momento T2a e termina al momento T2b.

U1 lavora su uno snapshot di TAB acquisito al momento T1a. Tra T1a e T1b, modifica TAB. Gli altri utenti non avranno accesso a queste modifiche fino al momento T1b, quando U1 esegue un COMMIT.

U2 lavora su uno snapshot di TAB acquisito al momento T2a, che è lo stesso snapshot utilizzato da U1 (a condizione che nessun altro utente abbia modificato l'originale nel frattempo). Non "vede" le modifiche che l'utente U1 potrebbe aver apportato a TAB. Potrà vederle solo al momento T1b.

Illustriamo questo punto utilizzando il nostro database [DBACCES]. Avremo i due utenti [ADMIN1] e [ADMIN2] che lavorano contemporaneamente. Passiamo alla connessione DBACCES(ADMIN1) e, nell'editor SQL di ADMIN1, eseguiamo le seguenti operazioni:

ADMIN1 ha modificato la riga 2 della tabella TA ma non ha ancora eseguito il commit (COMMIT) dell'operazione. L'utente ADMIN2 esegue quindi un SELECT sulla tabella TA (passiamo all'editor SQL di ADMIN2). Ci troviamo prima del momento T2a dell'esempio.

Torniamo all'editor SQL di ADMIN1, che esegue il commit dell'aggiornamento:

Torniamo all'editor SQL di ADMIN2 per rieseguire il SELECT:

ADMIN2 vede le modifiche apportate da ADMIN1. In modalità snapshot, una transazione non vede le modifiche apportate da altre transazioni finché queste ultime non sono state completate.

7.3.2.2. Modifica simultanea dello stesso oggetto del database da parte di due transazioni

Prendiamo un esempio dalla contabilità: U1 e U2 stanno lavorando sui conti. U1 addebita il conto X di un importo S e accredita il conto Y dello stesso importo. Lo farà in diversi passaggi:

      --------+----------+--------+-------+----------------------
              T1a       T1b     T1c      T1d

U1 avvia una transazione al tempo T1a, addebita comptex al tempo T1b, accredita comptey al tempo T1c e conferma entrambe le operazioni al tempo T1d. Supponiamo, inoltre, che U2 voglia fare la stessa cosa, avvii la propria transazione al tempo T2a e la concluda al tempo T2d secondo lo schema seguente:

      --------+----------+----+----+-------+------+-----+-------+---------
              T1a       T1b  T2a   T1c     T2b   T1d   T2c    T2d

Al tempo T2, viene acquisita un'istantanea della tabella dei conti per U2. Essa è coerente secondo il principio dell'istantanea. U2 vede lo stato iniziale dei conti comptex e comptey perché U1 non ha ancora effettuato il commit delle proprie transazioni.

Supponiamo che comptex abbia un saldo iniziale di 1.000 € e che entrambi gli utenti U1 e U2 vogliano addebitare 100 €.

Al tempo T1b, U1 addebita 100 € su comptex, portandolo a 90 €. Questa transazione non verrà confermata fino al tempo T1d.
Al tempo T2b, U2 vede comptex con 1.000 € (principio di lettura coerente) e lo decrementa di 100 €, portandolo a 90 €.
Alla fine, al momento T2d, quando tutto sarà stato convalidato, comptex avrà un saldo di 90 € invece degli 80 € previsti.

La soluzione a questo problema è impedire a U2 di modificare comptex finché U1 non ha completato la propria transazione. U2 sarà quindi bloccato fino al momento T1d. La modalità snapshot fornisce questo meccanismo.

Illustriamo questo concetto utilizzando il database DBACCES. ADMIN1 avvia una transazione nel proprio editor SQL (ADMIN1):

Abbiamo iniziato emettendo un COMMIT per assicurarci di avviare una nuova transazione. Quindi abbiamo eliminato la riga 4. La transazione non è ancora stata confermata.

ADMIN2 avvia quindi una transazione nel proprio editor SQL (ADMIN2):

La schermata a destra mostra che ADMIN2 ha tentato di modificare la riga 4. Gli è stato comunicato che ciò non era possibile perché qualcun altro l'aveva già modificata ma non aveva ancora confermato la modifica.

Torniamo all'editor SQL (ADMIN1) per eseguire il COMMIT:

Torniamo all'editor SQL (ADMIN2) per eseguire nuovamente il comando UPDATE:

L'operazione UPDATE viene completata con successo anche se la riga n. 4 non esiste più, come mostra la seguente istruzione SELECT. È a questo punto che ADMIN2 scopre che la riga non esiste più.

7.3.2.3. Modalità di lettura ripetibile

Illustriamo ora la modalità "Lettura ripetibile". Questo livello di isolamento è fornito dalla modalità "snapshot". Assicura che una transazione ottenga sempre lo stesso risultato durante la lettura del database.

Iniziamo lavorando con l'editor SQL di ADMIN2:

Passiamo ora all'editor SQL di ADMIN1:

L'utente ADMIN1 ha aggiunto due righe e ha confermato la transazione. Torniamo ora all'editor SQL (ADMIN2) per rieseguire il comando SELECT SUM:

Possiamo notare che ADMIN2 non vede le righe aggiunte da ADMIN1, anche se sono state confermate con un COMMIT. Il SELECT SUM restituisce lo stesso risultato di prima delle aggiunte. Questo è il principio della lettura ripetibile.

Ora, sempre nell'editor SQL (ADMIN2), eseguiamo il commit della transazione con un COMMIT e poi eseguiamo nuovamente il SELECT SUM:

Le righe aggiunte da ADMIN1 vengono ora prese in considerazione.

7.3.3. Modalità Committed Read

Illustriamo ora la modalità "Committed Read". Questo livello di isolamento è simile a quello di uno snapshot, tranne per quanto riguarda la "Lettura ripetibile".

Iniziamo modificando il livello di isolamento della transazione per entrambe le connessioni.

Disconnettiamo i due utenti, ADMIN1 e ADMIN2
Modifichiamo il livello di isolamento delle loro transazioni

Riconnettiamo gli utenti ADMIN1 e ADMIN2

Ora riprendiamo l'esempio precedente che illustrava la "Lettura ripetibile" per mostrare che non osserviamo più lo stesso comportamento. Iniziamo lavorando con l'editor SQL di ADMIN2:

Passiamo ora all'editor SQL di ADMIN1:

L'utente ADMIN1 ha aggiunto due righe e ha confermato la transazione. Torniamo ora all'editor SQL (ADMIN2) per rieseguire il comando SELECT SUM:

Il comando SELECT SUM non restituisce lo stesso risultato di prima delle aggiunte effettuate da ADMIN1. Questa è la differenza tra le modalità snapshot e read committed.