Pessimistic Concurrency - Uge 17

30. jul 2021
Den 5. måned starter ud med Locking, Blocking og Deadlocking på SQL Serveren.

SQL serveren anvender to modeller til at definere hvordan parallelle queries er eksekveret. Ikke dermed ment de queries som en enkelt query som eksekveres på flere tråde, men to forskellige queries som eksekveres samtidig.

I dette indlæg vil jeg give dig et overblik over de forskellige Isolation Levels som er den del af den pessimistiske model (pessimistic concurrency) for parallelle queries. I næste uge kommer en gennemgang af den optimiske model.
Brian Bønk Rueløkke
PRINCIPAL ARCHITECT
Uge 17

Pessimistiske Isolation Levels

Pessimistisk parallellitet betyder at SELECT queries blokerer for INSERT, UPDATE og DELETE queries, og vise versa. SQL serveren anvender Locks til dette formål.

  • SELECT får tildelt Shared Locks (S)
  • INSERT, UPDATE og DELETE for tildelt Exclusive Locks (X)

Begge Locks er ikke kompatible med hinanden. Dette betyder at man ikke kan læse fra og skrive til den samme datarække på samme tid. Hvis dette bliver forsøgt opstår der et såkaldt Blocking scenarie. Når man sætter en specifik Isolation Level på en transaktion, indvirker man direkte hvordan SELECT queries skal opretholde deres S locks. Man kan derimod ikke påvirke X locks – de bliver altid tildelt til INSERT, UPDATE og DELETE.

Pr. default bliver alle queries eksekveret i Isolation Level Read Commited. Read Commited betyder at SQL serveren tager en S Lock på en datarække i det tidsrum det tager at læse den. Lige så snart en datarække er læst og processeret, bliver S Lock frigivet øjeblikkeligt. Når man anvender en Scan Operator (i single tråd) bliver der kun anvendt 1 S Lock på et givnet tidspunkt. På baggrund af denne opsætning, er det muligt at en efterfølgende transaktion kan ændre i datarækken umiddelbart bagefter. Hvis man læser den samme datarække igen inden for den samme transaktion, opstår der såkaldt Non-Repeatable Read: man læser den samme datarække flere gange og man modtager forskellige værdier hver gang.

Hvis man gerne vil have en anden tilgang for læsning af data, kan man anvende en mere striks Isolation Level såsom Repeatable Read. Denne Isolation Level giver mulighed for at holde S Lock lige så længe som transaktionen forløber med læsning af datarækken. Derfor kan der heller ikke opnås X Locks (og dermed ikke ændres i data) i den periode – det ville skabe et scenarie af Blocking Queries. Denne tilgang har nogen fordele og ulemper. På den ene side får man mere korrekt data inden for samme transaktion og på den anden side vil man opleve flere blocking scenarier, fordi SELECT holder deres S Lock i længere tid.

Man kan være endnu mere striks i tilgangen og vælge Isolation Level Serializable. Med denne tilgang – den mest strikse på SQL serveren – kan man helt undgå såkaldte Phantom Records. En Phanton Record opstår og forsvinder igen når man tilgår det samme datasæt flere gange. For at undgå disse phantom records anvender SQL serveren en såkaldt Key Range Locking teknik ved at låse det subset af datarækker man som bruger først modtager.

Derfor kan ingen anden parallel query arbejde med disse datarækker. Det er derfor umuligt at lave INSERT, UPDATE og DELETE på de rækker. Disse queries vil blot blive blocket. Der er brug for et støttende index på search predicate som definerer datarækkerne. Med dette støttende index kan SQL serveren låse de individuelle index nøgler. Uden dette index bliver SQL serveren nødt til at låse hele tabellen, og dermed indvirke meget negativt på alle andre queries som tilgår denne tabel.

Slutteligt kan SQL serveren konfigureres til at bruge isolation level Read Uncommitted. Med denne isolation level bliver der ikke uddelt nogen S Locks ved læsning af data. Derfor er det muligt at læse data som endnu ikke er committed til databasen og som stadig er in-flight i en transaktion. Dette kaldes også for Dirty Reads. Hvis en sådan transaktion laver en Roll Back, vil man have læst data som aldrig har fandtes på databasen og som heller ikke kan genskabes på nogen måde. Dirty Reads opstår bl.a. når man bruger det berømte NOLOCK query hint.

Pessimistisk Isolation Levels er ikke så kompliceret – eller hvad synes du? 😀 Isolation level fortæller blot SQL serveren hvor længe en S Lock skal opretholdes ifm læsning af data. Og baseret på denne indstilling bliver der besluttet hvad der er tilladt og ikke tilladt under datalæsning. Jeg har forsøgt at lave et overblik som nedenstående illustration.

I tillæg til at opsætte en specifik Isolation Level, er det også muligt for SQL serveren midlertidigt at sætte Isolation Level på en query for at garantere rigtighed af data og resultatsættet.

Opsummering – Pessimistic Concurrency

Med dette indlæg og opstarten af 5. måned har jeg forsøgt at give et overblik over de forskellige pessimistiske isolation levels på SQL serveren. Med dette fundament, har du også fået første viden omkring fejlsøgning på locking og blocking scenarier på SQL serveren. SELECT blokerer for INSERT, UPDATE og DELETE – INSERT, UPDATE og DELETE blokerer for SELECT.

I næste uge tager jeg fat på 2 andre isolation levels på SQL serveren som anvendes i forbindelse med optimistic concurrency.

Har du misset nogle af de tidligere indlæg, kan du finde dem herunder:

Data & AI

Fra enkle datavisualiseringer til mere komplekse Business Intelligence og Data Warehouse løsninger.

Data & AI
Læs mere om vores Data & AI løsninger
Data & AI