Aspetti sistemistici
dell’SQL
SQL environment



Un SQL environment è un framework dove
esistono dati e possono aversi istruzioni SQL
eseguite su questi dati;
Tutti gli elementi di un database discussi fin ora:
tabelle, viste, trigger, stored procedure ecc. sono
definite dentro l’SQL environment;
Questi elementi sono organizzati in una gerachia
di strutture ognuna della quali ha un ruolo ben
preciso.
Schemi


Sono collezioni di tabelle, viste, asserzioni,
trigger, moduli PSM, ecc.
Gli schemi sono l’unità di base
dell’organizzazione gerarchica e sono vicini al
concetto di database che ognuno di noi ha.
Cataloghi

Sono collezioni di schemi. Ogni catalogo ha uno
o più schemi. Ogni catalogo ha uno schema
speciale chiamato
INFORMATION_SCHEMA che contiene
informazioni riguardo a tutti gli schemi
memorizzati in esso.
Cataloghi in SQL


L’INFORMATION_SCHEMA (cataloghi di sistema) contiente delle tabelle
speciali che contengono i meta-dati del DB (tabelle, view, vincoli, trigger,
utenti, autorizzazioni, indici etc..)
Esempi:

PASSWORD(NomeUtente,ParolaChiave)

SYSDB(NomeBaseDati, Proprietario, Cammino, Commenti)

SYSTABLE(NomeTabella, Proprietario, BaseODerivata,
NumeroColonne, NomeArchivioFisico, Commenti)

SYSCOLS(NomeColonna, Tabella, Numero, Tipo, Lunghezza,
Default, Commenti)

SYSINDEX(NomeIndice, Tabella, Proprietario, NumeroColonna,
Commenti)
Altri Cataloghi

Una decina di altre tabelle per view, vincoli,
grant ecc..

Altri riguardano aspetti quantitativi sui dati, le
statistiche, utilizzate dall’ottimizzatore delle query.
Utilizzo dei cataloghi

Normalmente consultabili ma non modificabili
dagli utenti. Possono tuttavia essere consultate
mediante SQL e per questa ragione sono
autoreferenziate (ad esempio SYSTABLE conterrà
una n-upla corrispondente a se stessa.
Cluster

Sono collezioni di cataloghi. Ogni utente ha un
cluster associato, relativo all’insieme di cataloghi
che egli può accedere.
Gerarchia
SQL environment
Cluster
Massimo scope per una
Operazione sul DB
Catalogo
Catalogo
Catalogo
Schema
Schema
Esepio: Cataloghi in ORACLE

DA FARE
Programmazione delle
Transazioni in SQL

Una transazione è un programma che il DBMS
esegue garantendone atomicità e serializzabilità.

L’atomicità viene garantita facendo si che quando una
transazione fallisce tutti i suoi effetti sul DB siano
annullati;

La serializzabilità viene garantita con il meccanismo
di blocco dei dati (record e table locking)
Locking

Consiste nel bloccare un dato in lettura o scrittura
rispettivamente prima di modificare o leggere quel dato.

Quando una transazione T1 cerca di ottenere un blocco in
scrittura su di un dato già bloccato da T2 allora T1 viene messa in
attesa finché T2 termina rilasciando il lock.

Si garantisce così la serializzabilità e l’isolamento in modo che
ogni transazione non veda mai le modifiche di un’altra
transazione non ancora terminata. Le richieste di blocco sono
fatte dal sistema automaticamente.
Transaction Management

In generale un programma applicativo è trattato dal
DBMS come un’unica transazione . Tuttavia si devono
poter ammettere le seguenti alternative:


Quando il programma scopre una condizione anomala che
impedisce il completamento , si deve poter disfare una parte delle
operazioni fatte, cercando di usare codice alternativo.
Quando il programma impiega un lungo tempo per terminare,
ad esempio perché interagisce con l’utente, allora è
opportuno spezzare il programma in più transazioni, in modo da
poter rilasciare quei dati che servono ad altre transazioni.
Transazioni nei linguaggi che
ospitano SQL

I DBMS relazionali permettono di spezzare i
programmi in più transazioni mediante i
comandi COMMIT e ROLLBACK.

Nel caso dell’SQL ospitato una transazione
viene considerata iniziata dal sistema quando un
programma esegue un’operazione su una tabella
(SELECT, UPDATE, INSERT, DELETE,
OPEN CURSOR)
Interruzione di transazioni

La transazione prosegue finché:


viene eseguito EXEC SQL COMMIT WORK : la
transazione termina normalmente con il rilascio dei blocchi sui
dati usati che diventano disponibili
viene eseguito EXEC SQL ROLLBACK WORK (abort
transaction) : comporta la terminazione prematura della
transazione e quindi




(a) il disfacimento di tutte le modifiche (atomicità)
(b) il rilascio dei blocchi usati.
Il programma termina senza errori , normalmente.
Il programma termina con fallimento e provoca l’aborto della
transazione
Esempio: Stampa Ammontare
Ordine e Clienti- Totale Ordini


Program Esempio;
Dichiarazioni ed Inizializzazioni

Lettura dei dati dal terminale


Prima transazione: ricerca ordine
EXEC SQL COMMIT WORK
Stampa risultato prima transazione
Seconda Transazione: recupero e stampa clienti-ammontare totale
END programma
Un esempio più complesso

Supponiamo di avere una tabella
Magazzino(Prodotto, Quantità, Prezzo);

Il seguente programma serve ad un venditore al
momento della richiesta di un ordine da parte di
un cliente.

Si legge la quantità disponibile ed il prezzo, si
legge la quantità ordinata e si crea l’ordine
Program Esempio 2;
VAR
EXEC SQL BEGIN DECLARE SECTION
xQuantita, xPrezzo, prezzoProposto,
quantitaRichiesta
: INTEGER;
NumProdotto: ARRAY [1..3] OF CHAR;
EXEC SQL END DECLARE SECTION
BEGIN
EXEC SQL CONNECT
IdUtente IDENTIFIED BY Password USING Database;
/*IdUtente,Password e Database siano tre costanti
per semplicità*/
Ricerca quantita’ e prezzo
Writeln(‘Scrivi il codice del prodotto’);
read(NumProdotto)
EXEC SQL /*inizio prima transazione*/
SELECT Quantita, Prezzo
INTO :xQuantita, :xPrezzo
FROM Magazzino
WHERE Prodotto=:NumProdotto
EXEC SQL COMMIT WORK
/*fine
della prima transazione*/
Stampa del risultato e lettura
della quantità richiesta
writeln(‘Quantita =‘, xQuantita, ‘Prezzo =‘,
xPrezzo);
writeln;
writeln(‘Scrivi quantita ordinata’);
readln(quantitaRichiesta);
prezzoProposto:= xPrezzo;
Esecuzione dell’ordine
EXEC SQL
SELECT Quantita,Prezzo
INTO :xQuantita, :xPrezzo
FROM Magazzino
WHERE Prodotto = :NumProdotto
IF xQuantita >= quantitaRichiesta AND xPrezzo = prezzoProposto THEN
xQuantita := xQuantita - quantitaRichiesta;
EXEC SQL
UPDATE Magazzino SET Quantita = :xQuantita
WHERE Prodotto = :NumProdotto
...{Soddisfacimento dell’ordine}
ELSE /*quantita’ insufficiente o il prezzo e’ cambiato dal momento
della proposta*/
BEGIN
EXEC SQL ROLLBACK WORK;
writeln(‘Insufficiente quantita’ o cambiamento prezzo’)
...
Transazioni con livelli diversi di
isolamento


Con l’aumentare del numero di transazioni eseguite
concorrentemente in modo serializzabile si può ridurre
l’effettivo grado di concorrenza del sistema a causa del fatto che
aumenta la probabilità di avere transazioni in attesa di dati
bloccati da altre o interrotte per il verificarsi di situazioni di stallo
Per questa ragione i sistemi commerciali prevedono la possibilità
di programmare transazioni rinunciando alla proprietà di
serializzabilità e quindi di isolamento delle transazioni.
SQL-92: SET TRANSACTION
SET TRANSACTION ISOLATION LEVEL
[READ UNCOMMITTED|
READ COMMITTED |
REPEATABLE READ |
SERIALIZABLE
]
READ UNCOMMITTED


Il primo livello di isolamento read uncommitted (detto
anche dirty read o degree of isolation 0) consente
transazioni che fanno solo operazioni di lettura che
vengono eseguite dal sistema senza bloccare in lettura i
dati.
Come conseguenza abbiamo che una transazione può
leggere dati modificati da un’altra transazione non
ancora completata.
READ COMMITTED


Il livello di isolamento read committed (detto anche cursor
stability o degree of isolation 1) stabilisce che i blocchi in
lettura vengano rilasciati subito mentre quelli in
scrittura vengono rilasciati alla terminazione della
transazione.
In questo modo una transazione T che modifica un
dato, quel dato non può essere letto da altri fino a che T
non abbia effettuato un commit o un rollback.
REPEATABLE READ


Repeatable read o anche degree of isolation 2 prevede
che i blocchi in lettura e scrittura siano assegnati
solo su n-uple di tabelle e vengano rilasciati alla
terminazione della transazione.
Presenta il fenomeno dei fantasmi (phantoms),
abbiamo che dati inseriti nella tabella possono
non essere visti.
SERIALIZABLE

Degree of isolation 3. Consiste nel blocco
temporaneo della tabella;

In certe applicazioni troppo 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 esempio.