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

Platí pro: Azure SQL Database

V tomto článku se dozvíte, jak identifikovat vzájemné zablokování ve službě Azure SQL Database, jak pomocí grafů vzájemného zablokování a úložiště dotazů identifikovat dotazy, u kterých dochází ke vzájemnému zablokování, a jak plánovat a testovat změny za účelem zabránění opakování vzájemného zablokování.

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í ve službě Azure SQL Database

Každá nová databáze ve službě Azure SQL Database má ve výchozím nastavení povolené nastavení databáze potvrzeného čtení (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í ale může v databázích ve službě Azure SQL Database stále nastat, protože:

• Dotazy, které upravují data, můžou navzájem blokovat.
• Dotazy můžou běžet pod úrovněmi izolace, které zvyšují blokování. Úrovně izolace je možné zadat pomocí metod klientské knihovny, tipů dotazů nebo příkazů SET v jazyce Transact-SQL.
• Analýza RCSI může být zakázaná, což způsobí, že databáze používá sdílené zámky (S) k ochraně příkazů SELECT spuštěných pod úrovní izolace potvrzené čtením. To může zvýšit blokování a zablokování.

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 se vzájemným blokováním budou čekat neomezeně dlouho, dokud nedojde k vrácení zpět některé ze zapojených transakcí (například z důvodu ukončení její relace).

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í cyklické závislosti, zvolí jeden z úkolů jako oběť a ukončí svou transakci s chybou 1205, "Transakce (ID procesu N) byla zablokována u uzamčených prostředků s jiným procesem a byla vybrána jako oběť zablokování. Spusťte transakci znovu." 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.

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é zpoždění před opakováním, 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). Analýza RCSI změní chování úrovně izolace potvrzené pro čtení tak, aby používala správu verzí řádků k zajištění konzistence na úrovni příkazů bez použití sdílených zámků (S) pro příkazy 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 se dá provést jenom 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, is_read_committed_snapshot_on vrátí sloupec hodnotu 1. Pokud je povolená izolace snímků, snapshot_isolation_state_desc sloupec vrátí hodnotu ZAPNUTO.

Pokud je analýza RCSI pro databázi ve službě Azure SQL Database zakázaná , prozkoumejte, proč byla analýza RCSI zakázaná, a teprve potom ji znovu povolte. Kód aplikace mohl být napsán s očekáváním, že dotazy, které čtou data, budou blokovány dotazy, které zapisují data, což vede k nesprávným výsledkům z podmínek časování, když je povolená analýza RCSI.

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, "Transakce (ID procesu N) byla zablokovaná u uzamčených prostředků s jiným procesem a byla zvolena jako oběť vzájemného zablokování. Spusťte transakci znovu."

Je ale užitečné nastavit upozornění, protože zablokování se může opakovat. Upozornění na vzájemné zablokování umožňují zjistit, jestli ve vaší databázi probíhá vzor opakovaných zablokování. V takovém případě se můžete rozhodnout provést akci, která zabrání opakované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 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žijeme AdventureWorksLT ukázkovou databázi k nastavení upozornění na zablokování, způsobíme například zablokování, analyzujeme graf vzájemného zablokování pro příklad zablokování a otestujeme změny, které zabrání opakovanému zablokování.

V tomto článku použijeme klienta aplikace SQL Server Management Studio (SSMS), protože obsahuje funkce k 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:

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

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

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

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=pUNbbsmDiMzXr1vuNGZh84zyOMBFaBjgWv53IhOzYWQ%3D'
    ;
GO

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

Pokud chcete způsobit vzájemné zablokování, budete muset k databázi připojit dvě relace AdventureWorksLT . Na tyto relace se budeme odkazovat jako na relaci A a relaci 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 TRAN

    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 SalesLT.ProductDescription tabulky. Dotaz se spojí s jinými tabulkami, včetně SalesLT.Product tabulky.

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';

K dokončení této aktualizace potřebuje relace B u řádků v tabulce SalesLT.Productzámek se sdíleným zámkem (S), včetně řádku, který je uzamčen relací A. Relace B bude blokována dne 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
        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';

Druhý příkaz aktualizace v relaci A bude blokován relací 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 vzájemné zablokování zjistí, že transakce v relaci A a Relaci B vzájemně blokují a že ani jeden z nich nemůže postupovat. 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:

Msg 1205, Level 13, State 51, Line 7 Transaction (Process ID 91) byl zablokovaný u uzamčených prostředků s jiným procesem a byl vybrán jako oběť zablokování. Znovu spusťte transakci.

Relace B se úspěšně dokončí.

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 jste nastavili relaci XEvents tak, aby shromažďovala zablokování a po spuštění relace došlo k vzájemnému zablokování, můžete zobrazit interaktivní grafické zobrazení grafu vzájemného zablokování a také XML pro graf vzájemného 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:

DECLARE @tracename sysname = N'deadlocks';

WITH ring_buffer AS (
    SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    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

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

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

WITH eft as (SELECT CAST(target_data AS XML) as rb
    FROM sys.dm_xe_database_sessions AS s 
    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

declare @filename nvarchar(2000) = N'https://yourstorageaccountname.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í. Můžete také chtít analyzovat zablokování 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ď zobrazí 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 zablokování 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 oběti proces24756e75088. 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 procesu serveru, označované také jako ID relace nebo 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 byl vybrán jako oběť vzájemného 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 popisu.

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 v rámci přihlášení chrisqpublic .
• Hodnota hash plánu dotazu posledního příkazu spuštěná před zablokováním naší oběti vzájemného zablokování 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 vzájemném 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žijeme dále v tomto článku k vyhledání dalších informací 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ý relací A hrál roli v vzájemném zablokování blokováním Relace B. Vstupní vyrovnávací paměť query_hasha související informace pro první příkaz spuštěný relací A nejsou zahrnuty do grafu vzájemného zablokování.

Pokud chcete identifikovat úplné spuštění jazyka Transact-SQL v transakci s více příkazy zapojené do zablokování, budete muset buď najít relevantní informace v uložené proceduře, nebo kód aplikace, který spustil dotaz, nebo spustit trasování pomocí rozšířených událostí zachytávat úplné příkazy spuštěné relacemi, které jsou součástí zablokování, zatímco dojde k jeho výskytu. Pokud byl příkaz zahrnutý do vzájemného zablokování zkrácen a ve vstupní vyrovnávací paměti se zobrazí pouze částečné transact-SQL, můžete najít transact-SQL pro příkaz v úložišti dotazů s plánem 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í:

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 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
JOIN sys.query_store_query_text as qt on qs.query_text_id=qt.query_text_id
JOIN sys.query_store_plan as qp on qs.query_id=qp.query_id
JOIN sys.query_store_runtime_stats qrs on qp.plan_id = qrs.plan_id
JOIN sys.query_store_runtime_stats_interval 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í úložiště dotazů CLEANUP_POLICY nebo QUERY_CAPTURE_MODE možná nebudete moct z úložiště dotazů získat plán provádění dotazů. 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ů provádění dotazů zapojených do zablokování se podívejte na vzory, které můžou přispět k blokování a zabloková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í můžou také způsobit interní provádění operací aktualizace na úrovni izolace potvrzené čtením.

• Úpravy sloupců odkazovaných v omezeních cizího klíče Když upravíte sloupce v tabulce, na které odkazuje omezení CIZÍ KLÍČ, musí databázový stroj hledat související řádky v odkazující tabulce. Pro tato čtení nelze použít verze řádků. 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 rady můžou zabránit podmínkám časování a zajistit platnost dat. Tyto rady mohou být možné ponechat na místě a zabránit budoucím zablokováním pomocí alternativní metody v části Zabránit vzájemnému zablokování v případě potřeby znovu v tomto článku.

  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í, včetně ladění indexu, vynucení plánů úložiště dotazů a úprav 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:

  exec sp_helpindex 'SalesLT.Product';
  GO
  

  Zkontrolujte sloupec index_description. Tabulka může mít pouze jeden clusterovaný index. Pokud je pro tabulku implementovaný clusterovaný index, index_description bude obsahovat 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 vytvoření nebo ladění clusterovaného indexu snížit nebo eliminovat blokování zablokování. V jiných případech může být potřeba použít další techniku, jako jsou ostatní v tomto seznamu.

• Vytvořte nebo upravte neclusterované indexy. 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 ve vzorech vyhledávání, které zvyšují blokující část tohoto článku, indexovaná zobrazení odkazující na více tabulek mohou zvýšit blokování a pravděpodobnost zablokování.

  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ů odkazovaných v omezeních cizího klíče, zajistěte, aby indexy v referenční tabulce cizího klíče podporovaly efektivní hledá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á, sníží 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ýchpřípadechch

  Tato technika bude s největší pravděpodobností úspěšná při použití příkazů SELECT při zakázání snímku pro čtení potvrzeného čtení v databázi. Pokud je potvrzený snímek pro čtení zakázaný, vyžadují dotazy SELECT využívající úroveň izolace potvrzené pro čtení zámky se sdílenými 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, ve kterých je povolená izolace snímků potvrzená čtením, nevyžadují dotazy SELECT sdílené zámky (S), takže mezi transakcemi, které upravují data, budou pravděpodobně probíhat vzájemné zablokování. V případech, kdy dojde k vzájemnému zablokování mezi několika transakcemi, které upravují data, může izolace snímků vést ke konfliktu aktualizací 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 vzájemném zablokování má více plánů spuštění a zablokování nastane pouze v případě, že se použije konkrétní 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.

Vyřazení relace XEvents

Můžete chtít opustit relaci XEvents, která shromažďuje informace o vzájemném zablokování spuštěné v důležitých databázích po dlouhou dobu. Mějte na paměti, že pokud používáte cíl souboru událostí, může to vést k velkým souborům, pokud dojde k několika zablokování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 Query string hodnotu ? 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ík služby Azure Storage..

Další kroky

Další informace o výkonu ve službě Azure SQL Database:

• Vysvětlení a řešení problémů s blokováním služby Azure SQL Database
• 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