Salgo sul palco per la prima sessione del pomeriggio: Livelli d'isolamento: guida all'uso corretto.
L'argomento non è dei più attraenti (a me, invece, piace moltissimo), in più come prima sessione dopo il pranzo rischiavo di far addormentare i ragazzi partecipanti.
Per questo motivo la sessione è iniziata con una demo dal titolo alquanto devastante: "La read committed come non l'avete mai vista".
Effettivamente scopo del codice era quello di stimolare l'attenzione su un risultato che (come si è dimostrato) era assolutamente inatteso.
Ve lo giro.
Costruiamo un db con una tabella [stipendi] assai banale, qualcosa come:
CREATE DATABASE STIPENDI
GO
USE STIPENDI
GO
/*
Attenzione alla costruzione della tabella:
- chiave primaria non clustered su colonna idRecord
- constraint unique su cognome, nome
- indice clustered su colonna valore
*/
Create table retribuzioni
(
idRecord smallint identity(1,1),
cognome varchar(35),
nome varchar(35),
valore decimal(10,2),
CONSTRAINT PK_retribuzioni PRIMARY KEY nonclustered (idRecord),
CONSTRAINT u_cognomeNome unique (cognome,nome)
)
GO
Create clustered index idx_valore on retribuzioni(valore)
GO
insert retribuzioni (cognome, nome, valore) values ('mauri','davide',1500)
insert retribuzioni (cognome, nome, valore) values ('benedetti','andrea',1000)
/* Vedo la tabella */
select * from retribuzioni
Quello che ottengo é:
idRecord cognome nome valore
-------- --------------- --------------- ---------------------------------------
2 benedetti andrea 1000.00
1 mauri davide 1500.00
A questo punto apriamo due finestre di query per simulare due differenti utenti che andranno a lavorare sulla stessa tabella: connessione1 e connessione2.
Questa la connessione 1:
/* ****************************
CONNESSIONE 1
**************************** */
USE Stipendi
GO
begin tran
update retribuzioni
set valore = valore * 2
where cognome = 'mauri'
--> vado su CONNESSIONE 2 che lancia una "select * ..." sulla tabella
--> quindi torno qui, eseguo la seconda update, la commit e la select sulla tabella
update retribuzioni
set valore = valore * 2
where cognome = 'benedetti'
commit tran
select * from retribuzioni
Questa la connessione 2:
/* ****************************
CONNESSIONE 2
**************************** */
USE Stipendi
GO
select * from retribuzioni
Cosa otteniamo al termine delle istruzioni sulla finestra di connessione 2?
Questo:
idRecord cognome nome valore
-------- --------------- --------------- ---------------------------------------
2 benedetti andrea 1000.00
2 benedetti andrea 2000.00
1 mauri davide 3000.00
Ovvero: DUE volte il mio record!
La domanda è: questo risultato è corretto, seppur non atteso? SI.
Perchè?
Perchè la connessione 1 applica un lock (tramite la update), all'interno di una transazione, il secondo record (secondo inteso come secondo in ordine di scrittura all'interno della data page).
La connessione 1 inizia a leggere e legge il primo record (idRecord 2 che non ha nessun lock) quindi, arrivato al secondo record, trovandolo lockato deve attendere che venga rilasciato.
A questo punto la connessione 2 esegue un update anche sul primo record (idRecord 2 che, avendo come nuovo "valore" una cifra più alta del secondo record, verrà scritto dopo idRecord 1 di " davide mauri") ed esegue un commit della transazione.
Ora, la connessione 1, non trovando più lock sul secondo record (idRecord 1), riprende a leggere ovvero:
- legge il record di davide mauri su cui è stato appena tolto il lock che bloccava la connessione di lettura
- continua la sua operazione di scan della tabella leggendo "nuovamente" il record di andrea benedetti che è ora scritto fisicamente dopo il record di davide mauri.
Perchè avviene?
Perchè il livello di isolamento read committed, livello di default di SQL Server, applica lock condivisi brevi, riga per riga, ovvero:
- durata: il tempo di leggere e processare la riga
- rilascio: prima di leggere la riga seguente
NON ho alcuna protezione su righe non ancora lette nè già lette.
Come sempre i commenti sono aperti!