Le transazioni

Di Andrea Benedetti - Microsoft MVP

In questa pagina

Le modalità di transazione di SQL Server Le modalità di transazione di SQL Server
Problemi di consistenza e livelli di isolamento Problemi di consistenza e livelli di isolamento
Il meccanismo di lock Il meccanismo di lock
SQL Server 2005, Versioning e livello SNAPSHOT SQL Server 2005, Versioning e livello SNAPSHOT
Conclusioni Conclusioni

Le transazioni, ovvero le unità elementari (logiche) di lavoro svolte da un’applicazione, sono una delle caratteristiche fondamentali di un database relazionale.

Sono, infatti, quel meccanismo che consente di mantenere, durante la vita della nostra base dati, tutte le nostre informazioni consistenti.

Durante le operazioni di modifica (scrittura, aggiornamento, cancellazione) delle nostre tabelle le transazioni fanno sì che nessuna di queste avrà effetto fino a quando i nuovi valori non verranno effettivamente scritti sulla base dati stessa.

Ogni transazione viene specificata racchiudendo una sequenza di operazioni, che fanno quindi parte della stessa unità di lavoro, in una coppia di istruzioni: una di inizio e una di conclusione.

Tramite il linguaggio TSQL siamo in grado di definire una transazione tramite i comandi:

  • BEGIN TRANSACTION, che specifica l’inizio della transazione,

  • COMMIT TRANSACTION per richiedere che tutti gli aggiornamenti effettuati vengano materializzati nella base dati,

  • ROLLBACK TRANSACTION per richiedere, al contrario, che tutti gli aggiornamenti vengano annullati (come se non fosse mai esistita quella o quelle modifiche che si stavano facendo).

Per il sistema quindi, che sia SQL Server o qualsiasi altro database relazionale, una transazione è una singola unità di esecuzione caratterizzata da proprietà “acide” (dall’acronimo inglese ACID), ovvero:

Atomic (atomicità): una transazione è un’unità indivisibile, ovvero se anche solo un’istruzione non dovesse andare a buon fine anche tutte le altre non andranno a buon fine (come dire: “o tutto o niente”).

Grazie a questa caratteristica in presenza di qualsiasi guasto (errore, interruzione, …) prima del commit, il motore del database eliminerà tutti gli effetti della transazione stessa ripristinando lo stato iniziale, come se nulla fosse successo.

Consistent (consistenza): l’esecuzione non deve violare nessun vincolo di integrità, ovvero non ci si può permettere di perdere la consistenza dei dati.

Ovvero l’esecuzione della transazione porterà la base dati da uno stato consistente a un altro, nel rispetto di tutti i vincoli d’integrità definiti.

Isolated (isolamento): l’esecuzione è assolutamente indipendente da altre esecuzioni di altre transazioni.

Durable (persistenza / durata): i dati committati, quindi le modifiche che vengono materializzate fisicamente all’interno della base dati, non devono essere persi.

Questa proprietà richiede che l’effetto prodotto da una transazione che ha effettuato un’operazione di commit non venga perso.

Le modalità di transazione di SQL Server

SQL Server è in grado di utilizzare tre differenti modalità di transazione: implicite, esplicite e autoCommit.

Le prime, implicite, vengono impostate tramite la proprietà IMPLICIT_TRANSACTIONS .

Quando tale proprietà è impostata a ON (attiva) tale modalità consente di avviare una transazione, solo se la connessione utilizzata non è già inclusa in un’altra transazione, per istruzioni di tipo: insert, delete, alter table, create, drop, revoke, select, truncate table e update.

Vediamone un esempio di funzionamento:

USE master
GO
-- creo un db di test
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'dbTest')
DROP DATABASE dbTest
GO
CREATE DATABASE dbTest
GO

USE dbTest
go

-- creo una tabella di test
CREATE TABLE T1(colonna int)
go
-- imposto la modalità di transazioni implicita
SET IMPLICIT_TRANSACTIONS ON;

-- faccio due inserimenti
insert T1 values (1)
insert T1 values (2)

-- vedo il numero di transazioni aperte
select N'Numero di transazioni aperte = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
select * from T1

-- rollback della transazione
ROLLBACK TRAN

-- vedo il numero di transazioni aperte
select N'Numero di transazioni aperte = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
select * from T1

-- cancello la tabella di test
drop table T1

-- vedo il numero di transazioni aperte
select N'Numero di transazioni aperte = ' + CAST(@@TRANCOUNT AS NVARCHAR(10));
IF (@@TRANCOUNT = 1)
COMMIT TRAN

SET IMPLICIT_TRANSACTIONS OFF;

Il risultato delle nostre istruzioni:

-----------------------------------------
Numero di transazioni aperte = 1

colonna
-----------
1
2


-----------------------------------------
Numero di transazioni aperte = 0

colonna
-----------


-----------------------------------------
Numero di transazioni aperte = 1

Le transazioni esplicite, al contrario, solo blocchi di istruzioni racchiuse tra un’istruzione di inizio e fine transazione, come:

begin transaction

update Tabella1 set colonna = ...
update Tabella2 set colonna = ...

commit transaction

Le ultime, autoCommit , la modalità predefinita, sono transazioni che vengono avviate in maniera automatica con ogni istruzione TSQL , ovvero sono transazioni che SQL Server genera, per mantenere la nostra base dati consistente.

Per fare un esempio potremmo pensare a una tabella come la seguente:

*

Ovvero una semplice tabella con tre colonne numeriche, sulla quale andremo a inviare in esecuzione un’istruzione di UPDATE come:

update numeri set col3 = col1/col2

È chiaro come questa istruzione vada a buon fine per le prime due righe ma non per la terza (errore di divisione per zero).

La modalità autoCommit, di fatto, effettuerà in maniera automatica un rollback delle istruzioni come se anche per le prime due righe non fosse avvenuta nessuna modifica.

Problemi di consistenza e livelli di isolamento

Lo standard SQL-99 definisce quattro livelli di isolamento, ovvero delle regole che specificano come una transazione deve essere isolata da modifiche apportate da altre transazioni, tutti supportati da SQL Server.

Queste regole si rendono necessarie per evitare potenziali problemi come:

Dirty reads (letture sporche): potrei leggere dati che, non ancora committati, potrebbero subire un rollback (ovvero potrei leggere dati mai esistiti)

Transazione 1

Transazione 2

 

Begin transaction

 

 

select a …

 

a = 2

a = a + 1

 

a = 3

Update … set a …

 

a = 3

 

Begin transaction

 

 

Select a …

a = 3

 

Commit

 

Rollback

 

a = 2

Nonrepeatable reads (letture inconsistenti): potrei, all’interno di una transazione, leggere dati che, prima di essere riletti, vengano modificati da un’altra transazione

Transazione 1

Transazione 2

 

Begin transaction

 

 

select a …

 

A = 2

 

Begin transaction

 

 

Select a …

A = 2

 

a = a + 1

A = 3

 

Update … a …

 

 

Commit

 

Select a …

 

A = 3

commit

 

 

Phantoms (dati fantasma): potrei, all’interno di una transazione, leggere quante righe stanno in una tabella e, prima di apportare un update su tutte queste un’altra transazione effettua, su quella stessa tabella, un’operazione di insert (di fatto farei un update su un numero differente di righe rispetto a quelle che avevo calcolato inizialmente)

Transazione 1

Transazione 2

 

Begin transaction

 

 

select * from T where col = 1

 

Leggo 2 righe

begin transaction

 

Insert T … col = 1

 

 

Commit

Update TSet x = 2Where col = 1

Scrivo 3 righe

Commit

Lost updates (perdita di aggiornamento): potrei, all’interno di una transazione T1 leggere una colonna e, prima di modificarne il valore, avere una seconda transazione T2 che effettui una differente modifica (che, quindi, andrebbe persa venendo soprascritta dalla mia, T1).

Transazione 1

Transazione 2

 

Begin transaction

 

 

Select a …

 

a = 2

a = a + 1

 

a = 3

 

Begin transaction

 

 

Select a …

a = 2

 

a = a + 5

a = 7

 

Update … a …

a = 7

 

Commit

 

Update … a …

 

a = 3

commit

 

 

SQL Server consente di specificare un livello di isolamento per ogni transazione.

Tale impostazione andrà quindi a determinare il tempo per il quale sarà necessario mantenere dei LOCK, ovvero dei blocchi che consentono a chi ne fa richiesta di utilizzare o meno delle informazioni, su oggetti e risorse, a scelta tra:

  • Read uncommited

  • Read committed

  • Repeatable read

  • Serializable

Il livello Read Uncommitted, da usare con la massima attenzione e cautela, viene utilizzato modificando il livello di isolamento come:

SET TRANSACTION ISOLATION LEVEL Read Uncommitted

Consentendo di leggere dati che non sono ancora stati committati, non offre alcuna garanzia circa la presenza dei dati letti al termine della transazione.

Il livello Read Committed, impostato come livello di default in SQL Server, consente di vedere e utilizzare solo dati che sono stati richiesti e quindi effettivamente materializzati nella nostra base dati.

Consente un elevato livello di coerenza dei dati che potrebbe portarmi a pagare della concorrenza nell’accesso alle risorse.

Il livello Repeatable Read preserva dalla modifica da parte di altri utenti tutti i dati utilizzati dalla transazione. Consente l’inserimento di nuovi dati e quindi potrebbe portarmi ad avere un numero di righe differente da quello eventualmente letto e, quindi, un risultato differente.

Ad esempio, con una transazione come:

set transaction isolation level repeatable read
go

begin tran
select avg(valuta) from contiCorrenti

select avg(valuta) from contiCorrenti
commit tran

Potrei effettuare, da una transazione differente, un’istruzione di insert sulla tabella “contiCorrenti” da cui sto leggendo dei valori proprio nel momento che intercorre tra le due istruzioni di select. Tale operazione porterebbe ad avere, di conseguenza, due risultati differenti con la stessa identica istruzione di lettura.

L’ultimo livello, Serializable, è il più restrittivo disponibile.

Non consente modifiche, né inserimenti, con i dati che sono coerenti con i criteri di query in uso.

Tale livello impedisce quindi, andando a bloccare a livello di dati ed anche a livello di indici, che il resultset interessato da un’istruzione subisca delle modifiche.

Nell’esempio precedente, dove si andava a calcolare la media dei conti correnti, non si sarebbe potuto quindi inserire una riga sulla tabella perché proprio tutta la tabella era interessata dall’operazione di lettura.

È importante sottolineare come non sia possibile “spegnere” le transazioni, né influenzare le proprietà di atomicità, consistenza e durata. L’unica possibilità che abbiamo è quella di intervenire sul livello di isolamento.

Nella griglia sottostante riepiloghiamo come i vari livelli di isolamento consentano di vedere o no dati fantasma, letture ripetibili o sporche:

 

Dirty Read

Non-Repeatable Read

Phantom Read

Lost Updates

Read uncommitted

SI

SI

SI

Read committed

NO

SI

SI

 

Repeatable read

NO

NO

SI

 

Serializable

NO

NO

NO

 

Il meccanismo di lock

Affinché tutto funzioni, SQL Server gestisce un meccanismo di locking per garantire le proprietà ACID delle transazioni. Le operazioni di lock possono essere di tipo condiviso (shared), esclusivo (exclusive) o di aggiornamento (update) e possono avere una granularità a livello di riga / chiave, di singola data page, di extent (otto data page fisicamente contigue), di intera tabella o di intero database.

Il blocco condiviso viene applicato durante operazioni di lettura e consente, ad altre transazioni, di avere sugli stessi dati un blocco dello stesso tipo (le operazioni di lettura non bloccano altre letture).

Il blocco esclusivo viene applicato in fase di scrittura e, per poter ottenere un blocco di questo stesso tipo, nessuna altra transazione può avere operazioni in corso sugli stessi dati (le scritture bloccano altre scritture sugli stessi dati).

L’ultimo blocco disponibile, di aggiornamento, funziona come una sorta di “diritto di precedenza”.

Un processo che richiede una modifica su dati bloccati li marca con un blocco di aggiornamento che rimarrà tale fino a che non verrà elevato a blocco esclusivo.

Riepilogando i meccanismi di blocking ricordiamo che:

  • Le scritture bloccano altre scritture. Sempre

  • Le scritture bloccano le letture in Read Committed o con livelli superiori

  • Le letture bloccano le scritture in Repeatable Read o con livelli superiori

SQL Server 2005, Versioning e livello SNAPSHOT

SQL Server 2005 porta con sé anche una novità relativa ai livelli di isolamento con il nuovo SNAPSHOT, introducendo il concetto di versioning.

Tale livello non consente di avere letture sporche, né letture inconsistenti, né dati fantasma.

Con questa impostazione le letture non bloccano le scritture, le scritture non bloccano le letture e le scritture bloccano altre scritture sugli stessi dati.

Con operazioni di lettura viene recuperata l’ultima versione delle righe che risulta essere “committed” quando la transazione che legge è partita.

Con operazioni di scrittura viene utilizzato la versione per selezionare le righe, SQL Server cerca di acquisire un lock esclusivo sui dati da modificare e, se le righe da modificare sono già state variate da un’altra transazione, genera un update conflict.

Per poter utilizzare questo livello di isolamento dovrò abilitarlo a livello di database tramite un’istruzione simile a:

ALTER DATABASE mioDatabase
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

Con questo livello di isolamento, ad esempio utile in scenari in cui report potrebbero utilizzare dati in aggiornamento o in applicazioni con molta contesa tra letture e scritture, sarà da tenere bene in considerazione lo storage (sul database di sistema TEMPDB) che si rende necessario per mantenere versioni delle stesse righe.

È utile ricordare che è possibile impostare l’opzione READ_COMMITTED_SNAPSHOT per tutte le transazioni che implementano il livello di isolamento read committed.

In questo modo, in tutte le operazioni di lettura, verranno restituite le righe per cui è stato eseguito il commit all’avvio dell’istruzione fornendo, di fatto, uno snapshot dei dati a livello di istruzione.

L’attivazione di questa opzione può essere fatta tramite un’istruzione di alter database, come:

ALTER DATABASE mioDatabase
    SET READ_COMMITTED_SNAPSHOT ON;

Conclusioni

Sicuramente costruire transazioni “solide” e non limitarsi ad utilizzare il livello di default potrebbe complicare la costruzione del nostro database e del suo codice (stored procedure, trigger, user function), ma è un requisito irrinunciabile per mantenere la corretta integrità dei dati.

Terminiamo sottolineando alcuni suggerimenti:

  • Utilizzare il miglior (meno caro) livello di isolamento andando a includere, all’interno delle transazioni, solo le istruzioni che devono effettivamente far parte dell’unità di lavoro (durata)

  • Evitare di aprire transazioni durante operazioni di analisi dei dati (la transazione, di fatto, non serve)

  • Mantenere transazioni più brevi possibili

  • Nelle transazioni accedere alla quantità minima di dati possibile (riducendo così al minimo i lock sulle risorse)


Mostra: