© Giuseppe Berio – DI - UNITO 1

Transazioni

© Giuseppe Berio – DI - UNITO 2

La Metodologia adottata nel Corso

Determinazione requisiti

Specifica dei requisiti

Progetto

Implementazione

1

2,3

4

5,6

Mission Statement

+Glossario

+Lista

Funzionalià

1

3 6

2 4 5

Descrizione Funzionalità

Schema Logico

Relazionale

SchemaFisico

CodiceTransazioniIn PL/SQL

Architettura: basedati centralizzata

Schema concettuale

EA

Verifica & Validazione7

© Giuseppe Berio – DI - UNITO 3

Contenuti

• Elementi preliminari

• Problemi canonici della concorrenza della transazioni nei DBMS

• Programmazione di transazioni in PL/SQL

© Giuseppe Berio – DI - UNITO 4

Transazioni: Motivi• Affidabilità

– protezione da malfunzionamenti (hardware, e del software di base con perdita dei dati nelle memorie centrali)

– Impedire che programmi possano effettuare “modifiche parziali” su una basedati dovute, ad esempio, a vincoli di integrità non verificati, ad errori durante l’esecuzione dei programmi stessi

• Controllo della concorrenza– Impedire, ragionevolmente, l’uso di dati non

completamente elaborati o incerti dovuti all’esecuzione concorrente di più programmi su una basedati

A

C

I

D

© Giuseppe Berio – DI - UNITO 5

Proprietà ACID

A atomicity

C consistency

I isolation

D durability

© Giuseppe Berio – DI - UNITO 6

Architettura di un DBMS Relazionale

Gestore accesso

Gestore strutture di memorizzazione

Gestore buffer

Gestore memoria permanente

Gestore concorrenza

Gestore

affidabilità

Ottimizzatore Esecutore piani

d’accesso

Gestore autorizzazioni

Gestore catalogo

Moduli di gestione delle transazioniDati

© Giuseppe Berio – DI - UNITO 7

Definizione di Transazione in ambiente relazionale

• Una transazione è una sequenza d’istruzioni (in SQL) su una basedati, con un inizio ed una fine

• In generale, la fine della transazione può essere esplicita attraverso le due istruzioni seguenti:– Commit

– Rollback

• L’istruzione di rollback può essere implicita cioè eseguita dal DBMS e non parte della transazione

© Giuseppe Berio – DI - UNITO 8

Database Voli e Prenotazioni

Tratte(NumVolo, Da, A, ora-partenza, ora-arrivo)

Voli(NumVolo, DataVolo, aeroplano, posti-totali)

Tariffe-disponibili(id-tariffa, NumVolo, DataVolo, num-posti, prezzo)

Prenotazioni(id-prenotazione, id-passeggero, NumVolo, DataVolo, prezzo)

(a) Transazione che prenota, per un dato cliente, un posto di minor prezzo nel volo 13 per il 13 Luglio.

(b) Transazione che stampa un rapporto contenente per ogni volo la percentuale di posti che sono prenotati e il guadagno totale.

Database Conti Bancari e Clienti

Clienti(cod-fiscale, nome-cognome, indirizzo, citta’, cap, telefono, stato)

Conti(num-conto, cod-fiscale, saldo)

Operazioni(num-conto, num-operazione, data-operazione, somma, sportello)

(a) Transazione che deposita una somma di denaro su un determinato conto corrente.

(b) Transazione che trasferisce soldi da un conto corrente ad un altro.

(c) Transazione che trova la somma dei prelievi relativi ad uno specifico sportello (automatico o meno).

(d) La transazione che stampa la lettera (la quale informa di un nuovo tipo di conto) per ogni cliente la cui somma dei saldi di ciascun conto supera 10000 Euro.

© Giuseppe Berio – DI - UNITO 9

Update Emp Set Department = ‘Sales’Where Department = ‘Service’And Position <> ‘Manager’

R(t) W(t)

Update Emp Set Department = ‘Sales’Where code=567

R(t1) R(t2)…. W(t1) W(t2)….

Fondamenti della teoria della gestione della concorrenza

Update Emp Set Department = ‘Sales’Where code=567

© Giuseppe Berio – DI - UNITO 10

Aggiornamento fantasma

P1 Passo P2

1 r (x) 2 x := x – 10 3 w (x)

sum := 0 4r (x) 5r (y) 6sum := sum +x 7sum := sum + y 8

9 r (y) 10 y := y + 10 11 w (y)

somma sbagliata

Osservazioni: il problema è l’interleaving r2(x) w2(x) r1(x) r1(y) r2(y) w2(y)il problema non sorge se l’esecuzione è sequenziale

schedule

© Giuseppe Berio – DI - UNITO 11

Lost Update

P1 Passo P2

/* x = 100 */r (x) 1

2 r (x)x := x+100 4 x := x+200w (x) 5

/* x = 200 */ 6 w (x)

/* x = 300 */

update “lost”

Osservazione: il problema è l’interleaving r1(x) r2(x) w1(x) w2(x)il problema non sorge se l’esecuzione è sequenziale

© Giuseppe Berio – DI - UNITO 12

Dirty Read

P1 Passo P2

r (x) 1x := x + 100 2w (x) 3

4 r (x) 5 x := x - 100

failure & rollback 6 7 w (x)

non può assicurarela validità dei dati letti

Osservazione: il rollback di una transazione incide sulle transazioni concorrentiil problema non sorge se l’esecuzione è sequenziale

© Giuseppe Berio – DI - UNITO 13

P1 Passo P2

1 r (x) 2 x := x – 10 3 w (x)

r (x) 4 5 6 x := x + 20 7 w (x)

r (x) 8

Non legge il medesimo valore

Osservazioni: il problema è l’interleaving r2(x) w2(x) r1(x) w2(x) r1(x)il problema non sorge se l’esecuzione è sequenziale

Letture inconsistenti

© Giuseppe Berio – DI - UNITO 14

Sequenzialità e Concorrenza

Ipotesi (conti correnti): 500000 clienti, 30000 transazioni di richiesta deposito,

30000*0,1’’=3000’’

30000*0,01’’=300’’

30000*0,001’’=30’’

Un cliente potrebbe attendere:

Necessario capire cosa deve essere concorrente e cosa deve essere sequenziale: teoria della concorrenza

© Giuseppe Berio – DI - UNITO 15

Serializzabilità

• Dato un qualunque schedule tra due o più transazioni, si dice serializzabile sse il risultato della sua esecuzione è uguale ad uno schedule sequenziale delle transazioni

• Uno schedule relativo a un insieme di transazioni è sequenziale sse corrisponde all’esecuzione in una certa sequenza della transazioni stesse

• La serializzabilità non coincide con la sequenzialità

© Giuseppe Berio – DI - UNITO 16

Livelli di Isolamento SQL 92

Livello Letture Sporche

Letture inconsistenti

Fantasma

Read uncommitted

Si Si Si

Read committed

No Si Si

Repeatable read

No No Si

Serializable No No No

SQL 92

© Giuseppe Berio – DI - UNITO 17

Realizzazione del Controllo della Concorrenza nei

DBMS• Controllo ottimistico

– La probabilità di dover annullare ciò che è stato fatto poiché non si ha serializzabilità è bassa

– Efficiente

• Controllo pessimistico – La probabilità di dover annullare ciò che è stato

fatto poiché non si ha serializzabilità è alta– Molto sicuro

© Giuseppe Berio – DI - UNITO 18

Gestire la serializzabilità con il Locking a due fasi

stato dato libero locked-R locked-W

richiesta

lock-R si si no

lock-W si no no

unlock NA si si

© Giuseppe Berio – DI - UNITO 19

Funzionamento del locking a due fasi

P1 Passo P2

1 Lock(x,w)r (x)

2 x := x – 10 3 w (x) 4 Lock(y,w) 5 r (y) 6 y := y + 10 7 w (y) 8 Unlock(x) 9 Unlock(y)

sum := 0 10Lock(x,r) 11Lock(y,r) 12r (x) 13r (y) 14sum := sum +x 15sum := sum + y 16Unlock(x) 17Unlock(y) 18

Fase 1

Fase 2

© Giuseppe Berio – DI - UNITO 20

Deadlock

P1 Time P2

1 Lock(y,w)Lock(x,w) 2Lock(y,w) 3

4 Lock(x,w)

© Giuseppe Berio – DI - UNITO 21

EmpJones Service Clerk 20000Meier Service Clerk 22000Paulus Service Manager 42000Smyth Toys Cashier 25000Brown Sales Clerk 28000Albert Sales Manager 38000

Name Department Position Salary

Update transaction t:

(a) Delete From EmpWhere Department = ‘Service’And Position = ‘Manager’

(b) Insert Into Emp Values(‘Smith’, ‘Service’, ‘Manager’, 40000)

(c) Update Emp Set Department = ‘Sales’Where Department = ‘Service’And Position <> ‘Manager’

(d) Insert Into Emp Values(‘Stone’, ‘Service’, ‘Clerk’, 13000)

Retrieval transaction q:

Select Name, Position, SalaryFrom EmpWhere Department = ‘Service’

Retrieval transaction p:

Select Name, Position, SalaryFrom EmpWhere Department = ‘Sales’

Osservazioni:• Interleaving di q e t produce un cosiddetto fantasma• Mettere un lock sui record nelle tabelle non risolve il problema ma è necessario passare ad un lock su predicato

Un problema aggiuntivo: il fantasma

© Giuseppe Berio – DI - UNITO 22

Gestione dell’affidabilità (in breve)

File di log

Transazione:

Delete From EmpWhere Department = ‘Service’And Position = ‘Manager’

Commit;

(del, BeforeState(p1!)); ….; (del, BeforeState(pn!))….commit;

Controllo della concorrenza

Dati

Gestore dell’affidabilità

© Giuseppe Berio – DI - UNITO 23

Controllo della Concorrenza in Oracle

• Controllo della concorrenza multiversione;

transazioneT1 transazione T2

dato

Snapshot 1 Snapshot 2

first commiter wins

© Giuseppe Berio – DI - UNITO 24

Programmazione di Transazioni• Una transazione è programmata attraverso una

serie di istruzioni SQL, in un qualche linguaggio di programmazione (e con un certo approccio)

• E’ quindi necessario distinguere tra:– il programma attraverso cui si programma(no) la(e)

transazione(i)– la singola transazione

• Ciò implica anche la distinzione del concetto di terminazione che può essere – del programma– della transazione

© Giuseppe Berio – DI - UNITO 25

Introduzione al PL/SQL

• Un linguaggio di programmazione completo di tutte le istruzioni e strutture dati di un linguaggio di programmazione imperativo

• Permette di usare select, update, delete e insert nel programma

• Permette di usare commit e rollback nel programma• Ipotizza un controllo ottimistico della concorrenza• Usa le eccezioni per gestire eventuali problemi che

possono sorgere nell’esecuzione di un’istruzione• Progettato anche per ORACLE come ORDBMS

© Giuseppe Berio – DI - UNITO 26

 

if qt1<> 0 thenbeginupdate Merce set quantitàdiponibile=quantitàdisponibile-qt1where tipo=1;update OrdiniTipo1 set stato='evaso' where codordine=ilcodordine;exceptionwhen others then RAISE MerceInsufficente;end; if qt2<> 0 thenbeginupdate Merce set quantitàdiponibile=quantitàdisponibile-qt2 where tipo=2;update OrdiniTipo2 set stato='evaso' where codordine=ilcodordine;exceptionwhen others then RAISE MerceInsufficente;end; if qt3<> 0 thenbeginupdate Merce set quantitàdiponibile=quantitàdisponibile-qt3 where tipo=3;update OrdiniTipo3 set stato='evaso' where codordine=ilcodordine;exceptionwhen others then RAISE MerceInsufficente;end; Commit;Exceptionwhen MerceInsufficente then begin rollback; :riprovare:='riprovare'; end;when others then rollback; end;/

print(riprovare); (SQLPLUS)

variable riprovare varchar; (SQLPLUS)

Declare ilcodordine varchar2(5); qt1,qt2,qt3 number(6);MerceInsufficente Exception; beginbeginselect quantitàordinata into qt1 from OrdiniTipo1 where codordine=ilcodordine;exceptionwhen no_data_found then qt1:=0;end;beginselect quantitàordinata into qt2 from OrdiniTipo2 where codordine=ilcodordine;exceptionwhen no_data_found then qt2:=0;end;beginselect quantitàordinata into qt3 from OrdiniTipo3 where codordine=ilcodordine;exceptionwhen no_data_found then qt3:=0;end;