settimana santa - Chiesa di Milano

Esercitazione 10
SQL: transazioni
Sistemi Informativi T
Versione elettronica: L10.transazioni.pdf
Transazioni



Le transazioni sono unità logiche di elaborazione per cui il DBMS
garantisce le proprietà ACID (Atomicity, Consistency, Isolation,
Durability)
DB2 mette a disposizione quattro livelli di isolamento per un tradeoff tra problemi evitati e livello di concorrenza delle transazioni
Phantom
Unrepeatable
Read
Dirty
Read
Lost
Update
Repeatable Read (RR)
NO
NO
NO
NO
Repeatable Read
Read Stability (RS)
YES
NO
NO
NO
Read Committed
Cursor Stability (CS)
YES
YES
NO
NO
Uncommitted Read
Uncommitted Read (UR)
YES
YES
YES
NO
SQL standard
DB2 terminology
Serializable
Il livello predefinito in DB2 è CS, per cambiarlo prima di connettersi
al database si esegue:
CHANGE ISOLATION TO [ RR | RS | CS | UR ]
Es. 10: transazioni
Sistemi Informativi T
2
Obiettivo dell’esercitazione


Scopo dell’esercitazione è fare esperienza diretta dei diversi livelli di
isolamento delle transazioni e dei relativi problemi:
Se una transazione viene “sospesa”,
cercare di capire perché, e se e perché viene “sbloccata”
L’ambiente di test è una semplice tabella con poche tuple:
CREATE TABLE ISOL(
K INT NOT NULL PRIMARY KEY,
V INT NOT NULL);
INSERT INTO ISOL
VALUES (1,1), (2,1), (3,2), (5,9);
COMMIT;

E’ utile inserire le istruzioni in uno script per poter riportare il DB in
uno stato noto
Es. 10: transazioni
Sistemi Informativi T
3
Come procedere

Disattivare l’autocommit invocando il CLP con DB2 +c (-t ecc.),
avviando due istanze del CLP, che chiameremo Sinistra e Destra
 Oppure lavorare in coppia, con permessi adeguati su ISOL

L’inizio di ogni transazione è implicito e ogni transazione deve essere
terminata esplicitamente da un comando di COMMIT o di ROLLBACK

Per ognuno dei seguenti quattro scenari eseguire i vari passi variando
il livello di isolamento delle transazioni (che va specificato prima di
connettersi al database).
E’ importante rispettare la sequenza con cui le operazioni delle due
transazioni si alternano
Per disconnettersi, eseguire subito prima COMMIT o ROLLBACK


Es. 10: transazioni
Sistemi Informativi T
4
Lost update



Incrementare di 1 l’attributo
V della tupla con K = 5

Incrementare di 1 l’attributo V
della tupla con K = 5 (!?)

Commit

Selezionare la tupla con K = 5
Commit
Commit
Selezionare la tupla con K = 5

Commit

Provare ad eseguire queste transazioni usando il livello di isolamento
Uncommitted Read (UR)
Si ricorda che il Lost update è un problema che viene sempre
evitato...
Es. 10: transazioni
Sistemi Informativi T


5
Dirty Read




Incrementare di 1 l’attributo V
della tupla con K = 5

Selezionare la tupla con K = 5 (!?)

Commit
Rollback
Eseguire le transazioni usando prima il livello di isolamento
Uncommitted Read (UR) (dirty read possibile) e poi Cursor Stability
(CS) (dirty read impossibile)
Ragionare sul valore di V che la transazione Destra vede nei due casi
Es. 10: transazioni
Sistemi Informativi T
6
Unrepeatable Read

Incrementare di 1 l’attributo V
della tupla con K = 5 (!?)

Commit

Selezionare la tupla con K = 5

Commit (solo per RS)

Selezionare la tupla con K = 5
Commit


Eseguire le transazioni usando prima il livello di isolamento Cursor
Stability (CS) e poi Read Stability (RS)
Es. 10: transazioni
Sistemi Informativi T
7
Phantom row (= unrepeatable query)



Selezionare le tuple con V < 5

Commit (solo per RR)

Selezionare le tuple con V < 5
Commit
Inserire la tupla con valori
K=100, V=1 (!?)
Commit



Eseguire queste transazioni usando prima il livello di isolamento Read
Stability (RS) e poi Repeatable Read (RR)
Dopo RS eliminare la tupla inserita (o inserirne una con K = 101, V = 1
per provare RR)
Es. 10: transazioni
Sistemi Informativi T
8
Deadlock ("blocco critico")


Incrementare di 1 l’attributo V
della tupla con K = 5

Incrementare di 1 l’attributo V
della tupla con K = 3

Selezionare la tupla con K = 5
…
Selezionare la tupla con K = 3

…

Eseguire le transazioni usando prima il livello di isolamento Cursor
Stability (CS) e poi Read Stability (RS)


Nel secondo caso attendere fiduciosi 
Es. 10: transazioni
Sistemi Informativi T
9
…e per finire (1)




“No fai da te…” è un servizio del comune di Bologna per fissare appuntamenti
con diverse tipologie di artigiani per esigenze domestiche.
Autenticandosi con il codice fiscale e fornendo nome, cognome, indirizzo e,
nel caso, telefono ed email, è possibile fare ricerche specificando la tipologia
di artigiano (idraulico, elettricista, ecc.), il tipo di intervento richiesto, una sua
descrizione specifica, e l'urgenza (in giornata, entro 2 giorni, in settimana,
ecc.). Sulla base del tipo di servizio offerto e dell’urgenza è fissata una tariffa
oraria, comune a tutti gli artigiani che offrono quel tipo di servizio.
Per ogni richiesta accettata viene generata una ricevuta in cui compaiono,
oltre a data e ora di prenotazione e dell’intervento, anche la partita IVA
dell’artigiano assegnato.
Dopo l'intervento, si registra l'effettivo numero di ore impiegate, l'importo
complessivo (calcolato) ed eventuali note
Es. 10: transazioni
Sistemi Informativi T
10
…e per finire (2)




Produrre con DB-Main uno schema concettuale
Produrre in DB2 uno schema relazionale
Completare con opportuni trigger
Definire le istruzioni SQL (con specifica di eventuali transazioni)
necessarie a supportare le seguenti operazioni



Gestione di una nuova richiesta di appuntamento
Accettazione di una richiesta
Registrazione di intervento eseguito
Es. 10: transazioni
Sistemi Informativi T
11

Download Report