Sdílet prostřednictvím

Vysvětlení a řešení blokujících problémů

Platí pro:Azure SQL DatabaseSQL databáze v prostředí Fabric

Tento článek popisuje blokování ve službě Azure SQL Database a fabric SQL Database a ukazuje, jak řešit potíže s blokováním a řešit je.

Účel

V tomto článku termín připojení odkazuje na jednu aktivní relaci v databázi. Každé připojení se zobrazí jako ID relace nebo session_id v mnoha zobrazeních dynamické správy. Každý z těchto identifikátorů relací se obvykle označuje jako proces, i když se nejedná o samostatný procesní kontext v obvyklém smyslu. Místo toho se každé ID relace skládá z prostředků serveru a datových struktur potřebných ke službě požadavků jednoho připojení z daného klienta. Jedna klientská aplikace může mít jedno nebo více připojení. Z pohledu služby Azure SQL Database neexistuje žádný rozdíl mezi více připojeními z jedné klientské aplikace na jednom klientském počítači a několika připojeními z více klientských aplikací nebo více klientských počítačů; jsou atomické. Jedno připojení může blokovat jiné připojení bez ohledu na zdrojového klienta.

Informace o řešení potíží s zablokováním najdete v tématu Analýza a zabránění zablokování ve službě Azure SQL Database a databázi Fabric SQL.

Poznámka:

Tento obsah se zaměřuje na Azure SQL Database. Azure SQL Database je založená na nejnovější stabilní verzi databázového stroje Microsoft SQL Serveru, takže většina obsahu je podobná, i když se možnosti řešení potíží a nástroje můžou lišit. Další informace o blokování sql Serveru najdete v tématu Vysvětlení a řešení problémů s blokováním SQL Serveru. Databáze SQL Fabric sdílí mnoho funkcí se službou Azure SQL Database. Další informace o monitorování výkonu naleznete v tématu Monitorování databáze SQL v Microsoft Fabric.

Porozumění blokování

Blokování je nevyhnutelná a účelová vlastnost všech relačních databázových systémů (RDBMS) se souběžností založenou na zámcích. K blokování v databázi Azure SQL dojde, když jedna relace drží zámek na konkrétním prostředku a ID druhé relace se pokusí získat konfliktní typ zámku na stejném prostředku. Časový rámec, pro který ID první relace uzamkne prostředek, je obvykle malý. Když vlastnící relace zámek uvolní, druhé připojení může získat vlastní zámek na prostředku a pokračovat ve zpracování. Toto chování je normální a může k tomu dojít mnohokrát v průběhu dne bez znatelného účinku na výkon systému.

Každá nová databáze ve službě Azure SQL Database má čtení potvrzeného snímku (RCSI) jako výchozí nastavení databáze. Blokování mezi relacemi, které čtou data, a relacemi, které zapisují data, se minimalizuje v rámci RCSI použitím správu verzí řádků ke zvýšení souběžnosti. Blokování a deadlocky ale mohou v databázích v Azure SQL Database stále nastat, protože:

  • Dotazy, které upravují data, můžou navzájem blokovat.
  • Dotazy mohou běžet za izolačních úrovní, které zvyšují blokování. Úrovně izolace je možné zadat v připojovacích řetězcích aplikací, nápovědích dotazů nebo příkazech SET v jazyce Transact-SQL.
  • RCSI může být zakázáno, což způsobí, že databáze používá sdílené (S) zámky k ochraně příkazů SELECT spuštěných pod úrovní izolace s potvrzeným čtením. To může zvýšit blokování a uváznutí.

Ú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 dodatečná úroveň izolace založená na řádcích, která poskytuje konzistenci na úrovni transakcí pro data a používá verze řádků k výběru řádků k aktualizaci. Chcete-li použít izolaci snímků, dotazy nebo připojení musí explicitně nastavit ú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ý RCSI, sloupec is_read_committed_snapshot_on vrátí hodnotu 1. Pokud je povolená izolace snímků, snapshot_isolation_state_desc vrátí sloupec hodnotu ZAPNUTO.

Kontext doby trvání a transakce dotazu určují, jak dlouho se zámky uchovávají, a jejich vliv na jiné dotazy. Příkazy SELECT spuštěné v rámci RCSI nezískávají sdílené zámky (S) na přečtených datech, a proto neblokují transakce, které upravují data. V případě příkazů INSERT, UPDATE a DELETE se zámky uchovávají během dotazu, a to jak pro konzistenci dat, tak pro umožnění vrácení dotazu zpět v případě potřeby.

U dotazů spuštěných v rámci explicitní transakce je typ zámků a doby trvání, pro které jsou zámky uloženy, určeny typem dotazu, úrovní izolace transakce a zda jsou v dotazu použity pokyny k uzamčení. Popis uzamčení, tipů k uzamčení a úrovní izolace transakcí najdete v následujících článcích:

Když uzamykání a blokování přetrvává v místě, kde je škodlivý vliv na výkon systému, je to z jednoho z následujících důvodů:

  • ID relace uchovává zámky u sady prostředků po delší dobu, než je uvolníte. Tento typ blokování se v průběhu času vyřeší, ale může způsobit snížení výkonu.

  • ID relace drží zámky na sadě prostředků a nikdy je neuvolní. Tento typ blokování se nevyřešil a zabraňuje neomezenému přístupu k ovlivněným prostředkům.

V prvním scénáři může být situace velmi dynamická, protože různá ID relací způsobují blokování různých prostředků v čase, což vytváří pohyblivý cíl. Tyto situace je obtížné vyřešit pomocí aplikace SQL Server Management Studio , aby se problém zúžil na jednotlivé dotazy. Druhá situace naopak vede k konzistentnímu stavu, který může být snazší diagnostikovat.

Optimalizované uzamčení

Optimalizované zamykání je nová funkce databázového stroje výrazně snižuje paměť zámků a počet zámků současně vyžadovaných pro zápisy. Optimalizované uzamykání používá dvě primární komponenty: uzamykání ID transakce (TID) (používá se také v jiných funkcích správy verzí řádků) a zámek po kvalifikaci (LAQ). Nevyžaduje žádnou další konfiguraci.

Tento článek se aktuálně týká chování databázového stroje bez optimalizovaného uzamčení.

Další informace a informace o tom, kde je k dispozici optimalizované uzamčení, najdete v tématu Optimalizované uzamčení.

Aplikace a blokování

Když čelíte blokujícímu problému, může být tendence zaměřit se na ladění na straně serveru a problémy s platformou. Pokud se však zaměříte pouze na databázi, nemusí to vést k řešení a může to zbytečně vyčerpat čas a energii, které by bylo lepší vynaložit na zkoumání klientské aplikace a na analýzu dotazů, které odesílá. Bez ohledu na to, jakou úroveň viditelnosti aplikace zveřejňuje ohledně provedených volání databáze, blokující problém často vyžaduje kontrolu přesných příkazů SQL odeslaných aplikací a přesné chování aplikace týkající se zrušení dotazu, správy připojení, načtení všech řádků výsledků atd. Pokud vývojový nástroj nepovoluje explicitní kontrolu nad správou připojení, zrušením dotazu, vypršením časového limitu dotazu, načtením výsledků atd., blokující problémy nemusí být možné vyřešit. Tento potenciál byste měli pečlivě prozkoumat před výběrem nástroje pro vývoj aplikací pro Azure SQL Database, zejména pro prostředí OLTP citlivá na výkon.

Věnujte pozornost výkonu databáze během fáze návrhu a výstavby databáze a aplikace. Konkrétně by se měla pro každý dotaz vyhodnotit spotřeba prostředků, úroveň izolace a délka cesty transakce. Každý dotaz a transakce by měly být co nejlehčí. Je nutné provést dobrou disciplínu správy připojení. Bez toho se může zdát, že aplikace má přijatelný výkon při nízkém počtu uživatelů, ale výkon může výrazně zhoršit, jak počet uživatelů roste.

Díky správnému návrhu aplikací a dotazů dokáže Azure SQL Database podporovat mnoho tisíc souběžných uživatelů na jednom serveru s malým blokováním.

Poznámka:

Další pokyny k vývoji aplikací najdete v tématu Řešení potíží s připojením a dalších chyb a zpracování přechodných chyb.

Řešení potíží s blokováním

Bez ohledu na to, ve které situaci blokování se nacházíme, je metodologie pro řešení potíží s uzamykáním stejná. Tyto logické oddělení určují zbytek složení tohoto článku. Koncept spočívá v nalezení hlavního blokátoru a identifikaci toho, co tento dotaz dělá, a proč blokuje. Jakmile je problematický dotaz identifikován (to znamená, co drží zámky po delší dobu), dalším krokem je analýza a určení, proč blokování probíhá. Jakmile pochopíme důvody, proč můžeme provést změny tím, že přepracujeme dotaz a transakci.

Postup při řešení potíží:

  1. Identifikace hlavní blokující relace (hlavní blokování)

  2. Vyhledejte dotaz a transakci, která způsobuje blokování (co drží zámky po delší dobu).

  3. Analýza/vysvětlení, proč dochází k dlouhodobému blokování

  4. Řešení problému s blokováním přepracováním dotazu a transakce

Pojďme se nyní ponořit do toho, jak určit hlavní blokující relaci pomocí vhodného záznamu dat.

Shromáždění informací o blokování

Aby se zabránilo potížím při řešení problémů s blokováním, může správce databáze používat skripty SQL, které neustále monitorují stav uzamčení a blokování v databázi ve službě Azure SQL Database. Pro shromáždění těchto dat existují v podstatě dvě metody.

Prvním je dotazování objektů dynamické správy (DMO) a uložení výsledků pro porovnání v průběhu času. Některé objekty zmíněné v tomto článku jsou zobrazení dynamické správy (DMV) a jiné jsou funkce dynamické správy (DMF). Druhou metodou je použití XEvents k zachycení toho, co se provádí.

Shromáždění informací z zobrazení dynamické správy

Vyhledání dynamických spravovacích zobrazení za účelem řešení problémů s blokováním má za cíl identifikovat ID relace v čele blokujícího řetězce a příkaz SQL. Vyhledejte ID relací obětí, která jsou blokovaná. Pokud je nějaké ID relace blokované jiným ID relace, prozkoumejte ID relace, které vlastní prostředek (ID blokující relace). Je toto ID relace vlastníka také blokované? Řetěz můžete projít a najít hlavní blokátor a pak zjistit, proč udržuje svůj zámek.

Nezapomeňte spustit každý z těchto skriptů v cílové databázi ve službě Azure SQL Database.

  • Příkazy sp_who a sp_who2 jsou starší příkazy, které zobrazují všechny aktuální relace. Dynamic Management Views sys.dm_exec_sessions vrací více dat v sadě výsledků, která se snadněji dotazuje a filtruje. sys.dm_exec_sessions najdete v jádru jiných dotazů.

  • Pokud už máte určitou relaci identifikovanou, můžete použít DBCC INPUTBUFFER(<session_id>) k vyhledání posledního příkazu odeslaného relací. Podobné výsledky je možné vrátit pomocí sys.dm_exec_input_buffer funkce dynamické správy (DMF) v sadě výsledků, která usnadňuje dotazování a filtrování a poskytuje session_id a request_id. Pokud chcete například vrátit nejnovější dotaz odeslaný session_id 66 a request_id 0:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • Podívejte se na sloupec blocking_session_id v sys.dm_exec_requests. Když blocking_session_id = 0, sezení není blokováno. Zatímco sys.dm_exec_requests seznam uvádí pouze žádosti, které jsou aktuálně spuštěny, jakékoli připojení (aktivní nebo ne) je uvedené v sys.dm_exec_sessions. Využijte tohoto společného spojení mezi sys.dm_exec_requests a sys.dm_exec_sessions v dalším dotazu.

  • Spuštěním tohoto ukázkového dotazu vyhledejte aktivně spouštěné dotazy a aktuální text dávky SQL nebo vstupní text vyrovnávací paměti pomocí sys.dm_exec_sql_text nebo sys.dm_exec_input_buffer zobrazení dynamické správy. Pokud je hodnota vrácená polem text z sys.dm_exec_sql_text NULL, dotaz se aktuálně nespouští. V takovém případě event_info pole sys.dm_exec_input_buffer obsahuje poslední řetězec příkazu předaný modulu SQL. Tento dotaz lze také použít k identifikaci relací, které blokují jiné relace, včetně seznamu ID relací blokovaných na ID relace.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Spusťte tento složitější ukázkový dotaz od podpory Microsoftu a identifikujte tak začátek řetězu blokování s více relacemi, včetně textu dotazu relací zahrnutých do řetězu blokování.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • Odkaz na sys.dm_os_waiting_tasks, která je v SQL ve vrstvě vlákna či úlohy. Toto vrátí informace o tom, jaký typ SQL čekání aktuálně zažívá požadavek. Podobně jako sys.dm_exec_requests, jsou vráceny pouze aktivní požadavky sys.dm_os_waiting_tasks.

Poznámka:

Další informace o typech čekání, včetně agregovaných statistik čekání v průběhu času, najdete v zobrazení dynamické správy sys.dm_db_wait_stats. Toto DMV vrací agregované statistiky čekání pouze pro aktuální databázi.

  • Pomocí zobrazení dynamické správy sys.dm_tran_locks potřebujete podrobnější informace o tom, jaké zámky byly umístěny dotazy. Toto zobrazení dynamické správy může vracet velké objemy dat v produkční databázi a je užitečné při diagnostice zámků, které se aktuálně uchovávají.

Vzhledem k tomu, že funkce INNER JOIN zapnutá sys.dm_os_waiting_tasks, následující dotaz omezuje výstup sys.dm_tran_locks pouze na aktuálně blokované požadavky, jejich stav čekání a zámky:

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';
  • Při použití pohledů dynamické správy (DMVs) poskytuje ukládání výsledků dotazu v průběhu času datové body, které umožňují přezkoumat blokování v zadaném časovém intervalu a identifikovat přetrvávající blokování nebo trendy.

Shromažďování informací z rozšířených událostí

Kromě předchozích informací je často nutné zachytit trasování aktivit na serveru, aby bylo možné důkladně prozkoumat problém blokování ve službě Azure SQL Database. Pokud například relace provádí více příkazů v rámci transakce, je reprezentován pouze poslední odeslaný příkaz. Jedním z předchozích tvrzení však může být důvod, proč se zámky stále uchovávají. Sledování umožňuje vidět všechny příkazy spuštěné relací v rámci aktuální transakce.

Trasování v SQL Serveru lze zachytit dvěma způsoby; Rozšířené události (XEvents) a trasování profileru Sql Server Profiler je však zastaralá trasovací technologie, která není pro Azure SQL Database podporovaná. Rozšířené události jsou novější technologie trasování, která umožňuje větší všestrannost a menší vliv na pozorovaný systém a jejíž rozhraní je integrováno do SQL Server Management Studio (SSMS).

Přečtěte si dokument, který vysvětluje, jak používat Průvodce novou relací rozšířených událostí v nástroji SSMS. SSMS však pro databáze Azure SQL poskytuje v Průzkumníku objektů podsložku Rozšířených událostí pod každou databází. K zachycení těchto užitečných událostí použijte průvodce relací rozšířených událostí:

  • Chyby kategorií:

    • Pozornost
    • Chyba_nahlášena
    • Varování před provedením

  • Upozornění kategorií:

    • Chybějící_join_predikát

  • Provádění kategorií:

    • Rpc_dokončeno
    • Rpc_starting
    • Sql_dávka_dokončena
    • Sql_dávka_startuje

  • Kategorie monitorování zablokování

    • databáze_xml_zpráva_o_zámku

  • Sezení kategorií

    • Existující_připojení
    • Přihlásit
    • Odhlášení

Poznámka:

Podrobné informace o vzájemném zablokování najdete v tématu Analýza a prevence vzájemného zablokování ve službě Azure SQL Database a databázi Fabric SQL.

Identifikace a řešení běžných scénářů blokování

Prozkoumáním předchozích informací můžete určit příčinu většiny blokujících problémů. Zbývající část tohoto článku popisuje, jak tyto informace použít k identifikaci a řešení některých běžných scénářů blokování. V této diskuzi se předpokládá, že jste k zachycení informací o ID blokujících relací použili blokující skripty (na které odkazujeme dříve) a zaznamenali jste aktivitu aplikace pomocí relace XEvent.

Analýza blokujících dat

  • Prozkoumejte výstup dynamických zobrazení správy sys.dm_exec_requests a sys.dm_exec_sessions k identifikaci hlav blokujících řetězců pomocí blocking_these a session_id. To nejjasněji identifikuje, které požadavky jsou blokované a které blokují. Podívejte se dále na relace, které jsou blokované a blokované. Existuje pro blokující řetězec nějaký společný bod nebo kořen? Pravděpodobně sdílejí stejnou tabulku a jedna nebo více relací zapojených do blokujícího řetězce provádí operaci zápisu.

  • Prohlédněte si výstup dynamických správců sys.dm_exec_requests a sys.dm_exec_sessions k získání informací o ID relací na začátku blokujícího řetězce. Vyhledejte následující pole:

    • sys.dm_exec_requests.status
      Tento sloupec zobrazuje stav konkrétního požadavku. Stav spánku obvykle označuje, že spuštění relace s daným ID bylo dokončeno a čeká na odeslání dalšího dotazu nebo dávky od aplikace. Stav připravený ke spuštění nebo spuštěný označuje, že ID relace právě zpracovává dotaz. Následující tabulka obsahuje stručné vysvětlení různých hodnot stavu.
    Stav Význam
    Pozadí ID relace spouští úlohu na pozadí, například detekci zablokování, zapisovač protokolu, nebo kontrolní bod.
    Spánek ID relace momentálně není vykonáván. Obvykle to značí, že ID relace čeká na příkaz z aplikace.
    Běží ID relace je aktuálně spuštěna v plánovači.
    Spustitelný ID relace je ve spustitelné frontě plánovače a čeká na přidělení času plánovače.
    Dočasně blokován. ID relace čeká na prostředek, například zámek nebo zarážka.

    • sys.dm_exec_sessions.open_transaction_count
      Toto pole vám udává počet otevřených transakcí v této seanci. Pokud je tato hodnota větší než 0, ID relace je v otevřené transakci a může obsahovat zámky získané libovolným příkazem v rámci transakce.

    • sys.dm_exec_requests.open_transaction_count
      Podobně toto pole informuje o počtu otevřených transakcí v tomto požadavku. Pokud je tato hodnota větší než 0, ID relace je v otevřené transakci a může obsahovat zámky získané libovolným příkazem v rámci transakce.

    • sys.dm_exec_requests.wait_type, wait_time a last_wait_type
      sys.dm_exec_requests.wait_type Pokud je hodnota NULL, požadavek aktuálně nečeká na nic a last_wait_type hodnota označuje posledníwait_type, ke kterému došlo. Další informace o sys.dm_os_wait_stats nejběžnějších typech čekání a jejich popis najdete v tématu sys.dm_os_wait_stats. Hodnotu wait_time lze použít k určení, jestli požadavek postupuje. Když dotaz na sys.dm_exec_requests tabulku vrátí hodnotu ve wait_time sloupci, který je menší než wait_time hodnota z předchozího dotazu sys.dm_exec_requests, znamená to, že předchozí zámek byl získán a vydán a nyní čeká na nový zámek (za předpokladu nenulového wait_time). To lze ověřit porovnáním wait_resource a sys.dm_exec_requests výstupů, které zobrazují prostředky, na které požadavek čeká.

    • sys.dm_exec_requests.wait_resource Toto pole označuje prostředek, na který čeká blokovaný požadavek. Následující tabulka uvádí běžné wait_resource formáty a jejich význam:

    Prostředek Formát Příklad Vysvětlení
    Table DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 V tomto případě je ID databáze 5 ukázková databáze pubs a ID objektu 261575970 je tabulka názvů a 1 je clusterovaný index.
    Stránka DatabaseID:FileID:PageID STRÁNKA: 5:1:104 V tomto případě je ID databáze 5 is pubs, ID souboru 1 je primární datový soubor a stránka 104 je stránka patřící do tabulky názvů. K identifikaci object_id stránky, ke které patří, použijte funkci dynamické správy sys.dm_db_page_info, předáním ID databáze, Id souboru a PageId z wait_resource.
    Klíč DatabaseID:Hobt_id (hodnota hash pro klíč indexu) KLÍČ: 5:72057594044284928 (3300a4f361aa) V tomto případě je ID databáze 5 pubsa Hobt_ID 72057594044284928 odpovídá index_id 2 pro object_id 261575970 (tabulka názvů). Pomocí zobrazení katalogu sys.partitions přidružte hobt_id k určitému index_id a object_id. Neexistuje způsob, jak převést hash klíče indexu zpět na konkrétní hodnotu klíče.
    Řádek DatabaseID:FileID:PageID:Slot(řádek) RID: 5:1:104:3 V tomto případě je ID databáze 5 pubs, id souboru 1 je primární datový soubor, stránka 104 je stránka, která patří do tabulky názvů, a slot 3 označuje pozici řádku na stránce.
    Kompilovat DatabaseID:FileID:PageID:Slot(řádek) RID: 5:1:104:3 V tomto případě je ID databáze 5 pubs, id souboru 1 je primární datový soubor, stránka 104 je stránka, která patří do tabulky názvů, a slot 3 označuje pozici řádku na stránce.
    • sys.dm_tran_active_transactions Dynamické zobrazení sys.dm_tran_active_transactions obsahuje data o otevřených transakcích, které lze připojit k jiným DMV pro ucelený přehled transakcí, které čekají na potvrzení nebo vrácení zpět. Následující dotaz slouží k vrácení informací o otevřených transakcích, propojených s jinými zobrazeními dynamické správy včetně sys.dm_tran_session_transactions. Zvažte aktuální stav transakce a další situační data k vyhodnocení, transaction_begin_timezda by mohla být zdrojem blokování.
    SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • Jiné sloupce

      Zbývající sloupce v sys.dm_exec_sessions a sys.dm_exec_request můžou také poskytnout přehled o kořenu problému. Jejich užitečnost se liší v závislosti na okolnostech problému. Můžete například určit, jestli k problému dochází pouze u určitých klientů (název hostitele), v určitých síťových knihovnách (net_library), kdy byla poslední dávka odeslaná ID relací last_request_start_time v sys.dm_exec_sessions, jak dlouho byla žádost spuštěna pomocí start_time v sys.dm_exec_requests, a tak dále.

Běžné scénáře blokování

Následující tabulka mapuje běžné příznaky jejich pravděpodobné příčiny.

Sloupce Waittype, Open_Trana Status odkazují na informace vrácené sys.dm_exec_request. Ostatní sloupce mohou být vráceny sys.dm_exec_sessions. Sloupec "Resolves?" označuje, jestli se blokování vyřeší samostatně nebo jestli má být relace zabita pomocí příkazu KILL. Další informace viz KILL.

Scénář Typ čekání Open_Tran Stav Řeší? Jiné příznaky
1 NESMÍ BÝT NULL >= 0 spustitelný Ano, po dokončení dotazu. V sys.dm_exec_sessions, reads, cpu_timea/nebo memory_usage sloupce se v průběhu času zvyšují. Doba trvání dotazu je vysoká po dokončení.
2 NULL >0 spánek Ne, ale ID relace lze ukončit. Signál upozornění může být zobrazen v relaci Rozšířených událostí pro toto ID relace, což znamená, že došlo k vypršení časového limitu dotazu nebo jeho zrušení.
3 NULL >= 0 spustitelný Ne. Nevyřeší se, dokud klient nenačte všechny řádky nebo nezavře připojení. Identifikátor relace lze ukončit, ale může to trvat až 30 sekund. Pokud je open_transaction_count = 0 a identifikátor relace drží zámky, zatímco úroveň izolace transakce je výchozí (READ COMMITTED), je to pravděpodobně příčina.
4 Je to různé. >= 0 spustitelný Ne. Nevyřeší se, dokud klient nezruší dotazy nebo nezavře připojení. ID relací se dají ukončit, ale může to trvat až 30 sekund. Sloupec hostname v sys.dm_exec_sessions pro ID relace v čele blokujícího řetězce je stejný jako jeden z ID relace, které blokuje.
5 NULL >0 vrácení zpět Ano. V relaci rozšířených událostí pro toto ID relace se může zobrazit signál upozornění, který označuje, že došlo k vypršení časového limitu dotazu nebo zrušení dotazu, nebo byl vydán příkaz vrácení zpět.
6 NULL >0 spánek Nakonec. Když Systém Windows zjistí, že relace už není aktivní, připojení ke službě Azure SQL Database je přerušené. Hodnota last_request_start_time v sys.dm_exec_sessions je mnohem dřívější než současný čas.

Podrobné scénáře blokování

  1. Blokování způsobené normálně spuštěným dotazem s dlouhou dobou provádění

    Řešení: Řešením tohoto typu problému blokování je hledat způsoby optimalizace dotazu. Ve skutečnosti může být tato třída problému s blokováním pouze problémem s výkonem a vyžadovat, abyste ho řešili jako takový. Informace o řešení potíží s konkrétním pomalým dotazem najdete v tématu Řešení potíží s pomalými dotazy na SQL Serveru. Další informace naleznete v tématu Monitorování a ladění výkonu.

    Sestavy z úložiště dotazů v SSMS jsou také vysoce doporučeným a cenným nástrojem pro identifikaci nejnákladnějších dotazů a neoptimálních plánů provádění. Také si projděte Query Performance Insight.

    Pokud dotaz provádí pouze operace SELECT, zvažte spuštění příkazu pod izolací snímků, pokud je ve vaší databázi povolené, zejména pokud je analýza RCSI zakázaná. Když je RCSI povolena, dotazy na čtení dat nevyžadují sdílené (S) zámky na úrovni izolace snímku. Izolace snímků navíc poskytuje konzistenci na úrovni transakce pro všechny příkazy v explicitní vícepříkazové transakci. Izolace snímků už může být ve vaší databázi povolená. S dotazy provádějícími úpravy lze také použít izolaci snímků, ale musíte zpracovat aktualizační konflikty.

    Pokud máte dlouhotrvající dotaz, který blokuje ostatní uživatele a nejde ho optimalizovat, zvažte jeho přesunutí z prostředí OLTP do vyhrazeného systému pro reporting, synchronní repliky databáze určené pouze pro čtení.

  2. Blokování způsobené spícím ID relace, které má nepotvrzenou transakci

    Tento typ blokování lze často identifikovat podle ID relace, které je ve stavu spánku nebo čeká na příkaz, ale jehož úroveň vnoření transakcí (@@TRANCOUNT, open_transaction_count z sys.dm_exec_requests) je větší než nula. K tomu může dojít v případě, že aplikace dojde k vypršení časového limitu dotazu nebo vydá zrušení, aniž by také vydal požadovaný počet příkazů ROLLBACK a/nebo COMMIT. Když ID relace obdrží vypršení časového limitu nebo zrušení, ukončí aktuální dotaz a dávkový proces, ale automaticky nevrátí zpět ani nepotvrdí transakci. Za to je zodpovědná aplikace, protože služba Azure SQL Database nemůže na základě zrušení jednoho dotazu předpokládat, že je potřeba vrátit celou transakci. Časový limit dotazu nebo jeho zrušení je zobrazen jako signalizační událost UPOZORNĚNÍ pro ID relace v relaci rozšířených událostí.

    Pokud chcete předvést nepotvrzenou explicitní transakci, zadejte následující dotaz:

    CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

    Potom spusťte tento dotaz ve stejném okně:

    SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

    Výstup druhého dotazu označuje, že úroveň vnoření transakcí je jedna. Všechny zámky získané v transakci jsou stále uloženy, dokud transakce nebyla potvrzena nebo vrácena zpět. Pokud aplikace explicitně otevřou a potvrdí transakce, může komunikace nebo jiná chyba zanechat relaci i její transakce v otevřeném stavu.

    Použijte skript na sys.dm_tran_active_transactions z tohoto článku k identifikaci aktuálně nepotvrzených transakcí v instanci.

    Řešení:

    • Kromě toho může být blokující problém také problém s výkonem a vyžaduje, abyste jej tak řešili. Pokud může dojít ke snížení doby provádění dotazu, nedojde k vypršení časového limitu dotazu nebo zrušení. Je důležité, aby aplikace dokázala zpracovat scénáře vypršení časového limitu nebo zrušení, pokud k nim dojde, ale můžete také využít zkoumání výkonu dotazu.

    • Aplikace musí správně spravovat úrovně vnoření transakcí nebo mohou způsobit problém blokování po zrušení dotazu tímto způsobem. Zvažte:

      • V obslužné rutině chyby klientské aplikace spusťte IF @@TRANCOUNT > 0 ROLLBACK TRAN po jakékoli chybě, i když klientská aplikace nemá za to, že je transakce otevřená. Vyžaduje se kontrola otevřených transakcí, protože uložená procedura volaná během dávky mohla spustit transakci bez znalosti klientské aplikace. Určité podmínky, jako je zrušení dotazu, brání provedení procedury po aktuálním příkazu, takže i když má procedura logiku pro kontrolu IF @@ERROR <> 0 a přerušení transakce, tento kód vrácení zpět se v takových případech nespustí.
      • Pokud se sdružování připojení používá v aplikaci, která připojení otevře a spustí několik dotazů před uvolněním připojení zpět do fondu, jako je například webová aplikace, může dočasné zakázání sdružování připojení zmírnit problém, dokud klientská aplikace nebude upravena tak, aby správně zpracovávala chyby. Zakázáním používání sdružování připojení způsobí uvolnění připojení fyzické odpojení od služby Azure SQL Database, což vede k tomu, že na serveru dojde k vrácení všech neuzavřených transakcí.
      • Použijte SET XACT_ABORT ON pro připojení nebo v jakýchkoli uložených procedurách, které začínají transakcemi a neřeší úklid po chybě. V případě chyby za běhu toto nastavení přeruší všechny otevřené transakce a vrátí řízení klientovi. Další informace naleznete v části SET XACT_ABORT.

    Poznámka:

    Připojení se neobnovuje, dokud se znovu nepoužívá z fondu připojení, takže je možné, že uživatel může otevřít transakci a poté uvolnit připojení k fondu připojení, ale nemusí se znovu použít po dobu několika sekund, během které transakce zůstane otevřená. Pokud se připojení znovu nepoužívá, transakce se přeruší, když vyprší časový limit připojení a odebere se z fondu připojení. Proto je optimální, aby klientská aplikace přerušila transakce ve své obslužné rutině chyby, nebo aby použila SET XACT_ABORT ON k zamezení tohoto potenciálního zpoždění.

    Upozornění

    Po SET XACT_ABORT ONnejsou příkazy T-SQL, které následují po příkazu způsobujícím chybu, vykonány. To může mít vliv na zamýšlený tok existujícího kódu.

  3. Blokování způsobené ID relace, jehož odpovídající klientská aplikace nedokončila načtení všech řádků výsledků

    Po odeslání dotazu na server musí všechny aplikace okamžitě načíst všechny řádky výsledků. Pokud aplikace nenačte všechny řádky výsledků, můžou na tabulkách zůstat zámky blokující ostatní uživatele. Pokud používáte aplikaci, která transparentně odesílá příkazy SQL na server, musí aplikace načíst všechny řádky výsledků. Pokud ne (a pokud to nejde nakonfigurovat), možná nebudete moct problém s blokováním vyřešit. Pokud se chcete tomuto problému vyhnout, můžete nevhodně se chovající aplikace omezit na databázi sestav nebo databázi pro podporu rozhodování oddělenou od hlavní databáze OLTP.

    Dopad tohoto scénáře je možné snížit povolením možnosti READ_COMMITTED_SNAPSHOT v databázi, což je výchozí konfigurace ve službě Azure SQL Database. Další informace najdete v části Vysvětlení blokování tohoto článku.

    Poznámka:

    Podívejte se na pokyny pro logiku opakování pro aplikace, které se připojují k Azure SQL Database.

    Řešení: Aplikace se musí přepsat, aby se načítly všechny řádky výsledku k dokončení. Tím se nevyloučí použití OFFSET a FETCH v klauzuli ORDER BY dotazu k provedení stránkování na straně serveru.

  4. Blokování způsobené relací ve stavu vrácení do původního stavu

    Dotaz na úpravu dat, který byl ukončen příkazem KILL nebo zrušen mimo uživatelsky definovanou transakci, je stornován. K tomu může dojít také jako vedlejší účinek, když dojde k odpojení relace klientské sítě, nebo když je žádost vybrána jako oběť zablokování. To lze často identifikovat pozorováním výstupu sys.dm_exec_requests, který může znamenat příkaz ROLLBACK a percent_complete sloupec může zobrazit průběh.

    Díky zrychlenému obnovení databáze zavedenému v roce 2019 by zdlouhavé operace zpětného zpracování měly být vzácné.

    Řešení: Počkejte, až identifikátor relace dokončí proces vrácení provedených změn.

    Abyste se této situaci vyhnuli, neprovádějte velké dávkové operace zápisu nebo vytváření indexů nebo operace údržby během zaneprázdněných hodin v systémech OLTP. Pokud je to možné, proveďte tyto operace během období nízké aktivity.

  5. Blokování způsobené opuštěným připojením

    Pokud klientská aplikace zachytí chyby nebo se restartuje klientská pracovní stanice, nemusí být síťová relace k serveru za určitých podmínek okamžitě zrušena. Z pohledu služby Azure SQL Database se zdá, že klient stále existuje a všechny získané zámky se můžou zachovat. Další informace naleznete v tématu Řešení potíží se osamocenými připojeními na SQL Serveru.

    Řešení: Pokud se klientská aplikace odpojila bez odpovídajícího čištění prostředků, můžete ID relace ukončit pomocí KILL příkazu. Příkaz KILL jako vstup přebírá hodnotu ID relace. Pokud například chcete ukončovat ID relace 99, zadejte následující příkaz:

    KILL 99

Související obsah

  • Last updated on