SQL Server: Transazioni, Lock e Deadlock. Un po di teoria spiegata in modo semplice!

Buongiorno a tutti e nuovamente ben ritrovati!

Oggi vi volevo parlare di alcuni concetti basilari relativi ai database relazionali che sono assolutamente da sapere.

Parleremo di Transazioni, di lock e anche di un tipo particolare di lock detto deadlock.
Vi racconterò un po di teoria ma non temete: come al solito cercherò di essere quanto più possibile chiaro!

Sei pronti? Allora buona lettura!

Diciamo subito cos'è una transazione.

Una transazione è una sequenza di operazioni che, se giunta a termine senza errori, produce una variazione di stato nella nostra base dati.

Ad esempio:

       
BEGIN TRAN
INSERT INTO TABELLA_A (CAMPO1, CAMPO2) VALUES ('VAL1','VAL2')
INSERT INTO TABELLA_B(CAMPO1,CAMPO2,CAMPO3) VAULES ('VAL1','VAL2','VAL3')
COMMIT
 

oppure

INSERT INTO TABELLA_A (CAMPO1, CAMPO2) VALUES ('VAL1','VAL2')
      

Nel primo caso parliamo di transazione esplicita, nel secondo invece di transazione implicita.


Cosa dobbiamo capire quindi?
Capiamo che abbiamo sempre e comunque a che fare con una transazione.

Se siamo noi ad aprire esplicitamente una transazione tramite il comando BEGIN TRAN sarà SQL Server, dietro le quinte, ad aprirne una implicita prima della esecuzione del comando T-SQL.

Semplice no?
 

Facciamo ora un passo avanti e diciamo quali sono le proprietà logiche che devono rispettare le transazioni. 

Per ricordarle a memoria è sufficiente ricordare l'acronimo: ACID!

La prima lettera "A" sta per Atomicity ovvero Atomicità.
Atomicità significa che la transazione è indivisibile e che la sua esecuzione deve essere o totale o nulla.
All'atto pratico quando la nostra operazione giungerà al termine abbiamo sono due possibilità:
Eseguire il COMMIT della transazione rendendo così persistenti le modifiche apportate.
Oppure eseguire il ROLLBACK della transazione annullando ogni modifica.

La seconda lettera "C" sta per Consistency ovvero Consistenza.
Questa proprietà dice che quando inizia una transazione un database si deve trovare in uno stato coerente. Quando la transazione termina il database dovrà trovarsi in un altro stato coerente.
Per ottenere questa coerenza non devono essere violati i vincoli di integrità oppure vincoli di dominio.

La terza proprietà detta Isolation ovvero Isolamento impone invece che ogni transazione debba essere eseguita in modo isolato ed indipendente dalle altre transazioni.
Il fallimento di una transazione non deve intererire con le altre transazioni in esecuzione.

Infine abbiamo la quarta ed ultima proprietà rappresentata dalla lettera "D" che sta per Durability traducibile in Durabilità.
Cosa vuol dire durabilità?
Durabilità significa che una volta effettuato il commit della transazione i cambiamenti apportati non dovranno più essere persi.

Parlando di durabilità dobbiamo aprire un picciola parentesi per introdurre il concetto di log delle transazioni.

Si è portati a credere che SQL Server soddisfi la proprietà di durabilità scrivendo immediatamente i dati su disco ma ciò in realtà non è vero
Il meccanismo adottato è infatti un altro e prende il nome di WRITE AHEAD LOGGING o WAL.

Questo meccanismo viene adottato per evitare che, tra il momento in cui SQL Server "si impegna" a scrivere i dati ed il momento in cui questi dati vengono scritti effettivamente, possano accadere problematiche dovute ad un malfunzionamento.  

Viene tenuto un registro di log dentro il quale sono annotate sutte le operazioni che sono eseguite sul database. Questo fondamentale registro è detto Log delle transazioni
 
Diciamo che è fondamentale perchè in caso di malfuzionamento sarà sempre possibile recuperare i dati ri-leggendo il log delle transazioni.
Se invece a rovinarsi è proprio il log delle transazioni il recupero delle informazioni non committare non sarà possibile.

I Lock

Facciamo adesso un ulteriore passo avanti ponendoci questa domanda:

Come fa SQL Server a gestire le transazioni?
Ovvero come fa a far si che vengano rispettate le quattro proprietà ACID appena spiegate? 

Semplice, lo fa imponendo e rilasciando dei lock.

Valgono le seguenti regole base:

• Se una transazione impone un lock su un oggetto tutte le altre transazioni che richiedono l’accesso allo stesso oggetto devono aspettare fino a che quel lock non viene rilasciato.

• Mentre un oggetto è bloccato, le altre transazioni non potranno effettuare nessuna modifica ai dati memorizzati in quell'oggetto. Solamente dopo il rilascio del lock mediante commit oppure rollback le altre transazioni potranno modificare i dati.

La materia è vasta percui vediamo altre informazioni sui lock.

I lock possono essere posti su oggetti diversi come sul database, sulle tabelle, sulle pagine oppure sulle righe delle tabelle.

I lock hanno anche una gerarchia che potete vedere qui sotto:

Vediamo adesso i quattro tipi di lock:

A) Il lock Exclusive (X) è un tipo di lock che serve per prevenire che altre transazioni possano modificare o leggere l’oggetto bloccato.​

Nota bene: Questo tipo di lock può essere imposto ad una pagina oppure ad una riga solamente se non è presente un altro lock di tipo Shared (S) oppure Exclusive (X).


B) Il lock Shared (S) è invece un tipo di lock che viene messo su un oggetto di tipo pagina o riga per dichiarare che è disponibile solo in lettura.​ Impedisce quindi alle altre transazioni di modificare l’oggetto che ha detiene il lock.

C) Il terzo lock detto di Update(U) è un tipo di lock utilizzato appunto negli Update.

A differenza di un lock Exclusive può essere imposto su un oggetto che ha già uno Shared lock. Durante un update, prima della fase di Write il lock di tipo Update (U) diventa Exclusive (X)​

 D) Infine l'ultimo lock è detto di Intent(I).

Viene posto quando SQL Server vuole acquisire un lock di tipo Shared o Exclusive (X) su una risorsa posta ad un livello inferiore come gerarchia.

Quando viene posto un lock su una pagina o su una riga viene messo un lock di tipo intent (I) sulla tabella.​

E’ un tipo di lock non strettamente necessario per SQL server ma viene utilizzato per migliorare le prestazioni.

Ad esempio un Intent Shared Lock dice a SQL Server che ci darà uno Shared lock ad un livello di gerarchia Inferiore.

Ma perchè serve a migliorare le prestazioni?

Il ragionamento è semplice:

Per acquisire un lock di tipo Exclusive a livello di tabella , SQL Server deve sapere se è presente o meno un tipo di lock incompatibile (esempio S o U) in qualche parte del record.​

Ecco, senza gli Intent lock SQL Server dovrebbe verificare su ogni record è presente un lock non compatibile.​

I deadlock

Veniamo ora ad un tipo particolare di lock detto deadlock.

Quando avviene un deadlock?

Un deadlock avviene quando due o più processi acquisiscono un lock su oggetti diversi e
i quali tentano successivamente di acquisire un altro lock su un oggetto precedentemente bloccato da un altro processo.

Facciamo un esempio:

Abbiamo due tabelle che chiameremo tabella A e tabella B.

Immaginiamo che un utente che chiamiamo A  apra una transazione ed aggiorni la tabella A.
Arriva successivamente un altro utente che chiamiamo B il quale apre a sua volta una transazione ed aggiorna la tabella B.

A questo punto il primo utente A effettua una select sulla tabella B.
La select rimarrà in attesa perchè la tabella B è bloccata.

A questo punto se il secondo utente B effettua una select sulla tabella A si genera un deadlock.

Se ci fate caso infatti la select sulla tabella B fatta dal utente A rimane in attesa che la tabella B venga sbloccata.
Parimenti la select fatta fatta dall'utente B sulla tabella A rimane in attesa che la tabella A venga sbloccata.

E quindi? Come si esce da questa situazione?

Fortunatamente SQL Server rileva questo tipo di problema ed agisce terminando uno dei due processi.

• Il processo che viene terminato è detto vittima.
• Il processo che a questo punto arriverà a termine è detto vincitore.

Ma come sceglie SQL Server la vittima ed il vincitore?

SQL Server sceglie come vittima il processo che richiede meno risorse per essere annullato.


Un consiglio per evitare i deadlock? cercate di avere transazioni più corte possibile cercando ad esempio di ottimizzare le Query dentro alle transazioni.



Per oggi terminiamo qui!
Spero l'articolo vi sia piaciuto!

L'intenzione è stata quella di raccontarvi i concetti di transazione, di lock e di deadlock.
Per non mettere troppa carne al fuoco, ho omesso volutamente degli argomenti come la concorrenza ottimistica e pessimistica, ne vi ho parlato dei livelli di isolamento e della lock escalation. Neppure abbiamo fatto esempi per vedere i lock in azione.
Per tutto questo e tanto altro ...rimanete in attesa della seconda parte che è già in fase di preparazione.

A presto,

Luca

Luca Biondi @ SQLServerPerformance blog!

 

 

 

 

 

Help me to share knowledge on my blog    

 

Next: SQL Server ottimizzazioni: perchè non utilizzare l’asterisco nelle SELECT?  

Previous post: SQL Server Ottimizzazioni: non omettere il nome dello schema! (schema name resolution)