Analýza a zabránění zablokování ve službě Azure SQL Database a databázi SQL Fabric

Platí pro: Azure SQL Database SQL Databasev prostředcích infrastruktury

V tomto článku se dozvíte, jak identifikovat vzájemné zablokování, pomocí grafů vzájemného zablokování a úložiště dotazů identifikovat dotazy ve vzájemném zablokování a plánovat a testovat změny, aby se zabránilo opakovanému zablokování. Tento článek se týká databáze Azure SQL Database a Infrastruktury SQL, která sdílí mnoho funkcí služby Azure SQL Database.

Tento článek se zaměřuje na identifikaci a analýzu zablokování kvůli kolizí zámků. Přečtěte si další informace o dalších typech zablokování v prostředcích, které můžou vzájemné zablokování.

Jak dochází k zablokování

Každá nová databáze ve službě Azure SQL Database má ve výchozím nastavení povolené nastavení potvrzené izolace snímků (RCSI). Blokování mezi relacemi při čtení dat a zápisu relací se minimalizuje v rámci RCSI, které ke zvýšení souběžnosti využívá správu verzí řádků. Blokování a vzájemné zablokování mohou v databázích ve službě Azure SQL Database nastat i nadále, protože:

• Dotazy, které upravují data, by mohly vzájemně blokovat.

• Dotazy můžou běžet na úrovních izolace, které zvyšují blokování. Úrovně izolace je možné zadat pomocí metod klientské knihovny, nápovědy k dotazůmnebo SET TRANSACTION ISOLATION LEVEL v transact-SQL.

• RCSI může být zakázána, což způsobí, že databáze používá sdílené zámky (S) k ochraně příkazů SELECT spuštěných pod úrovní potvrzené izolace čtení. To by mohlo zvýšit blokování a uvíznutí.

Příklad vzájemného zablokování

Zablokování nastane, když dva nebo více úkolů trvale zablokuje jeden z nich, protože každý úkol má zámek na zdroji, který se druhý úkol pokouší uzamknout. Vzájemné zablokování se také nazývá cyklická závislost: v případě vzájemného vzájemného zablokování transakce A má transakce A závislost na transakci B a transakce B zavře kruh tím, že má závislost na transakci A.

Příklad:

1. Relace A zahájí explicitní transakci a spustí příkaz aktualizace, který získá zámek aktualizace (U) na jednom řádku tabulky SalesLT.Product , která je převedena na výhradní zámek (X).

2. Relace B spustí příkaz aktualizace, který upraví SalesLT.ProductDescription tabulku. Příkaz update se spojí s SalesLT.Product tabulkou a najde správné řádky, které se mají aktualizovat.

   • Relace B získá zámek aktualizace (U) na 72 řádcích SalesLT.ProductDescription v tabulce.

   • Relace B potřebuje sdílený zámek na řádcích v tabulce SalesLT.Product, včetně řádku, který je uzamčen relací A. Relace B je zablokovaná SalesLT.Product.

3. Relace A pokračuje ve své transakci a nyní spustí aktualizaci tabulky SalesLT.ProductDescription . Relace A je blokována relací B dne SalesLT.ProductDescription.

Všechny transakce ve vzájemném zablokování čekají na neomezenou dobu, pokud není například jedna ze zúčastněných transakcí zrušena, třeba proto, že její relace byla ukončena.

Monitorování vzájemného zablokování databázového stroje pravidelně ověřuje úlohy, u kterých dochází ke vzájemném blokování. Pokud monitorování vzájemného zablokování zjistí cyklickou závislost, zvolí jeden z úkolů jako oběť a ukončí svou transakci s chybou 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and is chosen as the deadlock victim. Rerun the transaction. Přerušení vzájemné zablokování tímto způsobem umožňuje ostatním úkolům nebo úkolům ve vzájemném zablokování dokončit své transakce.

Poznámka:

Další informace o kritériích pro volbu oběti vzájemného zablokování najdete v části se seznamem procesů vzájemného zablokování v tomto článku.

Aplikace s transakcí zvolenou jako oběť vzájemného zablokování by měla opakovat transakci, která se obvykle dokončí po dokončení druhé transakce nebo transakce zapojené do zablokování.

Osvědčeným postupem je zavést krátké náhodně vygenerované zpoždění před dalším pokusem, abyste se vyhnuli opětovnému vzájemnému zablokování. Přečtěte si další informace o tom, jak navrhnout logiku opakování pro přechodné chyby.

Výchozí úroveň izolace ve službě Azure SQL Database

Nové databáze ve službě Azure SQL Database ve výchozím nastavení umožňují čtení potvrzených snímků (RCSI). RCSI změní chování potvrzené úrovně izolace čtení tak, aby používala verzování řádků k zajištění konzistence na úrovni příkazů bez použití sdílených (S) zámků pro SELECT.

S povolenou konfigurací RCSI:

• Příkazy, které čtou data, neblokují příkazy, které upravují data.
• Příkazy, které upravují data, neblokují příkazy čtení dat.

Úroveň izolace snímků je ve výchozím nastavení povolená také pro nové databáze ve službě Azure SQL Database. Izolace snímku je další úroveň izolace založená na řádcích, která poskytuje konzistenci na úrovni transakcí pro data a která používá verze řádků k výběru řádků k aktualizaci řádků. Chcete-li použít izolaci snímků, dotazy nebo připojení musí explicitně nastavit jejich úroveň izolace transakce na SNAPSHOT. To lze provést pouze v případě, že je pro databázi povolená izolace snímků.

Pomocí jazyka Transact-SQL můžete zjistit, jestli je povolená izolace RCSI nebo snímku. Připojte se k databázi ve službě Azure SQL Database a spusťte následující dotaz:

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

Pokud je povolená analýza RCSI, vrátí sloupec is_read_committed_snapshot_on hodnotu 1. Pokud je povolena izolace snímku, vrátí sloupec snapshot_isolation_state_desc hodnotu ON.

Pokud je RCSI zakázáno pro databázi ve službě Azure SQL Database, prozkoumejte, proč bylo RCSI zakázáno, před jeho opětovným povolením. Kód aplikace může očekávat, že dotazy, které čtou data, budou blokovány dotazy zápisu dat, což vede k nesprávným výsledkům ze závodních podmínek, když je RCSI povoleno.

Interpretace událostí vzájemného zablokování

Událost vzájemného zablokování se vygeneruje poté, co správce vzájemného zablokování ve službě Azure SQL Database zjistí vzájemné zablokování a vybere transakci jako oběť. Jinými slovy, pokud nastavíte upozornění na zablokování, oznámení se aktivuje po vyřešení jednotlivého zablokování. Pro toto zablokování není potřeba provést žádnou akci uživatele. Aplikace by měly být zapsány tak, aby zahrnovaly logiku opakování , aby automaticky pokračovaly po zobrazení chyby 1205: Transaction (Process ID <N>) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Je ale užitečné nastavit upozornění, protože zablokování se může opakovat. Upozornění na vzájemné zablokování vám umožňují zjistit, zda ve vaší databázi probíhá vzorec opakovaných zablokování, a v takovém případě byste se mohli rozhodnout zasáhnout a zabránit tak dalšímu vzájemnému zablokování. Další informace o upozorňování najdete v části Monitorování a upozornění na zablokování v tomto článku.

Hlavní metody, které brání zablokování

Nejnižším rizikovým přístupem, který brání opětovnému zablokování, je obecně ladění neclusterovaných indexů za účelem optimalizace dotazů zapojených do zablokování.

• Riziko je pro tento přístup nízké, protože ladění neclusterovaných indexů nevyžaduje změny samotného kódu dotazu, což snižuje riziko chyby uživatele při přepsání jazyka Transact-SQL, které způsobí vrácení nesprávných dat uživateli.

• Efektivní ladění neclusterovaného indexu pomáhá dotazům najít data, která se mají číst a efektivněji upravovat. Snížením množství dat, ke kterým dotaz potřebuje přístup, může být často zabráněno pravděpodobnosti blokování a zablokování může být často zabráněno.

V některých případech může vytvoření nebo ladění clusterovaného indexu snížit blokování a zablokování. Vzhledem k tomu, že clusterovaný index je součástí všech neclusterovaných definic indexů, může být vytvoření nebo úprava clusterovaného indexu náročná na vstupně-výstupní operace a časově náročná operace u větších tabulek s existujícími neclusterovanými indexy. Přečtěte si další informace o pokynech pro návrh clusterovaného indexu.

Pokud ladění indexu není úspěšné, aby se zabránilo zablokování, jsou k dispozici další metody:

• Pokud k vzájemnému zablokování dojde pouze v případě, že je pro některý z dotazů zapojených do zablokování vybrán konkrétní plán, vynucení plánu dotazu s úložištěm dotazů může zabránit opakovanému zablokování.

• Přepsání transact-SQL pro jednu nebo více transakcí zapojených do zablokování může také pomoct zabránit zablokování. Rozdělení explicitních transakcí do menších transakcí vyžaduje pečlivé kódování a testování, aby se zajistila platnost dat při souběžných úpravách.

Další informace o každém z těchto přístupů najdete v části Zabránění vzájemnému zablokování v části tohoto článku.

Monitorování a upozorňování na zablokování

V tomto článku používáme ukázkovou databázi AdventureWorksLT k nastavení výstrah pro zablokování, vyvolání příkladového zablokování, analýze grafu zablokování tohoto příkladu a testování změn, které zabrání opětovnému výskytu zablokování.

V tomto článku používáme klienta SQL Server Management Studio (SSMS), protože obsahuje funkci pro zobrazení grafů zablokování v interaktivním vizuálním režimu. Pomocí jiných klientů, jako je Azure Data Studio, můžete postupovat podle příkladů, ale grafy zablokování můžete zobrazit jenom jako XML.

Vytvoření databáze AdventureWorksLT

Pokud chcete postupovat podle příkladů, vytvořte novou databázi ve službě Azure SQL Database a jako zdroj dat vyberte Ukázková data.

Podrobné pokyny k vytvoření AdventureWorksLT pomocí webu Azure Portal, Azure CLI nebo PowerShellu získáte tak , že v rychlém startu vyberete přístup: Vytvoření izolované databáze Azure SQL Database.

Nastavení upozornění na vzájemné zablokování na webu Azure Portal

Pokud chcete nastavit upozornění na události zablokování, postupujte podle pokynů v článku Vytvoření upozornění pro Azure SQL Database a Azure Synapse Analytics pomocí webu Azure Portal.

Jako název signálu pro výstrahu vyberte vzájemné zablokování . Nakonfigurujte skupinu akcí tak, aby vás upozorňovat pomocí zvolené metody, jako je například typ akce E-mail/SMS/Nabízení/Hlas.

Shromažďování grafů vzájemného zablokování ve službě Azure SQL Database s rozšířenými událostmi

Grafy vzájemného zablokování jsou bohatým zdrojem informací týkajících se procesů a zámků zapojených do zablokování. Pokud chcete shromažďovat grafy vzájemného zablokování s rozšířenými událostmi (XEvents) ve službě Azure SQL Database, zachyťte sqlserver.database_xml_deadlock_report událost.

Grafy vzájemného zablokování s XEvents můžete shromažďovat pomocí cíle vyrovnávací paměti okruhu nebo cíle souboru události. Důležité informace o výběru vhodného cílového typu jsou shrnuté v následující tabulce:

Přístup	Zaměstnanecké výhody	Důležité informace	Scénáře použití
Cíl vyrovnávací paměti okruhu	- Jednoduché nastavení pouze s Transact-SQL.	– Data událostí se smažou, když se relace XEvents z jakéhokoli důvodu zastaví, například pokud je databáze přepnuta do offline režimu nebo dojde k automatickému převedení při selhání databáze. – Databázové prostředky se používají k údržbě dat ve kruhové vyrovnávací paměti a k dotazování dat relace.	– Shromážděte ukázková data trasování pro testování a učení. – Pro krátkodobé potřeby vytvořte, pokud nemůžete okamžitě nastavit relaci s použitím cílového souboru událostí. – Použijte jako přistávací podložku pro data trasování, když nastavíte automatizovaný proces pro uložení trasovacích dat do tabulky.
Cíl souboru události	– Ukládá data událostí do objektu blob ve službě Azure Storage, takže data jsou k dispozici i po zastavení relace. – Soubory událostí je možné stáhnout z webu Azure Portal nebo Průzkumníka služby Azure Storage a analyzovat místně, což nevyžaduje dotazování dat relací pomocí databázových prostředků.	– Nastavení je složitější a vyžaduje konfiguraci kontejneru Azure Storage a přihlašovacích údajů s oborem pro databázi.	– Obecné použití, pokud chcete, aby data událostí byla zachována i po ukončení relace události. – Chcete spustit trasování, které generuje větší objem dat událostí, než byste chtěli uchovávat v paměti.

Vyberte typ cíle, který chcete použít:

Cíl vyrovnávací paměti okruhu

Cíl vyrovnávací paměti okruhu je pohodlný a snadno nastavený, ale má omezenou kapacitu, což může způsobit ztrátu starších událostí. Vyrovnávací paměť okruhu neukládá události trvale do úložiště a cíl vyrovnávací paměti okruhu se vymaže, když je zastavena relace XEvents. To znamená, že všechny shromážděné události XEvent nejsou k dispozici, když se databázový stroj z jakéhokoli důvodu restartuje, například převzetí služeb při selhání. Cíl kruhového bufferu je nejvhodnější pro učení a krátkodobé potřeby, pokud nemáte okamžitě možnost nastavit relaci XEvents k cílovému souboru událostí.

Tento ukázkový kód vytvoří relaci XEvents, která zachycuje grafy zablokování v paměti pomocí cíle vyrovnávací paměti okruhu. Maximální povolená paměť pro cíl kruhové vyrovnávací paměti je 4 MB a relace se automaticky spustí, když se databáze připojí online, například po převzetí služeb při selhání.

Pokud chcete vytvořit a spustit relaci XEvents pro sqlserver.database_xml_deadlock_report událost, která zapisuje do cíle vyrovnávací paměti okruhu, připojte se k databázi a spusťte následující transact-SQL:

CREATE EVENT SESSION [deadlocks] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.ring_buffer
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = START;
GO

Cíl souboru události

Cíl souboru události zachovává grafy zablokování u souborů, aby byly dostupné i po zastavení relace XEvents. Cíl souboru událostí také umožňuje zachytit více grafů zablokování bez přidělení další paměti pro vyrovnávací paměť okruhu. Cíl souboru událostí je vhodný pro dlouhodobé použití a pro shromažďování větších objemů dat trasování.

Chcete-li vytvořit relaci XEvents, která zapisuje do cíle souboru události, postupujte následovně:

1. Nakonfigurujte kontejner Azure Storage pro uložení trasovacích souborů pomocí webu Azure Portal.
2. Vytvořte přihlašovací údaje s vymezeným oborem databáze pomocí jazyka Transact-SQL.
3. Vytvořte relaci XEvents pomocí jazyka Transact-SQL.

Konfigurace kontejneru Azure Storage

Pokud chcete nakonfigurovat kontejner Azure Storage, nejprve vytvořte nebo vyberte existující účet Azure Storage a pak kontejner vytvořte. Vygenerujte token sdíleného přístupového podpisu (SAS) pro kontejner. Tato část popisuje dokončení tohoto procesu na webu Azure Portal.

Poznámka:

Pokud chcete vytvořit a nakonfigurovat kontejner objektů blob služby Azure Storage pomocí PowerShellu, podívejte se na cílový kód souboru událostí pro rozšířené události ve službě Azure SQL Database. Alternativně může být vhodné použít Průzkumníka služby Azure Storage k vytvoření a konfiguraci kontejneru objektů blob služby Azure Storage místo webu Azure Portal.

Vytvoření nebo výběr účtu Azure Storage

K hostování kontejneru pro trasovací soubory můžete použít existující účet Azure Storage nebo vytvořit nový účet Azure Storage.

Použití existujícího účtu Azure Storage:

1. Na webu Azure Portal přejděte do skupiny prostředků, se kterou chcete pracovat.
2. V podokně Přehled v části Prostředkynastavte rozevírací seznam Typ na účet úložiště.
3. Vyberte účet úložiště, který chcete použít.

Pokud chcete vytvořit nový účet Azure Storage, postupujte podle kroků v části Vytvoření účtu úložiště Azure. Dokončete proces tak , že v posledním kroku vyberete Přejít k prostředku .

Vytvoření kontejneru

Na stránce účtu úložiště na webu Azure Portal:

1. V části Úložiště dat vyberte Kontejnery.
2. Vyberte + Kontejner a vytvořte nový kontejner. Zobrazí se podokno Nový kontejner.
3. Do pole Název zadejte název kontejneru.
4. Vyberte Vytvořit.
5. Po vytvoření vyberte kontejner ze seznamu.

Vytvoření sdíleného přístupového tokenu

Na stránce kontejneru na webu Azure Portal:

1. V části Nastavení vyberte Sdílené přístupové tokeny.

2. Nechte přepínač Podpisová metoda nastavený na výchozí výběr, Klíč účtu.

3. V rozevíracím seznamu oprávnění vyberte oprávnění čtení, zápisua seznamu.

4. Nastavte Začátek na datum a čas, který chcete mít možnost zapisovat trasovací soubory. Volitelně můžete nakonfigurovat časové pásmo v rozevíracím seznamu níže Spustit.

5. Nastavte vypršení platnosti na datum a čas, kdy chcete, aby platnost těchto oprávnění vypršela. Volitelně můžete nakonfigurovat časové pásmo v rozevíracím seznamu níže vypršení platnosti. Pokud chcete, můžete to nastavit na datum daleko v budoucnosti, například 10 let.

6. Vyberte Vygenerovat token a URL SAS. Na obrazovce se zobrazí token SAS objektu blob a adresa URL SAS objektu blob.

7. Zkopírujte a zachovejte token SAS objektu blob a hodnoty adresy URL SAS objektu blob pro použití v dalšíchkrocích

Vytvoření přihlašovacích údajů s vymezeným oborem databáze

Připojte se ke své databázi ve službě Azure SQL Database pomocí aplikace SSMS a spusťte následující kroky.

Pokud chcete vytvořit přihlašovací údaje s vymezeným oborem databáze, musíte nejprve v databázi vytvořit hlavní klíč databáze (DMK), pokud neexistuje.

Pokud DMK neexistuje, spusťte následující Transact-SQL k jeho vytvoření.

IF 0 = (SELECT COUNT(*)
        FROM sys.symmetric_keys
        WHERE symmetric_key_id = 101
              AND name = N'##MS_DatabaseMasterKey##')
    BEGIN
        PRINT N'Creating master key';
        CREATE MASTER KEY;
    END
ELSE
    BEGIN
        PRINT N'Master key already exists, no action taken';
    END
GO

Dále vytvořte přihlašovací údaje s vymezeným oborem databáze s následujícím jazykem Transact-SQL. Před spuštěním kódu:

• Upravte adresu URL tak, aby odrážela název vašeho účtu úložiště a název kontejneru. Tato adresa URL se nachází na začátku adresy SAS URL objektu blob , kterou jste zkopírovali při vytváření sdíleného přístupového tokenu. Text potřebujete jenom před prvním ? v řetězci.

• SECRET Upravte hodnotu tokenu SAS objektu blob, kterou jste zkopírovali při vytváření sdíleného přístupového tokenu.

CREATE DATABASE SCOPED CREDENTIAL [https://yourstorageaccountname.blob.core.windows.net/yourcontainername]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = 'sp=r&st=2022-04-08T14:34:21Z&se=2032-04-08T22:34:21Z&sv=2020-08-04&sr=c&sig=<sig>';
GO

Vytvoření relace XEvents

Vytvořte a spusťte relaci XEvents s následujícím jazykem Transact-SQL. Před spuštěním příkazu:

• Nahraďte hodnotu tak filename , aby odrážela název vašeho účtu úložiště a název kontejneru. Tato adresa URL se nachází na začátku adresy SAS URL objektu blob, kterou jste zkopírovali při vytváření přístupového tokenu. Text potřebujete jenom před prvním ? v řetězci.

• Volitelně můžete změnit uložený název souboru. Zadaný název souboru bude součástí skutečných názvů souborů používaných pro objekty blob, které ukládají data události: připojují se další hodnoty, aby všechny soubory událostí měly jedinečný název.

• Volitelně můžete do relace přidat další události.

CREATE EVENT SESSION [deadlocks_eventfile] ON DATABASE
ADD EVENT sqlserver.database_xml_deadlock_report
ADD TARGET package0.event_file
(
    SET filename = 'https://yourstorageaccountname.blob.core.windows.net/yourcontainername/deadlocks.xel'
)
WITH
(
    STARTUP_STATE = ON,
    MAX_MEMORY = 4 MB
);
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = START;
GO

Příčina zablokování v AdventureWorksLT

Poznámka:

Tento příklad pracuje v databázi AdventureWorksLT s výchozím schématem a daty, když je povolen RCSI. Pokyny k vytvoření databáze najdete v tématu Vytvoření databáze AdventureWorksLT.

Pokud chcete způsobit zablokování, musíte k databázi AdventureWorksLT připojit dvě relace. Tyto sezení se označují jako Sezení A a Sezení B.

V relaci A spusťte následující transact-SQL. Tento kód zahájí explicitní transakci a spustí jeden příkaz, který aktualizuje SalesLT.Product tabulku. Za tímto účelem transakce získá zámek aktualizace (U) na jednom řádku tabulky SalesLT.Product , který je převeden na výhradní zámek (X). Transakci ponecháme otevřenou.

BEGIN TRANSACTION;

UPDATE SalesLT.Product
SET SellEndDate = SellEndDate + 1
WHERE Color = 'Red';

Teď v relaci B spusťte následující transact-SQL. Tento kód explicitně nezačne transakci. Místo toho funguje v režimu automatické transakce. Tento příkaz aktualizuje SalesLT.ProductDescription tabulku. Aktualizace vyřadí zámek aktualizace (U) na 72 řádcích v SalesLT.ProductDescription tabulce. Dotaz se spojí s jinými tabulkami, včetně SalesLT.Product tabulky.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Silver';

K dokončení této aktualizace Relace B vyžaduje sdílený zámek (S) na řádcích tabulky SalesLT.Product, včetně řádku, který uzamyká Relace A. Relace B je blokována na SalesLT.Product.

Vraťte se do relace A. Spusťte následující příkaz jazyka Transact-SQL. Tím se spustí druhý příkaz UPDATE jako součást otevřené transakce.

UPDATE SalesLT.ProductDescription
    SET Description = Description
FROM SalesLT.ProductDescription AS pd
     INNER JOIN SalesLT.ProductModelProductDescription AS pmpd
         ON pd.ProductDescriptionID = pmpd.ProductDescriptionID
     INNER JOIN SalesLT.ProductModel AS pm
         ON pmpd.ProductModelID = pm.ProductModelID
     INNER JOIN SalesLT.Product AS p
         ON pm.ProductModelID = p.ProductModelID
WHERE p.Color = 'Red';

Druhý příkaz aktualizace v Relace A je blokován Relace B na SalesLT.ProductDescription.

Relace A a Relace B se teď vzájemně blokují. Žádná transakce nemůže pokračovat, protože každý potřebuje prostředek, který je uzamčen jiným.

Po několika sekundách monitor zablokování zjistí, že transakce v relaci Session A a relaci Session B se vzájemně blokují a že žádný z nich nemůže pokračovat. Měli byste vidět zablokování, u relace A zvolenou jako oběť vzájemného zablokování. V Relaci A se zobrazí chybová zpráva s textem podobným následujícímu textu:

Msg 1205, Level 13, State 51, Line 7
Transaction (Process ID 91) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Sezení B úspěšně dokončeno.

Pokud jste na webu Azure Portal nastavili upozornění na vzájemné zablokování, měli byste krátce po zablokování obdržet oznámení.

Zobrazení grafů vzájemného zablokování z relace XEvents

Pokud nastavíte relaci XEvents, která bude shromažďovat vzájemné zablokování a po spuštění relace dojde k zablokování, můžete zobrazit interaktivní grafické zobrazení grafu vzájemného zablokování a XML pro graf zablokování.

K dispozici jsou různé metody pro získání informací o vzájemném zablokování pro cíl vyrovnávací paměti okruhu a cíle souboru událostí. Vyberte cíl, který jste použili pro relaci XEvents:

Cíl vyrovnávací paměti okruhu

Pokud nastavíte relaci XEvents zápisu do vyrovnávací paměti okruhu, můžete dotazovat informace o vzájemném zablokování pomocí následujícího jazyka Transact-SQL. Před spuštěním dotazu nahraďte hodnotu @tracename názvem relace XEvents.

DECLARE @tracename AS sysname = N'deadlocks';

WITH ring_buffer
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'ring_buffer'),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM ring_buffer
CROSS APPLY rb.nodes('/RingBufferTarget/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Cíl souboru události

Pokud nastavíte relaci XEvents zápisu do souboru události, můžete si soubory stáhnout z webu Azure Portal a zobrazit je místně nebo můžete dotazovat soubory událostí pomocí jazyka Transact-SQL.

Stahování souborů z webu Azure Portal se doporučuje, protože tato metoda k dotazování dat relace nevyžaduje použití databázových prostředků.

Volitelně restartujte relaci XEvents.

Pokud je aktuálně spuštěná relace rozšířených událostí a zapisuje se do cíle souboru událostí, kontejner objektů blob, do kterého se zapisuje, bude mít na webu Azure Portal stavZapůjčení zapůjčení . Velikost je maximální velikost souboru. Pokud chcete stáhnout menší soubor, můžete před stažením souborů zastavit a restartovat relaci rozšířených událostí. Toto způsobí, že soubor změní svůj stav pronájmu na Dostupnýa velikost souboru bude odpovídat prostoru používanému událostmi v souboru.

Pokud chcete zastavit a restartovat relaci XEvents, připojte se k databázi a spusťte následující transact-SQL. Před spuštěním kódu nahraďte název relace XEvents odpovídající hodnotou.

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = STOP;
GO

ALTER EVENT SESSION [deadlocks_eventfile] ON DATABASE
STATE = START;
GO

Stažení trasovacích souborů z webu Azure Portal

Pokud chcete zobrazit události zablokování, které byly shromážděny napříč více soubory, stáhněte soubory relace událostí do místního počítače a zobrazte soubory v aplikaci SSMS.

Poznámka:

Pomocí Průzkumník služby Azure Storage můžete také rychle a pohodlně stahovat soubory relací událostí z kontejneru objektů blob ve službě Azure Storage.

Stažení souborů z webu Azure Portal:

1. Na webu Azure Portal přejděte k účtu úložiště, který je hostitelem vašeho kontejneru.
2. V části Úložiště dat vyberte Kontejnery.
3. Vyberte kontejner obsahující trasovací soubory XEvent.
4. Pro každý soubor, který chcete stáhnout, vyberte ... a pak Stáhnout.

Zobrazení trasovacích souborů XEvents v SSMS

Pokud jste stáhli více souborů, můžete otevřít události ze všech souborů společně v prohlížeči XEvents v nástroji SSMS. Postup:

1. Otevřete aplikaci SSMS.
2. Vyberte Soubor, pak Otevřít a potom Sloučit soubory rozšířených událostí....
3. Vyberte Přidat.
4. Přejděte do adresáře, do kterého jste stáhli soubory. Pomocí klávesy Shift vyberte více souborů.
5. Vyberte Otevřít.
6. V dialogovém okně Sloučit soubory rozšířených událostí vyberte OK.

Pokud jste stáhli jeden soubor, klikněte pravým tlačítkem myši na soubor a vyberte Otevřít s a pak SSMS. Tím se otevře prohlížeč XEvents v nástroji SSMS.

Procházejte mezi událostmi shromážděnými výběrem příslušného časového razítka. Pokud chcete zobrazit XML pro zablokování, poklikejte na xml_report řádek v dolním podokně.

Dotazování trasovacích souborů pomocí jazyka Transact-SQL

Důležité

Dotazování velkých (1 GB a větších) trasovacích souborů XEvents pomocí této metody se nedoporučuje, protože spotřebovává velké množství paměti v databázi nebo elastickém fondu.

Pokud chcete dotazovat trasovací soubory XEvents z kontejneru Azure Storage pomocí jazyka Transact-SQL, musíte zadat přesný název souboru trasování. Pokud chcete získat přístup k úložišti, musíte také spustit dotaz v kontextu databáze s přihlašovacími údaji, jinými slovy, stejnou databázi, která vytvořila soubory XEvents.

Spuštěním následujícího příkazu Transact-SQL zadejte dotaz na aktuálně aktivní trasovací soubor XEvents. Před spuštěním dotazu nahraďte @tracename názvem relace XEvents.

DECLARE @tracename AS sysname = N'deadlocks_eventfile', @filename AS NVARCHAR (2000);

WITH eft
AS (SELECT CAST (target_data AS XML) AS rb
    FROM sys.dm_xe_database_sessions AS s
         INNER JOIN sys.dm_xe_database_session_targets AS t
             ON CAST (t.event_session_address AS BINARY (8)) = CAST (s.address AS BINARY (8))
    WHERE s.name = @tracename
          AND t.target_name = N'event_file')
SELECT @filename = ft.evtdata.value('(@name)[1]', 'nvarchar(2000)')
FROM eft
CROSS APPLY rb.nodes('EventFileTarget/File') AS ft(evtdata);

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Pokud chcete dotazovat soubory, které nejsou aktivní, přejděte na webu Azure Portal do účtu úložiště a kontejneru a identifikujte názvy souborů.

Spuštěním následujícího dotazu Jazyka Transact-SQL na databázi zadejte dotaz na konkrétní soubor XEvents. Před spuštěním dotazu nahraďte název účtu úložiště, název kontejneru a název souboru v adrese URL pro @filename:

DECLARE @filename AS NVARCHAR (2000) = N'https://<storage-account>.blob.core.windows.net/yourcontainername/yourfilename.xel';

WITH xevents
AS (SELECT CAST (event_data AS XML) AS ed
    FROM sys.fn_xe_file_target_read_file(@filename, NULL, NULL, NULL)),
 dx
AS (SELECT dxdr.evtdata.query('.') AS deadlock_xml_deadlock_report
    FROM xevents
CROSS APPLY ed.nodes('/event[@name=''database_xml_deadlock_report'']') AS dxdr(evtdata))
SELECT d.query('/event/data[@name=''deadlock_cycle_id'']/value').value('(/value)[1]', 'int') AS [deadlock_cycle_id],
       d.value('(/event/@timestamp)[1]', 'DateTime2') AS [deadlock_timestamp],
       d.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(256)') AS [database_name],
       d.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,
       LTRIM(RTRIM(REPLACE(REPLACE(d.value('.', 'nvarchar(2000)'), CHAR(10), ' '), CHAR(13), ' '))) AS query_text
FROM dx
CROSS APPLY deadlock_xml_deadlock_report.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS ib(d)
ORDER BY [deadlock_timestamp] DESC;
GO

Zobrazení a uložení grafu vzájemného zablokování v JAZYCE XML

Zobrazení grafu vzájemného zablokování ve formátu XML umožňuje zkopírovat inputbuffer příkazy Jazyka Transact-SQL zapojené do zablokování. Zablokování můžete analyzovat také v textovém formátu.

Pokud jste k vrácení informací grafu vzájemného zablokování použili dotaz Transact-SQL, vyberte hodnotu ve deadlock_xml sloupci z libovolného řádku a otevřete XML grafu vzájemného zablokování v novém okně V SSMS.

Xml pro tento příklad grafu vzájemného zablokování je:

<deadlock>
  <victim-list>
    <victimProcess id="process24756e75088" />
  </victim-list>
  <process-list>
    <process id="process24756e75088" taskpriority="0" logused="6528" waitresource="KEY: 8:72057594045202432 (98ec012aa510)" waittime="192" ownerId="1011123" transactionname="user_transaction" lasttranstarted="2022-03-08T15:44:43.490" XDES="0x2475c980428" lockMode="U" schedulerid="3" kpid="30192" status="suspended" spid="89" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:49.250" lastbatchcompleted="2022-03-08T15:44:49.210" lastattention="1900-01-01T00:00:00.210" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1011123" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671096864" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="792" sqlhandle="0x02000000c58b8f1e24e8f104a930776e21254b1771f92a520000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Red'   </inputbuf>
    </process>
    <process id="process2476d07d088" taskpriority="0" logused="11360" waitresource="KEY: 8:72057594045267968 (39e18040972e)" waittime="2641" ownerId="1013536" transactionname="UPDATE" lasttranstarted="2022-03-08T15:44:46.807" XDES="0x2475ca80428" lockMode="S" schedulerid="2" kpid="94040" status="suspended" spid="95" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2022-03-08T15:44:46.807" lastbatchcompleted="2022-03-08T15:44:46.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft SQL Server Management Studio - Query" hostname="LAPTOP-CHRISQ" hostpid="16716" loginname="chrisqpublic" isolationlevel="read committed (2)" xactid="1013536" currentdb="8" currentdbname="AdventureWorksLT" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame procname="unknown" queryhash="0xef52b103e8b9b8ca" queryplanhash="0x02b0f58d7730f798" line="1" stmtstart="2" stmtend="798" sqlhandle="0x020000002c85bb06327c0852c0be840fc1e30efce2b7c8090000000000000000000000000000000000000000">
unknown    </frame>
      </executionStack>
      <inputbuf>
    UPDATE SalesLT.ProductDescription SET Description = Description
        FROM SalesLT.ProductDescription as pd
        JOIN SalesLT.ProductModelProductDescription as pmpd on
            pd.ProductDescriptionID = pmpd.ProductDescriptionID
        JOIN SalesLT.ProductModel as pm on
            pmpd.ProductModelID = pm.ProductModelID
        JOIN SalesLT.Product as p on
            pm.ProductModelID=p.ProductModelID
        WHERE p.Color = 'Silver';   </inputbuf>
    </process>
  </process-list>
  <resource-list>
    <keylock hobtid="72057594045202432" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.ProductDescription" indexname="PK_ProductDescription_ProductDescriptionID" id="lock2474df12080" mode="U" associatedObjectId="72057594045202432">
      <owner-list>
        <owner id="process2476d07d088" mode="U" />
      </owner-list>
      <waiter-list>
        <waiter id="process24756e75088" mode="U" requestType="wait" />
      </waiter-list>
    </keylock>
    <keylock hobtid="72057594045267968" dbid="8" objectname="9e011567-2446-4213-9617-bad2624ccc30.SalesLT.Product" indexname="PK_Product_ProductID" id="lock2474b588580" mode="X" associatedObjectId="72057594045267968">
      <owner-list>
        <owner id="process24756e75088" mode="X" />
      </owner-list>
      <waiter-list>
        <waiter id="process2476d07d088" mode="S" requestType="wait" />
      </waiter-list>
    </keylock>
  </resource-list>
</deadlock>

Uložení grafu vzájemného zablokování jako souboru XML:

1. Vyberte Soubor a Uložit jako....
2. Ponechte hodnotu Uložit jako typ jako výchozí soubory XML (*.xml)
3. Nastavte název souboru na název podle vašeho výběru.
4. Zvolte Uložit.

Uložení grafu zablokování jako souboru XDL, který se dá v SSMS zobrazit interaktivně

Zobrazení interaktivní reprezentace grafu vzájemného zablokování může být užitečné k získání rychlého přehledu procesů a prostředků zapojených do zablokování a rychlé identifikaci oběti zablokování.

Uložení grafu zablokování jako souboru, který může SSMS graficky zobrazit:

1. Výběrem hodnoty ve sloupci z libovolného deadlock_xml řádku otevřete XML grafu zablokování v novém okně V SSMS.

2. Vyberte Soubor a Uložit jako....

3. Nastavte možnost Uložit jako typ na Všechny soubory.

4. Nastavte Název souboru na název podle vašeho výběru s příponou nastavenou na .xdl.

5. Zvolte Uložit.

   

6. Zavřete soubor tak , že vyberete X na kartě v horní části okna nebo vyberete Soubor a pak zavřete.

7. Otevřete soubor v aplikaci SSMS tak , že vyberete Soubor, pak Otevřete a pak Soubor. Vyberte soubor, který jste uložili s příponou .xdl .

   Graf vzájemného zablokování se teď zobrazuje v SSMS s vizuální reprezentací procesů a prostředků zapojených do vzájemného zablokování.

   

Analýza vzájemného zablokování pro Azure SQL Database

Graf vzájemného zablokování má obvykle tři uzly:

• Seznam obětí. Identifikátor procesu oběti ve vzájemném zablokování.

• Seznam procesů Informace o všech procesech, které se na zablokování podílejí. Grafy zablokování používají termín "proces" k reprezentaci relace, která spouští transakci.

• Seznam zdrojů Informace o prostředcích, které jsou součástí zablokování.

Při analýze problému se zablokováním je užitečné procházet tyto uzly.

Seznam obětí zablokování

Seznam obětí vzájemného zablokování zobrazuje proces, který byl vybrán jako oběť vzájemného zablokování. Ve vizuální reprezentaci grafu vzájemného zablokování jsou procesy reprezentovány ovály. Proces oběti zablokování má "X" nakreslený přes ovál.

V zobrazení XML grafuvictim-list vzájemného zablokování uzel poskytuje ID procesu, který byl obětí zablokování.

V našem příkladu zablokování je ID procesu, který je obětí, process24756e75088. Toto ID můžeme použít při zkoumání uzlů seznamu procesů a seznamu prostředků, abychom se dozvěděli více o procesu oběti a prostředcích, které uzamykáním nebo žádostí o uzamčení.

Seznam procesů zablokování

Seznam procesů vzájemného zablokování je bohatým zdrojem informací o transakcích zapojených do zablokování.

Grafické znázornění grafu vzájemného zablokování zobrazuje pouze podmnožinu informací obsažených v xml grafu vzájemného zablokování. Ovály v grafu vzájemného zablokování představují proces a zobrazují informace, včetně:

• ID relace, označované také jako SPID.

• Priorita vzájemného zablokování relace. Pokud dvě relace mají různé priority vzájemného zablokování, je relace s nižší prioritou zvolena jako oběť vzájemného zablokování. V tomto příkladu mají obě relace stejnou prioritu vzájemného zablokování.

• Množství transakčního protokolu používaného relací v bajtech. Pokud mají obě relace stejnou prioritu vzájemného zablokování, monitor vzájemného zablokování zvolí relaci, která je levnější, aby se vrátila zpět jako oběť vzájemného zablokování. Náklady jsou určeny porovnáním počtu bajtů protokolu zapsaných k bodě každé transakce.

  V našem příkladu zablokování session_id 89 použilo nižší množství transakčního protokolu a bylo vybráno jako oběť zablokování.

Kromě toho můžete zobrazit vstupní vyrovnávací paměť posledního příkazu spuštěného v každé relaci před zablokováním tak, že na každý proces najedete myší. Vstupní vyrovnávací paměť se zobrazí v nápovědě.

Další informace jsou k dispozici pro procesy v zobrazení XML grafu zablokování, včetně:

• Identifikace informací pro relaci, například název klienta, název hostitele a přihlašovací jméno.

• Hodnota hash plánu dotazu pro poslední příkaz spuštěný každou relací před zablokováním. Hodnota hash plánu dotazu je užitečná pro načtení dalších informací o dotazu z úložiště dotazů.

V našem příkladu zablokování:

• Vidíme, že obě relace byly spuštěny pomocí klienta SSMS pod přihlašovacím jménem chrisqpublic.

• Hodnota hash plánu dotazu, posledního příkazu spuštěného před vzájemným zablokováním naší oběti, je 0x02b0f58d7730f798. Text tohoto příkazu vidíme ve vstupní vyrovnávací paměti.

• Hodnota hash plánu dotazu, posledního příkazu spuštěného druhou relací v našem zablokování, je také 0x02b0f58d7730f798. Text tohoto příkazu vidíme ve vstupní vyrovnávací paměti. V tomto případě mají oba dotazy stejnou hodnotu hash plánu dotazů, protože dotazy jsou stejné, s výjimkou literálové hodnoty použité jako predikát rovnosti.

Tyto hodnoty používáme dále v tomto článku k najít další informace v úložišti dotazů.

Omezení vstupní vyrovnávací paměti v seznamu procesů zablokování

Existují určitá omezení týkající se informací o vstupní vyrovnávací paměti v seznamu procesů vzájemného zablokování.

Text dotazu může být zkrácen ve vstupní vyrovnávací paměti. Vstupní vyrovnávací paměť je omezená na prvních 4 000 znaků příkazu, který se spouští.

Některé příkazy zapojené do vzájemného zablokování navíc nemusí být zahrnuty do grafu vzájemného zablokování. V našem příkladu relace A spustila dva příkazy aktualizace v rámci jedné transakce. Do grafu vzájemného zablokování je zahrnut pouze druhý aktualizační příkaz, aktualizace, která způsobila zablokování. První aktualizační příkaz spuštěný Session A hrál roli v zablokování blokováním Session B. Vstupní vyrovnávací paměť, query_hasha související informace pro první příkaz spuštěný Relace A není zahrnuta v grafu zablokování.

Pokud chcete identifikovat plné spuštění Transact-SQL v transakci s více příkazy, které se zúčastnily zablokování, musíte buď najít relevantní informace v uložené proceduře nebo v kódu aplikace, který spustil dotaz, nebo spustit trasování pomocí Rozšířených událostí, abyste zachytili kompletní příkazy, které v době zablokování prováděly relace. Pokud je příkaz zapojený do zablokování zkrácený a ve vstupní vyrovnávací paměti se zobrazí pouze částečné Transact-SQL, můžete najít Transact-SQL příkazu v úložišti dotazů pomocí plánu provádění.

Seznam prostředků zablokování

Seznam prostředků vzájemného zablokování ukazuje, které prostředky zámku vlastní a čekají na procesy v zablokování.

Prostředky jsou reprezentovány obdélníky ve vizuální reprezentaci zablokování:

Poznámka:

Názvy databází jsou v grafech vzájemného zablokování pro Azure SQL Database reprezentovány jako identifikátory GUID (uniqueidentifier). Toto je physical_database_name databáze uvedená v sys.databases a sys.dm_user_db_resource_governance zobrazení dynamické správy.

V tomto příkladu zablokování:

• Oběť vzájemného zablokování, kterou jsme označili jako Relace A:

  • Vlastní výhradní zámek (X) na klíči PK_Product_ProductID indexu v SalesLT.Product tabulce.

  • Vyžádá si zámek aktualizace (U) na klíč indexu PK_ProductDescription_ProductDescriptionID v SalesLT.ProductDescription tabulce.

• Druhý proces, který jsme označili jako Relace B:

  • Vlastní zámek aktualizace (U) na klíči PK_ProductDescription_ProductDescriptionID indexu v SalesLT.ProductDescription tabulce.

  • Vyžádá si sdílený zámek (S) na klíč v indexu PK_ProductDescription_ProductDescriptionID v SalesLT.ProductDescription tabulce.

Stejné informace vidíme v xml grafu vzájemného zablokování v uzlu seznamu prostředků.

Vyhledání plánů spouštění dotazů v úložišti dotazů

Často je užitečné prozkoumat plány provádění dotazů na příkazy zapojené do zablokování. Tyto plány provádění se často nacházejí v úložišti dotazů pomocí hodnoty hash plánu dotazu ze zobrazení XML seznamu procesů grafu zablokování.

Tento dotaz Transact-SQL hledá plány dotazů odpovídající hodnotě hash plánu dotazu, které jsme našli pro náš příklad zablokování. Připojte se k uživatelské databázi ve službě Azure SQL Database a spusťte dotaz.

DECLARE @query_plan_hash AS BINARY (8) = 0x02b0f58d7730f798;

SELECT qrsi.end_time AS interval_end_time,
       qs.query_id,
       qp.plan_id,
       qt.query_sql_text,
       TRY_CAST (qp.query_plan AS XML) AS query_plan,
       qrs.count_executions
FROM sys.query_store_query AS qs
     INNER JOIN sys.query_store_query_text AS qt
         ON qs.query_text_id = qt.query_text_id
     INNER JOIN sys.query_store_plan AS qp
         ON qs.query_id = qp.query_id
     INNER JOIN sys.query_store_runtime_stats AS qrs
         ON qp.plan_id = qrs.plan_id
     INNER JOIN sys.query_store_runtime_stats_interval AS qrsi
         ON qrs.runtime_stats_interval_id = qrsi.runtime_stats_interval_id
WHERE query_plan_hash = @query_plan_hash
ORDER BY interval_end_time, query_id;
GO

V závislosti na nastavení CLEANUP_POLICY úložiště dotazů nebo QUERY_CAPTURE_MODEmožná nebudete moct z úložiště dotazů získat plán provedení dotazu. V takovém případě můžete často získat potřebné informace zobrazením odhadovaného plánu spuštění dotazu.

Hledejte vzory, které zvyšují blokování.

Při zkoumání plánů dotazů v rámci zámků a blokací se zaměřte na schémata, která mohou přispívat k blokování.

• Prohledávání tabulek nebo indexů Při spouštění dotazů, které upravují data v rámci RCSI, se výběr řádků, které se mají aktualizovat, provádí pomocí blokující kontroly, kde se na řádku dat přebíná zámek aktualizace (U), když se čtou datové hodnoty. Pokud řádek dat nesplňuje kritéria aktualizace, uvolní se zámek aktualizace a další řádek se uzamkne a zkontroluje.

  Ladění indexů, které pomáhají upravovat dotazy, které umožňují efektivněji najít řádky, snižuje počet vydaných zámků aktualizací. Tím se sníží pravděpodobnost blokování a zablokování.

• Indexovaná zobrazení odkazující na více než jednu tabulku Když upravíte tabulku odkazovanou v indexovaném zobrazení, musí databázový stroj udržovat také indexované zobrazení. To vyžaduje vytáčení více zámků a může vést ke zvýšení blokování a zablokování. Indexovaná zobrazení mohou také způsobit, že se operace aktualizace provádí interně na úrovni izolace potvrzeného čtení.

• Úpravy sloupců odkazovaných v omezeních cizího klíče Když upravíte sloupce v tabulce, na které odkazuje omezení FOREIGN KEY, musí databázový stroj hledat související řádky v odkazující tabulce. Řádkové verze nelze pro tyto operace čtení použít. V případech, kdy jsou povoleny kaskádové aktualizace nebo odstranění, může být úroveň izolace eskalována k serializovatelné po dobu trvání příkazu pro ochranu proti fantomovým vložením.

• Zamknout rady. Vyhledejte rady tabulky, které určují úrovně izolace vyžadující více zámků. Tyto rady zahrnují HOLDLOCK (což je ekvivalent serializovatelné), SERIALIZABLE, READCOMMITTEDLOCK (který zakazuje RCSI) a REPEATABLEREAD. Kromě toho mohou rady, jako PAGLOCKje , TABLOCK, UPDLOCKa XLOCK zvýšit rizika blokování a zablokování.

  Pokud jsou tyto rady splněné, zjistěte, proč byly implementovány rady. Tyto tipy mohou zabránit podmínkám souběhu a zajistit platnost dat. Tyto rady je možné ponechat na místě a v případě potřeby zabránit budoucímu zablokování pomocí alternativní metody v části tohoto článku Zabránění opětovnému výskytu zablokování.

  Poznámka:

  Přečtěte si další informace o chování při úpravě dat pomocí správy verzí řádků v průvodci uzamčením transakcí a správa verzí řádků.

Při zkoumání úplného kódu transakce, ať už v plánu provádění nebo v kódu dotazu aplikace, vyhledejte další problematické vzory:

• Interakce uživatele v transakcích. Interakce uživatele uvnitř explicitní transakce s více příkazy významně zvyšuje dobu trvání transakcí. To zvyšuje pravděpodobnost, že se tyto transakce překrývají a zablokují a zablokují se.

  Podobně držení otevřené transakce a dotazování nesouvisející databáze nebo systémové mid-transakce výrazně zvyšuje pravděpodobnost blokování a zablokování.

• Transakce přistupující k objektům v různých objednávkách. Zablokování je méně pravděpodobné, když souběžné explicitní transakce s více příkazy se řídí stejnými vzory a přistupují k objektům ve stejném pořadí.

Zabránění opakovanému zablokování

K dispozici je několik technik, které brání opakovanému zablokování, jako je optimalizace indexu, vynucení plánů pomocí Query Store a úprava dotazů Transact-SQL.

• Zkontrolujte clusterovaný index tabulky. Většina tabulek těží z clusterovaných indexů, ale často se tabulky implementují jako haldy omylem.

  Jedním ze způsobů, jak zkontrolovat clusterovaný index, je použití sp_helpindex systémové uložené procedury. Můžeme například zobrazit souhrn indexů v SalesLT.Product tabulce spuštěním následujícího příkazu:

  EXECUTE sp_helpindex 'SalesLT.Product';
  GO
  

  Zkontrolujte sloupec index_description. Tabulka může mít pouze jeden clusterovaný index. Pokud byl pro tabulku implementován clusterovaný index, index_description obsahuje slovo clustered.

  Pokud není k dispozici žádný clusterovaný index, tabulka je haldou. V tomto případě zkontrolujte, jestli byla tabulka záměrně vytvořená jako halda pro řešení konkrétního problému s výkonem. Zvažte implementaci clusterovaného indexu na základě pokynů pro návrh clusterovaného indexu.

  V některých případech může vytváření nebo ladění clusterovaného indexu omezit nebo eliminovat blokování v zámkových situacích. V jiných případech můžete použít další techniku, jako jsou ostatní v tomto seznamu.

• Vytvoření nebo úprava neclusterovaných indexů. Ladění neclusterovaných indexů může vašim dotazům na úpravy pomoct rychleji najít data, která se mají aktualizovat, což snižuje počet požadovaných zámků aktualizací.

  V našem příkladu zablokování obsahuje plán provádění dotazů nalezený v úložišti dotazů kontrolu clusterovaného indexu proti indexu PK_Product_ProductID . Graf vzájemného zablokování označuje, že sdílený zámek (S) čeká na tento index komponentou v vzájemném zablokování.

  

  Tato kontrola indexu se provádí, protože náš aktualizační dotaz potřebuje upravit indexované zobrazení s názvem vProductAndDescription. Jak je uvedeno v části tohoto článku Hledání vzorů, které vedou ke zvýšení blokování, indexovaná zobrazení odkazující na více tabulek mohou zvýšit blokování a pravděpodobnost slepých uličení.

  Pokud v databázi vytvoříme následující neclusterovaný index AdventureWorksLT , který "pokrývá" sloupce z SalesLT.Product odkazovaného indexovaného zobrazení, pomůže dotaz najít řádky mnohem efektivněji:

  CREATE INDEX IX_Product_ProductID_Name_ProductModelID
      ON SalesLT.Product(ProductID, Name, ProductModelID);
  GO
  

  Po vytvoření tohoto indexu se zablokování už nebude opakovat.

  Pokud zablokování zahrnuje úpravy sloupců, na něž se odkazuje v omezeních cizího klíče, zajistěte, aby indexy v tabulce s odkazujícím cizím klíčem FOREIGN KEY efektivně podporovaly vyhledávání souvisejících řádků.

  Indexy sice můžou v některých případech výrazně zlepšit výkon dotazů, ale indexy mají také režijní náklady a náklady na správu. Projděte si obecné pokyny k návrhu indexů, které vám pomůžou posoudit výhody indexů před vytvořením indexů, zejména širokých indexů a indexů u velkých tabulek.

• Vyhodnoťte hodnotu indexovaných zobrazení. Další možností, jak zabránit opakovanému zablokování našeho příkladu SalesLT.vProductAndDescription , je vypustit indexované zobrazení. Pokud se toto indexované zobrazení nepoužívá, snižuje se tím režijní náklady na údržbu indexovaného zobrazení v průběhu času.

• Použijte izolaci snímků. V některých případech může nastavení úrovně izolace transakce na úroveň snímku u jedné nebo více transakcí zahrnutých v mrtvém uzlu zabránit opakovanému blokování a vzniku dalších mrtvých uzlů.

  Tato technika bude pravděpodobně úspěšná při použití u příkazů SELECT, pokud je v databázi zakázán režim potvrzeného snímku čtení. Pokud je zakázaný snímek potvrzený pro čtení, SELECT dotazy používající úroveň izolace potvrzené čtení vyžadují sdílené zámky (S). Izolace snímků u těchto transakcí eliminuje potřebu sdílených zámků, což může zabránit blokování a zablokování.

  V databázích, kde je povolená izolace potvrzených snímků pro čtení, dotazy SELECT nevyžadují sdílené (S) zámky, takže mezi transakcemi, které upravují data, vznikají pravděpodobněji patové situace. V případech, kdy nastane zablokování mezi více transakcemi, které upravují data, může izolace pomocí snímků vést ke konfliktu aktualizace místo zablokování. To podobně vyžaduje, aby jedna z transakcí zkusila operaci zopakovat.

• Vynuťte plán s úložištěm dotazů. Můžete zjistit, že jeden z dotazů v deadlocku má více exekučních plánů a zablokování nastane jen tehdy, když se použije specifický plán. Zablokování můžete zabránit opakovanému zablokování vynucením plánu v úložišti dotazů.

• Upravte transact-SQL. Možná budete muset upravit Transact-SQL, abyste zabránili opětovnému zablokování. Úpravy jazyka Transact-SQL by se měly provádět pečlivě a změny by měly být pečlivě testovány, aby se zajistila správnost dat při souběžném spuštění úprav. Při přepisu jazyka Transact-SQL zvažte následující:

  • Řazení příkazů v transakcích tak, aby přistupovaly k objektům ve stejném pořadí.

  • Pokud je to možné, rozdělte transakce na menší transakce.

  • Pokud je to potřeba, použijte nápovědu k dotazu k optimalizaci výkonu. Nápovědu můžete použít beze změny kódu aplikace pomocí úložiště dotazů.

Další způsoby minimalizace zablokování najdete v průvodci vzájemným zablokováním.

Poznámka:

V některých případech můžete upravit prioritu zablokování jedné nebo více relací, které jsou součástí zablokování, pokud je důležité, aby se jedna z relací úspěšně dokončila bez opakování, nebo pokud některý z dotazů zapojených do zablokování není kritický a měl by být vždy zvolen jako oběť. I když to nezabrání opětovnému zablokování, může to snížit účinek budoucích zablokování.

Vyřazení relace XEvents

Relaci XEvents můžete nechat shromažďovat informace o vzájemném zablokování na kritických databázích po dlouhou dobu. Pokud použijete cíl souboru událostí, může to vést k vytvoření velkých souborů, pokud dojde k několika zablokovaným situacím. Soubory objektů blob můžete z Azure Storage odstranit pro aktivní trasování s výjimkou souboru, do kterého se právě zapisuje.

Pokud chcete odebrat relaci XEvents, transact-SQL vyřadí relaci, je stejná bez ohledu na vybraný cílový typ.

Pokud chcete odebrat relaci XEvents, spusťte následující transact-SQL. Před spuštěním kódu nahraďte název relace odpovídající hodnotou.

ALTER EVENT SESSION [deadlocks] ON DATABASE
STATE = STOP;
GO

DROP EVENT SESSION [deadlocks] ON DATABASE;
GO

Použití Průzkumníka služby Azure Storage

Průzkumník služby Azure Storage je samostatná aplikace, která zjednodušuje práci s cíli souborů událostí uloženými v objektech blob ve službě Azure Storage. Pomocí Průzkumník služby Storage můžete:

• Vytvořte kontejner objektů blob pro uchovávání dat relace XEvent.

• Získejte sdílený přístupový podpis (SAS) pro kontejner objektů blob.

  • Jak je uvedeno v tématu Shromažďování grafů vzájemného zablokování ve službě Azure SQL Database s rozšířenými událostmi, vyžadují se oprávnění ke čtení, zápisu a seznamu.

  • Odeberte z hlavního znaku ? hodnotu Query string jako tajný kód při vytváření přihlašovacích údajů s vymezeným oborem databáze.

• Umožňuje zobrazit a stáhnout rozšířené soubory událostí z kontejneru objektů blob.

Stáhnout Průzkumníka služby Azure Storage.

Související obsah

• Vysvětlení a řešení blokujících problémů
• Průvodce uzamykáním transakcí a správou verzí řádků
• Průvodce vzájemným zablokováním
• NASTAVENÍ ÚROVNĚ IZOLACE TRANSAKCÍ
• Azure SQL Database: Vylepšení ladění výkonu pomocí automatického ladění
• Zajištění konzistentního výkonu s využitím Azure SQL
• logika opakování přechodných chyb