Aspetti Aspetti sistemistici sistemistici

dell’SQLdell’SQL

SQL environmentSQL environment

Un SQL environment è un framework Un SQL environment è un framework dove esistono dati e possono aversi dove esistono dati e possono aversi istruzioni SQL eseguite su questi dati;istruzioni SQL eseguite su questi dati;

Tutti gli elementi di un database Tutti gli elementi di un database discussi fin ora: tabelle, viste, trigger, discussi fin ora: tabelle, viste, trigger, stored procedure ecc. sono definite stored procedure ecc. sono definite dentro l’SQL environment;dentro l’SQL environment;

Questi elementi sono organizzati in Questi elementi sono organizzati in una gerachia di strutture ognuna della una gerachia di strutture ognuna della quali ha un ruolo ben preciso.quali ha un ruolo ben preciso.

SchemiSchemi

Sono collezioni di tabelle, viste, Sono collezioni di tabelle, viste, asserzioni, trigger, moduli PSM, ecc.asserzioni, trigger, moduli PSM, ecc.

Gli schemi sono l’unità di base Gli schemi sono l’unità di base dell’organizzazione gerarchica e dell’organizzazione gerarchica e sono vicini al concetto di sono vicini al concetto di database database che ognuno di noi ha.che ognuno di noi ha.

CataloghiCataloghi

Sono collezioni di Sono collezioni di schemi. schemi. Ogni Ogni catalogo ha uno o più schemi. Ogni catalogo ha uno o più schemi. Ogni catalogo ha uno schema speciale catalogo ha uno schema speciale chiamato chiamato INFORMATION_SCHEMA INFORMATION_SCHEMA che che contiene informazioni riguardo a contiene informazioni riguardo a tutti gli schemi memorizzati in esso.tutti gli schemi memorizzati in esso.

Cataloghi in SQLCataloghi in SQL L’INFORMATION_SCHEMAL’INFORMATION_SCHEMA ( (cataloghi di sistema)cataloghi di sistema)

contiente delle contiente delle tabelle specialitabelle speciali che contengono i che contengono i meta-datimeta-dati del DB (tabelle, view, vincoli, trigger, utenti, del DB (tabelle, view, vincoli, trigger, utenti, autorizzazioni, indici etc..)autorizzazioni, indici etc..)

Esempi:Esempi: PASSWORDPASSWORD(NomeUtente,ParolaChiave)(NomeUtente,ParolaChiave)

SYSDBSYSDB(NomeBaseDati, Proprietario, Cammino, Commenti)(NomeBaseDati, Proprietario, Cammino, Commenti)

SYSTABLESYSTABLE(NomeTabella, Proprietario, BaseODerivata, (NomeTabella, Proprietario, BaseODerivata, NumeroColonne, NomeArchivioFisico, NumeroColonne, NomeArchivioFisico,

Commenti)Commenti)

SYSCOLSSYSCOLS(NomeColonna, Tabella, Numero, Tipo, Lunghezza, (NomeColonna, Tabella, Numero, Tipo, Lunghezza, Default, Commenti) Default, Commenti)

SYSINDEXSYSINDEX(NomeIndice, Tabella, Proprietario, NumeroColonna, (NomeIndice, Tabella, Proprietario, NumeroColonna, Commenti) Commenti)

Altri CataloghiAltri Cataloghi

Una decina di altre tabelle per view, Una decina di altre tabelle per view, vincoli, grant ecc..vincoli, grant ecc..

Altri riguardano aspetti quantitativi Altri riguardano aspetti quantitativi sui dati, le sui dati, le statistichestatistiche, utilizzate , utilizzate dall’ottimizzatore delle query.dall’ottimizzatore delle query.

Utilizzo dei cataloghiUtilizzo dei cataloghi

Normalmente consultabili ma Normalmente consultabili ma non non modificabili modificabili dagli utenti. Possono dagli utenti. Possono tuttavia essere consultate mediante tuttavia essere consultate mediante SQL e per questa ragione sono SQL e per questa ragione sono autoreferenziateautoreferenziate (ad esempio (ad esempio SYSTABLE conterrà una n-upla SYSTABLE conterrà una n-upla corrispondente a se stessa.corrispondente a se stessa.

ClusterCluster

Sono collezioni di cataloghi. Ogni Sono collezioni di cataloghi. Ogni utente ha un cluster associato, utente ha un cluster associato, relativo all’insieme di cataloghi che relativo all’insieme di cataloghi che egli può accedere.egli può accedere.

GerarchiaGerarchia

SQL environmentCluster

Massimo scope per unaOperazione sul DB

Catalogo Catalogo

Schema

Schema

Catalogo

Esepio: Cataloghi in Esepio: Cataloghi in ORACLEORACLE

DA FAREDA FARE

Programmazione delle Programmazione delle Transazioni in SQLTransazioni in SQL

Una Una transazione transazione è un programma che il DBMS è un programma che il DBMS esegue garantendoneesegue garantendone atomicità atomicità e e serializzabilitàserializzabilità..

L’L’atomicitàatomicità viene garantita facendo si che viene garantita facendo si che quando una transazione fallisce tutti i suoi effetti quando una transazione fallisce tutti i suoi effetti sul DB siano annullati;sul DB siano annullati;

La serializzabilità viene garantita con il La serializzabilità viene garantita con il meccanismo di blocco dei dati (meccanismo di blocco dei dati (recordrecord e e table table lockinglocking))

LockingLocking Consiste nel bloccare un dato in lettura o scrittura Consiste nel bloccare un dato in lettura o scrittura

rispettivamente prima di modificare o leggere quel dato. rispettivamente prima di modificare o leggere quel dato.

Quando una transazione Quando una transazione TT11 cerca di ottenere un blocco in cerca di ottenere un blocco in scrittura su di un dato già bloccato da scrittura su di un dato già bloccato da TT22 allora allora TT11 viene viene messa in attesa finché messa in attesa finché TT22 termina rilasciando il lock. termina rilasciando il lock.

Si garantisce così la serializzabilità e l’isolamento in modo Si garantisce così la serializzabilità e l’isolamento in modo che ogni transazione non veda mai le modifiche di un’altra che ogni transazione non veda mai le modifiche di un’altra transazione non ancora terminata. Le richieste di blocco transazione non ancora terminata. Le richieste di blocco sono fatte dal sistema automaticamente.sono fatte dal sistema automaticamente.

Transaction Transaction ManagementManagement

In generale un programma applicativo è In generale un programma applicativo è trattato dal DBMS come un’trattato dal DBMS come un’unica transazione unica transazione . . Tuttavia si devono poter ammettere le seguenti Tuttavia si devono poter ammettere le seguenti alternative:alternative: Quando il programma scopre una Quando il programma scopre una condizione condizione

anomalaanomala che che impedisce il completamento impedisce il completamento , si deve , si deve poter poter disfare una partedisfare una parte delle operazioni fatte, delle operazioni fatte, cercando di usare cercando di usare codice alternativocodice alternativo..

Quando il programma impiega un Quando il programma impiega un lungo tempo lungo tempo per per terminare, ad esempio perché interagisce con terminare, ad esempio perché interagisce con l’utente, allora è opportuno l’utente, allora è opportuno spezzare il programma spezzare il programma in più transazioniin più transazioni, in modo da poter rilasciare quei , in modo da poter rilasciare quei dati che servono ad altre transazioni.dati che servono ad altre transazioni.

Transazioni nei linguaggi Transazioni nei linguaggi che ospitano SQLche ospitano SQL

I DBMS relazionali permettono di I DBMS relazionali permettono di spezzare i programmi in più transazioni spezzare i programmi in più transazioni mediante i comandi mediante i comandi COMMITCOMMIT e e ROLLBACKROLLBACK..

Nel caso dell’SQL ospitato una Nel caso dell’SQL ospitato una transazione viene considerata iniziata transazione viene considerata iniziata dal sistema quando un programma dal sistema quando un programma esegue un’operazione su una tabella esegue un’operazione su una tabella (SELECT, UPDATE, INSERT, DELETE, (SELECT, UPDATE, INSERT, DELETE, OPEN CURSOR)OPEN CURSOR)

Interruzione di Interruzione di transazionitransazioni

La transazione prosegue finché:La transazione prosegue finché: viene eseguito EXEC SQL COMMIT WORK : la viene eseguito EXEC SQL COMMIT WORK : la

transazione transazione termina normalmentetermina normalmente con il rilascio con il rilascio dei blocchi sui dati usati che diventano disponibilidei blocchi sui dati usati che diventano disponibili

viene eseguito EXEC SQL ROLLBACK WORK viene eseguito EXEC SQL ROLLBACK WORK ((abort transactionabort transaction) : comporta la terminazione ) : comporta la terminazione prematuraprematura della transazione e quindi della transazione e quindi

(a) il disfacimento di tutte le modifiche (atomicità) (a) il disfacimento di tutte le modifiche (atomicità) (b) il rilascio dei blocchi usati.(b) il rilascio dei blocchi usati.

Il programma Il programma termina senza erroritermina senza errori , , normalmente.normalmente.

Il programma Il programma termina con fallimentotermina con fallimento e provoca e provoca l’aborto della transazionel’aborto della transazione

Esempio: Stampa Esempio: Stampa Ammontare Ordine e Ammontare Ordine e Clienti- Totale OrdiniClienti- Totale Ordini

Program Esempio; Program Esempio; Dichiarazioni ed Inizializzazioni Dichiarazioni ed Inizializzazioni

Lettura dei dati dal terminale Lettura dei dati dal terminale Prima transazionePrima transazione: ricerca ordine : ricerca ordine

EXEC SQL COMMIT WORK EXEC SQL COMMIT WORK

Stampa risultato prima transazione Stampa risultato prima transazione Seconda Transazione: Seconda Transazione: recupero e stampa clienti-recupero e stampa clienti-

ammontare totale ammontare totale

END programmaEND programma

Un esempio più Un esempio più complessocomplesso

Supponiamo di avere una tabella Supponiamo di avere una tabella Magazzino(Prodotto, Quantità, Prezzo); Magazzino(Prodotto, Quantità, Prezzo);

Il seguente programma serve ad un Il seguente programma serve ad un venditore al momento della richiesta di venditore al momento della richiesta di un ordine da parte di un cliente. un ordine da parte di un cliente.

Si legge la quantità disponibile ed il Si legge la quantità disponibile ed il prezzo, si legge la quantità ordinata e si prezzo, si legge la quantità ordinata e si crea l’ordinecrea l’ordine

Program Esempio 2; Program Esempio 2; VAR VAR EXEC SQL BEGIN DECLARE SECTION EXEC SQL BEGIN DECLARE SECTION xQuantita, xPrezzo, prezzoProposto, xQuantita, xPrezzo, prezzoProposto, quantitaRichiesta : INTEGER; quantitaRichiesta : INTEGER; NumProdotto: NumProdotto: ARRAY [1..3] OF CHAR; EXEC ARRAY [1..3] OF CHAR; EXEC SQL END DECLARE SECTION SQL END DECLARE SECTION

BEGIN BEGIN EXEC SQL CONNECT EXEC SQL CONNECT IdUtente IDENTIFIED BY Password USING Database; IdUtente IDENTIFIED BY Password USING Database; /*IdUtente,Password e Database siano tre costanti /*IdUtente,Password e Database siano tre costanti per semplicità*/per semplicità*/

Ricerca quantita’ e Ricerca quantita’ e prezzoprezzo

Writeln(‘Scrivi il codice del prodotto’);Writeln(‘Scrivi il codice del prodotto’);

read(NumProdotto) read(NumProdotto)

EXEC SQL /*inizio prima transazione*/ EXEC SQL /*inizio prima transazione*/ SELECT Quantita, Prezzo SELECT Quantita, Prezzo INTO :xQuantita, :xPrezzo INTO :xQuantita, :xPrezzo FROM Magazzino FROM Magazzino WHERE Prodotto=:NumProdotto WHERE Prodotto=:NumProdotto

EXEC SQL COMMIT WORKEXEC SQL COMMIT WORK /*fine /*fine della prima transazione*/della prima transazione*/

Stampa del risultato e Stampa del risultato e lettura della quantità lettura della quantità

richiestarichiestawriteln(‘Quantita =‘, xQuantita, ‘Prezzo =‘, writeln(‘Quantita =‘, xQuantita, ‘Prezzo =‘,

xPrezzo); xPrezzo);

writeln; writeln;

writeln(‘Scrivi quantita ordinata’); writeln(‘Scrivi quantita ordinata’);

readln(quantitaRichiesta); readln(quantitaRichiesta);

prezzoProposto:= xPrezzo;prezzoProposto:= xPrezzo;

Esecuzione dell’ordineEsecuzione dell’ordine

EXEC SQL EXEC SQL SELECT Quantita,Prezzo SELECT Quantita,Prezzo INTO :xQuantita, :xPrezzo INTO :xQuantita, :xPrezzo FROM Magazzino WHERE FROM Magazzino WHERE Prodotto = :NumProdotto Prodotto = :NumProdotto

IF xQuantita >= quantitaRichiesta AND xPrezzo = prezzoProposto THEN IF xQuantita >= quantitaRichiesta AND xPrezzo = prezzoProposto THEN xQuantita := xQuantita - quantitaRichiesta; xQuantita := xQuantita - quantitaRichiesta;

EXEC SQL EXEC SQL UPDATE Magazzino SET Quantita = :xQuantita UPDATE Magazzino SET Quantita = :xQuantita WHERE Prodotto = :NumProdotto WHERE Prodotto = :NumProdotto ...{Soddisfacimento dell’ordine} ...{Soddisfacimento dell’ordine} ELSE /*quantita’ insufficiente o il prezzo e’ cambiato dal ELSE /*quantita’ insufficiente o il prezzo e’ cambiato dal momento della proposta*/ momento della proposta*/ BEGIN BEGIN EXEC EXEC SQL ROLLBACK WORK;SQL ROLLBACK WORK; writeln(‘Insufficiente writeln(‘Insufficiente quantita’ o cambiamento prezzo’) quantita’ o cambiamento prezzo’) ......

Transazioni con livelli Transazioni con livelli diversi di isolamentodiversi di isolamento

Con l’aumentare del numero di transazioni Con l’aumentare del numero di transazioni eseguite concorrentemente in modo eseguite concorrentemente in modo serializzabile si può ridurre l’effettivo grado di serializzabile si può ridurre l’effettivo grado di concorrenza del sistema a causa del fatto che concorrenza del sistema a causa del fatto che aumenta la probabilità di avere transazioni in aumenta la probabilità di avere transazioni in attesa di dati bloccati da altre o interrotte per il attesa di dati bloccati da altre o interrotte per il verificarsi di situazioni di stalloverificarsi di situazioni di stallo

Per questa ragione i sistemi commerciali Per questa ragione i sistemi commerciali prevedono la possibilità di programmare prevedono la possibilità di programmare transazioni rinunciando alla proprietà di transazioni rinunciando alla proprietà di serializzabilità e quindi di isolamento delle serializzabilità e quindi di isolamento delle transazioni.transazioni.

SQL-92: SET SQL-92: SET TRANSACTIONTRANSACTION

SET TRANSACTION ISOLATION LEVEL SET TRANSACTION ISOLATION LEVEL

[READ UNCOMMITTED|[READ UNCOMMITTED|

READ COMMITTED |READ COMMITTED |

REPEATABLE READ |REPEATABLE READ |

SERIALIZABLE ]SERIALIZABLE ]

READ UNCOMMITTEDREAD UNCOMMITTED

Il primo livello di isolamento Il primo livello di isolamento read read uncommitted uncommitted (detto anche (detto anche dirty readdirty read o o degree of isolation 0degree of isolation 0) consente transazioni ) consente transazioni che fanno solo operazioni di lettura che che fanno solo operazioni di lettura che vengono eseguite dal sistema senza vengono eseguite dal sistema senza bloccare in lettura i dati.bloccare in lettura i dati.

Come conseguenza abbiamo che una Come conseguenza abbiamo che una transazione può leggere dati modificati da transazione può leggere dati modificati da un’altra transazione non ancora un’altra transazione non ancora completata.completata.

READ COMMITTEDREAD COMMITTED

Il livello di isolamento Il livello di isolamento read committed read committed (detto anche (detto anche cursor stability cursor stability o o degree of degree of isolation 1isolation 1) stabilisce che i blocchi in ) stabilisce che i blocchi in lettura vengano rilasciati subito mentre lettura vengano rilasciati subito mentre quelli in scrittura vengono rilasciati alla quelli in scrittura vengono rilasciati alla terminazione della transazione. terminazione della transazione.

In questo modo una transazione In questo modo una transazione TT che che modifica un dato, quel dato non può modifica un dato, quel dato non può essere letto da altri fino a che essere letto da altri fino a che TT non abbia non abbia effettuato un commit o un rollback.effettuato un commit o un rollback.

REPEATABLE READREPEATABLE READ

Repeatable readRepeatable read o anche o anche degree of degree of isolation 2isolation 2 prevede che i blocchi in prevede che i blocchi in lettura e scrittura siano assegnati solo lettura e scrittura siano assegnati solo su n-uple di tabelle e vengano rilasciati su n-uple di tabelle e vengano rilasciati alla terminazione della transazione. alla terminazione della transazione.

Presenta il fenomeno dei Presenta il fenomeno dei fantasmi fantasmi (phantoms)(phantoms), abbiamo che dati inseriti , abbiamo che dati inseriti nella tabella possono non essere visti.nella tabella possono non essere visti.

SERIALIZABLESERIALIZABLE

Degree of isolation 3. Degree of isolation 3. Consiste nel Consiste nel blocco temporaneo della tabella;blocco temporaneo della tabella;

In certe applicazioni troppo In certe applicazioni troppo restrittivo.restrittivo.

Livello Letture sporche

Letture non ripetibili Dati fantasmi

READ UNCOMMITTED X X X

READ COMMITTED - X X

REPEATABLE READ - - X

SERIALIZABLE - - -

Letture sporche: un Letture sporche: un esempio.esempio.