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.