Beskriv blokering og låsning

  • 12 minutter

Låsning er en vigtig funktion i relationsdatabaser, der er afgørende for at opretholde acid-modellens atomitet, konsistens og isolationsegenskaber. Alle RDBMSs blokerer handlinger, der overtræder konsistensen og isoleringen af databaseskrivninger. SQL-programmører skal starte og afslutte transaktioner på de rette punkter for at sikre ensartet data. Databaseprogrammet indeholder låsemekanismer til at beskytte den logiske konsekvens af de berørte tabeller, som er grundlæggende for relationsmodellen.

I SQL Server forekommer blokering, når én proces har en lås på en bestemt ressource (række, side, tabel, database), og en anden proces forsøger at hente en lås med en inkompatibel låsetype på den samme ressource. Låse opbevares typisk i en kort periode, og når den proces, der holder låsen, frigiver den, kan den blokerede proces hente låsen og fuldføre dens transaktion.

SQL Server låser den mindste mængde data, der kræves for at fuldføre en transaktion, hvilket giver mulighed for maksimal samtidighed. Hvis SQL Server f.eks. låser en enkelt række, forbliver alle andre rækker i tabellen tilgængelige for andre processer og aktiverer samtidig arbejde. Hver lås kræver dog hukommelsesressourcer, så det er ikke omkostningseffektivt for én proces at holde tusindvis af individuelle låse på en enkelt tabel. Sql Server bruger en teknik, der kaldes låseeskalering, til at balancere samtidighed med omkostninger. Hvis mere end 5.000 rækker i et enkelt objekt skal låses i en enkelt sætning, eskalerer SQL Server låsene med flere rækker til en enkelt tabellås.

Låsning er en normal funktionsmåde og forekommer ofte hele dagen. Det bliver kun problematisk, når det medfører blokering, der ikke løses hurtigt. Der er to typer problemer med ydeevnen, der skyldes blokering:

  • En proces indeholder låse på et sæt ressourcer i en længere periode, før de frigives, hvilket medfører, at andre processer blokerer og nedgraderer forespørgselsydeevnen og samtidighed.
  • En proces henter låse på et sæt ressourcer og frigiver dem aldrig, hvilket kræver, at administratorhandlingen løses.

Deadlocking er et andet blokeringsscenarie, der opstår, når én transaktion har en lås på en ressource, og en anden transaktion har en lås på en anden ressource. Hver transaktion forsøger derefter at hente en lås på den ressource, der i øjeblikket er låst af den anden transaktion, hvilket fører til en uendelig ventetid, da ingen af transaktionerne kan fuldføres. SQL Server-programmet registrerer disse scenarier og løser problemet ved at dræbe en af transaktionerne baseret på, hvilken transaktion der har udført den mindste mængde arbejde, der skal tilbageføres. Den transaktion, der er dræbt, er kendt som dødvande offer. Deadlocks registreres i den system_health udvidede begivenhedssession, som er aktiveret som standard.

Det er vigtigt at forstå begrebet en transaktion. Autocommit er standardtilstanden for SQL Server og Azure SQL Database, hvilket betyder, at de ændringer, der foretages af følgende sætning, automatisk registreres i databasens transaktionslog.

INSERT INTO DemoTable (A) VALUES (1);

For at give udviklere mulighed for at have mere detaljeret kontrol over deres programkode giver SQL Server dig også mulighed for eksplicit at styre dine transaktioner. Følgende forespørgsel ville tage en lås på en række i tabellen DemoTable , som ikke ville blive frigivet, før en efterfølgende kommando til bekræftelse af transaktionen blev tilføjet.

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

Den korrekte måde at skrive følgende forespørgsel på er som følger:

BEGIN TRANSACTION

INSERT INTO DemoTable (A) VALUES (1);

COMMIT TRANSACTION

Kommandoen COMMIT TRANSACTION bekræfter eksplicit en registrering af ændringerne i transaktionsloggen. De ændrede data kommer til sidst asynkront ind i datafilen. Disse transaktioner repræsenterer en arbejdsenhed for databaseprogrammet. Hvis udvikleren glemmer at udstede COMMIT TRANSACTION kommandoen, forbliver transaktionen åben, og låsene frigives ikke. Dette er en af hovedårsagerne til langvarige transaktioner.

Den anden mekanisme, som databaseprogrammet bruger til at hjælpe databasen med samtidighed, er rækkeversionering. Når et isolationsniveau for rækkeversioner er aktiveret for databasen, vedligeholder programmet versioner af hver ændret række i TempDB. Dette bruges typisk i arbejdsbelastninger med blandet brug for at forhindre, at læseforespørgsler blokerer forespørgsler, der skriver til databasen.

Hvis du vil overvåge åbne transaktioner, der afventer bekræftelse eller annullering, skal du køre følgende forespørgsel:

SELECT tst.session_id, [database_name] = db_name(s.database_id)
    , tat.transaction_begin_time
    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
        WHEN 2 THEN 'Read-only transaction'
        WHEN 3 THEN 'System transaction'
        WHEN 4 THEN 'Distributed transaction' END
    , input_buffer = ib.event_info, tat.transaction_uow     
    , transaction_state  = CASE tat.transaction_state    
        WHEN 0 THEN 'The transaction has not been completely initialized yet.'
        WHEN 1 THEN 'The transaction has been initialized but has not started.'
        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
        WHEN 6 THEN 'The transaction has been committed.'
        WHEN 7 THEN 'The transaction is being rolled back.'
        WHEN 8 THEN 'The transaction has been rolled back.' END 
    , transaction_name = tat.name, request_status = r.status
    , tst.is_user_transaction, tst.is_local
    , session_open_transaction_count = tst.open_transaction_count  
    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;

Isolationsniveauer

SQL Server tilbyder flere isolationsniveauer, så du kan definere det niveau af ensartethed og korrekthed, du har brug for, og som du skal garantere for dine data. Isolationsniveauer giver dig mulighed for at finde en balance mellem samtidighed og ensartethed. Isolationsniveauet påvirker ikke de låse, der tages for at forhindre ændring af data. En transaktion får altid en eksklusiv lås på de data, der ændres. Dit isolationsniveau kan dog påvirke den tid, dine låse opbevares. Lavere isolationsniveauer øger muligheden for, at flere brugerproces kan få adgang til data på samme tid, men øger risikoen for datakonsistens, der kan opstå. Isolationsniveauerne i SQL Server er som følger:

  • Læs ubekræftet – Laveste tilgængelige isolationsniveau. Ugyldige læsninger er tilladt, hvilket betyder, at én transaktion kan se ændringer foretaget af en anden transaktion, der endnu ikke er bekræftet.

  • Læst bekræftet – gør det muligt for en transaktion at læse data, der tidligere er læst, men ikke ændret af en anden transaktion uden at vente på, at den første transaktion afsluttes. Dette niveau frigiver også læselåse, så snart den valgte handling udføres. Dette er SQL Server-standardniveauet.

  • Gentagelig læsning – Dette niveau bevarer læse- og skrivelåse, der hentes på udvalgte data, indtil transaktionen er afsluttet.

  • Serialiserbar – Dette er det højeste niveau af isolation, hvor transaktioner er isoleret. Læse- og skrivelåse hentes på valgte data og frigives ikke, før transaktionen er afsluttet.

SQL Server indeholder også to isolationsniveauer, der omfatter rækkeversionsstyring.

  • Læst bekræftet øjebliksbillede – På dette niveau kræver læsehandlinger ingen række- eller sidelåse, og programmet præsenterer hver handling med et ensartet øjebliksbillede af dataene, som de fandtes i starten af forespørgslen. Dette niveau bruges typisk, når brugerne kører hyppige rapporteringsforespørgsler mod en OLTP-database for at forhindre, at læsehandlinger blokerer skrivehandlinger.

  • Øjebliksbillede – Dette niveau giver læsekonsistens på transaktionsniveau via rækkeversionering. Dette niveau er sårbart over for opdateringskonflikter. Hvis en transaktion, der kører under dette niveau, læser data, der er ændret af en anden transaktion, afsluttes en opdatering af snapshottransaktionen og annulleres. Dette er ikke et problem med skrivebeskyttet snapshotisolation.

Isolationsniveauer angives for hver session med T-SQL-kommandoen SET , som vist:

SET TRANSACTION ISOLATION LEVEL

 { READ UNCOMMITTED

 | READ COMMITTED

 | REPEATABLE READ

 | SNAPSHOT

 | SERIALIZABLE

 }

Det er ikke mulig at angive et globalt isolationsniveau for alle forespørgsler, der kører i en database, eller for alle forespørgsler, der køres af en bestemt bruger. Det er en indstilling for sessionsniveau.

Overvågning af blokeringsproblemer

Det kan være en udfordring at identificere blokeringsproblemer på grund af deres sporadiske karakter. DMV sys.dm_tran_locksindeholder oplysninger om låsene i hver session, når den er joinforbundet med sys.dm_exec_requests. En mere effektiv måde at overvåge blokeringsproblemer på er at bruge programmet Udvidede hændelser løbende.

Blokeringsproblemer falder typisk i to kategorier:

  • Dårligt transaktionsdesign: En transaktion uden en COMMIT TRANSACTION vil f.eks. aldrig slutte. Hvis du forsøger at udføre for meget arbejde i en enkelt transaktion eller have en distribueret transaktion ved hjælp af en sammenkædet serverforbindelse, kan det føre til uforudsigelig ydeevne.
  • Langvarige transaktioner, der skyldes skemadesign: Dette omfatter ofte en opdatering af en kolonne med et manglende indeks eller en dårligt designet opdateringsforespørgsel.

Overvågning af problemer med låserelateret ydeevne giver dig mulighed for hurtigt at identificere forringelse af ydeevnen i forbindelse med låsning.

Du kan finde flere oplysninger om, hvordan du overvåger blokering, under Forstå og løse SQL Server-blokeringsproblemer.