Corso di Basi di Dati
Il Linguaggio SQL
Home page del corso:
http://www.cs.unibo.it/~difelice/dbsi/
Il Linguaggio SQL
SQL (Structured Query Language) e’ il linguaggio di
riferimento per le basi di dati relazionali.
Diverse versioni del linguaggio:







SQL-86  Costrutti base
SQL-89  Integrita’ referenziale
SQL-92 (SQL2)  Modello relazionale, struttura a livelli
SQL:1999 (SQL3)  Modello ad oggetti
SQL:2003 (SQL3)  Nuove parti: SQL/JRT, SQL/XML
SQL:2006 (SQL3)  Estensione di SQL/XML
SQL:2008 (SQL3)  Lievi aggiunte
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui,
esistono molti costrutti avanzati (i) definiti in
SQL3 e/o (ii) dipendenti dallo specifico DBMS.
 Procedure (Stored Procedures)
 Trigger
 Permessi
 Transazioni
Il Linguaggio SQL
Stored Procedures  Frammenti di codice SQL,
con la possibilita’ di specificare un nome, dei
parametri in ingresso e dei valori di ritorno.
Procedure ModificaStipendio (MatricolaNew:
varchar(20), StipendioNew: smallint)
update Impiegati
set Stipendio=StipendioNew
where Matricola=MatricolaN
Ogni DBMS offre estensioni procedurali differenti …
Il Linguaggio SQL
SQL
DB
TABELLE
APPLICAZIONE
ESTERNA
MODELLO senza
STORED PROCEDURE
DATI
MODELLO con
STORED PROCEDURE
 Efficienza
 Maggiore espressivita’
 …
NOME PROCEDURA
+ PARAMETRI
APPLICAZIONE
ESTERNA
DB
TABELLE
PROCEDURE
DATI
Il Linguaggio SQL
Esempio: definizione di funzioni in MySQL:
CREATE FUNCTION function_name
RETURNS type_return
… List of SQL routine statements
CREATE FUNCTION echo(s CHAR(20))
RETURNS CHAR(50)
RETURN(s)
mysql>> SELECT echo(“Hello”);
Il Linguaggio SQL
Esempio: definizione di funzioni in PostgreSQL:
CREATE FUNCTION add_three_values(v1
anyelement, v2 anyelement, v3 anyelement)
RETURNS anyelement AS $$
DECLARE
result ALIAS for $0
BEGIN
result:=v1 + v2 +v3;
RETURN result
END
Il Linguaggio SQL
Le estensioni procedurali consentono di:
 Creare funzioni e procedure trigger-based.
 Aggiungere strutture di controllo al linguaggio SQL (es.
cicli, strutture condizionali if then else, etc).
 Dichiarare variabili e tipi di dato user-defined.
 Definire funzioni avanzate ed ottimizzate, che sono
ritenute “sicure” dal DBMS.
Il Linguaggio SQL
Ogni DBMS offre una sua estensione procedurale:
 PL/SQL  Linguaggio di Oracle Server
 SQL PL  Linguaggio di IBM DB2
 PL/pgSQL  Linguaggio di PostgreSQL
…
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
 Costrutti condizionali
if <Condizione> then … else … endif
IF user_id <> 0 THEN
UPDATE USERS
SET USERS.email=v_email
WHERE (USERS.user_id = user_id)
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
 Costrutti iterativi (while)
while (Expression) LOOP
statements
END LOOP
WHILE ncycle>0 LOOP
UPDATE SALARY
SET SALARY.amount=SALARY.amount -100
ncycle:=ncycle -1;
END LOOP
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
 Costrutti iterativi (for)
For record_or_row IN query LOOP
statements
END LOOP
FOR Studente IN SELECT * FROM STUDENTI LOOP
UPDATE ESAMI
SET Voto=30
WHERE (Corso=“Basi di Dati”)
END LOOP
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui,
esistono molti costrutti avanzati (i) definiti in
SQL3 e/o (ii) dipendenti dallo specifico DBMS.
 Procedure (Stored Procedures)
 Trigger
 Permessi
 Transazioni
Il Linguaggio SQL
ORDINE
Nome
Codice
Quantita
Xbee Radio Shield
123
3
Arduino Uno Shield
5565
2
Arduino Ethernet
14354
1
Vorrei implementare un comportamento del tipo:
 Ogni volta che viene inserito/modificato un nuovo ordine
con piu’ di 5 quantita’ nel DB viene inviata una mail al
Titolare dell’azienda ..
Il Linguaggio SQL
ORDINE
MAGAZZINO
ACQUISTO
Nome
Codice
Quantita
Codice
Quantita
Codice
Q.a
Data
Xbee Radio Shield
123
3
123
0
123
3
Arduino Uno
Shield
5565
2
5565
2
1/2/
2012
Arduino Ethernet
14354
1
14354
1
Vorrei implementare un comportamento del tipo:
 Ogni volta in cui l’utente fa un’ordine, si aggiorna la tabella
Magazzino, e nel caso non ci siano piu’ prodotti di quel
tipo, si aggiorni anche la tabella Acquisti …
Il Linguaggio SQL
Trigger (o regole attive)  meccanismi di gestione
della base di dati basati sul paradigma ECA
(Evento/Condizione/Azione).
 Evento: primitive per la manipolazione dei dati
(insert, delete, update)
 Condizione: Predicato booleano
 Azione: sequenza di istruzioni SQL, talvolta
procedure SQL specifiche del DBMS.
Il Linguaggio SQL
Trigger (o regole attive)  meccanismi di gestione
della base di dati basati sul paradigma ECA
(Evento/Condizione/Azione).
A che servono i Trigger?
1. Garantire il soddifacimento di vincoli di
integrita’ referenziale, e/o specificare meccanismi
di reazione ad hoc in caso di violazione dei vincoli!
Il Linguaggio SQL
CORSI
ESAMI
Nome
Codice
Crediti
Corso
Studente
Voto
Basi di dati
6464
12
0121
4324235245
30L
Programmazione
1213
12
1213
4324235245
25
Sistemi Operativi
1455
6
1213
9854456565
18
Q. Che accade se un valore nella tabella esterna viene
cancellato o viene modificato?
A. Il vincolo di integrita’ referenziale nella tabella interna
potrebbe non essere piu’ valido! Cosa fare?
Il Linguaggio SQL
Trigger (o regole attive)  meccanismi di gestione
della base di dati basati sul paradigma ECA
(Evento/Condizione/Azione).
A che servono i Trigger?
2. Specificare regole aziendali (business rules),
ossia vincoli generici sulo schema della base di dati
(es. Un impiegato non puo’ avere un aumento di stipendio
superiore al 10%, pena annullamento della transazione).
Il Linguaggio SQL
Trigger (o regole attive)  meccanismi di gestione
della base di dati basati sul paradigma ECA
(Evento/Condizione/Azione).
SINTASSI SQL3
Create trigger Nome
Modo Evento on Tabella
[referencing Referenza]
[for each Livello]
[when (IstruzioneSQL)]
Istruzione/ProceduraSQL
EVENTO
CONDIZIONE
AZIONE
Il Linguaggio SQL
 Modo  before/after
 Evento  insert/delete/update
 Referencing  qui possono essere inserite variabili
globali per aumentare l’espressivita’ del trigger…
 Livello  row (Il trigger agisce a livello di righe)
statement (Il trigger agisce globalmente a livello
di tabella)
Due modalita’ di esecuzione: immediata vs differita.
Il Linguaggio SQL
Esempio di Trigger in SQL3
CREATE TRIGGER CHECKAUMENTO
BEFORE UPDATE OF CONTO ON IMPIEGATO
FOR EACH ROW
WHEN (NEW.STIPENDIO > OLD.STIPENDIO * 1.2)
SET NEW.STIPENDIO=OLD.STIPENDIO * 1.2
 Modo e’ definito come before.
 Evento e’ definito come update.
 Livello e’ definito come row.
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui,
esistono molti costrutti avanzati (i) definiti in
SQL3 e/o (ii) dipendenti dallo specifico DBMS.
 Procedure (Stored Procedures)
 Trigger
 Permessi
 Transazioni
Il Linguaggio SQL
SQL2/SQL3 prevede meccanismi di controllo di
accesso alle risorse dello schema del DB.
Di default, ogni risorsa appartiene all’utente che
l’ha definita … Su ciascuna risorsa sono definiti
dei privilegi (grant):




insert/update/delete  tabelle/viste
select  tabelle/viste
references tabelle/attributi
usage  domini
Il Linguaggio SQL
Il comando grant consente di assegnare privilegi
su una certa risorsa ad utenti specifici.
grant Privilegio on Risorsa/e to Utente/i
[with grant option]
L’opzione with grant option consente di
propagare il privilegio ad altri utenti del sistema…
grant select on Impiegati to Marco with grant option
grant delete on Impiegati, Salari to Marco, Michele
Il Linguaggio SQL
Il comando revoke consente di revocare privilegi
su una certa risorsa ad utenti specifici.
revoke Privilegio on Risorsa/e from Utente/i
[cascade|restrict]
L’opzione cascade agisce ricorsivamente sui
privilegi eventualmente concessi da quell’utente …
revoke select on Impiegati to Marco cascade
revoke delete on Impiegati, Salari to Marco, Michele
Il Linguaggio SQL
In SQL3 e’ possibile definire dei ruoli per l’accesso
alle risorse di un database.
Ruolo = Contenitore di privilegi
ESEMPIO di RUOLO
Insert su Tabella Impiegati
Select su Tabella Retribuzioni
Update su Tabella Progetti
 Comandi SQL3: create role/set role
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui,
esistono molti costrutti avanzati (i) definiti in
SQL3 e/o (ii) dipendenti dallo specifico DBMS.
 Procedure (Stored Procedures)
 Trigger
 Permessi
 Transazioni
Gestione delle Transazioni
Le transazioni rappresentano unita’ di lavoro
elementare (insiemi di istruzioni SQL) che modificano
il contenuto di una base di dati.
start transaction
update SalariImpiegati
set conto=conto*1.2
where (CodiceImpiegato = 123)
commit work
Le transazioni
sono comprese
tra una start
transaction
ed una
commit/
rollback
Gestione delle Transazioni
Le transazioni rappresentano unita’ di lavoro
elementare (insiemi di istruzioni SQL) che modificano
il contenuto di una base di dati.
start transaction
update SalariImpiegati
set conto=conto-10
where (CodiceImpiegato = 123)
if conto >0 commit work;
else rollback work
Le transazioni
sono comprese
tra una start
transaction
ed una
commit/
rollback
Gestione delle Transazioni
PROPRIETA’ ACIDE DELLE TRANSAZIONI
 Atomicita’  La transazione deve essere eseguita con la
regola del “tutto o niente”.
 Consistenza  La transazione deve lasciare il DB in uno
stato consistente, eventuali vincoli di integrita’ non
devono essere violati.
 Isolamento  L’esecuzione di una transazione deve
essere indipendente dalle altre.
 Persistenza  L’effetto di una transazione che ha fatto
commit work non deve essere perso.
Gestione delle Transazioni
Gestione delle transazioni
Gestione della concorrenza
Gestione dell’affidabilita’
Gestore dell’affidabilita’  garantisce atomicita’ e persistenza
… COME? Usando log e checkpoint.
Gestore della concorrenza  garantisce l’isolamento in caso di
esecuzione concorrente di piu’ transazioni.
Gestione delle Transazioni
In un sistema reale, le transazioni vengono eseguite
in concorrenza per ragioni di efficienza / scalabilita’.
… Tuttavia, l’esecuzione concorrente determina un
insieme di problematiche che devono essere gestite …
T1= Read(x); x=x+1; Write(x); Commit Work
T2= Read(x); x=x+1; Write(x); Commit Work
Se x=3, al termine delle due transazioni x vale 5 (esecuzione
sequenziale) … cosa accade in caso di esecuzione concorrente?
Gestione delle Transazioni
Problema 1: Perdita di Aggiornamento
Transazione1 (T1)
Transazione2 (T2)
Read(x)
x=x+1
Read(x)
x=x+1
Write(x)
Commit work
T1
scrive 4
Write(x)
Commit work
T2
scrive 4
Gestione delle Transazioni
Problema 2: Lettura sporca
Transazione1 (T1)
Transazione2 (T2)
Read(x)
x=x+1
Write(x)
Read(x)
Commit work
Rollback work
T2
legge 4!
Gestione delle Transazioni
Problema 3: Letture incosistenti
T1
legge 3!
Transazione1 (T1)
Transazione2 (T2)
Read(x)
Read(x)
x=x+1
Write(x)
Commit work
T1
legge 4!
Read(x)
Commit work
Gestione delle Transazioni
Problema 4: Aggiornamento Fantasma
Vincolo:
x+y+z
deve
essere =
a 1000
Transazione1 (T1)
Transazione2 (T2)
Read(x)
Read(y)
Read(y)
y=y-100
Read(z)
z=z+100
Write(y), Write(z)
Commit work
Vincolo
violato!!
Read(z)
s=x+y+z; commit work
Gestione delle Transazioni
Date un insieme di transazioni T1,T2, Tn, di cui
ciascuna formata da un certo insieme di operazioni di
scrittura (wi) e lettura (ri):
Es. T1=r1(x) r1(y) r1(z) w1(y) …
Si definisce schedule la sequenza di operazioni di
lettura/scrittura di tutte le transazioni cosi’ come
eseguite sulla base di dati:
r1(x) r2(y) r1(y) w4(y) w2(z) …
Gestione delle Transazioni
Uno schedule S si dice seriale se le azioni di ciascuna
transazione appaiono in sequenza, senza essere
inframezzate da azioni di altre transazioni.
S={T1, T2, … Tn}
Schedule seriale ottenibile se:
(i) Le transazioni sono eseguite uno alla volta
(scenario non realistico)
(ii) Le transazioni sono completamente indipendenti
l’una dall’altra (improbabile)
Gestione delle Transazioni
Uno schedule S si dice serializzabile se produce lo
stesso risultato di un qualunque scheduler seriale S’
delle stesse transazioni.
Schedule
Serializzabili
Schedule
Gestione delle Transazioni
Come implementare il controllo della concorrenza?
I DMBS commerciali usano il meccanismo dei lock
 per poter effettuare una qualsiasi operazioni di
lettura/scrittura su una risorsa (tabella o valore di una
cella), e’ necessario aver precedentemente acquisito il
controllo (lock) sulla risorsa stessa.
 Lock in lettura (accesso condiviso)
 Lock in scrittura (mutua esclusione)
Gestione delle Transazioni
Su ogni lock possono essere definite due operazioni:
 Richiesta del lock in lettura/scrittura.
 Rilascio del lock (unlock) acquisito in precedenza.
AZIONE
STATO DELLA RISORSA
Libero
r_locked
w_locked
r_lock
OK/r_locked
OK/r_locked
NO/w_locked
w_lock
OK/w_locked
NO/r_locked
NO/w_locked
unlock
Errore
OK/dipende
OK/libero
Gestione delle Transazioni
Lock Manager  componente del DBMS
responsabile di gestire i lock alle risorse del DB, e di
rispondere alle richieste delle transazioni.
STRUTTURE DATI del LOCK MANAGER
Per ciascun oggetto x del DBMS:
State(x)  stato dell’oggetto (libero/r_locked/w_locked)
Active(x)  lista transazioni attive sull’oggetto
Queued(x)  lista transazioni bloccate sull’oggetto
Gestione delle Transazioni
Lock Manager  componente del DBMS
responsabile di gestire i lock alle risorse del DB, e di
rispondere alle richieste delle transazioni.
AZIONI DEL LOCK MANAGER
1. Riceve una richiesta (r_lock, w_lock, unlock) da una
transazione T, su un oggetto x (oggetto=tabella, colonna, etc).
2. Controlla la tabella stato/azione (slide precedente).
3. Se la risposta e’ OK, aggiorna lo stato della risorsa, e
concede il controllo della risorsa alla transazione T.
4. Se la risposta e’ NO, inserisce la transazione T in una coda
associata all’oggetto x.
Gestione delle Transazioni
RISORSA x
STATO(x): r_locked
T1: r_lock(x)
LOCK
MANAGER
ACTIVE(x): {T
{} 1}
QUEUED(x): {}
Answer to T1: OK
Gestione delle Transazioni
RISORSA x
STATO(x): r_locked
T2: r_lock(x)
LOCK
MANAGER
ACTIVE(x): {T
{} 1,
1}T2}
QUEUED(x): {}
Answer to T2: OK
Gestione delle Transazioni
RISORSA x
STATO(x): r_locked
T3: w_lock(x)
LOCK
MANAGER
ACTIVE(x): {T
{} 1,
1}T2}
QUEUED(x): {T
{} 3}
Answer to T3: NO
Gestione delle Transazioni
Two Phase Lock (2PL)  Una transazione, dopo
aver rilasciato un lock, non puo’ acquisirne un altro.
Risorse
 In pratica, una transazione acquisisce prima tutti
i lock delle risorse di cui necessita …
Time
Gestione delle Transazioni
TRANSAZIONI
T1= r(x), w(y), Commit
T2= r(y), Commit
T1
T2
r_lock(x)
r(x)
unlock(x)
r_lock(y)
r(y)
SCHEDULE
unlock(y)
Commit
w_lock(y)
w(y)
A. NO!
unlock(y)
Commit
Gestione delle Transazioni
TRANSAZIONI
T1= r(x), w(y), Commit
T2= r(y), Commit
T1
T2
r_lock(x)
r(x)
w_lock(y)
r_lock(y)
SCHEDULE
w(y)
unlock(y)
unlock(x)
r(y)
unlock(y)
A. SI!
Commit
Commit
Gestione delle Transazioni
Two Phase Lock (2PL)  Una transazione, dopo
aver rilasciato un lock, non puo’ acquisirne un altro.
 Ogni schedule che rispetta il 2PL e’ anche
serializzabile (perche’ ?).
 Ogni schedule che rispetta il 2PL non puo’
incorrere in configurazioni erronee dovute a:
aggiornamento fantasma, lettura inconsistente,
perdita di aggiornamento … che accade in caso
di lettura sporca?
Gestione delle Transazioni
TRANSAZIONI
T1= r(x), w(y), Commit
T2= r(y), Commit
T1
T2
r_lock(x)
r(x)
w_lock(y)
r_lock(y)
SCHEDULE
w(y)
unlock(y)
unlock(x)
r(y)
unlock(y)
Abort
Commit
Gestione delle Transazioni
Strict Two Phase Lock (2PL)  I lock di una
transazione sono rilasciati solo dopo aver effettuato
le operazioni di commit/abort.
 Variante strict del 2PL, utilizzato in alcuni
DBMS commerciali.
 Uno schedule che rispetta lo S2PL eredita tutte
le proprieta’ del 2PL, ed inoltre NON presenta
anomalie causate da problemi di lettura sporca.
Gestione delle Transazioni
TRANSAZIONI
T1= r(x), w(y), Commit
T2= r(y), Commit
T1
T2
r_lock(x)
r(x)
w_lock(y)
r_lock(y)
SCHEDULE
w(y)
Abort
unlock(x)
unlock(y)
r(y)
A. SI!
Commit
unlock(y)
Gestione delle Transazioni
PROBLEMA: I protocolli 2PL e S2PL possono
generare schedule con situazioni di deadlock.
TRANSAZIONI
T1= r(x), w(y), Commit
T2= r(y), w(x), Commit
T1
T2
r_lock(x)
r_lock(y)
r(x)
r(y)
SCHEDULE
w_lock(y)
w_lock(x)
Gestione delle Transazioni
Per gestire le situazioni di deadlock causate dal Lock
Manager, si possono usare tre tecniche:
1. Uso dei timeout  ogni operazione di una
transazione ha un timeout entro il quale deve
essere completata, pena annullamento (abort)
della transazione stessa.
T1: r_lock(x,4000), r(x), w_lock(y,2000), w(y),
commit, unlock(x), unlock(y)
Gestione delle Transazioni
Per gestire le situazioni di deadlock causate dal Lock
Manager, si possono usare tre tecniche:
2. Deadlock avoidance prevenire le configurazioni
che potrebbero portare ad un dealock … COME?
 Lock/Unlock di tutte le risorse allo stesso tempo.
 Utilizzo di time-stamp o di classi di priorita’ tra
transazioni (problema: puo’ determinare starvation!)
Gestione delle Transazioni
Per gestire le situazioni di deadlock causate dal Lock
Manager, si possono usare tre tecniche:
3. Deadlock detection utilizzare algoritmi per
identificare eventuali situazioni di deadlock, e
prevedere meccanismi di recovery dal deadlock
 Grafo delle richieste/risorse utilizzato per identificare la
presenza di cicli (corrispondenti a deadlock)
 In caso di ciclo, si fa abort delle transazioni coinvolte
nel ciclo in modo da eliminare la mutua dipendenza …
Gestione delle Transazioni
Un metodo alternativo al 2PL per la gestione della
concorrenza in un DBMS prevede l’utilizzo dei timestamp delle transazioni (metodo TS).
 Ad ogni transazione si associa un timestamp che
rappresenta il momento di inizio della transazione.
 Ogni transazione non puo’ leggere o scrivere un
dato scritto da una transazione con timestamp
maggiore.
 Ogni transazione non puo’ scrivere su un dato gia’
letto da una transazione con timestamp maggiore.
Gestione delle Transazioni
Un metodo alternativo al 2PL per la gestione della
concorrenza in un DBMS prevede l’utilizzo dei timestamp delle transazioni (metodo TS).
 Ad ogni oggetto x si associano due indicatori:
 WTM(x)  timestamp della transazione che ha
fatto l’ultima scrittura su x.
 RTM(x)  timestamp dell’ultima transazione
(ultima=con t piu’ alto) che ha letto x.
Gestione delle Transazioni
Lo scheduler di sistema verifica se un’eventuale
azione (rt(x) o wt(x)) eseguita da una transazione
T con timestamp t puo’ essere eseguita o meno:
 rt(x)  Se t<WTM(x) allora la transazione
viene uccisa. Se t>=WTM(x), la richiesta viene
eseguita, ed RTM(x) viene aggiornato al massimo
tra il valore precedente di RTM(x) e t stesso.
Gestione delle Transazioni
Lo scheduler di sistema verifica se un’eventuale
azione (rt(x) o wt(x)) eseguita da una transazione
T con timestamp t puo’ essere eseguita o meno:
 wt(x)  Se t<WTM(x) oppure t<RTM(x)
allora la transazione viene uccisa. Altrimenti, la
richiesta viene accettata, e WTM(x) viene posto
uguale a t.
Gestione delle Transazioni
ESEMPIO: RTM(x)=6, WTM(x)=3
T5: r5(x)
 OK, RTM(x)=6
T9: w9(x)
 OK, WTM(x)=9
T6: w6(x)
 NO, T6 uccisa
T8: r8(x)
 NO, T8 uccisa
T10: r10(x)
 OK, RTM(x)=10
Gestione delle Transazioni
In SQL-3, ed in molti DBMS commerciali (DB2,
MySQL, PostgreSQL, Oracle, etc) sono definiti
quattro livelli di isolamento tra transazioni:
Livello
Descrizione
read uncommitted
(read only) La transazione non emette lock in lettura,
e non rispetta lock esclusivi da altre transazioni.
read committed
Richiede lock condivisi per effettuare le letture.
repeteable read
Applica il protocollo S2PL anche in lettura.
serializable
Applica il protocollo S2PL con lock di predicato.
 S2PL utilizzato per le operazioni di scrittura, da tutti i livelli.
Gestione delle Transazioni
SINTASSI MySQL
 Iniziare una transazione e completarla:
START TRANSACTION
… (Statements SQL)
COMMIT/ROLLBACK
 Configurare livello di isolamento di esecuzione:
SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ | READ COMMITTED |
READ UNCOMMITTED | SERIALIZABLE
Gestione delle Transazioni
SINTASSI MySQL
 Le transazioni sono utilizzabili solo su tabelle di
tipo InnoDB (ACID-compliant).
 E’ possibile gestire manualmente le operazioni di
lock su tabelle (non consigliabile su tabelle di tipo
InnoDB):
LOCK TABLES
tabella { READ | WRITE }