Transazioni in MySQL 4
•
Transazioni in MySQL 4
Francesco Brancati – [email protected]
Cristina Pucci – [email protected]
Transazioni vs Operazioni Atomiche (1)
• MySQL supporta le transazioni con i metodi di
memorizzazione InnoDB e BDB (BerkleyDB)
– InnoDB permette di ottenere transazioni ACID
• Atomic, Consistent, Isolated, Durability
• Altri metodi di MySQL (come MyISAM) seguono un’altra
filosofia per l’integrità dei dati: “Atomic Operations”
– In pratica le tabelle MyISAM operano in modalità autocommit=1
• Il commit viene eseguito automaticamente dopo ogni transazione
Interazione tra basi di
dati e web
Transazioni
2
Transazioni vs Operazioni Atomiche (2)
• Transazioni
– Pro
• Maggiori funzionalità (rollback, savepoint, etc)
– Contro
• Necessitano di più memoria sul disco
• Comportano un maggior uso della CPU
• Operazioni Atomiche
– Pro
• Maggiore velocità a parità di integrità di dati
– Contro
• Problemi più difficili da codificare
Interazione tra basi di
dati e web
Transazioni
3
Transazioni vs Operazioni Atomiche (3)
• Le transazioni assicurano che gli update non finiti o
archivi corrotti non vengano scritti sul database
– Il server dà l’opportunità di fare rollback automatici e di salvare il
database
• Le operazioni atomiche possono essere rese altrettanto
sicure includendo controlli prima degli updates ed
eseguendo script che controllano l’integrità del database
riparandolo automaticamente o comunque avvisando in
caso di inconsistenze
– Si usano i log di MySQL per ripristinare correttamente le tabelle
senza perdita nell’ integrità dei dati
Interazione tra basi di
dati e web
Transazioni
4
Transazioni vs Operazioni Atomiche (4)
• In genere tutti i problemi d’integrità che le transazioni
risolvono, possono essere risolti anche con l’uso di LOCK
TABLES ed operazioni atomiche, escludendo i crash di
sistema
• Non esistono DBMS in grado di garantire l’integrità di
dati al 100%
• In MySQL è sufficiente tenere i backup dei dati ed i log
binari attivati per recuperare ogni possibile situazione
che possa essere recuperata con un qualsiasi altro
database transazionale
Interazione tra basi di
dati e web
Transazioni
5
COMMIT e ROLLBACK
• Di default MySQL ha la variabile AUTOCOMMIT abilitata
– Appena si esegue un statement che comporta una modifica al
database, MySql memorizza il risultato sul disco
• Con tabelle transazionali è necessario disabilitare
autocommit per sfruttare la funzionalità di rollback
– Set autocommit=0;
– Attenzione: quando autocommit è disabilitata è necessario fare
sempre il commit per rendere permanenti i cambiamenti
Interazione tra basi di
dati e web
Transazioni
6
START TRANSACTION
• Se si vuole disabilitare temporaneamente AUTOCOMMIT
per una serie di statement si usa START TRANSACTION
START TRANSACTION;
SELECT * FROM table1 WHERE type=1;
UPDATE table2 SET summary=“A” WHERE type=1;
COMMIT;
– Autocommit rimane disabilitata finché non si esegue un commit
o un rollback
• E’posibile modificare il livello di isolamento della
transazione (globalmente o per la sessione corrente)
utilizzando SET TRANSACTION ISOLATION LEVEL
– READ UNCOMMITTED, READ COMMITTED, REPEATABLE
READ (default), SERIALIZABLE
Interazione tra basi di
dati e web
Transazioni
7
Statement che causano un COMMIT implicito
• Ognuno dei seguenti comandi implicitamente termina
una transazione, come se fosse eseguito un COMMIT
prima di eseguire il comando:
ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP INDEX
DROP TABLE
LOAD MASTER DATA
LOCK TABLES
RENAME TABLE
SET AUTOCOMMIT=1
START TRANSACTION
TRUNCATE TABLE
• Comandi che non ammettono ROLLBACK
– In genere, tutti i comandi DDL (create, drop, alter, etc.)
– Le transazioni non dovrebbero contenere suddetti comandi che
invaliderebbero l’effetto di un rollback.
Interazione tra basi di
dati e web
Transazioni
8
SAVEPOINT e ROLLBACK TO SAVEPOINT
• Lo statement SAVEPOINT permette di salvare lo stato di
una transazione.
• Se la transazione corrente ha un savepoint con lo stesso
nome, il vecchio savepoint viene cancellato e ne viene
settato uno nuovo.
– SAVEPOINT identifier
• ROLLBACK TO SAVEPOINT riporta una transazione al
savepoint nominato.
– Le modifiche che la transazione corrente fa dopo il savepoint
risultano nulle.
– ROLLBACK TO SAVEPOINT identifier
Interazione tra basi di
dati e web
Transazioni
9
LOCK TABLES ed UNLOCK TABLES (1)
• Sintassi:
LOCK TABLES tbl_name [AS alias] {READ [LOCAL] |
[LOW_PRIORITY] WRITE} [, tbl_name [AS alias] {READ
[LOCAL] | [LOW_PRIORITY] WRITE}] ...UNLOCK TABLES
• LOCK TABLES blocca una o più tabelle per il processo
corrente.
• UNLOCK TABLES rilascia ogni lock mantenuto dal
processo corrente.
– Tutte le tabelle bloccate dal processo corrente sono
implicitamente sbloccate quando il processo dichiara un altro
LOCK TABLES o quando la connessione al server viene chiusa
Interazione tra basi di
dati e web
Transazioni
10
LOCK TABLES ed UNLOCK TABLES (2)
• Quando si usa LOCK TABLES è nesessario bloccare
tutte le tabelle che si utilizzeranno nelle query.
– Non è possibile accedere a nessun’altra tabella non bloccata
• Non è possibile usare più volte una tabella bloccata in
una singola query: si usano gli alias.
– mysql> LOCK TABLES t WRITE, t
mysql> INSERT INTO t SELECT *
ERROR 1100: Table 't' was not
mysql> INSERT INTO t SELECT *
AS t1 WRITE;
FROM t;
locked with LOCK TABLES
FROM t AS t1;
• Bloccando la tabella non si bloccano anche gli alias e
viceversa
– mysql> LOCK TABLES t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
Interazione tra basi di
dati e web
Transazioni
11
LOCK TABLES e tabelle transazionali
•
LOCK TABLES non è transaction-safe ed esegue un commit
implicito di ogni transazione attiva prima di provare a bloccare le
tabelle
–
•
Iniziare una transazione (es. con START TRANSACTION)
implicitamente causa un UNLOCK TABLES.
Uso corretto di LOCK TABLES con tabelle transazionali (InnoDB):
1.
2.
impostare AUTOCOMMIT = 0
non eseguire UNLOCK TABLES prima del commit esplicito della
transazione.
•
Quando si fa un LOCK TABLES, InnoDB blocca internamente le tabelle, e
MySQL fa lo stesso. InnoDB rilascia il lock al successivo commit, mentre
MySQL rilascerà il proprio solo quando verrà chiamato UNLOCK TABLES.
Se AUTOCOMMIT = 1 InnoDB rilascia il blocco immediatamente dopo la
chiamata a LOCK TABLES e si possono facilmente verificare deadlock.
–
•
A partire dalla versione 4.1.9 non viene acquisito il lock da InnoDB se
AUTOCOMMIT=1.
ROLLBACK non rilascia i lock delle tabelle
Interazione tra basi di
dati e web
Transazioni
12
LOCK TABLES e tabelle non transazionali
Tecniche per lavorare con tabelle che non supportano
transazioni:
Usare LOCK TABLES per bloccare le tabelle a cui si vuole
accedere
Testare le condizioni che devono essere vere prima di realizzare
l’update
Eseguire l’update solo se tutto è a posto
Usare UNLOCK TABLES per rilasciare i blocchi
1.
2.
3.
4.
•
Solitamente questo metodo è più veloce dell’utilizzo di transazioni
con possibile rollback.
Problema:
•
–
Se il processo viene terminato nel mezzo di un update, tutti i lock
vengono rilasciati, e non è garantito che tutti gli update siano stati
eseguiti
Interazione tra basi di
dati e web
Transazioni
13
READ e WRITE LOCK
• Se un processo ottiene un READ LOCK su una tabella, questo e tutti
gli altri processi potranno solo leggere dalla tabella.
• Se un processo ottiene un WRITE LOCK su una tabella, solo il
processo che ha bloccato può scrivere sulla tabella. Gli altri sono
bloccati in scrittura finchè il lock non viene rilasciato.
• Differenza fra READ LOCAL e READ:
– READ LOCAL permette l’esecuzione di istruzioni INSERT non-conflicting
(concorrenti) durante il blocco della tabella.
– Per InnoDB READ LOCAL non esegue il lock delle tabelle.
• L’uso di READ LOCAL per tabelle InnoDB è sconsigliato, si ottiene lo stesso
risultato facendo una SELECT senza dover inserire nessun blocco.
Interazione tra basi di
dati e web
Transazioni
14
WRITE LOCK
•
I lock in scrittura normalmente hanno priorità maggiore
dei lock in lettura, questo assicura che gli update siano
processati il prima possibile.
–
–
Se un processo ottiene un READ lock ed un altro richiede un
WRITE lock, le richiese di READ lock seguenti attenderanno
finquando il processo scrittore abbia ottenuto il WRITE lock e lo
abbia rilasciato
Si possono usare blocchi LOW_PRIORITY WRITE per
permettere agli altri processi di ottenere READ lock mentre il
processo attende per il WRITE lock.
•
Si dovrebbero usare LOW_PRIORITY WRITE locks solo se si è
sicuri che ci sarà un momento in cui nessun processo chiederà un
READ lock.
Interazione tra basi di
dati e web
Transazioni
15
LOCK TABLES e DEADLOCK
• Algoritmo di LOCK TABLES :
1. Ordina in un modo predefinito dal sistema tutte le tabelle che
devono essere bloccate.
2. Se una tabella deve essere bloccata sia con un read che con un
write lock, sposta il write lock prima del read lock.
3. Blocca una tabella per volta finché il processo non ottiene tutti i
lock richiesti.
• Questa politica assicura che il bloccaggio della tabella non provochi
deadlock.
Interazione tra basi di
dati e web
Transazioni
16
Esempio sull’uso di LOCK TABLES
• Questo esepio richiede LOCK TABLES per
un’esecuzione “safe”
– mysql> LOCK TABLES fatture READ, fornitori WRITE;
mysql> SELECT SUM(importo) FROM fatture WHERE
fornitore_id=qualche_id;
mysql> UPDATE fornitori
->
SET totale=somma_precedente
->
WHERE fornitore_id=qualche_id;
mysql> UNLOCK TABLES;
– senza LOCK TABLES è possibile che un altro processo inserisca una
nuova riga nella tabella fatture tra l’esecuzione della SELECT e
l’UPDATE.
Interazione tra basi di
dati e web
Transazioni
17
Risorse
•
•
•
•
•
http://freephp.html.it/
http://www.risorse.net/
http://www.php.net/
http://smarty.php.net/
http://www.mysql.com/
Interazione tra basi di
dati e web
Risorse
18
