Isolation level read commited - linii duplicate
Voi discuta astazi tot despre niste rezultate surprinzatoare, de data aceasta pentru un select ce ruleaza in isolation level read commited. Scenariul pe care il propun consta in folosirea a 2 conexiuni:
1. Una ce foloseste isolation level read commited si executa in cadrul unei tranzactii un select pe o tabela mare (>500k linii) organizata ca un index clusterat.
2. A doua ce foloseste tot isolation level read commited si, in cadrul unei tranzactii executate in timpul selectului, muta liniile in btree din frunzele cele mai din stanga catre frunze aflate la capatul din drepta.
Rezultatul selectului de pe conexiunea 1 este surprinzator prin faptul ca liniile mutate vor fi luate in considerare de 2 ori. Surprinderea este data de faptul ca, in credinta populara, in cadrul isolation level read commited lockurile shared luate pentru citirea tabelei sunt pastrate pana la sfarsitul statement-ului select. Aceasta credinta e generata probabil si de Books Online unde citim:
“If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared locks are released when the statement completes.”
Ce se intampla de fapt, pe timpul rularii statement-ului select, database engine-ul proceseaza paginile de pe ultimul nivel din btree achizitionand lock-uri pe 1 pagina, maxim 2 si renuntand la ele odata ce procesarea paginii s-a incheiat. El previne astfel dirty read-uri insa sufera de nonreapeatable reads in scenariul descris. Un nivel de izolare repeatable read sau mai mare rezolva aceasta problema deoarece pastreaza lockurile (in exemplul nostru luat direct la nivel de tabela si nu de pagini) pana la sfarsitul tranzactiei.
Trebuie sa intelegem insa: comportamentul observat, desi neasteptat, este normal pentru nivelul de izolare ales. Sql Server achizitioneaza lockuri la un nivel cat mai mic si renunta la ele cat mai devreme pentru a mari concurenta, in acelasi timp respectand functionalitatea descrisa in Books Online unde nonreapeatable reads este un side-effect al nivelului de izolare read commited. In cazul in care suntem afectati de asemeneaza efecte in cadrul unei aplicatii trebuie sa ridicam nivelul de izolare pentru ca acest tip de probleme sa dispara.
Scenariu:
1. Se creaza tabela organizata ca si index clusterat (campul codloc asignat ca si primary key duce la crearea unui index clusterat unique pe acesta).
CREATE TABLE [dbo].[test](
[codloc] int primary key,
[codjud] [char](2) NULL,
[tip] [char](1) NULL,
[localit] [varchar](25) NULL
) ON [PRIMARY]
2. Umplem tabela test cu 2^19 linii in 19 pasi, fiecare dubland numarul de linii:
insert into test values(1, 'AB', 'O', 'Alba Iulia')
declare @step int, @maxkey int
set @step = 1
while @step <= 19
begin
select @maxkey = max(codloc) from test
insert into test
select codloc + @maxkey, codjud, tip, localit from test
set @step = @step + 1
end
3. Pe aceeasi conexiune construim selectul care va dura mult. Deoarece numarul de linii folosite in exemplu nu este foarte mare, aceasta va contine multe functii matematice care incearca sa incetineasca procesarea. De asemenea incercam sa nu duplicam functiile deoarece vor fi evaluate o singura data.
set isolation level read committed
begin tran
select count(0) cnt,
max(sqrt(ascii(codjud)+1)),
max(sqrt(ascii(codjud)+2)),
max(sqrt(ascii(codjud)+3)),
max(sqrt(ascii(codjud)+4)),
max(log10(exp(ascii(codjud)+1))),
max(log10(exp(ascii(codjud)+2))),
max(log10(exp(ascii(codjud)+3))),
max(log10(exp(ascii(codjud)+4))),
max(cos(ascii(localit)+1)),
max(cos(ascii(localit)+2)),
max(cos(ascii(localit)+3)),
max(cos(ascii(localit)+4)),
max(sin(exp(ascii(localit)+1))),
max(sin(exp(ascii(localit)+2))),
max(sin(exp(ascii(localit)+3))),
max(sin(exp(ascii(localit)+4)))
from test
commit tran
Din rezultatele selectului ne intereseaza cnt care va fi 524288.
cnt
------
524288
4. Deschidem o conexiune noua in care rulam de mai multe ori sp_lock in timp ce selectul de mai sus ruleaza. Observam faptul ca de fiecare data avem un lock S pe o singura pagina in afara de cel IS pe tabela:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
62 7 565577053 0 TAB IS GRANT
62 7 565577053 1 PAG 1:1741 S GRANT
5. Deschidem o noua conexiune pe care vom rula codul:
set isolation level read committed
begin tran
update test set codloc = 1000000 + codloc where codloc < 1000
commit tran
Rezultatul este:
(999 row(s) affected)
6. Rulam selectul de pe conexiunea descrisa la punctul 3. In timp ce ruleaza pornim si update-ul de la punctul 5. In rezultatul selectului vedem:
cnt
------
525287
Numarul de linii din [test] nu s-a schimbat (nu au avut loc insert-uri sau delete-uri). Totusi obtinem o valoare pentru [cnt] mai mare cu 999, ceea ce inseamna ca liniile mutate prin update au fost luat in considerare de doua ori.
posted @ Wednesday, August 22, 2007 4:18 PM