7. Gestione dell’accesso concorrente 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. Si pone quindi la domanda: chi può utilizzare una determinata tabella e in che modo (consultazione, inserimento, cancellazione, aggiunta, ...)?

7.1. Creazione di utenti Firebird

Quando abbiamo lavorato con IB-Expert, ci siamo collegati come utente SYSDBA. È possibile trovare questa informazione nelle proprietà della connessione aperta a SGBD:

A destra si vede che l’utente connesso è [SYSDBA]. Ciò che non si vede è la sua password [masterkey]. [SYSDBA] è un utente speciale di Firebird: dispone di tutti i diritti su tutti gli oggetti gestiti da SGBD. È possibile creare nuovi utenti con IBExpert utilizzando l’opzione [Tools / User Manager] o l’icona seguente:

Si apre la finestra di gestione degli utenti:

Il pulsante [Add] consente di creare nuovi utenti:

Creiamo quindi i seguenti utenti:

nome	password
ADMIN1	admin1
ADMIN2	admin2
SELECT1	select1
SELECT2	select2
UPDATE1	aggiornamento1
UPDATE2	aggiornamento2

7.2. Concedere i diritti di accesso agli utenti

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

e registriamola con l’alias DBACCES (ADMIN1). L’uso degli alias consente di aprire connessioni su uno stesso database assegnando loro identificativi diversi, il che permette di individuarle più facilmente nell’esploratore dei database di IBExpert:

Creiamo ora le due tabelle TA e TB seguenti:

Tabella TA

Tabella TB

Queste tabelle non sono collegate tra loro.

Con IB-Expert, creiamo una seconda connessione al database [DBACCES], questa volta con il nome [ADMIN2 / admin2]. A tal fine utilizziamo l’opzione [Database / Register Database]:

Posizioniamoci su DBACCES (ADMIN2) e apriamo un editor SQL (Shift + F12):

Avremo modo di utilizzare diverse connessioni sullo stesso database [DBACCES]. Per ciascuna di esse, avremo un editor SQL. In [1], l’editor SQL indica l’alias del database a cui si è connessi. Utilizzate questa indicazione per capire in quale editor SQL vi trovate. Questo sarà importante poiché creeremo connessioni che non avranno gli stessi diritti di accesso sugli oggetti del database.

Richiediamo il contenuto della tabella TA:

Otteniamo 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 di questo database. Può concedere diritti di accesso ad altri utenti con il comando SQL GRANT. Questo comando ha diverse sintassi. Una di queste è la seguente:

syntaxe	GRANT privilegio1, privilegio2, ...| ALL PRIVILEGES ON table/vue TO utente1, utente2, ...| PUBLIC [ WITH GRANT OPTION ]
action	concede i privilegi di accesso privilègei o tutti i privilegi (ALL PRIVILEGES) su table o vue agli utenti utilisateuri o a tutti gli utenti ( PUBLIC ). La clausola WITH GRANT OPTION consente agli utenti che hanno ricevuto i privilegi di trasferirli a loro volta ad altri utenti.

Tra i privilegi privilègei che possono essere concessi figurano i seguenti:

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

Concediamo all'utente [ADMIN2] il diritto SELECT sulla tabella TA. Solo il proprietario della tabella può concedere questo diritto, c.a.d. In questo caso, [ADMIN1]. Passiamo alla connessione DBACCES (ADMIN1) e apriamo un nuovo editor SQL (Shift+F12):

Successivamente, passeremo da un editor SQL all’altro. Per orientarci, possiamo utilizzare l’opzione [Windows] dal menu:

Nell’immagine sopra sono visibili i due editor SQL, ciascuno associato a un utente specifico. Torniamo all’editor SQL (ADMIN1) ed eseguiamo il seguente comando:

Quindi confermiamolo con un COMMIT:

Fatto ciò, passiamo all’editor dell’utente ADMIN2 per rifare il SELECT che non era andato a buon fine:

Otteniamo il seguente messaggio di errore:

L’utente [ADMIN2] non dispone ancora dei diritti necessari per consultare la tabella [TA]. In effetti, sembra che i diritti di un utente vengano caricati al momento della connessione. [ADMIN2] avrebbe quindi sempre gli stessi diritti che aveva all’inizio della connessione, ovvero nessuno. Verifichiamolo. Disconnettiamo l’utente [ADMIN2]:

• selezionare la sua connessione
• richiedere la disconnessione cliccando con il tasto destro sulla connessione e selezionando l’opzione [Deconnect from database] oppure (Shift + Ctrl + D)

Se una finestra di dialogo richiede un [COMMIT], inserite [COMMIT]. Quindi ricolleghiamo l’utente [ADMIN2] selezionando l’opzione [Reconnect] sopra indicata. Fatto ciò, torniamo all’editor SQL (ADMIN2) e rieseguiamo la richiesta SELECT che non è andata a buon fine:

Si ottiene quindi il seguente risultato:

Questa volta ADMIN2 può consultare la tabella TA grazie al diritto SELECT che gli è stato concesso dal suo proprietario ADMIN1. Normalmente questo è l’unico diritto di cui dispone. Verifichiamolo. Sempre nell’editor SQL (ADMIN2):

La schermata a destra mostra che ADMIN2 non dispone del diritto DELETE sulla tabella TA.

Torniamo nell’editor di SQL (ADMIN1) per assegnare ulteriori diritti all’utente ADMIN2. Eseguiamo in successione i due comandi seguenti:

• il primo comando assegna all'utente ADMIN2 tutti i diritti di accesso alla tabella [TA], oltre alla possibilità di concedere a sua volta dei diritti (WITH GRANT OPTION)
• il secondo comando conferma il precedente

Fatto ciò, come in precedenza, riattiviamo la connessione dell'utente [ADMIN2] (Disconnetti / Riconnetti), quindi nell'editor SQL (ADMIN2) inseriamo i seguenti comandi:

ADMIN2 ha eliminato tutte le righe della tabella TA. Annulliamo questa eliminazione con un ROLLBACK:

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

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

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

L'utente SELECT1 dispone effettivamente del diritto SELECT sulla tabella TA. Ha la possibilità di trasferire tale diritto all'utente SELECT2?

L'operazione non è andata a buon fine perché l'utente SELECT1 non ha ricevuto l'autorizzazione a trasferire l'autorizzazione SELECT che aveva ricevuto dall'utente ADMIN2. A tal fine, sarebbe stato necessario che l'utente ADMIN2 utilizzasse la clausola WITH GRANT OPTION nel proprio ordine SQL GRANT. Le regole di trasmissione sono semplici:

• un utente può trasmettere solo i diritti che ha ricevuto e non di più
• può trasmetterli solo se li ha ricevuti con il privilegio [WITH GRANT OPTION]

Un diritto concesso può essere revocato con il comando REVOKE:

syntaxe	REVOKE privilegio1, privilegio2, ...| ALL PRIVILEGES ON table/vue FROM utente1, utente2, ...| PUBLIC
action	revoca i privilegi di accesso privilègei o tutti i privilegi (ALL PRIVILEGES) su table o vue per gli utenti utilisateuri o per tutti gli utenti ( PUBLIC ).

Proviamo. Torniamo nell’editor di SQL di ADMIN2 per rimuovere il diritto SELECT che abbiamo concesso all’utente SELECT1:

Disconnettiamo e poi riconnettiamo la connessione dell'utente SELECT1. Quindi, nell'editor SQL (SELECT1), richiediamo il contenuto della tabella TA:

L'utente SELECT1 ha effettivamente perso il diritto di lettura della tabella TA. Si noti che è stato ADMIN2 a concedergli tale diritto e ADMIN2 a revocarlo. Se ADMIN1 tenta di revocarglielo, non viene segnalato alcun errore, ma si può poi constatare che SELECT1 ha mantenuto il proprio diritto SELECT.

È possibile concedere un diritto a tutti utilizzando la sintassi: GRANT diritto(i) ON tabella / vista TO PUBLIC. Concediamo quindi a tutti il diritto SELECT sulla tabella TA. Per farlo, è possibile utilizzare ADMIN1 o ADMIN2. Utilizziamo ADMIN2:

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

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

L'utente USER1 dispone effettivamente del diritto SELECT sulla tabella TA.

7.3. Le transazioni

7.3.1. Livelli di isolamento

Passiamo ora dal problema dei diritti di accesso agli oggetti di un database a quello degli accessi concorrenti a tali oggetti. Due utenti in possesso di diritti di accesso sufficienti a un oggetto del database, ad esempio una tabella, desiderano utilizzarlo contemporaneamente. Cosa succede?

Ogni utente opera all’interno di una transazione. Una transazione è una sequenza di comandi SQL che viene eseguita in modo “atomico”:

• o tutte le operazioni vanno a buon fine
• oppure una di esse fallisce e quindi tutte quelle precedenti vengono annullate

Alla fine, le operazioni di una transazione sono state tutte applicate con successo oppure nessuna è stata applicata. Quando l’utente ha il controllo della transazione (come in tutto questo documento), la convalida con un comando COMMIT o la annulla con un comando ROLLBACK.

Ogni utente opera all’interno di una transazione a lui propria. Si distinguono solitamente quattro livelli di isolamento tra i diversi utenti:

• Lettura non confermata
• Lettura confermata
• Lettura ripetibile
• Serializable

Lettura non confermata

Questa modalità di isolamento è nota anche come "Dirty Read". Ecco un esempio di ciò che può accadere in questa modalità:

1. un utente U1 avvia una transazione su una tabella T
2. un utente U2 avvia una transazione sulla stessa tabella T
3. l'utente U1 modifica alcune righe della tabella T ma non le salva ancora
4. l’utente U2 “vede” queste modifiche e prende decisioni in base a ciò che vede
5. l'utente annulla la propria transazione tramite un ROLLBACK

Si nota che al punto 4, l’utente U2 ha preso una decisione sulla base di dati che in seguito si riveleranno errati.

Committed Read

Questa modalità di isolamento evita l’insidiosa situazione descritta in precedenza. In questa modalità, l’utente U2 al punto 4 non “vedrà” le modifiche apportate dall’utente U1 alla tabella T. Le vedrà solo dopo che U1 avrà completato la propria transazione.

In questa modalità, nota anche come “Unrepeatable Read”, si possono verificare le seguenti situazioni:

1. un utente U1 avvia una transazione su una tabella T
2. un utente U2 avvia una transazione sulla stessa tabella T
3. L'utente U2 esegue un SELECT per calcolare la media della colonna C delle righe di T che soddisfano una determinata condizione
4. l'utente U1 modifica (UPDATE) alcuni valori della colonna C di T e li convalida (COMMIT)
5. l'utente U2 ripete la stessa operazione SELECT descritta al 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 “convalidate” da U1. Tuttavia, pur rimanendo nella stessa transazione, due operazioni identiche (le operazioni 3 e 5) danno risultati diversi. Il termine «Unrepeatable Read» indica questa situazione. Si tratta di una situazione fastidiosa per chi desidera avere un’immagine stabile della tabella T.

Lettura ripetibile

In questa modalità di isolamento, un utente ha la garanzia di ottenere gli stessi risultati nelle sue letture del database fintanto che rimane nella stessa transazione. Lavora su un’istantanea in cui non vengono mai riportate le modifiche apportate da altre transazioni, anche se convalidate. Le vedrà solo quando lui stesso terminerà la propria transazione con un COMMIT o un ROLLBACK.

Questa modalità di isolamento, tuttavia, non è ancora perfetta. Dopo l’operazione 3 sopra descritta, le righe consultate dall’utente U2 vengono bloccate. Durante l’operazione 4, l’utente U1 non potrà modificare (UPDATE) i valori della colonna C di tali righe. Potrà tuttavia aggiungere nuove righe (INSERT). Se alcune delle righe aggiunte soddisfano la condizione verificata al punto 3, l'operazione 5 fornirà una media diversa da quella ottenuta al punto 3 a causa delle righe aggiunte.

Per risolvere questo nuovo problema, è necessario passare al livello di isolamento «Serializable».

Serializable

In questa modalità di isolamento, le transazioni sono completamente isolate le une dalle altre. Essa 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, in cui l’utente U1 intende aggiungere righe che modificherebbero il risultato della transazione SELECT dell’utente U1, gli verrà impedito di farlo. Un messaggio di errore gli indicherà che l’inserimento non è possibile. Diventerà possibile solo quando l’utente U2 avrà confermato la propria transazione.

I quattro livelli di isolamento delle transazioni SQL non sono disponibili in tutti i SGBD. 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 tramite il comando SET TRANSACTION:

syntaxe

SET TRANSACTION [READ WRITE | READ ONLY] [WAIT|NOWAIT] ISOLATION LEVEL [SNAPSHOT | READ COMMITTED]

fonctionnement

le parole chiave sottolineate sono i valori predefiniti READ WRITE: la transazione può leggere e scrivere READ ONLY: la transazione può solo leggere WAIT: in caso di conflitto tra due transazioni, quella che non è riuscita a eseguire la propria operazione attende che l'altra transazione venga convalidata. Non può più emettere comandi SQL. NOWAIT: la transazione che non è riuscita a eseguire la propria operazione non viene bloccata. Riceve un messaggio di errore e può continuare a operare. ISOLATION LEVEL [SNAPSHOT | READ COMMITTED]: livello di isolamento

Proviamo. Nell’editor SQL (ADMIN1) inseriamo il seguente comando SQL:

Si nota che non è stato autorizzato. Non si capisce perché...

IB-Expert consente di impostare la modalità di isolamento in un altro modo. Facciamo clic con il tasto destro sulla connessione DBACCES(ADMIN1) per selezionare l’opzione [Database Registration Info]:

La schermata a destra mostra la presenza di un'opzione [Transactions]. Questa ci consentirà di impostare il livello di isolamento delle transazioni. Lo impostiamo qui su [snapshot]. Procediamo allo stesso modo con la connessione DBACCES (ADMIN2).

7.3.2. La modalità snapshot

Esaminiamo il livello di isolamento snapshot, che è la modalità di isolamento predefinita di Firebird. Quando l’utente avvia una transazione, viene scattata un’istantanea del database. L’utente lavorerà quindi su questa istantanea. Ogni utente lavora quindi su un’istantanea del database a lui specifica. Se apporta modifiche a tale istantanea, gli altri utenti non le vedono. Le vedranno solo quando l’utente che le ha apportate le avrà confermate con un COMMIT.

Si possono considerare due casi:

• 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

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

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

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

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

U1 sta lavorando su una foto di TAB scattata in quel momento T1a. Tra T1a e T1b, modifica TAB. Gli altri utenti avranno accesso a queste modifiche solo al momento T1b, quando U1 effettuerà un COMMIT.

U2 sta lavorando su una foto di TAB scattata al momento T2a, quindi la stessa foto utilizzata da U1 (se nel frattempo altri utenti non hanno modificato l'originale). Non “vede” le modifiche che l’utente U1 potrebbe aver apportato a TAB. Potrà vederle solo al momento T1b.

Illustriamo questo punto sulla nostra base [DBACCES]. Faremo lavorare contemporaneamente i due utenti [ADMIN1] e [ADMIN2]. Passiamo alla connessione DBACCES (ADMIN1) e nell’editor SQL di ADMIN1, eseguiamo le seguenti operazioni:

ADMIN1 ha modificato la riga n. 2 della tabella TA ma non ha ancora confermato (COMMIT) la propria operazione. L'utente ADMIN2 esegue quindi un'operazione SELECT sulla tabella TA (si passa dall’editor SQL a ADMIN2). Ci troviamo prima del momento T2a dell’esempio.

Ritorno nell'editor SQL da ADMIN1 che convalida la sua aggiunta:

Ritorno nell'editor SQL da ADMIN2 per rifare il SELECT:

ADMIN2 vede le modifiche apportate da ADMIN1. Nella modalità snapshot, una transazione non vede le modifiche apportate dalle altre transazioni finché queste ultime non sono terminate.

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

Prendiamo un esempio in ambito contabile: U1 e U2 stanno lavorando su alcuni conti. U1 addebita a comptex un importo S e accredita a comptey lo stesso importo. Lo farà in più fasi:

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

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

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

Al momento T2, viene acquisita un'istantanea della tabella dei conti per U2. Essa è coerente secondo il principio di snapshot. U2 visualizza lo stato iniziale dei conti comptex e comptey poiché U1 non ha ancora convalidato le proprie operazioni.

Supponiamo che comptex abbia un saldo iniziale di 1000 € e che ciascuno degli utenti U1 e U2 voglia addebitargli 100 €.

• Al tempo T1b, U1 addebita 100 € a comptex, portandone il saldo a 90 €. Questa operazione verrà convalidata solo al tempo T1d.
• Al momento T2b, U2 vede comptex con 1000 € (principio di lettura coerente) e lo decrementa di 100 €, portandolo quindi 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 consiste nell’impedire a U2 di modificare comptex finché U1 non avrà completato la propria transazione. U2 rimarrà quindi bloccato fino al momento T1d. La modalità snapshot fornisce questo meccanismo.

Illustriamolo con la base DBACCES. ADMIN1 avvia una transazione nel proprio editor SQL (ADMIN1):

Abbiamo iniziato eseguendo un COMMIT per assicurarci di avviare una nuova transazione. Successivamente abbiamo eliminato la riga n. 4. La transazione non è ancora stata convalidata.

A sua volta, ADMIN2 avvia una transazione nel proprio editor SQL (ADMIN2):

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

Torniamo nell’editor SQL (ADMIN1) per creare il COMMIT:

Torniamo all’editor SQL(ADMIN2) per rieseguire il comando UPDATE:

L'operazione UPDATE va a buon fine anche se la riga n. 4 non esiste più, come mostra il SELECT che segue. È in quel momento che ADMIN2 rileva che la riga non esiste più.

7.3.2.3. La modalità Repeatable Read

Illustriamo ora la modalità «Repeatable Read». 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 riprodurre il SELECT SUM:

Si nota che ADMIN2 non rileva le righe aggiunte da ADMIN1, sebbene siano state convalidate da un COMMIT. Il SELECT SUM fornisce lo stesso risultato di prima delle aggiunte. È il principio del «Repeatable Read».

Ora, sempre nell’editor SQL (ADMIN2), convalidiamo la transazione con un COMMIT, quindi ripetiamo il SELECT SUM:

Le righe aggiunte da ADMIN1 vengono ora prese in considerazione.

7.3.3. La modalità Committed Read

Illustriamo ora la modalità "Committed Read". Questo livello di isolamento è analogo a quello di snapshot, tranne per quanto riguarda il "Repeatable Read".

Iniziamo modificando il livello di isolamento delle transazioni di entrambe le connessioni.

• Disconnettiamo i due utenti ADMIN1 e ADMIN2
• modifichiamo il livello di isolamento delle loro transazioni

• riconnettiamo gli utenti ADMIN1 e ADMIN2

Riprendiamo ora l’esempio precedente che illustrava il “Repeatable Read” per dimostrare che non si osserva 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 riprodurre la transazione SELECT SUM:

Il SELECT SUM non fornisce lo stesso risultato di prima delle modifiche apportate dal ADMIN1. Si tratta della differenza tra le modalità snapshot e read committed.