Basi di dati - Informatica per il management

ESEMPIO DATABASE IN AULA

DROP DATABASE IF EXISTS MYCINEMA;

CREATE DATABASE IF NOT EXISTS MYCINEMA;

CREATE TABLE ATTORE( ID INT AUTO_INCREMENT, Nome VARCHAR(30), Cognome VARCHAR(30), NumeroFilm INT DEFAULT 0 );

CREATE TABLE FILM ( Nome VARCHAR(30) PRIMARY KEY, Anno INT, Genere ENUM("Horror", "Commedia", "Drammatico") );

CREATE TABLE RECITAZIONE( IdAttore INT, NomeFilm VARCHAR(30), PRIMARY KEY(IdAttore,NomeFilm), FOREIGN KEY (IdAttore) REFERENCES ATTORE(Id), FOREIGN KEY (NomeFilm) REFERENCES FILM(Nome) );

DELIMITER $

CREATE TRIGGER AumentaFilm AFTER INSERT ON RECITAZIONE

VIEW FILMGUSSMAN (NomeFilm) AS 
SELECT NomeFilm 
FROM RECITAZIONE 
WHERE (IdAttore = 1);

SELECT * 
FROM FILMGUSSMAN

CREATE PROCEDURE InserisciAttore(IN NomeAttore VARCHAR(30), IN CognomeAttore VARCHAR(30))
BEGIN 
    DECLARE Contatore INT;
    SET Contatore = SELECT COUNT(*) FROM ATTORE WHERE (Nome = NomeAttore) AND (Cognome = CognomeAttore);
    IF (Contatore = 0) THEN
        INSERT INTO ATTORE(Nome, Cognome) VALUES (NomeAttore, CognomeAttore);
    ELSE
        -- non faccio nulla
    END IF;
END;

-- Dato che viene eseguito riga per riga, formo un blocco di codice da eseguire assieme con il delimiter
-- inserimento parziale perché c'è autoincrement
INSERT INTO ATTORE(Nome,Cognome) VALUES ("Vittorio", "Gassman");
INSERT INTO ATTORE(Nome,Cognome) VALUES ("Marcello", "Mastroianni");
CALL InserisciAttore("Anna", "Magnani");
SELECT * FROM ATTORE;
INSERT INTO RECITAZIONE VALUES(3,

“Titanic”);

INSERT INTO FILM VALUES (“Armata Brancaleone”, 1970, “Commedia”);

INSERT INTO FILM VALUES (“I soliti ignoti”, 1972, “Commedia”);

INSERT INTO FILM VALUES (“Il sorpasso”, 1962, “Commedia”);

→ inserisce se la riga non c’è o sostituisce se la riga c’è già

REPLACE INTO FILM VALUES (“Armata Brancaleone”, 1968, “Commedia”);

SELECT * FROM FILM;

INSERT INTO RECITAZIONE VALUES(1, “Armata Brancaleone”);

INSERT INTO RECITAZIONE VALUES(1, “I soliti ignoti”);

INSERT INTO RECITAZIONE VALUES(1, “Il sorpasso”);

TRANSAZIONI

Le TRANSAZIONI rappresentano unità di lavoro elementare (insiemi di istruzioni SQL) che modificano il contenuto di una base di dati. Le transazioni sono comprese tra una start transaction ed una commit/rollback.

Gestione delle transazioni per tabelle INNODB:

- Di default, la modalità

AUTOCOMMIT è abilitata, quindi tutti gli aggiornamenti sono effettuati immediatamente sul database.

Nel caso in cui gli autocommit siano disabilitati, è necessario indicare l'inizio della transazione (START TRANSACTION) e terminarla con un comando di COMMIT o ROLLBACK.

Le PROPRIETÀ ACID DELLE TRANSAZIONI sono:

• La transazione deve essere eseguita con la regola del "tutto o niente".
• ATOMICITÀ: La transazione deve lasciare il database in uno stato consistente, eventuali vincoli di integrità non devono essere violati.
• L'esecuzione di una transazione deve essere indipendente dalle altre.
• ISOLAMENTO: (durability) L'effetto di una transazione che ha fatto non deve essere perso.

GESTIONE DELLE TRANSAZIONI:

• GESTORE DELL'AFFIDABILITÀ garantisce e ATOMICITÀ: COME? Usando LOG e CHECKPOINT.
• GESTORE DELLA CONCORRENZA

garantisce l'inISOLAMENTOcaso di esecuzione concorrente di più transazioni.Date un insieme di transazioni T1,T2, Tn, di cui ciascuna formata da un certo insieme di operazioni di scrittura (w ) e lettura (r ):i iEs. T =r (x) r (y) r (z) w (y) …1 1 1 1 1Si definisce SCHEDULE la sequenza di operazioni di lettura/scrittura di tutte le transazioni così come eseguite sulla base di dati:r (x) r (y) r (y) w (y) w (z) …1 2 1 4 2X, Y, Z sono , quindi quando faccio una delle tabelle sto eseguendo una , mentre quando faccio unaTABELLE READ SELECT WRITEsto eseguendo un o .INSERT UPDATEUna TRANSAZIONE è un insieme di operazioni di lettura (non cambio lo stato della base di dati) e scrittura (cambio lo statodella base di dati).Uno schedule S si dice se le azioni di ciascuna transazione appaiono in sequenza, senza essere inframezzate da azioni diSERIALEaltre transazioni. S={T1, T2, … Tn}Lo schedule seriale è ottenibile se:1. Le transazioni sono

1. Le transazioni sono completamente indipendenti l'una dall'altra (improbabile). → Tuttavia, in un sistema reale, le transazioni vengono eseguite in concorrenza per ragioni di efficienza / scalabilità. L'esecuzione concorrente determina un insieme di problematiche che devono essere gestite.
2. T1= Read(x); x=x+1; Write(x); Commit Work
3. T2= Read(x); x=x+1; Write(x); Commit Work

1. PERDITA DI AGGIORNAMENTO: entrambe le transazioni arrivano al termine, ma entrambe danno come risultato 4 al posto che 5, questo perché non si rendono conto che sono due.
2. LETTURA SPORCA: una transazione legge un dato che è già stato modificato dall'altra transazione.
3. LETTURE INCONSISTENTI: due operazioni di lettura, ma in

contemporanea si aggiunge un'altra transazione che modifica x, quindi la prima transazione avrà risultati diversi in base all'istante di tempo in cui legge.

4. AGGIORNAMENTO FANTASMA (violazione di vincoli) ho due transazioni che rispettano i vincoli, ma eseguite in maniera parallela le transazioni violeranno il vincolo della somma.

Uno schedule S si dice se produce lo stesso risultato di un qualunque SERIALIZZABILE schedule seriale S' delle stesse transazioni.

Per implementare il i DMBS commerciali usano il meccanismo dei LOCK per poter effettuare CONTROLLO DELLA CONCORRENZA una qualsiasi operazioni di lettura/scrittura su una risorsa (tabella o valore di una cella), è necessario aver precedentemente acquisito il controllo (lock) sulla risorsa stessa. Esistono due tipi di lock:

• IN LETTURA (accesso condiviso le transazioni possono avvenire in contemporanea)
• IN SCRITTURA (mutua esclusione può essere dato solo ad una transizione)

Su ogni lock possono essere definite due operazioni:

• Richiesta del lock in lettura/scrittura
• Rilascio del lock (unlock) acquisito in precedenza

Per quanto riguarda il codice con lock:

• r(x) è bloccante
• w(y) non è bloccante

Il LOCK MANAGER è il componente del DBMS responsabile di gestire i lock alle risorse del database e di rispondere alle richieste delle transazioni. Associa a ciascun elemento del DBMS almeno una base di dati. Le strutture dati del Lock Manager per ciascun oggetto x del DBMS sono:

• State(x): stato dell'oggetto (libero / r_locked / w_locked)
• Active(x): lista transazioni attive sull'oggetto
• Queued(x): lista transazioni bloccate sull'oggetto (in attesa di poter leggere/scrivere sulla tabella x)

Il Lock Manager esegue diverse azioni, quali:

1. Riceve una richiesta (r_lock, w_lock, unlock) da una transazione T, su un oggetto x.
2. Controlla la tabella stato/azione (tabella successiva).
3. Se la risposta è OK,

aggiorna lo stato della risorsa, e concede il controllo della risorsa alla transazione T.

Se la risposta è NO, inserisce la transazione T in una coda associata ad x.

Esempio: → una TWO PHASE LOCK (2PL) transazione, dopo aver rilasciato un lock, non può acquisirne un altro. In pratica, una transazione acquisisce prima tutti i lock delle risorse di cui necessita.

Ogni schedule che rispetta il 2PL è anche e non può incorrere in configurazioni erronee dovute ad un aggiornamento fantasma, una lettura inconsistente o una perdita di aggiornamento. Però in caso di LETTURA SPORCA accade che: → i lock di una transazione sono rilasciati solo dopo aver effettuato le operazioni di COMMIT/ABORT.

Uno schedule che rispetta S2PL (la variante strict del 2PL) eredita tutte le proprietà del 2PL, ed inoltre NON presenta anomalie causate da problemi di LETTURA SPORCA.

PROBLEMA: I protocolli 2PL e S2PL possono generare

1. TIMEOUT: ogni operazione di una transazione ha un timeout entro il quale deve essere completata, la pena è l'annullamento (abort) della transazione stessa.
2. DEADLOCK AVOIDANCE: previene le configurazioni che potrebbero portare ad un deadlock, tramite:
   • Lock/Unlock di tutte le risorse allo stesso tempo.
   • Utilizzo di time-stamp o di classi di priorità tra transazioni (può determinare STARVATION).
3. DEADLOCK DETECTION: utilizza algoritmi per identificare e risolvere situazioni di deadlock.