Optimerad låsning

gäller för:Azure SQL DatabaseSQL-databas i Microsoft Fabric

Den här artikeln introducerar optimerad låsning, en databasmotorfunktion som erbjuder en förbättrad mekanism för transaktionslåsning för att minska förbrukningen av låsminne och blockera samtidiga transaktioner.

Vad är optimerad låsning?

Optimerad låsning hjälper till att minska låsminnet eftersom mycket få lås hålls även för stora transaktioner. Dessutom undviker låsoptimering även låseskaleringar. Detta ger mer samtidig åtkomst till tabellen.

Optimerad låsning består av två primära komponenter: transaktions-ID (TID) låsning och låsning efter kvalificering (LAQ).

• Ett transaktions-ID (TID) är en unik identifierare för en transaktion. Varje rad är märkt med den senaste TID som ändrade den. I stället för potentiellt många nyckel- eller radidentifierare låses ett enda lås på TID. Mer information finns i transaktions-ID (TID) som låser.
• Lås efter kvalificering (LAQ) är en optimering som utvärderar frågepredikat med hjälp av den senaste bekräftade versionen av raden utan att hämta ett lås, vilket förbättrar samtidigheten. För mer information, se Lås efter kvalificering (LAQ).

Till exempel:

• Utan optimerad låsning kan uppdatering av 1 000 rader i en tabell kräva 1 000 exklusiva radlås (X) som hålls kvar till slutet av transaktionen.
• Med optimerad låsning kan uppdatering av 1 000 rader i en tabell kräva 1 000 X radlås, men varje lås släpps så snart varje rad uppdateras och endast ett TID-lås hålls kvar till slutet av transaktionen. Eftersom lås frigörs snabbt minskar användningen av låsminnet, och låseskalering inträffar mycket mindre ofta, vilket förbättrar samtidigheten i arbetsbelastningen.

Anteckning

Aktivering av optimerad låsning minskar eller eliminerar rad- och sidlås som hämtas av DML-instruktioner (Data Modification Language), till exempel INSERT, UPDATE, DELETE, MERGE. Det påverkar inte andra typer av databas- och objektlås, till exempel schemalås.

Tillgänglighet

Optimerad låsning är tillgänglig i Azure SQL Database och SQL-databas endast i Microsoft Fabric, på alla tjänstnivåer och beräkningsstorlekar.

Optimerad låsning är för närvarande inte tillgänglig i Azure SQL Managed Instance eller i SQL Server.

Är optimerad låsning aktiverad?

Optimerad låsning är aktiverad per användardatabas. Anslut till databasen och använd sedan följande fråga för att kontrollera om optimerad låsning är aktiverad:

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

Resultat	Beskrivning
0	Optimerad låsning är inaktiverad.
1	Optimerad låsning är aktiverad.
NULL	Optimerad låsning är inte tillgänglig.

Optimerad låsning bygger på andra databasfunktioner:

• Optimerad låsning kräver accelererad databasåterställning (ADR) att aktiveras i databasen.
• För den största fördelen med optimerad låsning bör läsa incheckad ögonblicksbildisolering (RCSI) aktiveras för databasen. Komponenten LAQ för optimerad låsning gäller endast om RCSI är aktiverat.

Både ADR och RCSI är aktiverade som standard i Azure SQL Database. Om du vill kontrollera att de här alternativen är aktiverade för den aktuella databasen ansluter du till databasen och kör följande T-SQL-fråga:

SELECT name,
       is_read_committed_snapshot_on,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = DB_NAME();

Låsningsöversikt

Det här är en kort sammanfattning av beteendet när optimerad låsning inte är aktiverad. Mer information finns i guiden Transaktionslåsning och Radversioner.

I databasmotorn är låsning en mekanism som förhindrar att flera transaktioner uppdaterar samma data samtidigt för att garantera ACID- egenskaper för transaktioner.

När en transaktion behöver ändra data begär den ett lås på data. Låset beviljas om inga andra motstridiga lås lagras på data och transaktionen kan fortsätta med ändringen. Om ett annat konflikterande lås innehas på datan, måste transaktionen vänta tills låset släpps innan den kan fortsätta.

När flera transaktioner försöker komma åt samma data samtidigt måste databasmotorn lösa potentiellt komplexa konflikter med samtidiga läsningar och skrivningar. Låsning är en av de mekanismer som motorn kan använda för att tillhandahålla semantik för ANSI SQL-transaktionen isoleringsnivåer. Även om det är viktigt att låsa i databaser kan minskad samtidighet, dödlägen, komplexitet och låskostnader påverka prestanda och skalbarhet.

Låsning av transaktions-ID (TID)

När radversioneringsbaserade isoleringsnivåer används eller när ADR är aktiverat, innehåller varje rad internt i databasen ett transaktions-ID (TID). Denna TID sparas på disken. Varje transaktion som ändrar en rad stämplar raden med sin TID.

Vid TID-låsning, istället för att låsa nyckeln för raden, låses TID för raden. Den ändringstransaktion som har ett X-lås på sin TID. Andra transaktioner tar ett S-lås på TID och väntar tills den första transaktionen är avslutad. Med TID-låsning fortsätter sid- och radlås att vidtas för ändringar, men varje sida och radlås släpps så snart varje rad har ändrats. Det enda låset som hålls till slutet av transaktionen är det enda X lås på TID-resursen och ersätter flera sid- och radlås (nyckel).

Tänk dig följande exempel som visar lås för den aktuella sessionen medan en skrivtransaktion är aktiv:

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

UPDATE t0
SET b = b + 10;

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
      AND
      resource_type IN ('PAGE','RID','KEY','XACT');

COMMIT TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

Om optimerad låsning är aktiverad innehåller begäran bara ett enda X lås på resursen XACT (transaktion).

Om optimerad låsning inte är aktiverad innehåller samma begäran fyra lås – ett IX (exklusivt) lås på sidan som innehåller raderna och tre X nyckellås på varje rad:

Den sys.dm_tran_locks dynamiska hanteringsvyn (DMV) är användbar för att undersöka eller felsöka låsningsproblem, genom att observera hur optimerad låsning fungerar i praktiken.

Lås efter kvalificering (LAQ)

På TID-infrastrukturen ändrar optimerad låsning hur DML-instruktioner som INSERT, UPDATEoch DELETE skaffar lås.

Utan optimerad låsning kontrolleras frågepredikat rad för rad i en genomsökning genom att först ta ett uppdaterings -U) radlås. Om predikatet är uppfyllt tas ett exklusivt (X) radlås innan raden uppdateras och hålls kvar till slutet av transaktionen.

Med optimerad låsning och när ögonblicksbildisoleringsnivån READ COMMITTED (RCSI) är aktiverad kan predikaten kontrolleras optimistiskt på den senaste bekräftade versionen av raden utan att några lås tas. Om predikatet inte uppfylls flyttas frågan till nästa rad i genomsökningen. Om predikatet är uppfyllt tas ett X radlås för att uppdatera raden.

Med andra ord tas låset efter att raden har kvalificerats för ändring. X-radlåset släpps så snart raduppdateringen är klar, före transaktionens slut.

Eftersom predikatutvärdering utförs utan att hämta några lås blockeras inte samtidiga frågor som ändrar olika rader.

Till exempel:

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

INSERT INTO t1
VALUES (1,10),(2,20),(3,30);
GO

Session 1	Session 2
BEGIN TRANSACTION; UPDATE t1 SET b = b + 10 WHERE a = 1;
	BEGIN TRANSACTION; UPDATE t1 SET b = b + 10 WHERE a = 2;
COMMIT TRANSACTION;
	COMMIT TRANSACTION;

Utan optimerad låsning blockeras session 2 eftersom session 1 har ett U-lås på den rad som session 2 måste uppdatera. Men med optimerad låsning blockeras inte session 2 eftersom U-lås inte tas, och i den senaste bekräftade versionen av rad 1 är kolumn a lika med 1, vilket inte uppfyller predikatet för session 2.

LAQ utförs optimistiskt under antagandet att en rad inte ändras efter att ha kontrollerat predikatet. Om predikatet är uppfyllt och raden inte har ändrats efter kontroll av predikatet ändras den av den aktuella transaktionen.

Eftersom U-låsen inte tas kan en samtidig transaktion eventuellt ändra raden efter att predikatet har utvärderats. Om det finns en aktiv transaktion som har ett X TID-lås på raden väntar databasmotorn på att den ska slutföras. Om raden har ändrats efter att predikatet utvärderades tidigare utvärderar databasmotorn (kvalificerar om) predikatet igen innan raden ändras. Om predikatet fortfarande är uppfyllt ändras raden.

Predicate re-qualification stöds av en delmängd av frågemotoroperatorerna. Om predikatomvärdering behövs, men frågeplanen använder en operator som inte stöder predikatomvalificering, avbryter databasmotorn internt instruktionsbearbetningen och startar om den utan LAQ. När en sådan abort inträffar utlöses den lock_after_qual_stmt_abort utökade händelsen.

Vissa instruktioner, till exempel UPDATE-instruktioner med variabeltilldelning och -instruktioner med satsen OUTPUT, kan inte avbrytas och startas om utan att deras semantik ändras. För sådana uttalanden används inte LAQ.

I följande exempel utvärderas predikatet igen eftersom en annan transaktion har ändrat raden:

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO

Session 1	Session 2
BEGIN TRANSACTION; UPDATE t3 SET b = b + 10 WHERE a = 1;
	BEGIN TRANSACTION; UPDATE t3 SET b = b + 10 WHERE a = 1;
COMMIT TRANSACTION;
	COMMIT TRANSACTION;

LAQ-heuristik

Som beskrivs i Lås efter kvalificering (LAQ), när LAQ används, kan vissa instruktioner startas om internt och bearbetas utan LAQ. Om detta sker ofta kan kostnaderna för upprepad bearbetning bli betydande. För att hålla detta arbete till ett minimum använder optimerad låsning en heuristikmekanism för att spåra upprepad bearbetning. Den här mekanismen inaktiverar LAQ för databasen om omkostnaderna överskrider ett tröskelvärde.

I heuristikmekanismen mäts det arbete som utförs av en instruktion i antalet sidor som bearbetas (logiska läsningar). Om databasmotorn ändrar en rad som har ändrats av en annan transaktion efter att instruktionsbearbetningen har startats behandlas det arbete som utförs av -instruktionen som potentiellt bortkastat eftersom -instruktionen kan avbrytas och startas om. Systemet håller reda på det totala potentiellt bortkastade arbetet och det totala arbetet som utförs av alla satser i databasen.

LAQ inaktiveras för databasen om procentandelen potentiellt bortkastat arbete överskrider ett tröskelvärde. LAQ inaktiveras också om antalet omstartade instruktioner överskrider ett tröskelvärde.

Om det bortkastade arbetet och antalet omstartade instruktioner understiger respektive tröskelvärden aktiveras LAQ på nytt för databasen.

Frågebeteendeändringar med optimerad låsning och RCSI

Samtidiga arbetsbelastningar under skrivskyddad ögonblicksbildisolering (RCSI) som förlitar sig på strikt körningsordning för transaktioner kan uppleva skillnader i frågebeteende när optimerad låsning är aktiverad.

Tänk dig följande exempel där transaktion T2 uppdaterar tabellen t4 baserat på kolumn b som uppdaterades under transaktion T1.

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

Session 1	Session 2
BEGIN TRANSACTION T1; UPDATE t4 SET b = 2 WHERE a = 1;
	BEGIN TRANSACTION T2; UPDATE t4 SET b = 3 WHERE b = 2;
COMMIT TRANSACTION;
	COMMIT TRANSACTION;

Nu ska vi utvärdera resultatet av det tidigare scenariot med och utan lås efter kvalificering (LAQ).

utan LAQ

Utan LAQ blockeras UPDATE-instruktionen i transaktion T2 i väntan på att transaktionen T1 ska slutföras. När T1 har slutförts uppdaterar T2 kolumnen för radinställning från b till 3 eftersom T2:s villkor är uppfyllt.

Efter båda transaktionerna innehåller tabellen t4 följande rader:

 a | b
 1 | 3

med LAQ

Med LAQ använder transaktion T2 den senaste bekräftade versionen av raden där kolumnen b är lika med 1 för att utvärdera dess predikat (b = 2). Raden kvalificerar sig inte, därför hoppas den över och instruktionen slutförs utan att blockeras av transaktion T1. I det här exemplet tar LAQ bort blockering men leder till olika resultat.

Efter båda transaktionerna innehåller tabellen t4 följande rader:

 a | b
 1 | 2

Viktig

Även utan LAQ bör program inte förutsätta att databasmotorn garanterar strikt ordning utan att använda låsningstips när radversionsbaserade isoleringsnivåer används. Vår allmänna rekommendation för kunder som kör samtidiga arbeten under RCSI där man förlitar sig på strikt utföringsordning av transaktioner (som visas i föregående exempel) är att använda strängare isoleringsnivåer som REPEATABLE READ och SERIALIZABLE.

Diagnostiska tillägg för optimerad låsning

Följande förbättringar hjälper dig att övervaka och felsöka blockering och dödlägen när optimerad låsning är aktiverad:

• Väntetyper för optimerad låsning
  • XACT väntetyper för att vänta på S-lås på TID och resursbeskrivningar i sys.dm_os_wait_stats (Transact-SQL):
    • LCK_M_S_XACT_READ – Inträffar när en uppgift väntar på ett delat lås på en XACTwait_resource typ, med syfte att läsa.
    • LCK_M_S_XACT_MODIFY – Inträffar när en uppgift väntar på att få ett delat lås på en XACTwait_resource typ, med avsikt att göra ändringar.
    • LCK_M_S_XACT – Inträffar när en uppgift väntar på ett delat lås på en XACTwait_resource typ, där avsikten inte kan härledas. Det här scenariot är inte vanligt.
• Låsa resurssynlighet
  • XACT låser resurser. För mer information, se resource_description i sys.dm_tran_locks (Transact-SQL).
• Synlighet för väntande resurs
  • XACT vänta på resurser. Mer information finns i wait_resource i sys.dm_exec_requests (Transact-SQL).
• Deadlock-graf
  • Under varje resurs i dödlägesrapporten <resource-list>rapporterar <xactlock>-elementet de underliggande resurserna samt specifik information om lås för varje medlem i ett dödläge. Mer information och ett exempel finns i Optimerad låsning och dödlägen.
• Utökade händelser
  • lock_after_qual_stmt_abort-händelsen triggas när en instruktion avbryts internt och startas om på grund av en konflikt med en annan transaktion. För mer information, se Lås efter kvalificering (LAQ).

Metodtips med optimerad låsning

Aktivera skrivskyddade ögonblicksbildisolering (RCSI)

För att maximera fördelarna med optimerad låsning rekommenderar vi att du aktiverar skrivskyddad isolering av ögonblicksbilder (RCSI) på databasen och använder READ COMMITTED isolering som standardisoleringsnivå. Om det inte redan är aktiverat aktiverar du RCSI genom att ansluta till master-databasen och köra följande instruktion:

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

I Azure SQL Database är RCSI aktiverat som standard och READ COMMITTED är standardisoleringsnivån. När RCSI är aktiverat och när du använder isoleringsnivån READ COMMITTED läser läsarna en version av raden från snapshoten som togs i början av instruktionen. Med LAQ kvalificerar författare rader enligt predikatet baserat på den senaste bekräftade versionen av raden och utan att hämta U lås. Med LAQ väntar en sökfråga bara om raden uppfyller kraven och det finns en aktiv skrivprocess på den raden. Om du kvalificerar baserat på den senaste bekräftade versionen och endast låser de kvalificerade raderna minskar blockeringen och ökar samtidigheten.

Förutom minskad blockering minskas det nödvändiga låsminnet. Detta beror på att läsarna inte tar några lås, och skrivare tar bara kortvariga lås, i stället för de lås som hålls till slutet av transaktionen. När du använder striktare isoleringsnivåer, till exempel REPEATABLE READ eller SERIALIZABLE, innehåller databasmotorn rad- och sidlås till slutet av transaktionen även med optimerad låsning aktiverad, för både läsare och skrivare, vilket resulterar i ökad blockering och låsning av minnesanvändning.

Undvik att låsa ledtrådar

Även om tabell- och frågeledtrådar, som ,, UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCKosv., respekteras när optimerad låsning är aktiverad, minskar de effekten av denna optimering. Låsledtrådar tvingar databasmotorn att ta rad- eller sidlås och hålla kvar dem till slutet av transaktionen för att respektera avsikten med låsledtrådarna. Vissa program har logik där låstips behövs, till exempel när du läser en rad med UPDLOCK tips och sedan uppdaterar den senare. Vi rekommenderar att du endast använder låstips där det behövs.

Med optimerad låsning finns det inga begränsningar för befintliga frågor och frågor behöver inte skrivas om. Frågor som inte använder tips drar nytta av optimerad låsning mest.

Ett tabelltips för en tabell i en fråga inaktiverar inte optimerad låsning för andra tabeller i samma fråga. Dessutom påverkar optimerad låsning endast låsningsbeteendet för tabeller som uppdateras av en DML-instruktion, till exempel INSERT, UPDATE, DELETEeller MERGE. Till exempel:

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

I föregående frågeexempel påverkas endast tabell t6 av låstipset, medan t5 fortfarande kan dra nytta av optimerad låsning.

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

I föregående frågeexempel använder endast tabell t5REPEATABLE READ isoleringsnivå och håller lås tills slutet av transaktionen. Andra uppdateringar av t5 kan fortfarande dra nytta av optimerad låsning. Samma sak gäller för HOLDLOCK-ledtråd.

Vanliga frågor och svar

Är optimerad låsning aktiverad som standard i både nya och befintliga databaser?

I Azure SQL-databasen, ja.

Hur kan jag identifiera om optimerad låsning är aktiverat?

Se Är den optimerade låsningen aktiverad?.

Vad händer när accelererad databasåterställning (ADR) inte är aktiverad i min databas?

Om ADR är inaktiverat inaktiveras även optimerad låsning automatiskt.

Vad händer om jag vill tvinga frågekommandon att blockera trots optimerad låsning?

För kunder som använder RCSI, använd READCOMMITTEDLOCK frågehint för att tvinga fram blockering mellan två frågor när optimerad låsning är aktiverad.

Används optimerad låsning på skrivskyddade sekundära repliker?

Nej, eftersom DML-instruktioner inte kan köras på skrivskyddade repliker och motsvarande rad- och sidlås inte tas.

Används optimerad låsning vid ändring av data i tempdb och i temporära tabeller?

Inte just nu.

Relaterat innehåll

• Guide för transaktionslåsning och radversionering
• RCSI (Isolering vid läsning av åtagna ögonblicksbilder)
• sys.dm_tran_locks (Transact-SQL)
• Accelererad databasåterställning