Optimalizované uzamčení

platí pro:Azure SQL DatabaseSQL databáze v Microsoft Fabric

Tento článek představuje optimalizovanou funkci uzamčení, novou funkci databázového stroje, která nabízí vylepšený mechanismus uzamykání transakcí, který snižuje spotřebu paměti zámku a blokuje souběžné transakce.

Co je optimalizované uzamčení?

Optimalizované uzamykání pomáhá snížit paměť zámků, protože jen velmi málo zámků se uchovává i u velkých transakcí. Kromě toho optimalizované uzamčení také zabraňuje eskalaci zámků. To umožňuje více souběžného přístupu k tabulce.

Optimalizované zamykání se skládá ze dvou hlavních komponent: uzamčení ID transakce (TID) a uzamčení po kvalifikaci (LAQ).

• ID transakce (TID) je jedinečný identifikátor transakce. Každý řádek je označený posledním TID, který ho upravil. Na TID se použije místo potenciálně mnoha zámků klíčových nebo řádkových identifikátorů jediný zámek. Další informace naleznete v tématu Uzamykání ID transakce (TID).
• Zámek po kvalifikaci (LAQ) je optimalizace, která vyhodnocuje predikáty dotazů pomocí nejnovější potvrzené verze řádku bez získání zámku, čímž se zlepší souběžnost. Další informace naleznete v tématu Zámek po kvalifikaci (LAQ).

Například:

• Bez optimalizovaného uzamčení může aktualizace 1 000 řádků v tabulce vyžadovat 1 000 výhradních (X) zámků řádků uchovávaných až do konce transakce.
• S optimalizovaným uzamčením může aktualizace 1 000 řádků v tabulce vyžadovat 1 000 X zámků řádků, přičemž se každý zámek uvolní ihned po aktualizaci příslušného řádku, a do konce transakce se drží pouze jeden zámek TID. Vzhledem k tomu, že se zámky uvolňují rychle, snižuje se využití paměti zámků a eskalace zámků je mnohem méně pravděpodobná, což zlepšuje konkurenceschopnost úloh.

Poznámka

Povolení optimalizovaného uzamčení snižuje nebo eliminuje zámky řádků a stránek, které jsou získávány operacemi DML (Data Modification Language), jako jsou INSERT, UPDATE, DELETE, MERGE. Nemá žádný vliv na jiné druhy zámků databáze a objektů, jako jsou zámky schématu.

Dostupnost

Optimalizované zamykání je k dispozici pouze ve službě Azure SQL Database a databázi SQL v Microsoft Fabric, a to ve všech úrovních služby a velikostech výpočetních prostředků.

Optimalizované uzamčení není v současné době dostupné ve službě Azure SQL Managed Instance ani na SQL Serveru.

Je optimalizované uzamčení povolené?

Optimalizované uzamčení je povolené pro každou uživatelskou databázi. Připojte se k databázi a pomocí následujícího dotazu zkontrolujte, jestli je povolené optimalizované uzamčení:

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

Výsledek	Popis
0	Optimalizované uzamčení je zakázáno.
1	Optimalizované uzamčení je povolené.
NULL	Optimalizované uzamčení není k dispozici.

Optimalizované uzamykání vychází z dalších databázových funkcí:

• Optimalizované zamykání vyžaduje, aby bylo v databázi povoleno zrychlené obnovení databáze (ADR) pro.
• Pro většinu výhod optimalizovaného uzamčení by měla být pro databázi povolená izolace snímků potvrzené čtením (RCS I). Komponenta LAQ optimalizace uzamčení vejde v platnost pouze v případě, že je povolená RCSI.

Ve službě Azure SQL Database jsou ve výchozím nastavení povolené rozhraní ADR i RCSI. Pokud chcete ověřit, že jsou pro aktuální databázi povolené tyto možnosti, připojte se k databázi a spusťte následující dotaz T-SQL:

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

Přehled uzamčení

Toto je krátký souhrn chování, pokud není povolené optimalizované uzamčení. Pro více informací si přečtěte průvodce o uzamčení transakcí a o správě verzí řádků.

V databázovém systému je zamykání mechanismus, který brání více transakcím v aktualizaci stejných dat zároveň, aby zajistil ACID vlastnosti transakcí.

Když transakce potřebuje upravit data, požádá o zámek dat. Zámek je udělen, pokud nejsou v datech uloženy žádné jiné konfliktní zámky a transakce může pokračovat v úpravě. Pokud se na datech uchovává jiný konfliktní zámek, transakce musí počkat, až se zámek uvolní, než bude možné pokračovat.

Když se více transakcí pokusí získat přístup ke stejným datům současně, databázový stroj musí vyřešit potenciálně složité konflikty se souběžnými čteními a zápisy. Uzamčení je jedním z mechanismů, kterými může modul poskytovat sémantiku pro transakci ANSI SQL úrovně izolace. I když je zamykání v databázích nezbytné, snížená souběžnost, zablokování, složitost a režijní náklady na uzamčení mohou ovlivnit výkon a škálovatelnost.

Optimalizované uzamčení a uzamčení ID transakce (TID)

Při správy verzí řádků úrovně izolace založené na základech se používají nebo když je povolená možnost ADR, každý řádek v databázi interně obsahuje ID transakce (TID). Tento TID je trvalý na disku. Každá transakce, která upravuje řádek, opatří tento řádek svým TID.

V zamykání pomocí TID se místo zamčení klíče řádku přebírá zámek na TID řádku. Modifikující transakce má X zámek na svém TID. Ostatní transakce získávají zámek S na TID, aby vyčkaly na dokončení první transakce. Při uzamčení TID se zámky pro stránky a řádky používají nadále při úpravách, ale každý zámek pro stránku a řádek se uvolní, jakmile je daný řádek upraven. Jediným zámkem drženým až do konce transakce je jeden zámek X na prostředku TID, který nahrazuje více zámků na stránkách a řádcích (klíče).

Podívejte se na následující příklad, který ukazuje zámky pro aktuální sezení, zatímco je transakce zápisu aktivní:

/* 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;

Pokud je povolené optimalizované uzamčení, požadavek obsahuje pouze jeden zámek X na prostředku XACT (transakce).

Pokud optimalizované uzamčení není povolené, obsahuje stejný požadavek čtyři zámky – tři zámky kláves X na každém řádku a jeden zámek IX (výhradní záměr) na stránce obsahující řádky:

Dynamické zobrazení správy (DMV) sys.dm_tran_locks je užitečné pro zkoumání nebo řešení problémů s uzamčením, například sledování optimalizovaného uzamčení v praxi.

Optimalizované uzamčení a uzamčení po kvalifikaci (LAQ)

Optimalizace zamykání využívající infrastrukturu TID mění způsob, jakým příkazy DML, jako jsou INSERT, UPDATE, DELETEa MERGE, získávají zámky.

Bez optimalizovaného zamykání se predikáty dotazu kontrolují řádek po řádku ve skenu, přičemž nejprve je použit zámek řádku pro aktualizaci (U). Pokud je predikát splněn, před aktualizací řádku se převezme exkluzivní zámek řádku (X), který je držen až do konce transakce.

S optimalizovaným uzamčením a když je povolená úroveň izolace snímku READ COMMITTED (RCSI), predikáty se kontrolují na nejnovější potvrzené verzi řádku bez nutnosti provádět zámky. Pokud predikát nevyhovuje, dotaz se přesune na další řádek prohledávání. Pokud je predikát splněný, je uzamčen řádek X, aby se řádek aktualizoval. Zámek řádku X se uvolní hned po dokončení aktualizace řádku před koncem transakce.

Vzhledem k tomu, že se vyhodnocení predikátu provádí bez získání zámků, souběžné dotazy, které upravují různé řádky, navzájem neblokují.

Například:

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

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

sezení 1	Sezení 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;

Bez optimalizovaného uzamčení je relace 2 blokovaná, protože relace 1 drží zámek typu U na řádku, který relace 2 potřebuje aktualizovat. S optimalizovaným uzamčením ale relace 2 není blokovaná, protože U zámky nejsou přijaty, a protože ve nejnovější potvrzené verzi řádku 1 se sloupec a rovná 1, což nesplňuje podmínku relace 2.

Vzhledem k tomu, že u LAQ U zámků se neberou, může souběžná transakce po vyhodnocení predikátu upravit řádek. Pokud je predikát splněn a na řádku není žádná další aktivní transakce (bez X TID lock), řádek se upraví. Pokud je aktivní transakce, databázový stroj počká na dokončení a znovu vyhodnotí predikát v době úpravy, protože druhá transakce mohla upravit řádek. Pokud je predikát stále splněn, řádek se upraví.

Podívejte se na následující příklad, kdy se vyhodnocení predikátu automaticky opakuje, protože jiná transakce změnila řádek:

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

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

sezení 1	Sezení 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;

Změny chování dotazů při použití optimalizovaného uzamykání a RCSI

Souběžné úlohy v rámci izolace čtení potvrzeného snímku (RCSI), které spoléhají na přísné pořadí provádění transakcí, mohou při povolení optimalizovaného uzamčení zaznamenat rozdíly v chování dotazů.

Představte si následující příklad, kdy transakce T2 aktualizuje tabulku t4 na základě sloupce b, který byl aktualizován během transakce T1.

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

INSERT INTO t4
VALUES (1,1);
GO

Sezení 1	Sezení 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;

Vyhodnoťme výsledek předchozího scénáře se zámkem po kvalifikaci i bez něj (LAQ).

bez LAQ

Bez LAQ je příkaz UPDATE v transakci T2 blokován a čeká na dokončení transakce T1. Po dokončení T1 aktualizuje T2 sloupec nastavení řádku b na 3, protože jeho predikát je splněn.

Po potvrzení obou transakcí obsahuje tabulka t4 následující řádky:

 a | b
 1 | 3

s LAQ

U LAQ transakce T2 používá nejnovější potvrzenou verzi řádku, kde se sloupec b rovná 1, k vyhodnocení jeho predikátu (b = 2). Řádek nevyhovuje kritériím; proto se přeskočí a příkaz se dokončí bez blokování transakcí T1. V tomto příkladu LAQ odebere blokování, ale vede k různým výsledkům.

Po potvrzení obou transakcí obsahuje tabulka t4 následující řádky:

 a | b
 1 | 2

Důležitý

I bez LAQ by aplikace neměly předpokládat, že databázový stroj zaručuje přísné řazení bez použití tipů pro uzamčení při použití úrovní izolace založené na verzích řádků. Naším obecným doporučením pro zákazníky, kteří používají souběžné úlohy v rámci RCSI, které spoléhají na striktní pořadí provádění transakcí (jak je znázorněno v předchozím příkladu), je používat přísnější úrovně izolace, jako jsou REPEATABLE READ a SERIALIZABLE.

Diagnostické doplňky pro optimalizované uzamčení

Následující vylepšení vám pomohou monitorovat a řešit problémy s blokováním a záběry, pokud je povoleno optimalizované uzamčení.

• Typy čekání pro optimalizované uzamčení
  • XACT typy čekání na uzamčení S na TID a popisy prostředků v sys.dm_os_wait_stats (Transact-SQL):
    • LCK_M_S_XACT_READ – nastane, když úloha čeká na sdílený zámek u typu XACTwait_resource se záměrem čtení.
    • LCK_M_S_XACT_MODIFY – nastane, když úloha čeká na sdílený zámek typu XACTwait_resource se záměrem upravit.
    • LCK_M_S_XACT – nastane, když úloha čeká na sdílený zámek u typu XACTwait_resource, kde záměr nelze odvodit. Tento scénář není běžný.
• Zamknutí viditelnosti zdrojů
  • XACT uzamčení prostředků. Další informace naleznete v resource_description v rámci sys.dm_tran_locks (Transact-SQL).
• Čekání na viditelnost prostředků
  • XACT čekačka na prostředky. Viz wait_resource v sys.dm_exec_requests (Transact-SQL)pro více informací.
• Graf zablokování
  • Pod každým prostředkem v sestavě zablokování <resource-list>, každý prvek <xactlock> oznamuje podkladové prostředky a konkrétní informace o zámcích každého účastníka zablokování. Další informace a příklad najdete v tématu Optimalizované uzamčení a zablokování.

Osvědčené postupy s optimalizovaným uzamykáním

Povolení izolace potvrzených snímků pro čtení (RCSI)

Pokud chcete maximalizovat výhody optimalizovaného uzamčení, doporučujeme povolit izolaci potvrzených snímků pro čtení (RCSI) v databázi a použít izolaci READ COMMITTED jako výchozí úroveň izolace. Pokud ještě není povoleno, povolte RCSI připojením k databázi master a spuštěním následujícího příkazu:

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

Ve službě Azure SQL Database je RCSI ve výchozím nastavení povoleno a READ COMMITTED je výchozí úroveň izolace. Když je povoleno RCSI a při použití úrovně izolace READ COMMITTED, procesy čtení čtou verzi řádku z databázového snímku pořízeného na začátku příkazu. V případě LAQ autoři posuzují řádky podle predikátu na základě nejnovější potvrzené verze řádku a bez potřeby získání zámků U. Když používáte LAQ, dotaz počká jenom v případě, že řádek splňuje podmínky a na řádku je aktivní transakce zápisu. Kvalifikace na základě nejnovější potvrzené verze a uzamčení pouze kvalifikovaných řádků snižuje blokování a zvyšuje souběžnost.

Kromě omezeného blokování se požadovaná paměť zámku sníží. Je to proto, že čtenáři nepoužívají žádné zámky a autoři používají pouze krátkodobé zámky, místo zámků, které se drží až do konce transakce. Pokud používáte přísnější úrovně izolace, jako je REPEATABLE READ nebo SERIALIZABLE, databázový stroj uchovává zámky řádků a stránek až do konce transakce, a to i při aktivovaném optimalizovaném uzamčení pro čtenáře i zapisovače, což vede ke zvýšenému blokování a využití paměti pro zámky.

Vyhněte se záměrům na zamykání

I když rady pro tabulky a dotazy, jako jsou UPDLOCK, READCOMMITTEDLOCK, XLOCK, HOLDLOCKatd., jsou při povoleném optimalizovaném uzamčení dodrženy, snižují výhodu optimalizovaného uzamčení. Tzv. lock hinty vynucují, aby databázový engine použil zámky řádků nebo stránek a držel je až do konce transakce, aby respektoval jejich záměr. Některé aplikace mají logiku, kde jsou potřeba zámkové náznaky, například při čtení řádku s náznakem UPDLOCK a jeho následné aktualizaci. Doporučujeme používat pouze nápovědy k uzamčení, jen pokud je to potřeba.

S optimalizovaným uzamčením nejsou žádná omezení stávajících dotazů a dotazy není třeba přepisovat. Dotazy, které nepoužívají nápovědy, nejvíce těží z optimalizovaného uzamykání.

Nápověda k tabulce v jedné tabulce v dotazu nezakáže optimalizované uzamčení pro ostatní tabulky ve stejném dotazu. Optimalizované uzamčení dále ovlivňuje pouze chování uzamčení tabulek, které jsou aktualizovány příkazem DML, jako jsou INSERT, UPDATE, DELETEnebo MERGE. Například:

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;

V předchozím příkladu dotazu se týká zamykání pouze tabulky t6, zatímco tabulka t5 může stále využívat optimalizované zamykání.

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

V předchozím příkladu dotazu používá pouze tabulka t5 úroveň izolace REPEATABLE READ a blokování zámků až do konce transakce. Další aktualizace t5 stále mohou těžit z optimalizovaného uzamčení. Totéž platí pro nápovědu HOLDLOCK.

Nejčastější dotazy

Je ve výchozím nastavení optimalizované uzamčení v nových i existujících databázích?

Ve službě Azure SQL Database ano.

Jak zjistím, jestli je povolené optimalizované uzamčení?

Viz Je optimalizované uzamčení povoleno?.

Co se stane, když v databázi není povolené zrychlené obnovení databáze (ADR)?

Pokud je služba ADR zakázaná, optimalizované uzamčení je také automaticky zakázáno.

Co když chci vynutit blokování dotazů i přes optimalizované uzamčení?

Pro zákazníky, kteří používají RCSI, použijte dotazový tip READCOMMITTEDLOCK, abyste vynutili blokování mezi dvěma dotazy, když je povoleno optimalizované uzamčení.

Používá se optimalizované uzamčení sekundárních replik jen pro čtení?

Ne, protože příkazy DML nejde spustit na replikách jen pro čtení a odpovídající zámky řádků a stránek se nepřebíjejí.

Používá se optimalizované uzamčení při úpravě dat v databázi tempdb a v dočasných tabulkách?

V tuto chvíli ne.

Související obsah

• Průvodce uzamčením transakcí a verzováním řádků
• Izolace pomocí potvrzených snímků pro čtení (RCSI)
• sys.dm_tran_locks (Transact-SQL)
• akcelerované obnovení databáze