Průvodce zamykáním transakcí a verzováním řádků

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)databáze SQL v Microsoft Fabric

V jakékoli databázi chybné správa transakcí často vede k kolizím a problémům s výkonem v systémech, které mají mnoho uživatelů. S rostoucím počtem uživatelů, kteří přistupují k datům, je důležité, aby aplikace efektivně používaly transakce. Tato příručka popisuje mechanismy zamykání a správy verzí řádků, které databázový stroj používá k zajištění integrity každé transakce a poskytuje informace o tom, jak mohou aplikace efektivně řídit transakce.

Poznámka

optimalizované uzamčení je funkce databázového stroje představená v roce 2023, která výrazně snižuje paměť zámků a počet zámků potřebných pro souběžné zápisy. Tento článek je aktualizovaný tak, aby popisoval chování databázového stroje s optimalizovaným uzamčením a bez něj.

• Další informace a informace o tom, kde je k dispozici optimalizované uzamčení, najdete v tématu Optimalizované uzamčení.
• Pokud chcete zjistit, jestli je pro databázi povolené optimalizované uzamčení, přečtěte si téma Je optimalizované uzamčení povolené?

Optimalizované zamykání přináší významné změny některých částí tohoto článku, mezi které patří:

• Uzamčení v databázovém stroji
• Odstranění operace
• operace vložení
• eskalace zámku
• Omezení uzamčení a eskalace
• Chování při úpravě dat
• chování v souhrnném
• Tipy pro uzamykání

Základy transakcí

Transakce je posloupnost operací prováděných jako jedna logická jednotka práce. Logická jednotka práce musí vykazovat čtyři vlastnosti, označované jako atomicita, konzistence, izolace a stálost (ACID), aby bylo možno kvalifikovat jako transakci.

atomická
Transakce musí být atomická jednotka činnosti; buď se provedou všechny úpravy dat, nebo se neprovede žádná z nich.

Konzistence
Po dokončení musí transakce ponechat všechna data v konzistentním stavu. V relační databázi musí být všechna pravidla použita na úpravy transakce, aby se zachovala veškerá integrita dat. Všechny interní datové struktury, jako jsou indexy stromu B nebo doubly propojené seznamy, musí být na konci transakce správné.

Poznámka

Dokumentace používá termín B-tree obecně v odkazu na indexy. V indexech rowstore databázový stroj implementuje strom B+. To neplatí pro indexy columnstore ani indexy v tabulkách optimalizovaných pro paměť. Další informace najdete v SQL Serveru a architektuře indexu Azure SQL a průvodci návrhem.

izolace
Změny provedené souběžnými transakcemi musí být izolované od úprav provedených jinými souběžnými transakcemi. Transakce buď rozpozná data ve stavu, ve kterém byla předtím, než je změnila jiná souběžná transakce, nebo data rozpozná až po dokončení druhé transakce, ale nerozpozná žádný mezičasový stav. To se označuje jako serializovatelnost, protože výsledkem je schopnost znovu načíst počáteční data a přehrát řadu transakcí, aby skončila s daty ve stejném stavu, ve kterém byla po provedení původních transakcí.

stálost
Po dokončení plně odolné transakce jsou její účinky trvale zavedeny v systému. Změny se uchovávají i v případě selhání systému. SQL Server 2014 (12.x) a novější umožňují zpožděné trvalé transakce. Zpožděné trvalé transakce se potvrdí před uložením záznamu transakčního protokolu na disk. Další informace o opožděné odolnosti transakce naleznete v článku Řízení odolnosti transakce.

Aplikace zodpovídají za počáteční a koncové transakce v bodech, které vynucují logickou konzistenci dat. Aplikace musí definovat posloupnost úprav dat, které zanechávají data v konzistentním stavu v souladu s obchodními pravidly organizace. Aplikace provádí tyto úpravy v jedné transakci, aby databázový stroj mohl vynutit integritu transakce.

Je zodpovědností podnikového databázového systému, jako je instance databázového stroje, poskytovat mechanismy zajišťující integritu každé transakce. Databázový stroj poskytuje:

• Uzamykání zařízení, která zachová izolaci transakcí.

• Protokolovací mechanismy pro zajištění trvanlivosti transakcí. U plně trvalých transakcí je záznam protokolu posílen na disk před potvrzením transakcí. I když tedy serverový hardware, operační systém nebo instance samotného databázového stroje selže, instance použije protokoly transakcí při restartování k automatickému vrácení neúplných transakcí do bodu selhání systému. Zpožděné trvalé transakce se potvrdí před tím, než je záznam v transakčním protokolu zapsán na disk. Tyto transakce mohou být ztraceny, pokud dojde k selhání systému před posílením záznamu protokolu na disk. Další informace o opožděné odolnosti transakce naleznete v článku Řízení odolnosti transakce.

• Funkce správy transakcí, které vynucuje nedělitelnost transakcí a konzistenci. Jakmile je transakce spuštěna, musí být úspěšně dokončena (potvrzena), nebo Databázový stroj vrátí zpět všechny úpravy dat provedené transakcí od spuštění transakce. Tato operace se označuje jako vrácení transakce zpět, protože vrací data do stavu, který byl před těmito změnami.

Řízení transakcí

Aplikace řídí transakce hlavně zadáním, kdy transakce začíná a končí. To lze zadat pomocí příkazů Transact-SQL nebo funkcí rozhraní API (Database Application Programming Interface). Systém musí být také schopen správně zpracovat chyby, které ukončují transakci před dokončením. Další informace naleznete v tématu Transakce, Provádění transakcí v rozhraní ODBCa Transakce v sql Server Native Client.

Ve výchozím nastavení se transakce spravují na úrovni připojení. Když je transakce spuštěna v připojení, všechny Transact-SQL příkazy spuštěné na daném připojení jsou součástí transakce až do konce transakce. Během relace s více aktivními sadami výsledků (MARS) se však Transact-SQL explicitní nebo implicitní transakce stane transakcí řízenou na úrovni dávkového zpracování. Když se dávka dokončí, pokud transakce v rámci dávky není potvrzena nebo vrácena zpět, databázový stroj ji automaticky vrátí zpět. Další informace naleznete v tématu Použití více aktivních sad výsledků (MARS).

Zahájení transakcí

Pomocí funkcí rozhraní API a příkazů Transact-SQL můžete spustit transakce jako explicitní, automatické nebo implicitní transakce.

explicitní transakce

Explicitní transakce je jedna, ve které explicitně definujete začátek i konec transakce prostřednictvím funkce rozhraní API nebo vydáním příkazu Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTIONnebo ROLLBACK WORK Transact-SQL. Když transakce skončí, připojení se vrátí do režimu transakce, ve kterém byl před spuštěním explicitní transakce, což může být implicitní nebo automatický režim.

Všechny příkazy Transact-SQL můžete použít v explicitní transakci s výjimkou následujících příkazů:

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• CREATE FULLTEXT CATALOG
• ALTER FULLTEXT CATALOG
• DROP FULLTEXT CATALOG
• DROP FULLTEXT INDEX
• ALTER FULLTEXT INDEX
• CREATE FULLTEXT INDEX
• BACKUP
• RESTORE
• RECONFIGURE
• Uložené procedury fulltextového systému
• sp_dboption nastavit možnosti databáze nebo jakýkoli systémový postup, který upravuje databázi master uvnitř explicitních nebo implicitních transakcí.

Poznámka

UPDATE STATISTICS lze použít uvnitř explicitní transakce. Nicméně, UPDATE STATISTICS se potvrzuje nezávisle na obklopující transakci a nelze ji vrátit zpět.

automatické transakce

Režim automatického dokončování je výchozí režim správy transakcí databázového stroje. Každý příkaz Transact-SQL se po dokončení potvrdí nebo vrátí zpět. Pokud se příkaz úspěšně dokončí, je potvrzen; pokud dojde k nějaké chybě, vrátí se zpět. Připojení k instanci databázového engine funguje v režimu automatického commitu vždy, když tento výchozí režim nebyl přepsán explicitními nebo implicitními transakcemi. Režim automatického dokončování je také výchozím režimem pro SqlClient, ADO, OLE DB a ODBC.

implicitní transakce

Pokud připojení pracuje v implicitní transakční režim, instance databázového stroje automaticky spustí novou transakci po potvrzení nebo vrácení aktuální transakce zpět. Není třeba nic dělat k vymezení začátku transakce, pouze potvrdíte nebo vrátíte zpět každou transakci. Implicitní režim transakce generuje nepřetržitý řetěz transakcí. Nastavte implicitní režim transakcí prostřednictvím funkce rozhraní API nebo příkazu Transact-SQL SET IMPLICIT_TRANSACTIONS ON. Tento režim se také označuje jako Autocommit OFF, viz setAutoCommit Method (SQLServerConnection).

Po povolení implicitního režimu transakce pro připojení instance databázového stroje automaticky spustí transakci při prvním spuštění některého z těchto příkazů:

• ALTER TABLE
• CREATE
• DELETE
• DENY
• DROP
• FETCH
• GRANT
• INSERT
• OPEN
• REVOKE
• SELECT
• TRUNCATE
• UPDATE

Transakce s dávkovým rozsahem

Platí pouze pro více aktivních sad výsledků (MARS), Transact-SQL explicitní nebo implicitní transakce, která začíná v relaci MARS, se stane oborově omezenou transakcí. Transakce s oborem dávky, která není potvrzena nebo vrácena zpět, když se dávka dokončí, se automaticky vrátí zpět databázovým strojem.

distribuované transakce

Distribuované transakce zahrnují dva nebo více serverů označovaných jako správci prostředků. Správa transakce musí být koordinovaná mezi správci prostředků komponentou serveru, která se nazývá správce transakcí. Každá instance databázového stroje může fungovat jako správce prostředků v distribuovaných transakcích koordinovaných správci transakcí, jako je například Microsoft Distributed Transaction Coordinator (MS DTC) nebo jiné správce transakcí, které podporují specifikaci Open Group XA pro distribuované zpracování transakcí. Další informace naleznete v dokumentaci MS DTC.

Transakce v rámci jedné instance databázového stroje, která zahrnuje dvě nebo více databází, je distribuovaná transakce. Instance spravuje distribuovanou transakci interně; uživateli funguje jako místní transakce.

V aplikaci se distribuovaná transakce spravuje podobně jako místní transakce. Na konci transakce aplikace požaduje, aby transakce byla potvrzena nebo vrácena zpět. Distribuované potvrzení musí správce transakcí spravovat jinak, aby se minimalizovalo riziko, že selhání sítě může vést k úspěšnému potvrzení některých správců prostředků, zatímco jiné vrátí transakce zpět. Toho se dosahuje správou procesu potvrzení ve dvou fázích (fáze přípravy a fáze potvrzení), která se označuje jako dvoufázové potvrzení.

• fáze přípravy

  Když správce transakcí obdrží žádost o potvrzení, odešle příkaz prepare všem správcům prostředků zapojeným do transakce. Každý správce prostředků pak provede vše potřebné k tomu, aby transakce byla trvalá, a všechny vyrovnávací paměti transakčního protokolu jsou uloženy na disk. Jakmile každý správce prostředků dokončí přípravnou fázi, oznámí správci transakcí úspěch nebo selhání fáze. SQL Server 2014 (12.x) zavedl zpožděnou odolnost transakcí. Zpožděné trvalé transakce se potvrdí dříve, než se vyrovnávací paměti transakčního protokolu na každém správci prostředků vyprázdní na disk. Další informace o opožděné odolnosti transakce naleznete v článku Řízení odolnosti transakce.

• fáze potvrzení

  Pokud správce transakcí obdrží úspěšné přípravy od všech správců prostředků, odešle příkazy potvrzení každému správci prostředků. Správci prostředků pak můžou potvrzení dokončit. Pokud všichni správci prostředků nahlásí úspěšné potvrzení, správce transakcí odešle do aplikace oznámení o úspěchu. Pokud některý správce prostředků nahlásil selhání přípravy, správce transakcí odešle každému správci prostředků příkaz vrácení zpět a indikuje selhání potvrzení do aplikace.

  Aplikace databázového stroje mohou spravovat distribuované transakce prostřednictvím Transact-SQL nebo prostřednictvím rozhraní API databáze. Další informace naleznete v tématu BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Koncové transakce

Transakce můžete ukončit pomocí příkazu COMMIT nebo ROLLBACK nebo prostřednictvím odpovídající funkce rozhraní API.

• potvrzení

  Pokud je transakce úspěšná, potvrďte ji. Prohlášení COMMIT zaručuje, že všechny změny transakce jsou trvalé části databáze. Potvrzení také uvolní prostředky, jako jsou zámky, používané transakcí.

• Vrátit zpět

  Pokud dojde k chybě v transakci nebo pokud se uživatel rozhodne transakci zrušit, vraťte transakci zpět. Příkaz ROLLBACK vrátí všechny změny provedené v transakci vrácením dat do stavu, ve který byla na začátku transakce. Vrácení zpět také uvolní prostředky uchovávané transakcí.

Poznámka

U několika relací aktivních sad výsledků (MARS) nelze potvrdit explicitní transakci zahájenou přes funkci API, pokud jsou stále zpracovávány požadavky. Při každém pokusu o potvrzení tohoto typu transakce při provádění požadavků dojde k chybě.

Chyby během zpracování transakcí

Pokud chyba brání úspěšnému dokončení transakce, databázový stroj automaticky vrátí transakci zpět a uvolní všechny prostředky uchovávané transakcí. Pokud dojde k přerušení síťového připojení klienta k instanci databázového serveru, všechny nevyřízené transakce pro toto připojení se vrátí zpět, když síť oznámí instanci přerušení připojení. Pokud klientská aplikace selže nebo dojde k výpadku nebo restartování klientského počítače, přeruší se připojení a instance databázového stroje vrátí zpět všechny nevyřízené transakce, když ji síť upozorní na přerušení připojení. Pokud se klient odpojí od databázového stroje, všechny nevyřízené transakce se vrátí zpět.

Pokud v dávce dojde k chybě příkazu za běhu (například porušení omezení), výchozím chováním databázového stroje je vrátit zpět pouze příkaz, který chybu vygeneroval. Toto chování můžete změnit pomocí příkazu SET XACT_ABORT ON. Po provedení SET XACT_ABORT ON způsobí jakákoli chyba příkazu runtime automatické vrácení aktuální transakce zpět. Chyby kompilace, jako jsou chyby syntaxe, nejsou ovlivněny SET XACT_ABORT. Další informace naleznete v tématu SET XACT_ABORT (Transact-SQL).

Pokud dojde k chybám, měla by být do kódu aplikace zahrnuta příslušná akce (COMMIT nebo ROLLBACK). Jedním z efektivních nástrojů pro zpracování chyb, včetně těch v transakcích, je Transact-SQL TRY...CATCH konstrukce. Další informace s příklady, které zahrnují transakce, najdete v tématu TRY...CATCH (Transact-SQL). Počínaje SQL Serverem 2012 (11.x) můžete pomocí příkazu THROW vyvolat výjimku a převést provádění do CATCH bloku TRY...CATCH konstruktoru. Další informace naleznete v tématu THROW (Transact-SQL).

Chyby kompilace a doby běhu v režimu automatického dokončování

V režimu automatického dokončování se někdy zdá, že instance databázového stroje vrátila celou dávku místo jen jednoho příkazu SQL. K tomu dochází v případě, že došlo k chybě kompilace, ne k chybě za běhu. Chyba kompilace brání databázovému stroji ve vytvoření plánu vykonání, a proto nemůže být v dávce nic provedeno. I když se zdá, že všechny příkazy před tou, která vygenerovala chybu, byly vráceny zpět, tato chyba zabránila spuštění čehokoli v dávce. V následujícím příkladu se žádný z příkazů INSERT ve třetí dávce nespustí kvůli chybě kompilace. Zdá se, že první dva příkazy INSERT jsou vráceny zpět, i přesto že se nikdy nespustí.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

V následujícím příkladu třetí příkaz INSERT vygeneruje chybu duplicitního primárního klíče během běhu programu. První dva příkazy INSERT jsou úspěšné a potvrzené, takže zůstanou i po chybě během běhu.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Databázový stroj používá odložené překlady názvů, kdy se názvy objektů překládají v době provádění, ne v době kompilace. V následujícím příkladu se spustí a potvrdí první dva příkazy INSERT a tyto dva řádky zůstanou v tabulce TestBatch po třetím příkazu INSERT, vygenerováním chyby za běhu odkazem na tabulku, která neexistuje.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Základy zamykání a verzování řádků

Databázový stroj používá následující mechanismy k zajištění integrity transakcí a zachování konzistence databází, když více uživatelů přistupuje k datům současně:

• uzamčení

  Každá transakce vyžaduje zámky různých typů prostředků, jako jsou řádky, stránky nebo tabulky, na kterých je transakce závislá. Zámky brání dalším transakcím upravovat prostředky takovým způsobem, který by způsobil problémy transakci, jež žádá o zámek. Každá transakce uvolní své zámky, jakmile už nemá žádnou závislost na uzamčených prostředcích.

• verzování řádků

  Při použití úrovně izolace založené na správě verzí řádků databázový stroj udržuje verze každého upraveného řádku. Aplikace mohou určit, že transakce používá verze řádků k zobrazení dat, jak existovaly na začátku transakce nebo příkazu, namísto ochrany všech čtení pomocí zámků. Díky použití správy verzí řádků je pravděpodobnost, že operace čtení blokuje jiné transakce, výrazně snížena.

Uzamčení verzí řádků brání uživatelům ve čtení nepotvrzených dat a zabránění tomu, aby se více uživatelů pokusilo změnit stejná data najednou. Bez zamknutí nebo verzování řádků mohou dotazy nad těmito daty vést k neočekávaným výsledkům tím, že vrátí data, která dosud nebyla potvrzena v databázi.

Aplikace mohou zvolit úrovně izolace transakcí, které definují úroveň ochrany transakce před úpravami provedenými jinými transakcemi. Pro jednotlivé příkazy Transact-SQL je možné zadat rady na úrovni tabulky, které dále přizpůsobí chování tak, aby vyhovovaly požadavkům aplikace.

Správa souběžného přístupu k datům

Uživatelům, kteří přistupují k prostředku současně, se říká, že k prostředku přistupují souběžně. Souběžný přístup k datům vyžaduje mechanismy, které brání nepříznivým účinkům, když se více uživatelů pokusí upravit prostředky, které aktivně používají jiní uživatelé.

Efekty souběžnosti

Uživatelé, kteří upravují data, můžou ovlivnit jiné uživatele, kteří čtou nebo upravují stejná data najednou. Tito uživatelé mají přístup k datům souběžně. Pokud databáze nemá žádnou kontrolu souběžnosti, můžou se uživatelům zobrazit následující vedlejší účinky:

• ztracené aktualizace

  Ztráta aktualizací nastane, když dvě nebo více transakcí vybere stejný řádek a pak aktualizuje řádek na základě původně vybrané hodnoty. Každá transakce neví o ostatních transakcích. Poslední aktualizace přepíše aktualizace provedené ostatními transakcemi, což vede ke ztrátě dat.

  Například dva editory vytvoří elektronickou kopii stejného dokumentu. Každý editor změní kopii nezávisle a pak uloží změněnou kopii, čímž přepíše původní dokument. Editor, který uloží změněnou kopii, naposledy přepíše změny provedené jiným editorem. Tento problém by se mohl vyhnout, pokud jeden editor nemohl získat přístup k souboru, dokud druhý editor nedokončil a potvrdil transakci.

• nepotvrzená závislost (nečisté čtení)

  Nepotvrzená závislost nastane, když druhá transakce přečte řádek, který je aktualizován jinou transakcí. Druhá transakce čte data, která ještě nebyla potvrzena a která může být změněna transakcí, která aktualizuje řádek.

  Editor například provádí změny elektronického dokumentu. Během změn druhý editor vezme kopii dokumentu, která obsahuje všechny dosud provedené změny a distribuuje dokument zamýšlené cílové skupině. První editor pak rozhodne, že dosud provedené změny jsou nesprávné, a odebere úpravy a uloží dokument. Distribuovaný dokument obsahuje úpravy, které již neexistují a měly by být považovány za ty, které nikdy neexistovaly. Tento problém by se mohl vyhnout, pokud nikdo nemohl přečíst změněný dokument, dokud první editor neukončí konečné uložení změn a potvrzení transakce.

• nekonzistentní analýza (neopakovatelné čtení)

  Nekonzistentní analýza nastane, když druhá transakce přistupuje ke stejnému řádku několikrát a pokaždé čte různá data. Nekonzistentní analýza je podobná nepotvrzené závislosti v tom, že jiná transakce mění data, která druhá transakce čte. V nekonzistentní analýze však data přečtená druhou transakcí byla potvrzena transakcí, která provedla změnu. Nekonzistentní čtení také zahrnuje více čtení (dvě nebo více) stejného řádku, a pokaždé, když je informace změněna jinou transakcí; proto výraz neopakovatelné čtení.

  Editor například čte stejný dokument dvakrát, ale mezi každým čtením autor dokument přepíše. Když editor načte dokument podruhé, změnil se. Původní čtení nebylo opakovatelné. Tento problém by se mohl vyhnout, pokud zapisovač nemohl změnit dokument, dokud editor nedokončil jeho poslední čtení.

• fantomové čtení

  Fantomové čtení je situace, která nastane, když se spustí dva identické dotazy a sada řádků vrácených druhým dotazem se liší. Následující příklad ukazuje, jak k tomu může dojít. Předpokládejme, že se obě transakce provádějí současně. Dva SELECT příkazy v první transakci mohou vrátit různé výsledky, protože INSERT příkaz v druhé transakci změní data používaná oběma.

  --Transaction 1
  BEGIN TRAN;
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 AND ID < 10;
  
  --The INSERT statement from the second transaction occurs here.
  
  SELECT ID
  FROM dbo.employee
  WHERE ID > 5 and ID < 10;
  
  COMMIT;
  

  --Transaction 2
  BEGIN TRAN;
  INSERT INTO dbo.employee (Id, Name)
  VALUES(6 ,'New');
  
  COMMIT;
  

• Chybějící a dvojité čtení způsobené aktualizacemi řádků

  • Chybějící aktualizovaný řádek nebo zobrazení aktualizovaného řádku několikrát

    Transakce spuštěné na úrovni READ UNCOMMITTED (nebo příkazy používající nápovědu tabulky NOLOCK) nevydávají sdílené zámky, aby zabránily jiným transakcím v úpravě dat přečtených aktuální transakcí. Transakce spuštěné na úrovni READ COMMITTED vydávají sdílené zámky, ale po přečtení řádku se uvolní zámky řádku nebo stránky. V obou případech, když prohledáváte index, pokud jiný uživatel změní sloupec klíče indexu pro řádek během vašeho čtení, může se řádek znovu zobrazit, pokud ho změna klíče přesunula na pozici před vaším prohledáváním. Podobně nemusí být řádek vůbec přečten, pokud změna klíče přesunula řádek na pozici v indexu, který jste již přečetli. Pokud se tomu chcete vyhnout, použijte nápovědu SERIALIZABLE nebo HOLDLOCK, nebo verzování řádků. Další informace naleznete v části Tipy pro tabulku (Transact-SQL).

  • Chybí jeden nebo více řádků, které nebyly cílem aktualizace.

    Pokud používáte READ UNCOMMITTED, pokud dotaz čte řádky pomocí kontroly pořadí přidělování (pomocí stránek IAM), můžete vynechat řádky, pokud jiná transakce způsobuje rozdělení stránky. K tomu nedojde, pokud používáte úroveň izolace READ COMMITTED.

Typy souběžnosti

Pokud se více transakcí pokusí upravit data v databázi současně, musí být implementován systém ovládacích prvků, aby změny provedené jednou transakcí neměly nepříznivý vliv na ty z jiné transakce. Tomu se říká řízení souběžnosti.

Teorie řízení souběžnosti má dvě klasifikace metod zřizování řízení souběžnosti:

• pesimistické řízení souběžnosti

  Systém zámků brání transakcím v úpravě dat způsobem, který ovlivňuje jiné transakce. Jakmile transakce provede akci, která způsobí použití zámku, ostatní transakce nemohou provádět akce, které by byly v konfliktu s zámkem, dokud vlastník ho neuvolní. Tomu se říká pesimistické řízení, protože se obvykle používá v systémech, kde je vysoká konkurence pro data, kde jsou náklady na ochranu dat při použití zámků nižší než náklady na vrácení transakcí zpět v případě konfliktů ve souběžném přístupu.

• optimistické řízení souběžnosti

  Při řízení optimistické souběžnosti transakce nezamykají data při jejich čtení. Když však transakce aktualizuje data, systém zkontroluje, zda jiná transakce změnila data po přečtení. Pokud data aktualizovala jiná transakce, vyvolá se chyba. Transakce, která přijímá chybu, se obvykle vrátí zpět a začne znovu. To se označuje jako optimistická, protože se obvykle používá v systémech, kde dochází k nízkému konfliktu dat, a kdy jsou náklady na občasné vrácení zpět transakce nižší než náklady na uzamčení dat při jejich čtení.

Databázový stroj podporuje obě metody řízení souběžnosti. Uživatelé určují typ řízení souběžnosti výběrem úrovní izolace transakcí pro připojení nebo možnosti souběžnosti na kurzorech. Tyto atributy lze definovat pomocí příkazů Transact-SQL nebo prostřednictvím vlastností a atributů programovacích rozhraní databázových aplikací (API), jako jsou ADO, ADO.NET, OLE DB a ODBC.

Úrovně izolace v databázovém stroji

Transakce určují úroveň izolace, která definuje stupeň, do kterého musí být jedna transakce izolovaná od prostředku nebo úprav dat provedených jinými transakcemi. Úrovně izolace jsou popsány z hlediska vedlejších účinků souběžného zpracování, jako je povolení špinavého nebo fantomového čtení.

Řízení úrovní izolace transakcí:

• Určuje, jestli se zámky získávají při čtení dat a o jaký typ zámků se žádá.
• Jak dlouho se zámky čtení drží.
• Zda operace čtení odkazující na řádky upravené jinou transakcí:
  • Zablokuje, dokud se nezablokuje výhradní zámek na řádku.
  • Načte potvrzenou verzi řádku, která existovala v době spuštění příkazu nebo transakce.
  • Přečte nepotvrzenou úpravu dat.

Důležitý

Volba úrovně izolace transakcí nemá vliv na zámky získané za účelem ochrany úprav dat. Transakce vždy obsahuje výhradní zámek pro provádění úprav dat a uchovává tento zámek až do dokončení transakce bez ohledu na úroveň izolace nastavenou pro danou transakci. U operací čtení úrovně izolace transakcí primárně definují úroveň ochrany před účinky úprav provedených jinými transakcemi.

Nižší úroveň izolace zvyšuje schopnost mnoha transakcí přistupovat k datům najednou, ale také zvyšuje počet účinků souběžnosti (například špinavé čtení nebo ztráty aktualizací), na které mohou narazit transakce. Naopak vyšší úroveň izolace snižuje typy účinků souběžnosti, na které mohou transakce narazit, ale vyžaduje více systémových prostředků a zvyšuje pravděpodobnost, že jedna transakce blokuje jinou. Volba odpovídající úrovně izolace závisí na vyvážení požadavků aplikace na integritu dat oproti režii každé úrovně izolace. Nejvyšší úroveň izolace, SERIALIZABLE, zaručuje, že transakce načte přesně stejná data pokaždé, když opakuje operaci čtení, ale dělá to provedením úrovně uzamčení, která bude pravděpodobně mít vliv na jiné transakce v systémech s více uživateli. Nejnižší úroveň izolace, READ UNCOMMITTED, může načíst data, která byla změněna, ale nebyla potvrzena jinými transakcemi. Všechny důsledky souběžného přístupu mohou nastat v READ UNCOMMITTED, ale neexistuje žádné uzamčení čtení ani správa verzí, takže režijní náklady jsou minimalizovány.

Úrovně izolace databázového stroje

Standard ISO definuje následující úrovně izolace, z nichž všechny jsou podporovány databázovým strojem:

Úroveň izolace	Definice
READ UNCOMMITTED	Nejnižší úroveň izolace, kde jsou transakce izolované pouze dostatečně, aby se zajistilo, že fyzicky nekonzistentní data nebudou čtená. Na této úrovni jsou povoleny špinavé čtení, takže jedna transakce může vidět dosud nepotvrzené změny provedené jinými transakcemi.
READ COMMITTED	Umožňuje transakci číst data dříve přečtená (neupravována) jinou transakcí bez čekání na dokončení první transakce. Databázový stroj uchovává zámky zápisu (získané u vybraných dat) až do konce transakce, ale zámky čtení se uvolní, jakmile se operace čtení provede. Toto je výchozí úroveň databázového stroje.
REPEATABLE READ	Databázový stroj uchovává zámky čtení a zápisu, které jsou získány u vybraných dat až do konce transakce. Vzhledem k tomu, že zámky rozsahu nejsou spravovány, může dojít k fantomovým čtením.
SERIALIZABLE	Nejvyšší úroveň, kde jsou transakce zcela izolované od sebe. Databázový stroj uchovává zámky čtení a zápisu získané u vybraných dat až do konce transakce. Zámky rozsahu se získávají, když operace SELECT používá rozsahovou klauzuli WHERE, aby se zabránilo fantomovým čtením. Poznámka: operace a transakce DDL v replikovaných tabulkách můžou selhat, když je požadována úroveň izolace SERIALIZABLE. Důvodem je to, že dotazy replikace používají rady, které mohou být nekompatibilní s úrovní izolace SERIALIZABLE.

Databázový stroj podporuje také dvě další úrovně izolace transakcí, které používají správu verzí řádků. Jedním z nich je implementace READ COMMITTED úrovně izolace a jedna je SNAPSHOT úroveň izolace transakce.

Úroveň izolace verzí řádků	Definice
Read Committed Snapshot (RCSI)	Pokud je možnost READ_COMMITTED_SNAPSHOT databáze nastavena ON, což je výchozí nastavení ve službě Azure SQL Database, úroveň izolace READ COMMITTED používá verzování řádků k zajištění konzistence čtení na úrovni jednotlivých příkazů. Operace čtení vyžadují pouze zámky na úrovni tabulky pro stabilitu schématu (Sch-S) a žádné zámky stránek nebo řádků. To znamená, že databázový stroj používá správu verzí řádků k prezentaci každého příkazu s transakčně konzistentním snímkem dat, jako existoval na začátku příkazu. Zámky se nepoužívají k ochraně dat před aktualizacemi jinými transakcemi. Uživatelem definovaná funkce může vracet data, která byla potvrzena po začátku provádění příkazu obsahujícího tuto funkci. Pokud je možnost READ_COMMITTED_SNAPSHOT databáze nastavena OFF, což je výchozí nastavení v SQL Serveru a Azure SQL Managed Instance, READ COMMITTED izolace používá sdílené zámky, aby zabránila jiným transakcím v modifikaci řádků během probíhající čtecí operace aktuální transakce. Sdílené zámky také blokují příkaz číst řádky změněné jinými transakcemi, dokud se druhá transakce nedokončí. Obě implementace splňují definici ISO izolace READ COMMITTED.
SNAPSHOT	Úroveň izolace snímku používá verzování řádků k zajištění konzistence čtení na úrovni transakce. Operace čtení nezískávají žádné zámky stránek ani řádků; jsou získány pouze zámky stability schématu pro tabulku (Sch-S). Při čtení řádků upravených jinou transakcí operace čtení načítají verzi řádku, který existoval při spuštění transakce. Izolaci SNAPSHOT můžete použít pouze v případech, kdy je možnost ALLOW_SNAPSHOT_ISOLATION databáze nastavená na ON. Ve výchozím nastavení je tato možnost nastavená na OFF pro uživatelské databáze v SQL Serveru a Azure SQL Managed Instance a je nastavená na ON pro databáze v Azure SQL Database. Poznámka: Databázový stroj nepodporuje správu verzí metadat. Z tohoto důvodu existují omezení toho, jaké operace DDL lze provádět v explicitní transakci, která běží pod izolací snímků. Následující příkazy DDL nejsou povoleny v rámci izolace snímků po BEGIN TRANSACTION příkazu: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEMEnebo jakýkoli příkaz CLR (Common Language Runtime). Tyto příkazy jsou povoleny, pokud používáte izolaci snímků v rámci implicitních transakcí. Implicitní transakce je podle definice jediným příkazem, který umožňuje vynutit sémantiku izolace na úrovni snímků, a to dokonce i s příkazy DDL. Porušení tohoto principu může způsobit chybu 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

Následující tabulka ukazuje vedlejší účinky souběžnosti povolené různými úrovněmi izolace.

Úroveň izolace	Špinavé čtení	Neopakovatelné čtení	Fantom
READ UNCOMMITTED	Ano	Ano	Ano
READ COMMITTED	Ne	Ano	Ano
REPEATABLE READ	Ne	Ne	Ano
SNAPSHOT	Ne	Ne	Ne
SERIALIZABLE	Ne	Ne	Ne

Další informace o konkrétních typech uzamčení nebo verzování řádků řízených jednotlivými úrovněmi izolace transakcí naleznete v tématu SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Úrovně izolace transakcí je možné nastavit pomocí Transact-SQL nebo prostřednictvím databázového rozhraní API.

Transact-SQL
Transact-SQL skripty používají příkaz SET TRANSACTION ISOLATION LEVEL.

ADO
Aplikace ADO nastavily vlastnost IsolationLevel objektu Connection na adXactReadUncommitted, adXactReadCommitted, adXactRepeatableReadnebo adXactReadSerializable.

ADO.NET
aplikace ADO.NET používající spravovaný obor názvů System.Data.SqlClient mohou volat metodu SqlConnection.BeginTransaction a nastavit možnost IsolationLevel na Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializablenebo Snapshot.

OLE DB
Při spuštění transakce, při které aplikace používají volání OLE DB na ITransactionLocal::StartTransaction, je isoLevel nastaveno na ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOTnebo ISOLATIONLEVEL_SERIALIZABLE.

Při zadávání úrovně izolace transakcí v režimu automatického dokončování mohou aplikace OLE DB nastavit DBPROPSET_SESSION vlastnost DBPROP_SESS_AUTOCOMMITISOLEVELS na DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATEDnebo DBPROPVAL_TI_SNAPSHOT.

ODBC
Aplikace ODBC volají SQLSetConnectAttr s Attribute nastaveným na SQL_ATTR_TXN_ISOLATION a ValuePtr nastaveným na SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READnebo SQL_TXN_SERIALIZABLE.

U transakcí snímků aplikace volají SQLSetConnectAttr s atributem nastaveným na SQL_COPT_SS_TXN_ISOLATION a ValuePtr nastaveným na SQL_TXN_SS_SNAPSHOT. Transakce snímku lze načíst pomocí SQL_COPT_SS_TXN_ISOLATION nebo SQL_ATTR_TXN_ISOLATION.

Uzamčení v databázovém systému

Uzamčení je mechanismus používaný databázovým strojem k synchronizaci přístupu více uživatelů ke stejné části dat najednou.

Než transakce získá závislost na aktuálním stavu části dat, například čtením nebo úpravou dat, musí se chránit před účinky jiné transakce, která upravuje stejná data. Transakce to dělá vyžádáním zámku na kus dat. Zámky mají různé režimy, například sdílené (S) nebo exkluzivní (X). Režim uzamčení definuje úroveň závislosti, která transakce má na datech. Žádné transakci nelze udělit zámek, který by byl v konfliktu s režimem zámku již uděleným na těchto datech jiné transakci. Pokud transakce požaduje režim uzamčení, který je v konfliktu s zámkem, který již byl udělen na stejných datech, databázový stroj pozastaví žádost o transakci, dokud nebude vydán první zámek.

Když transakce upraví jednotku dat, uchovává určité zámky chránící úpravu až do konce transakce. Jak dlouho transakce uchovává zámky získané za účelem ochrany operací čtení, závisí na nastavení na úrovni izolace transakce a na tom, zda optimalizované uzamčení je povoleno.

• Pokud není povolené optimalizované uzamčení, jsou zámky řádků a stránek nezbytné pro zápisy uloženy až do konce transakce.

• Pokud je povolené optimalizované uzamčení, bude do konce transakce uložen pouze zámek ID transakce (TID). Ve výchozí úrovni izolace READ COMMITTED nebudou transakce držet zámky na řádcích a stránkách nezbytné pro zápisy až do konce transakce. To snižuje nároky na paměť pro zámky a zamezuje nutnosti eskalace zámku. Pokud je navíc povolené optimalizované uzamčení, zámek po kvalifikaci (LAQ) optimalizace vyhodnocuje predikáty dotazu na nejnovější potvrzenou verzi řádku bez získání zámku, což zlepšuje souběžnost.

Všechny zámky držené transakcí jsou uvolněny po dokončení transakce (buď potvrzení, nebo vrácení zpět).

Aplikace obvykle nevyžadují zámky přímo. Zámky se spravují interně pomocí části databázového stroje označovaného jako správce zámků. Když instance databázového stroje zpracuje příkaz Transact-SQL, procesor dotazů databázového stroje určuje, ke kterým prostředkům se má přistupovat. Procesor dotazů určuje, jaké typy zámků jsou potřeba k ochraně jednotlivých prostředků na základě typu přístupu a nastavení na úrovni izolace transakcí. Procesor dotazů pak požádá o příslušné zámky od správce zámků. Správce zámků udělí zámky, pokud neexistují žádné konfliktní zámky uchovávané jinými transakcemi.

Zamčení granulárnosti a hierarchií

Databázový stroj má vícegranulární uzamykání, které umožňuje, aby různé typy prostředků byly uzamčeny transakcí. Aby se minimalizovaly náklady na uzamčení, databázový stroj uzamkne prostředky automaticky na úrovni odpovídající úloze. Uzamčení na menší úrovni granularity, jako jsou řádky, zvyšuje souběžnost, ale zvyšuje i režii, protože při uzamčení mnoha řádků je nutné spravovat více zámků. Uzamčení s větší členitostí, jako jsou tabulky, jsou nákladné z hlediska souběžnosti, protože uzamčení celé tabulky omezuje přístup k jakékoli části tabulky jinými transakcemi. Má ale nižší režii, protože se udržuje méně zámků.

Databázový Stroj často musí získat zámky na více úrovních podrobnosti, aby plně chránil prostředek. Tato skupina zámků na více úrovních členitosti se nazývá hierarchie zámků. Pokud například chcete plně chránit čtení indexu, může instance databázového systému potřebovat získat sdílené zámky na řádcích a záměrové sdílené zámky na stránkách a tabulce.

Následující tabulka ukazuje prostředky, které databázový stroj může uzamknout.

Zdroj	Popis
RID	Identifikátor řádku sloužící k uzamčení jednoho řádku v haldě.
KEY	Zámek řádku pro uzamčení jednoho řádku v indexu B-strom.
PAGE	Stránka 8 kilobajtů (KB) v databázi, například data nebo indexové stránky.
EXTENT	Souvislá skupina osmi stránek, jako jsou data nebo indexové stránky.
HoBT 1	Halda nebo B-strom. Zámek chránící datové stránky B-tree (index) nebo haldy v tabulce, která nemá clusterovaný index.
TABLE 1	Celá tabulka, včetně všech dat a indexů.
FILE	Soubor databáze.
APPLICATION	Prostředek specifikovaný aplikací.
METADATA	Zámky metadat
ALLOCATION_UNIT	Alokační jednotka.
DATABASE	Celá databáze.
XACT 2	Zámek ID transakce (TID) použitý v Optimalizované uzamčení. Pro více informací viz uzamčení ID transakce (TID).

¹HoBT a zámky TABLE mohou být ovlivněny volbou LOCK_ESCALATIONALTER TABLE.

² Další prostředky zamykání jsou k dispozici pro uzamčení XACT, viz diagnostické doplňky pro optimalizované uzamčení.

Režimy uzamčení

Databázový stroj uzamkne prostředky pomocí různých režimů uzamčení, které určují, jak lze k prostředkům přistupovat souběžnými transakcemi.

Následující tabulka ukazuje režimy uzamčení prostředků, které databázový stroj používá.

Režim uzamčení	Popis
sdílené (S)	Používá se pro operace čtení, které nemění nebo neaktualizují data, jako je například příkaz SELECT.
Update (U)	Používá se u prostředků, které je možné aktualizovat. Zabrání běžné formě zablokování, ke kterému dochází, když více relací čte, uzamyká a potenciálně později aktualizuje prostředky.
Exclusive (X)	Používá se pro operace úpravy dat, jako jsou INSERT, UPDATEnebo DELETE. Zajišťuje, že více aktualizací nelze provést ve stejném prostředku současně.
Záměr	Používá se k vytvoření hierarchie zámků. Typy zámků úmyslu zahrnují: sdílený úmysl (IS), výhradní úmysl (IX) a sdílený s výhradním úmyslem (SIX).
Schéma	Používá se, když se provádí operace závislá na schématu tabulky. Typy zámků schématu jsou: úprava schématu (Sch-M) a stabilita schématu (Sch-S).
hromadná aktualizace (BU)	Používá se při hromadném kopírování dat do tabulky s ukazatelem TABLOCK.
rozsah klíčů	Chrání rozsah řádků přečtených dotazem při použití úrovně izolace transakce SERIALIZABLE. Zajišťuje, že ostatní transakce nemohou vkládat řádky, které by splňovaly kritéria dotazů transakce SERIALIZABLE, kdyby byly dotazy znovu spuštěny.

Sdílené zámky

Sdílené zámky (S) umožňují souběžným transakcím číst prostředek v rámci pesimistické kontroly souběžnosti. Žádné jiné transakce nemohou upravovat data, zatímco na prostředku existují sdílené zámky (S). Sdílené zámky (S) u prostředku se uvolní, jakmile se operace čtení dokončí, pokud není úroveň izolace transakce nastavená na REPEATABLE READ nebo vyšší, nebo pokud se použije uzamykací hint, který zachová sdílené zámky (S) po dobu trvání transakce.

Aktualizace zámků

Databázový stroj umístí uzamčení aktualizací (U), když se připravuje na provedení aktualizace. U zámky jsou kompatibilní se zámky S, ale pouze jedna transakce může v daném okamžiku držet U zámek na daném prostředku. Toto je klíč – mnoho souběžných transakcí může obsahovat S zámky, ale pouze jedna transakce může obsahovat U zámek prostředku. Zámky aktualizací (U) se nakonec upgradují na výhradní zámky (X), pro aktualizaci řádku.

Aktualizace (U) zámky lze provést také příkazy jiné než UPDATE, pokud upDLOCK rady tabulky je zadán v příkazu.

• Některé aplikace používají vzor "select a row, then update the row" (vybrat řádek a pak aktualizovat řádek), kde jsou čtení a zápis explicitně odděleny v rámci transakce. Pokud je úroveň izolace REPEATABLE READ nebo SERIALIZABLE, můžou souběžné aktualizace způsobit zablokování následujícím způsobem:

  Transakce načte data, získání sdíleného zámku (S) prostředku a potom upraví data, což vyžaduje převod zámku na výhradní zámek (X). Pokud dvě transakce získávají sdílené (S) zámky u prostředku a pak se pokusí aktualizovat data souběžně, jedna transakce se pokusí provést převod zámku na výhradní zámek (X). Převod zámku ze sdíleného na výhradní musí počkat, protože výhradní zámek (X) pro jednu transakci není kompatibilní se sdíleným zámkem (S) druhé transakce; dojde k čekání na zámek. Druhá transakce se pokusí získat výhradní zámek (X) pro jeho aktualizaci. Protože se obě transakce převádějí na výhradní zámky (X) a každá čeká, až druhá transakce uvolní svůj sdílený zámek (S), dojde k pattové situaci.

  Ve výchozí úrovni izolace READ COMMITTED mají zámky S krátkou dobu trvání a jsou uvolněny ihned po použití. I když je vzájemné zablokování popsané výše stále možné, s krátkodobými zámky je to mnohem méně pravděpodobné.

  Aby se zabránilo tomuto typu zablokování, mohou aplikace postupovat podle vzoru "vyberte řádek s nápovědou UPDLOCK a pak aktualizujte řádek".

• Pokud UPDLOCK tip je použit v zápisu při použití izolace SNAPSHOT, transakce musí mít přístup k nejnovější verzi řádku. Pokud už nejnovější verze není viditelná, je možné přijmout Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict. Jako příklad viz Práce s izolací snímků.

Exkluzivní zámky

Exkluzivní zámky (X) brání přístupu k prostředku souběžnými transakcemi. S výhradním zámkem (X) nemohou žádné jiné transakce upravovat data chráněná zámkem; Operace čtení mohou probíhat pouze s použitím NOLOCK nápovědy nebo úrovně izolace READ UNCOMMITTED.

Příkazy pro úpravy dat, jako jsou INSERT, UPDATEa DELETE kombinují operace čtení i úpravy. Příkaz nejprve provede operace čtení pro získání dat před provedením požadovaných operací úprav. Příkazy pro úpravu dat proto obvykle požadují sdílené zámky i exkluzivní zámky. Například příkaz UPDATE může upravit řádky v jedné tabulce na základě spojení s jinou tabulkou. V tomto případě příkaz UPDATE požaduje sdílené zámky na řádcích přečtených ve spojovací tabulce a také výhradní zámky na aktualizovaných řádcích.

Zámky úmyslu

Databázový stroj používá zámky záměrů k ochraně při umisťování sdíleného zámku (S) nebo výhradního zámku (X) na prostředky níže v hierarchii zámků. Zámky záměrů se takto nazývají, protože se získávají před zámky na nižší úrovni, čímž signalizují záměr umístit tyto zámky na nižší úroveň.

Zámky záměru slouží dvěma účelům:

• Aby se zabránilo jiným transakcím upravit zdroj vyšší úrovně tak, že by zneplatnily zámek na nižší úrovni.
• Zvýšení účinnosti databázového stroje při zjišťování konfliktů zámků na vyšší úrovni podrobnosti.

Například zámek pro sdílený záměr je vyžadován na úrovni tabulky předtím, než jsou na stránkách nebo řádcích v této tabulce vyžadovány sdílené zámky (S). Nastavení zámku záměru na úrovni tabulky zabrání dalším transakcím v následném získání výhradního zámku (X) na tabulce obsahující danou stránku. Zámky záměrů zlepšují výkon, protože databázový engine zkoumá zámky záměrů pouze na úrovni tabulky, aby zjistil, jestli může transakce bezpečně získat zámek na této tabulce. Tím se odebere požadavek na prozkoumání každého řádku nebo zámku stránky v tabulce a určení, jestli transakce může uzamknout celou tabulku.

Zámky záměru zahrnují záměr sdílený (IS), záměr výhradní (IX) a sdílený se záměrem výhradním (SIX).

Režim uzamčení	Popis
Sdílený záměr (IS)	Chrání požadované nebo získané sdílené zámky u některých (ale ne všech) prostředků nižších v hierarchii.
Exkluzivní záměr (IX)	Chrání požadované nebo získané exkluzivní zámky u některých (ale ne všech) prostředků v nižší části hierarchie. IX je nadmnožinou ISa také poskytuje ochranu při žádání o sdílené zámky na prostředcích nižší úrovně.
SIX Sdíleno s výhradním záměrem ()	Chrání požadované nebo získané sdílené zámky u všech prostředků nižších v hierarchii a záměrně exkluzivní zámky u některých (ale ne všech) prostředků nižší úrovně. Souběžné IS zámky na nejvyšší úrovni prostředku jsou povolené. Například získání zámku SIX v tabulce také získá výhradní zámky na stránkách, které se upravují, a exkluzivní zámky na upravených řádcích. Najednou může existovat pouze jeden zámek SIX na prostředek, který brání aktualizacím prostředku provedeným jinými transakcemi, ačkoli ostatní transakce mohou číst prostředky na nižší úrovni hierarchie získáním zámků IS na úrovni tabulky.
Aktualizace záměru (IU)	Chrání požadované nebo získané zámky aktualizací u všech prostředků nižších v hierarchii. IU zámky se používají jenom u prostředků stránky. IU zámky se převedou na zámky IX, pokud dojde k operaci aktualizace.
SIU aktualizace sdíleného záměru ()	Kombinace S a IU zámků v důsledku samostatného získání těchto zámků a současného držení obou zámků. Transakce například provede dotaz s nápovědou PAGLOCK a pak provede operaci aktualizace. Dotaz s nápovědou PAGLOCK získá zámek S a operace aktualizace získá zámek IU.
výhradní záměr aktualizace (UIX)	Kombinace U a IX zámků v důsledku samostatného získání těchto zámků a současného držení obou zámků.

Zámky schématu

Databázový stroj používá zámky úpravy schématu (Sch-M) během operace DDL (jazyk Data Definition Language), například při přidávání sloupce nebo vyřazování tabulky. Po dobu, po kterou je uchováván zámek Sch-M, zabraňuje souběžný přístup k tabulce. To znamená, že zámek Sch-M blokuje všechny vnější operace, dokud se zámek nevolní.

Některé operace jazyka DML (Data Manipulat Language), jako je například zkrácení tabulky, používají zámky Sch-M, aby se zabránilo přístupu k ovlivněným tabulkám souběžnými operacemi.

Databázový stroj používá při kompilaci a spouštění dotazů zámky stability schématu (Sch-S). Sch-S zámky neblokují žádné transakční zámky, včetně výhradních zámků (X). Proto ostatní transakce, včetně těch, které mají X zámky na tabulce, pokračují v běhu, zatímco se dotaz kompiluje. Souběžné operace DDL a souběžné operace DML, které získávají Sch-M zámky, jsou však blokované Sch-S zámky.

Hromadná aktualizace zámků

Zámky hromadné aktualizace (BU) umožňují více vláknům hromadně načítat data současně do stejné tabulky, přičemž zabraňují přístupu ostatním procesům, které nehromadně načítají data, k této tabulce. Databázový stroj používá hromadnou aktualizaci (BU), pokud platí obě následující podmínky.

• K hromadnému kopírování dat do tabulky použijete příkaz Transact-SQL BULK INSERT nebo funkci OPENROWSET(BULK) nebo použijete jeden z příkazů rozhraní API hromadného vkládání, jako je .NET SqlBulkCopy, rozhraní OLEDB FAST Load API nebo rozhraní ODBC Bulk Copy API.
• Je zadána TABLOCK nápověda nebo je možnost table lock on bulk load tabulky nastavena pomocí sp_tableoption.

Spropitné

Na rozdíl od příkazu BULK INSERT, který obsahuje méně omezující zámek hromadné aktualizace (BU), INSERT INTO...SELECT s nápovědou TABLOCK obsahuje u tabulky zámek záměru výhradní (IX). To znamená, že řádky nelze vložit pomocí paralelních operací vložení.

Zámky rozsahu klíčů

Zámky rozsahu klíčů chrání rozsah řádků, které jsou implicitně zahrnuty v sadě záznamů čtené příkazem Transact-SQL při použití transakční izolační úrovně SERIALIZABLE. Uzamykání rozsahu klíčů zabraňuje fantomovým čtením. Tím, že chrání rozsahy klíčů mezi řádky, zabraňuje také fiktivním vkladům nebo odstraněním do sady záznamů, ke které přistupuje transakce.

Kompatibilita zámků

Kompatibilita zámků určuje, zda mohou více transakcí současně získat zámky na stejném prostředku ve stejný čas. Pokud je prostředek již uzamčen jinou transakcí, může být nová žádost o zámek udělena pouze v případě, že režim požadovaného zámku je kompatibilní s režimem existujícího zámku. Pokud režim požadovaného zámku není kompatibilní s existujícím zámkem, transakce požadující nový zámek čeká na uvolnění stávajícího zámku nebo vypršení časového limitu uzamčení. Například žádné režimy uzamčení nejsou kompatibilní s exkluzivními zámky. Pokud je držen výhradní zámek (X), žádná jiná transakce nemůže získat zámek jakéhokoli druhu (sdílený, aktualizační nebo výhradní) na tento prostředek, dokud se tento výhradní zámek (X) neuvolní. Naopak platí, že pokud byl u prostředku použit sdílený zámek (S), můžou jiné transakce také získat sdílený zámek nebo aktualizaci (U) u daného prostředku, i když se první transakce nedokončila. Jiné transakce však nemohou získat výhradní zámek, dokud nebude uvolněn sdílený zámek.

Následující tabulka ukazuje kompatibilitu nejběžnějších režimů uzamčení.

Existující udělený režim	IS	S	U	IX	SIX	X
požadovaný režim
Sdílený záměr (IS)	Ano	Ano	Ano	Ano	Ano	Ne
sdílené (S)	Ano	Ano	Ano	Ne	Ne	Ne
Update (U)	Ano	Ano	Ne	Ne	Ne	Ne
Exkluzivní záměr (IX)	Ano	Ne	Ne	Ano	Ne	Ne
SIX Sdíleno s výhradním záměrem ()	Ano	Ne	Ne	Ne	Ne	Ne
Exclusive (X)	Ne	Ne	Ne	Ne	Ne	Ne

Poznámka

Exkluzivní zámek záměru (IX) je kompatibilní s režimem zámku IX, protože IX znamená, že záměrem je aktualizovat pouze některé řádky, nikoli všechny. Jiné transakce, které se pokusí číst nebo aktualizovat některé řádky, jsou také povoleny, pokud nejsou stejnými řádky, které jsou aktualizovány jinými transakcemi. Dále platí, že pokud se dvě transakce pokusí aktualizovat stejný řádek, obě transakce mají udělen zámek IX na úrovni tabulek a stránek. Je však udělen zámek X na úrovni řádku jedné transakci. Druhá transakce musí počkat, až se řádkový zámek odebere.

Pomocí následující tabulky určete kompatibilitu všech režimů uzamčení dostupných v databázovém stroji.

Klíč	Popis
N	Žádný konflikt
Já	Ilegální
C	Konflikt
NL	Bez zámku
SCH-S	Zámek stability schématu
SCH-M	Zámek změny schématu
S	Společný
U	Aktualizace
X	Výhradní
JE	Sdílený záměr
IU	Aktualizace záměru
IX	Výhradní záměr
SIU	Sdílení s aktualizací záměru
ŠEST	Sdílet s výhradním záměrem
UIX	Aktualizace s výhradním záměrem
BU	Hromadná aktualizace
RS-S	Sdílený rozsah
RS-U	Aktualizace sdíleného rozsahu
RI-N	Vložení hodnoty range-null
RI-S	Vložte sdílený rozsah
RI-U	Vložit aktualizaci rozsahu
RI-X	Vložit exkluzivní rozsah
RX-S	Exkluzivní sdílení rozsahu
RX-U	Exkluzivní rozsahová aktualizace
RX-X	Exkluzivní řada-exkluzivní

Uzamykání rozsahu klíčů

Zámky rozsahu klíčů chrání rozsah řádků, které jsou implicitně zahrnuty v sadě záznamů čtené příkazem Transact-SQL při použití transakční izolační úrovně SERIALIZABLE. Úroveň izolace SERIALIZABLE vyžaduje, aby každý dotaz spuštěný během transakce musel získat stejnou sadu řádků při každém spuštění během transakce. Zámek rozsahu klíčů splňuje tento požadavek tím, že brání ostatním transakcím v vkládání nových řádků, jejichž klíče spadají do rozsahu klíčů přečtených SERIALIZABLE transakcí.

Uzamykání rozsahu klíčů zabraňuje fantomovým čtením. Při ochraně rozsahů klíčů mezi řádky také zabraňuje phantomovým vložením do sady záznamů, ke které přistupuje transakce.

Zámek rozsahu klíčů je umístěn na indexu a určuje počáteční a koncovou hodnotu klíče. Tento zámek blokuje všechny pokusy o vložení, aktualizaci nebo odstranění libovolného řádku s hodnotou klíče, která spadá do rozsahu, protože tyto operace by nejprve musely získat zámek v indexu. Například transakce SERIALIZABLE může vydat příkaz SELECT, který čte všechny řádky, jejichž hodnoty klíče odpovídají podmínce BETWEEN 'AAA' AND 'CZZ'. Zámek rozsahu klíčů na hodnotách klíče v rozsahu od 'AAA' do 'CZZ' zabraňuje jiným transakcím vkládat řádky s hodnotami klíčů kdekoli v tomto rozsahu, například 'ADG', 'BBD', nebo 'CAL'.

Režimy zámku rozsahu klíčů

Zámky rozsahu klíčů zahrnují oblast i součást řádku zadanou ve formátu řádku rozsahu:

• Rozsah představuje režim uzamčení, který chrání rozsah mezi dvěma po sobě jdoucími položkami indexu.
• Řádek představuje režim uzamčení, který chrání záznam v rejstříku.
• Režim představuje kombinovaný režim uzamčení, který se používá. Režimy uzamčení rozsahu klíčů se skládají ze dvou částí. První představuje typ zámku použitého k uzamčení rozsahu indexu (rozsahT) a druhý představuje typ zámku, který slouží k uzamčení konkrétního klíče (K). Tyto dvě části jsou spojeny s pomlčkou (-), například oblastT-K.

Rozmezí	Řada	Režim	Popis
RangeS	S	RangeS-S	Sdílený rozsah, zámek sdíleného prostředku; prohledávání rozsahu SERIALIZABLE.
RangeS	U	RangeS-U	Sdílený rozsah, aktualizace zámku zdrojů; SERIALIZABLE skenování aktualizací.
RangeI	Null	RangeI-N	Vložit rozsah, zámek prostředku null; slouží k testování rozsahů před vložením nového klíče do indexu.
RangeX	X	RangeX-X	Exkluzivní rozsah, exkluzivní zamykání zdrojů; používá se při aktualizaci klíče v daném rozsahu.

Poznámka

Interní režim uzamčení Null je kompatibilní se všemi ostatními režimy uzamčení.

Režimy uzamčení rozsahu klíčů mají matici kompatibility, která ukazuje, které zámky jsou kompatibilní s jinými zámky získanými na překrývajících se klávesách a rozsazích.

Existující udělený režim	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
požadovaný režim
sdílené (S)	Ano	Ano	Ne	Ano	Ano	Ano	Ne
Update (U)	Ano	Ne	Ne	Ano	Ne	Ano	Ne
Exclusive (X)	Ne	Ne	Ne	Ne	Ne	Ano	Ne
RangeS-S	Ano	Ano	Ne	Ano	Ano	Ne	Ne
RangeS-U	Ano	Ne	Ne	Ano	Ne	Ne	Ne
RangeI-N	Ano	Ano	Ano	Ne	Ne	Ano	Ne
RangeX-X	Ne	Ne	Ne	Ne	Ne	Ne	Ne

Zámky převodu

Zámky převodu se vytvoří, když zámek rozsahu klíčů překrývá jiný zámek.

Zámek 1	Zámek 2	Zámek převodu
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

Zámky převodu se dají pozorovat po krátkou dobu za různých složitých okolností, někdy při spouštění souběžných procesů.

Serializovatelný rozsah skenu, jednotlivé načtení, odstranění a vložení

Zamykání rozsahu klíčů zajišťuje, že lze serializovat následující operace:

• Dotaz na prohledávání rozsahu
• Jednorázové načtení neexistujícího řádku
• Operace odstranění
• Operace vložení

Před uzamčením rozsahu klíčů musí být splněny následující podmínky:

• Úroveň izolace transakce musí být nastavena na SERIALIZABLE.
• Procesor dotazů musí k implementaci predikátu filtru rozsahu použít index. Klauzule WHERE v příkazu SELECT může například vytvořit podmínku rozsahu s tímto predikátem: ColumnX BETWEEN N'AAA' AND N'CZZ'. Zámek rozsahu klíčů lze získat pouze v případě, že ColumnX je pokryt indexovým klíčem.

Příklady

Následující tabulka a index se používají jako základ pro následující příklady uzamčení rozsahu klíčů.

Dotaz na prohledávání rozsahu

Aby se zajistilo, že dotaz prohledávání rozsahu je serializovatelný, měl by stejný dotaz vrátit stejné výsledky při každém spuštění v rámci stejné transakce. Nové řádky nesmí být vloženy do dotazu prohledávání rozsahu jinými transakcemi; jinak se z nich stanou fantomové vložky. Následující dotaz například používá tabulku a index na předchozím obrázku:

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Zámky na rozsah klíčů jsou umístěny na položky indexu odpovídající rozsahu řádků, kde je název mezi hodnotami Adam a Dale, čímž zamezují přidání nebo odstranění řádků kvalifikujících v předchozím dotazu. I když je křestní jméno v této oblasti Adam, zámek rozsahu klíčů v režimu RangeS-S u této položky indexu zajišťuje, že před Anelze přidat žádné nové názvy začínající písmenem Adam , například Abigail. Podobně zámek rozsahu klíčů RangeS-S u položky rejstříku pro Dale zajišťuje, že nelze po Cpřidat žádné nové názvy začínající písmenem Carlos, například Clive.

Poznámka

Počet zámků RangeS-S, které jsou drženy, je n+1, kde n představuje počet řádků, které splňují dotaz.

Singleton načítá neexistující data

Pokud se dotaz v rámci transakce pokusí vybrat řádek, který neexistuje, vydání dotazu v pozdějším bodě v rámci stejné transakce musí vrátit stejný výsledek. Žádná jiná transakce nesmí být povolena k vložení tohoto neexistujícího řádku. Například vzhledem k tomuto dotazu:

SELECT name
FROM mytable
WHERE name = 'Bill';

Zámek rozsahu klíčů je umístěn na položku indexu odpovídající rozsahu názvů od Ben do Bing, protože název Bill by se vložil mezi tyto dvě sousední položky indexu. Zámek klíčového rozsahu RangeS-S je umístěn na položku indexu Bing. Tím zabráníte jiným transakcím vkládat hodnoty, jako je například Bill, mezi položky indexu Ben a Bing.

Operace odstranění bez optimalizovaného uzamčení

Když odstraníte řádek v rámci transakce, oblast, do které řádek spadá, nemusí být uzamčena po dobu trvání transakce provádějící operaci odstranění. Uzamčení odstraněné hodnoty klíče až do konce transakce je dostačující pro zachování serializovatelnosti. Například vzhledem k tomuto příkazu DELETE:

DELETE mytable
WHERE name = 'Bob';

Výhradní zámek (X) je umístěn na položku rejstříku odpovídající názvu Bob. Jiné transakce mohou vložit nebo odstranit hodnoty před nebo za řádek s hodnotou Bob, která je odstraněna. Nicméně všechny transakce, které se pokusí číst, vložit nebo odstranit řádky odpovídající hodnotě Bob, jsou blokovány, dokud transakce mazání není buď potvrzena, nebo vrácena zpět. (Možnost databáze READ_COMMITTED_SNAPSHOT a úroveň izolace SNAPSHOT také umožňují čtení z verze řádku stavu dříve potvrzeného.)

Odstranění rozsahu lze provést pomocí tří základních režimů uzamčení: řádek, stránka nebo zámek tabulky. Strategii zamykání řádků, stránek nebo tabulek rozhoduje optimalizátor dotazů nebo může uživatel určit pomocí tipů optimalizátoru dotazů, jako jsou ROWLOCK, PAGLOCKnebo TABLOCK. Při použití PAGLOCK nebo TABLOCK databázový stroj okamžitě uvolní indexovou stránku, pokud se z této stránky odstraní všechny řádky. Naproti tomu při použití ROWLOCK jsou všechny odstraněné řádky označeny pouze jako odstraněné; později se z indexové stránky odeberou pomocí úlohy na pozadí.

Operace odstranění s optimalizovaným uzamčením

Při odstraňování řádku v rámci transakce se zámky řádků a stránek získávají a uvolňují postupně, a nejsou drženy po celou dobu trvání transakce. Například vzhledem k tomuto příkazu DELETE:

DELETE mytable
WHERE name = 'Bob';

Zámek TID je umístěn na všech upravených řádcích po dobu trvání transakce. Zámek je získán v TID řádků indexu odpovídající hodnotě Bob. Při optimalizovaném uzamčení se zámky stránek a řádků i nadále uplatňují pro aktualizace, ale každý zámek stránky a řádku se uvolní, jakmile se příslušný řádek aktualizuje. Zámek TID chrání řádky před aktualizací, dokud transakce nebude dokončena. Všechny transakce, které se pokusí číst, vložit nebo odstranit řádky s hodnotou Bob, jsou blokovány, dokud odstraňující transakce buď nepotvrdí, nebo nevrátí zpět. (Možnost databáze READ_COMMITTED_SNAPSHOT a úroveň izolace SNAPSHOT také umožňují čtení z verze řádku stavu dříve potvrzeného.)

Jinak je mechanismus uzamčení operace odstranění stejný jako bez optimalizovaného uzamčení.

Operace vložení bez optimalizovaného uzamčení

Při vkládání řádku do transakce nemusí být oblast, do které řádek spadá, uzamčena po dobu trvání transakce provádějící operaci vložení. Uzamčení vložené hodnoty klíče až do konce transakce je dostačující pro zachování serializovatelnosti. Například vzhledem k tomuto příkazu INSERT:

INSERT mytable VALUES ('Dan');

Zámek klíčového rozsahu v režimu RangeI-N je umístěn na řádku indexu odpovídajícím názvu David, aby bylo možné otestovat rozsah. Pokud je zámek udělený, vloží se řádek s hodnotou Dan a na vložený řádek se umístí výhradní zámek (X). Zámek RangeI-N režimu klíč-rozsah je nezbytný pouze k otestování rozsahu a není uložen po dobu trvání transakce provádějící operaci vložení. Jiné transakce mohou vložit nebo odstranit hodnoty před nebo za vložený řádek s hodnotou Dan. Nicméně všechny transakce, které se pokoušejí číst, vložit nebo odstranit řádek s hodnotou Dan, jsou blokovány, dokud transakce vkládající buď nepotvrdí, nebo nevrátí zpět.

Operace vložení s optimalizovaným uzamykáním

Při vkládání řádku do transakce nemusí být oblast, do které řádek spadá, uzamčena po dobu trvání transakce provádějící operaci vložení. Zámky řádků a stránek jsou získány jen zřídka, a to pouze tehdy, když probíhá online opětovné sestavení indexu, nebo když existují souběžné transakce typu SERIALIZABLE. Pokud jsou získány zámky řádků a stránek, uvolní se rychle a nejsou drženy po dobu trvání transakce. Umístění výhradního zámku TID na vloženou hodnotu klíče až do konce transakce je dostačující pro zachování serializovatelnosti. Například vzhledem k tomuto příkazu INSERT:

INSERT mytable VALUES ('Dan');

S optimalizovaným uzamčením je zámek RangeI-N získán pouze v případě, že v instanci existuje alespoň jedna transakce, která používá úroveň izolace SERIALIZABLE. Zámek klíčového rozsahu v režimu RangeI-N je umístěn na řádku indexu odpovídajícím názvu David, aby bylo možné otestovat rozsah. Pokud je zámek udělený, vloží se řádek s hodnotou Dan a na vložený řádek se umístí výhradní zámek (X). Zámek RangeI-N režimu klíč-rozsah je nezbytný pouze k otestování rozsahu a není uložen po dobu trvání transakce provádějící operaci vložení. Jiné transakce mohou vložit nebo odstranit hodnoty před nebo za vložený řádek s hodnotou Dan. Nicméně všechny transakce, které se pokoušejí číst, vložit nebo odstranit řádek s hodnotou Dan, jsou blokovány, dokud transakce vkládající buď nepotvrdí, nebo nevrátí zpět.

Eskalace zámku

Eskalace zámků je proces převodu mnoha jemnozrnných zámků na méně hrubozrnných zámků, čímž dojde ke snížení systémové režie a zároveň ke zvýšení pravděpodobnosti kolizí při souběžném zpracování.

Eskalace zámků se chová jinak v závislosti na tom, jestli je povolené optimalizované uzamčení.

Eskalace zámku bez optimalizovaného uzamčení

Vzhledem k tomu, že databázový stroj získává zámky nízké úrovně, umístí také záměrové zámky na objekty, které obsahují objekty nižší úrovně:

• Při zamknutí řádků nebo rozsahů klíčů indexu umístí databázový stroj na stránky, které obsahují řádky nebo klíče, zámek záměru.
• Při zamknutí stránek databázový stroj umístí zámek záměru na objekty vyšší úrovně, které obsahují stránky. Kromě zámku záměru na objektu jsou zámky stránek záměru požadovány u následujících objektů:
  • Stránky na úrovni listu neklastrovaných indexů
  • Datové stránky clusterovaných indexů
  • Datové stránky haldy

Databázový stroj může provést uzamčení řádků i stránek pro stejný příkaz, aby se minimalizoval počet zámků a snížila pravděpodobnost, že je potřeba eskalaci zámku. Databázový stroj může například umístit zámky stránek do neclusterovaného indexu (pokud je pro splnění dotazu vybráno dostatek souvislých klíčů v uzlu indexu) a zámky řádků v clusterovém indexu nebo haldě.

Chcete-li eskalovat zámky, databázový stroj se pokusí změnit zámek záměru na tabulce na odpovídající úplný zámek, například změnou zámku záměru výhradního (IX) na výhradní zámek (X) nebo zámku záměru sdíleného (IS) na sdílený zámek (S). Pokud pokus o eskalaci zámku proběhne úspěšně a získá se zámek celé tabulky, všechny zámky typu HoBT, zámky na úrovni stránky (PAGE) nebo na úrovni řádků (RID, KEY), které jsou drženy transakcí na haldě nebo indexu, se uvolní. Pokud se úplný zámek nedá získat, neprobíná se v té době žádná eskalace zámku a databázový stroj bude dál získávat zámky řádků, klíčů nebo stránek.

Databázový stroj neeskaluje zámky řádků nebo rozsahů klíčů na zámky stránek, ale přímo na zámky tabulek. Podobně se zámky stránek vždy eskalují na zámky tabulek. Uzamčení particionovaných tabulek může být místo zámku celé tabulky eskalováno na úroveň HoBT pro příslušný oddíl. Zámek na úrovni HoBT nemusí nutně zamknout přiřazené HoBT pro oddíl.

Poznámka

Zámky na úrovni HoBT obvykle zvyšují souběžnost, ale představují potenciál zablokování, když transakce, které zamykají různé oddíly, chtějí rozšířit své exkluzivní zámky na ostatní oddíly. Ve výjimečných případech může granularita zamykání TABLE fungovat lépe.

Pokud pokus o eskalaci zámku selže kvůli konfliktním zámkům uchovávaným souběžnými transakcemi, databázový stroj opakuje eskalaci zámku pro každý dalších 1 250 zámků získaných transakcí.

Každá událost eskalace funguje primárně na úrovni jednoho příkazu Transact-SQL. Při spuštění události se databázový stroj pokusí eskalovat všechny zámky vlastněné aktuální transakcí v kterékoli z tabulek, na které odkazuje aktivní příkaz za předpokladu, že splňuje požadavky na prahovou hodnotu eskalace. Pokud se událost eskalace spustí před tím, než příkaz přejde k tabulce, nebude proveden žádný pokus o eskalaci zámků v této tabulce. Pokud se eskalace zámku uskuteční, dojde k eskalaci všech zámků získaných transakcí v předchozím příkazu, které jsou stále drženy v době, kdy událost začíná, pokud je tabulka odkazována aktuálním příkazem a je zahrnuta do eskalační události.

Předpokládejme například, že relace provádí tyto operace:

• Zahájí transakci.
• Aktualizuje TableA. Tím se vygenerují exkluzivní zámky řádků v TableA, které se uchovávají, dokud transakce nebude dokončena.
• Aktualizuje TableB. Tím se vygenerují exkluzivní zámky řádků v TableB, které se uchovávají, dokud transakce nebude dokončena.
• Provede SELECT, které spojí TableA s TableC. Plán provádění dotazu určuje, že řádky se mají načíst z TableA před načtením řádků z TableC.
• Příkaz SELECT aktivuje eskalaci zámku při načítání řádků z TableA a před přístupem k TableC.

Pokud eskalace zámku proběhne úspěšně, eskalují se pouze zámky uchovávané relací na TableA. To zahrnuje sdílené zámky z příkazu SELECT i výhradní zámky z předchozího příkazu UPDATE. Zatímco se počítají pouze zámky, které relace získala v TableA pro příkaz SELECT, aby bylo možné určit, jestli má být provedena eskalace zámku, jakmile je eskalace úspěšná, všechny zámky držené relací v TableA jsou eskalovány na výhradní zámek v tabulce a všechny ostatní zámky s nižší granularitou, včetně záměrných zámků na TableA, se uvolní.

Není proveden žádný pokus o eskalaci zámků na TableB, protože v příkazu TableB nebyl žádný aktivní odkaz na SELECT. Podobně nejsou podniknuty žádné pokusy o eskalaci zámků na TableC, které nejsou eskalovány, protože k nim dosud nedošlo přistoupení, když k eskalaci došlo.

Eskalace zámku s optimalizovaným uzamčením

Optimalizované uzamykání pomáhá snížit paměť uzamčení, protože po dobu trvání transakce se uchovává velmi málo zámků. S tím, jak databázový stroj získává zámky řádků a stránek, může dojít k eskalaci zámku podobně, ale mnohem méně často. Optimalizované uzamykání obvykle proběhne úspěšně, protože se zabrání eskalaci zámků, sníží počet zámků a množství potřebné paměti zámku.

Pokud je povolené optimalizované uzamčení a ve výchozí úrovni izolace READ COMMITTED, databázový stroj uvolní zámky řádků a stránek, jakmile je řádek upraven. Po dobu trvání transakce se neuchovávají žádné zámky řádků a stránek, s výjimkou jednoho zámku ID transakce (TID). Tím se snižuje pravděpodobnost eskalace zámku.

Prahové hodnoty eskalace zámku

Eskalace zámku se aktivuje, když není eskalace zámku v tabulce zakázaná pomocí možnosti ALTER TABLE SET LOCK_ESCALATION a pokud existuje některý z následujících podmínek:

• Jeden příkaz Transact-SQL získá alespoň 5 000 zámků na jedné neparticionované tabulce nebo indexu.
• Jeden příkaz Transact-SQL získá alespoň 5 000 zámků v jedné části rozdělené tabulky a možnost ALTER TABLE SET LOCK_ESCALATION je nastavená na AUTO.
• Počet zámků v instanci databázového stroje překračuje limity paměti nebo konfigurace.

Pokud zámky nelze eskalovat kvůli konfliktům zámků, databázový stroj pravidelně aktivuje eskalaci zámku při každém získaném 1 250 nových zámkech.

Prahová hodnota eskalace pro příkaz Transact-SQL

Když databázový stroj zkontroluje možné eskalace při každých 1 250 nově získaných zámkech, dojde k eskalaci zámku, pokud a pouze v případě, že příkaz Transact-SQL získal alespoň 5 000 zámků na jednom odkazu na tabulku. Eskalace zámku se aktivuje, když příkaz Transact-SQL získá alespoň 5 000 zámků na jednom záznamu tabulky. Například eskalace zámku se neaktivuje, pokud příkaz získá 3 000 zámků v jednom indexu a 3 000 zámků v jiném indexu stejné tabulky. Podobně se eskalace zámků neaktivuje, pokud má příkaz v tabulce vlastní spojení a každý odkaz na tabulku získá pouze 3 000 zámků v tabulce.

Eskalace zámku se provádí pouze u tabulek, ke kterým se přistupuje v době, kdy se eskalace aktivuje. Předpokládejme, že jeden příkaz SELECT je spojení, které přistupuje ke třem tabulkám v této sekvenci: TableA, TableBa TableC. Příkaz získá 3 000 zámků řádků v clusterovém indexu pro TableA a minimálně 5 000 zámků řádků v clusterovém indexu pro TableB, ale ještě nemá přístup k TableC. Když databázový stroj detekuje, že příkaz získal alespoň 5 000 zámků řádků v TableB, pokusí se eskalovat všechny zámky držené aktuální transakcí na TableB. Snaží se také eskalovat všechny zámky uchovávané aktuální transakcí na TableA, ale vzhledem k tomu, že počet zámků na TableA je menší než 5 000, eskalace nebude úspěšná. U TableC se nepokoušela žádná eskalace zámků, protože se k ní ještě nepřistupovalo, když došlo k eskalaci.

Prahová hodnota eskalace instance databázového stroje

Kdykoli je počet zámků větší než prahová hodnota paměti pro eskalaci zámku, databázový stroj aktivuje eskalaci zámku. Prahová hodnota paměti závisí na nastavení zámků konfiguračního nastavení:

• Pokud je možnost locks nastavená na výchozí nastavení 0, dosáhne se prahová hodnota eskalace zámku, pokud je paměť používaná objekty zámků 24 procent paměti používané databázovým strojem, s výjimkou paměti AWE. Datová struktura používaná k reprezentaci zámku je přibližně 100 bajtů dlouhá. Tato prahová hodnota je dynamická, protože databázový stroj dynamicky získává a uvolní paměť, aby se přizpůsobil různým úlohám.

• Pokud je možnost locks jinou hodnotou než 0, je prahová hodnota eskalace zámku 40 procent (nebo méně, pokud je tlak paměti) hodnoty možnosti zámků.

Databázový stroj může zvolit libovolný aktivní příkaz z jakékoli relace pro eskalaci a pro každých 1 250 nových zámků zvolí příkazy pro eskalaci, pokud paměť zámku použitá v instanci zůstane nad prahovou hodnotou.

Eskalace zámku se smíšenými typy zámků

Při zvýšení úrovně zámku je zámek vybraný pro haldu nebo index dostatečně silný, aby splňoval požadavky nejpřísněji omezujícího zámku na nižší úrovni.

Předpokládejme například relaci:

• Zahájí transakci.
• Aktualizuje tabulku obsahující clusterovaný index.
• Vydá příkaz SELECT, který odkazuje na stejnou tabulku.

Příkaz UPDATE získá tyto zámky:

• Exkluzivní zámky (X) na aktualizovaných řádcích dat.
• Výhradní záměr (IX) se uzamkne na clusterovaných indexových stránkách obsahujících tyto řádky.
• Jeden zámek IX na clusterovaném indexu a další na tabulce.

Příkaz SELECT získá tyto zámky:

• Sdílené (S) se zamkne na všech řádcích dat, které čte, pokud už není řádek chráněný X zámkem z příkazu UPDATE.
• Záměr sdílený (IS) se zamkne na všech stránkách clusterovaného indexu obsahujících tyto řádky, pokud už není stránka chráněna zámkem IX.
• U clusterovaného indexu nebo tabulky není žádný zámek, protože jsou již chráněny zámky IX.

Pokud příkaz SELECT získá dostatek zámků pro aktivaci eskalace zámku a eskalace proběhne úspěšně, zámek tabulky IX se převede na zámek X, přičemž se uvolní všechny zámky na řádcích, stránkách a indexech. Aktualizace i čtení jsou chráněny zámkem X na tabulce.

Omezení zamykání a eskalace zámku

Ve většině případů databázový stroj poskytuje nejlepší výkon při provozu s výchozím nastavením pro uzamčení a eskalaci zámku.

• Využijte optimalizované uzamykání.

  • optimalizované uzamčení nabízí vylepšený mechanismus uzamykání transakcí, který snižuje spotřebu paměti a blokuje souběžné transakce. Eskalace zámků je mnohem méně pravděpodobná, když je povolené optimalizované uzamčení.
  • Nepoužívejte tabulková doporučení s optimalizovaným uzamčením. Rady tabulky mohou snížit efektivitu optimalizovaného uzamčení.
  • Povolte možnost READ_COMMITTED_SNAPSHOT pro databázi, abyste co nejvíce využili optimalizovaného uzamčení. Toto je výchozí hodnota ve službě Azure SQL Database.
  • Optimalizované zamykání vyžaduje, aby bylo v databázi povoleno zrychlené obnovení databáze (ADR).

Pokud instance databázového stroje generuje velké množství zámků a dochází k častým eskalacím zámků, zvažte snížení počtu uzamčení pomocí následujících strategií:

• Použijte úroveň izolace, která negeneruje sdílené zámky pro operace čtení:

  • READ COMMITTED úroveň izolace, pokud je možnost databáze READ_COMMITTED_SNAPSHOTON.
  • Úroveň izolace SNAPSHOT.
  • Úroveň izolace READ UNCOMMITTED. To lze použít pouze pro systémy, které mohou pracovat s nečistými čteními.

• Pomocí nápovědy k tabulce PAGLOCK nebo TABLOCK zajistěte, aby databázový stroj používal zámky stránky, haldy nebo indexu místo zámků nízké úrovně. Tato možnost však zvyšuje problémy uživatelů blokujících jiné uživatele, kteří se pokoušejí o přístup ke stejným datům, a neměli byste je používat v systémech s více než několika souběžnými uživateli.

• Pokud optimalizované uzamčení není k dispozici pro dělené tabulky, použijte LOCK_ESCALATION možnost ALTER TABLE k eskalaci zámků do oddílu místo tabulky nebo k zakázání eskalace zámku pro tabulku.

• Rozdělte velké dávkové operace do několika menších operací. Předpokládejme například, že jste spustili následující dotaz, který z tabulky auditu odebral několik stovek tisíc starých řádků, a pak jste zjistili, že to způsobilo eskalaci zámku, která blokovala ostatní uživatele:

  DELETE FROM LogMessages
  WHERE LogDate < '2024-09-26'
  

  Odebráním těchto řádků po několika stech najednou můžete výrazně snížit počet zámků, které se hromadí na každou transakci, a zabránit eskalaci zámků. Například:

  DECLARE @DeletedRows int;
  
  WHILE @DeletedRows IS NULL OR @DeletedRows > 0
  BEGIN
      DELETE TOP (500)
      FROM LogMessages
      WHERE LogDate < '2024-09-26'
  
      SELECT @DeletedRows = @@ROWCOUNT;
  END;
  

• Zmenšete zátěž uzamčení dotazu tím, že dotaz uděláte co nejefektivnější. Velké kontroly nebo velký počet vyhledávání klíčů mohou zvýšit pravděpodobnost eskalace zámku; navíc zvyšuje pravděpodobnost zablokování a obecně nepříznivě ovlivňuje souběžnost a výkon. Jakmile najdete dotaz, který způsobuje eskalaci zámku, hledejte příležitosti pro vytvoření nových indexů nebo přidání sloupců do existujícího indexu, abyste eliminovali plné prohledávání indexů nebo tabulek a maximalizovali efektivitu vyhledávání indexů. Zvažte použití Advisoru pro ladění databázového stroje k provedení automatické analýzy indexu pro dotaz. Další informace naleznete v tématu Kurz: Poradce pro ladění databázového stroje. Jedním z cílů této optimalizace je zajistit, aby index vracel co nejméně řádků, a tím minimalizovat náklady na vyhledávání klíčů (maximalizovat selektivitu indexu pro konkrétní dotaz). Pokud databázový stroj odhaduje, že logický operátor vyhledávání klíčů může vrátit mnoho řádků, může k provedení vyhledávání použít optimalizaci předběžného načtení. Pokud databázový server používá předběžné načítání pro vyhledávání, musí zvýšit úroveň izolace transakcí pro určitou část dotazu na REPEATABLE READ. To znamená, že to, co může vypadat podobně jako příkaz SELECT na úrovni izolace READ COMMITTED, může získat mnoho tisíc klíčových zámků (na clusterovém indexu i v jednom neclusterovaném indexu), což může způsobit, že takový dotaz překročí prahové hodnoty pro eskalaci zámků. To je zvlášť důležité, pokud zjistíte, že eskalovaný zámek je zámek sdílené tabulky, který se ale běžně nezobrazuje na výchozí úrovni izolace READ COMMITTED.

  Pokud vyhledání klíče s optimalizací předběžného načtení způsobuje eskalaci zamykání, zvažte přidání dalších sloupců do neclusterovaného indexu, který se objevuje ve vyhledání indexu nebo ve skenu indexu pod logickým operátorem vyhledání klíče v plánu dotazu. Je možné vytvořit krytý index (index, který zahrnuje všechny sloupce v tabulce použité v dotazu), nebo alespoň index, který pokrývá sloupce použité pro kritéria spojení nebo v klauzuli WHERE, pokud je zahrnutí všeho v seznamu sloupců SELECT nepraktické. Spojení vnořené smyčky může také využívat optimalizaci pro předběžné načítání, což vede ke stejnému chování uzamčení.

• Eskalace zámku nemůže nastat, pokud jiný identifikátor SPID aktuálně drží nekompatibilní zámek tabulky. Eskalace zámku vždy vede k zámku tabulky a nikdy ke zámkům stránek. Pokud navíc pokus o eskalaci zámku selže, protože jiný identifikátor SPID obsahuje nekompatibilní zámek tabulky, dotaz, který se pokusil o eskalaci, neblokuje při čekání na uzamčení tabulky. Místo toho stále získává zámky na původní, podrobnější úrovni (řádek, klíč nebo stránka), pravidelně provádí další pokusy o eskalaci. Jednou z metod, jak zabránit eskalaci zámku u konkrétní tabulky, je získat a podržet zámek na jiném připojení, které není kompatibilní s eskalovaným typem zámku. Zámek záměru (IX) na úrovni tabulky nezamkne žádné řádky ani stránky, ale stále není kompatibilní s eskalovaným sdíleným zámkem (S) nebo s výhradním zámkem tabulky (X). Předpokládejme například, že musíte spustit dávkovou úlohu, která upravuje velký počet řádků v tabulce mytable a která způsobila blokování, ke kterému dochází kvůli eskalaci zámku. Pokud se tato úloha dokončí vždy za méně než hodinu, můžete vytvořit Transact-SQL úlohu, která obsahuje následující kód, a naplánovat, aby se nová úloha spustila několik minut před časem spuštění dávkové úlohy:

  BEGIN TRAN;
  
  SELECT *
  FROM mytable WITH (UPDLOCK, HOLDLOCK)
  WHERE 1 = 0;
  
  WAITFOR DELAY '1:00:00';
  
  COMMIT TRAN;
  

  Tento dotaz získá a uchovává IX zámek na mytable po dobu jedné hodiny, což brání eskalaci zámku v tabulce během této doby. Tato dávka neupravuje žádná data ani neblokuje jiné dotazy (pokud jiný dotaz nevynutí zámek tabulky pomocí nápovědy TABLOCK nebo pokud správce zakázal uzamčení stránky nebo řádků v indexu na mytable).

• Můžete také použít trasovací příznaky 1211 a 1224 k zakázání všech nebo některých eskalací zámků. Tyto příznaky trasování zakazují všechny eskalace zámků globálně pro celou instanci databázového stroje. Eskalace zámků slouží k užitečnému účelu v databázovém stroji tím, že maximalizuje efektivitu dotazů, které se jinak zpomalují režií při získávání a uvolnění několika tisíc zámků. Eskalace zámků také pomáhá minimalizovat požadovanou paměť pro sledování zámků. Paměť, kterou může databázový stroj dynamicky přidělovat strukturám zámků, je omezená, takže pokud zakážete eskalaci zámku a paměť zámku se dostatečně rozroste, pokusí se přidělit další zámky pro jakýkoli dotaz může selhat a dojde k následující chybě: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

  Poznámka

  Když dojde k chybě MSSQLSERVER_1204, zastaví zpracování aktuálního příkazu a způsobí vrácení aktivní transakce zpět. Vrácení zpět může blokovat uživatele nebo vést k dlouhé době obnovení databáze, pokud restartujete databázovou službu.

  Poznámka

  Použití zámkového náznaku, například jako ROWLOCK, mění pouze počáteční získání zámku. Nápovědy k zámkům nezabrání eskalaci zámků.

Od verze SQL Server 2008 (10.0.x) se chování eskalace zámku změnilo zavedením možnosti LOCK_ESCALATION tabulky. Pro více informací se podívejte na možnost LOCK_ESCALATION v ALTER TABLE.

Eskalace monitorovacího zámku

Sledujte eskalaci zámků pomocí lock_escalation rozšířené události, například v následujícím příkladu:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

Dynamické uzamykání

Použití zámků nízké úrovně, jako jsou zámky řádků, zvyšuje souběžnost snížením pravděpodobnosti, že dvě transakce vyžadují zámky na stejné části dat najednou. Použití zámků nízké úrovně také zvyšuje počet zámků a prostředků potřebných ke správě. Použití vysoké úrovně zámků pro tabulky nebo stránky snižuje režii, ale na úkor snížení souběžnosti.

Databázový stroj používá strategii dynamického zamykání k určení nejúčinnějších zámků. Databázový stroj automaticky určí, jaké zámky jsou při spuštění dotazu nejvhodnější, na základě charakteristik schématu a dotazu. Pokud například chcete snížit režii zamykání, může optimalizátor při prohledávání indexu zvolit zámky stránek v indexu.

Dělení uzamykání

U velkých počítačových systémů se zámky na často odkazované objekty můžou stát úzkým hrdlem výkonu, protože získávání a uvolnění zámků způsobují konkurenční zátěž na interní zámkové prostředky. Rozdělování zámků zvyšuje výkon uzamčení tím, že rozděluje jeden prostředek zámku na několik prostředků zámku. Tato funkce je dostupná pouze pro systémy s 16 nebo více logickými procesory a je automaticky povolená a nelze ji zakázat. Pouze zámky objektů lze dělit. Zámky objektů, které mají podtyp, nejsou rozdělené na oddíly. Další informace naleznete v tématu sys.dm_tran_locks (Transact-SQL).

Pochopit dělení zámků

Uzamčení úloh přistupuje k několika sdíleným prostředkům, z nichž dvě jsou optimalizované uzamčením oddílů:

• Spinlock

  Tím se řídí přístup k prostředku zámku, například k řádku nebo tabulce.

  Bez dělení na oddíly spravuje jeden spinlock všechny požadavky na uzamčení pro jeden zamykací prostředek. V systémech, u kterých dochází k velkému objemu aktivit, může dojít k kolizím, protože žádosti o uzamčení čekají na zpřístupnění zámku. V této situaci se získání zámků může stát kritickým bodem a může negativně ovlivnit výkon.

  Aby se snížila kolize jednoho prostředku zámku, rozdělením zámku na více prostředků zámku se zatížení rozloží mezi více spinlocků.

• paměť

  K ukládání struktur zámku prostředků se to používá.

  Jakmile se zámek získá, uloží se struktury zámků do paměti a pak se k němu přistupují a případně upraví. Distribuce zámku přístupu mezi více prostředků pomáhá eliminovat potřebu přenosu bloků paměti mezi procesory, což pomáhá zlepšit výkon.

Implementace a monitorování dělení zámků

Uzamčení dělení je ve výchozím nastavení zapnuté pro systémy s 16 nebo více procesory. Pokud je povolené dělení zámků, v protokolu chyb SQL Serveru se zaznamená informační zpráva.

Při získávání zámků na rozděleném zdroji:

• Na jednom oddílu jsou získány pouze režimy zámků NL, Sch-S, IS, IUa IX.

• Shared (S), exclusive (X) a další zámky v jiných režimech než NL, Sch-S, IS, IUa IX musí být získány ve všech oddílech počínaje ID oddílu 0 a následující v pořadí ID oddílu. Tyto zámky na děleném prostředku využívají více paměti než zámky ve stejném režimu na neděleném prostředku, protože každý oddíl má efektivně svůj vlastní zámek. Zvýšení paměti je určeno počtem oddílů. Čítače výkonu uzamčení SQL Serveru zobrazují informace o paměti používané particemi a nepartitionovanými zámky.

Transakce je přiřazena k oddílu při spuštění transakce. U transakce všechny požadavky na uzamčení, které lze rozdělit, používají oddíl přiřazený k této transakci. Tímto způsobem se přístup k uzamčení prostředků stejného objektu různými transakcemi distribuuje napříč různými oddíly.

Sloupec resource_lock_partition v zobrazení dynamické správy sys.dm_tran_locks poskytuje ID uzamčeného oddílu pro uzamčený prostředek. Další informace naleznete v tématu sys.dm_tran_locks (Transact-SQL).

Práce s particionací zámků

Následující příklady kódu ilustrují dělení zámků. V příkladech se dvě transakce spouští ve dvou různých relacích, aby bylo možné zobrazit chování dělení zámků v počítačovém systému s 16 procesory.

Tyto příkazy Transact-SQL vytvářejí testovací objekty, které se používají v následujících příkladech.

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

Příklad A

Sezení 1:

Příkaz SELECT se provádí v rámci transakce. Kvůli nápovědě k uzamčení HOLDLOCK tento příkaz získá a zachová sdílený zámek záměru (IS) na tabulce (v tomto příkladu se ignorují zámky na řádcích a stránkách). Zámek IS je získán pouze v oddílu přiřazeného k transakci. V tomto příkladu se předpokládá, že se zámek IS získá na id oddílu 7.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Sezení 2:

Transakce se spustí a příkaz SELECT, který běží pod touto transakcí, získá a zachová sdílený zámek (S) na tabulce. Zámek S se získá ve všech oddílech, což má za následek několik zámků tabulky, jeden pro každý oddíl. Například v systému s 16 procesory bude vydáno 16 zámků S napříč ID oddílů zámků 0–15. Vzhledem k tomu, že zámek S je kompatibilní se zámkem IS drženým na oddílu s ID 7 transakcí v relaci 1, nedochází k blokování mezi transakcemi.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Sezení 1:

Následující příkaz SELECT se provádí v rámci transakce, která je stále aktivní v rámci relace 1. Z důvodu exkluzivní nápovědy (X) k uzamčení tabulky se transakce pokusí získat zámek X na tabulce. Nicméně zámek S, držený transakcí v relaci 2, blokuje zámek X v oddílu ID 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Příklad B

Sezení 1:

Příkaz SELECT se provádí v rámci transakce. Kvůli nápovědě k uzamčení HOLDLOCK tento příkaz získá a zachová sdílený zámek záměru (IS) na tabulce (v tomto příkladu se ignorují zámky na řádcích a stránkách). Zámek IS je získán pouze v oddílu přiřazeného k transakci. V tomto příkladu se předpokládá, že se zámek IS získá na identifikátoru oddílu 6.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Sezení 2:

Příkaz SELECT se provádí v rámci transakce. Z důvodu příznaku zamknutí TABLOCKX se transakce pokusí získat výhradní (X) zámek na tabulce. Nezapomeňte, že zámek X musí být získán pro všechny oddíly počínaje ID oddílu 0. Zámek X se získá na všech ID oddílů 0–5, ale zablokuje ho zámek IS získaný v ID oddílu 6.

Na oddílech s ID 7–15, na které zámek X ještě nedosáhl, mohou ostatní transakce nadále získávat zámky.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Úrovně izolace založené na verzích řádků v databázovém stroji

Počínaje SQL Serverem 2005 (9.x) nabízí databázový stroj implementaci existující úrovně izolace transakcí, READ COMMITTED, která poskytuje snímek na úrovni příkazů pomocí správy verzí řádků. Databázový stroj nabízí také úroveň izolace transakcí, SNAPSHOT, která poskytuje snímek na úrovni transakce pomocí verzování řádků.

Správa verzí řádků je obecná architektura SQL Serveru, která používá mechanismus kopírování při zápisu při úpravě nebo odstranění řádku. To vyžaduje, aby během běhu transakce, stará verze řádku byla k dispozici pro transakce, které vyžadují dřívější konzistentní stav. Správa verzí řádků se používá k implementaci následujících funkcí:

• Sestavte tabulky inserted a deleted v triggerech. Všechny řádky upravené triggerem jsou verze. To zahrnuje řádky upravené příkazem, který trigger spustil, a také všechny změny dat provedené triggerem.
• Podpora více aktivních sad výsledků (MARS). Pokud relace MARS vydá příkaz pro úpravu dat (například INSERT, UPDATEnebo DELETE) v době, kdy existuje aktivní sada výsledků, budou řádky ovlivněné příkazem úpravy verze.
• Podpora operací indexu, které určují možnost ONLINE.
• Podpora úrovní izolace transakcí založených na verzích řádků:
  • Nová implementace úrovně izolace READ COMMITTED, která používá správu verzí řádků k zajištění konzistence čtení na úrovni příkazů.
  • Nová úroveň izolace, SNAPSHOT, která zajišťuje konzistenci čtení na úrovni transakcí.

Verze řádků jsou uložené v úložišti verzí. Pokud je v databázi povolené zrychlené obnovení databáze (ADR), vytvoří se v této databázi úložiště verzí. Jinak se úložiště verzí vytvoří v databázi tempdb.

Databáze musí mít dostatek místa pro úložiště verzí. Pokud je úložiště verzí v tempdba databáze tempdb je plná, operace aktualizace přestanou generovat verze, ale budou pokračovat úspěšně, ale operace čtení mohou selhat, protože konkrétní potřebná verze řádku neexistuje. To má vliv na operace, jako jsou triggery, MARS a online indexování.

Když se použije ADR a úložiště verzí je plné, operace čtení budou pokračovat úspěšně, ale operace zápisu, které generují verze, například UPDATE a DELETE selžou. INSERT operace budou nadále úspěšné, pokud má databáze dostatek místa.

Použití verzování řádků pro transakce READ COMMITTED a SNAPSHOT je proces o dvou krocích:

1. Nastavte buď jednu, nebo obě možnosti databáze READ_COMMITTED_SNAPSHOT a ALLOW_SNAPSHOT_ISOLATION na ON.

2. Nastavte v aplikaci odpovídající úroveň izolace transakcí:

   • Pokud je možnost databáze READ_COMMITTED_SNAPSHOT nastavena na ON, transakce nastavující úroveň izolace READ COMMITTED používají správu verzí řádků.
   • Pokud je databázová možnost ALLOW_SNAPSHOT_ISOLATION nastavena na ON, transakce mohou nastavit úroveň izolace SNAPSHOT.

Pokud je možnost READ_COMMITTED_SNAPSHOT nebo ALLOW_SNAPSHOT_ISOLATION databáze nastavena na ON, databázový stroj přiřadí každé transakci pořadové číslo (XSN) každé transakci, která manipuluje s daty pomocí správy verzí řádků. Transakce začínají v okamžiku, kdy se spustí příkaz BEGIN TRANSACTION. Pořadové číslo transakce však začíná první operací čtení nebo zápisu za příkazem BEGIN TRANSACTION. Pořadové číslo transakce se při každém přiřazení zvýší o jedno.

Pokud jsou možnosti READ_COMMITTED_SNAPSHOT nebo ALLOW_SNAPSHOT_ISOLATION databáze nastaveny na ON, budou zachovány logické kopie (verze) pro všechny úpravy dat provedené v databázi. Pokaždé, když je řádek upraven konkrétní transakcí, instance databázového stroje ukládá verzi dříve potvrzené image řádku v úložišti verzí. Každá verze je označena pořadovým číslem transakce, která provedla změnu. Verze upravených řádků jsou zřetězeny pomocí spojového seznamu. Nejnovější hodnota řádku je vždy uložena v aktuální databázi, a zřetězená s verzionovanými řádky v úložišti verzí.

Poznámka

Při úpravě velkých objektů (LOB) se do úložiště verzí zkopíruje pouze změněný fragment.

Verze řádků jsou uchovávány dostatečně dlouho, aby splňovaly požadavky transakcí probíhajících pod úrovněmi izolace založenými na verzích řádků. Databázový stroj sleduje nejstarší užitečné číslo posloupnosti transakcí a pravidelně odstraňuje všechny verze řádků označené pořadovými čísly transakcí, které jsou nižší než nejstarší užitečné pořadové číslo.

Pokud jsou obě možnosti databáze nastaveny na OFF, verzovány jsou pouze řádky, které byly změněny triggery, relacemi MARS nebo přečteny online indexovými operacemi. Tyto verze řádků se vydávají, pokud už nejsou potřeba. Proces na pozadí odstraňuje zastaralé verze řádků.

Poznámka

U krátkodobých transakcí se může verze upraveného řádku dostat do mezipaměti ve fondu vyrovnávací paměti, aniž by se zapisovala do úložiště verzí. Pokud je potřeba řádek s verzí krátkodobá, řádek se z fondu vyrovnávací paměti vyřadí a nevznikají režijní náklady na vstupně-výstupní operace.

Chování při čtení dat

Při transakcích spuštěných v rámci čtení dat založených na správě verzí řádků se operace čtení nezískávají sdílené (S) na přečtených datech, a proto neblokují transakce, které upravují data. Zamykání prostředků je minimalizováno také tím, že je snížen počet získaných zámků. Izolace READ COMMITTED pomocí správy verzí řádků a izolace SNAPSHOT jsou navrženy tak, aby poskytovaly konzistenci čtení na úrovni příkazu nebo transakce u verzovaných dat.

Všechny dotazy, včetně transakcí spuštěných v rámci úrovní izolace založené na verzování řádků, získávají zámky pro stabilitu schématu (Sch-S) během kompilace a provádění. Z tohoto důvodu jsou dotazy blokovány, když souběžná transakce drží zámek na úpravu schématu (Sch-M) na tabulce. Například operace jazyka DDL (Data Definition Language) získá zámek Sch-M předtím, než upraví informace o schématu tabulky. Transakce, včetně těch spuštěných na úrovni izolace založené na správě verzí na řádku, jsou při pokusu o získání zámku Sch-S blokované. Naopak dotaz, který obsahuje zámek Sch-S blokuje souběžnou transakci, která se pokusí získat Sch-M zámek.

Když se spustí transakce používající úroveň izolace SNAPSHOT, instance databázového stroje zaznamenává všechny aktuálně aktivní transakce. Když SNAPSHOT transakce přečte řádek, který má řetězec verzí, databázový stroj se řídí řetězem a načte řádek, kde je pořadové číslo transakce:

• Nejbližší, ale nižší pořadové číslo než číslo transakce snímku, která čte řádek.

• Není v seznamu transakcí aktivních při spuštění transakce snímku.

Operace čtení prováděné SNAPSHOT transakcí načítají poslední verzi každého řádku, který byl potvrzen v okamžiku spuštění SNAPSHOT transakce. To poskytuje konzistentní transakční snímek dat, jak existoval na začátku transakce.

READ COMMITTED transakce využívající verzování řádků fungují podobným způsobem. Rozdíl je v tom, že transakce READ COMMITTED nepoužívá vlastní pořadové číslo transakce při výběru verzí řádků. Při každém spuštění příkazu transakce READ COMMITTED přečte nejnovější pořadové číslo transakce vydané pro příslušnou instanci databázového enginu. Toto je pořadové číslo transakce použité k výběru verzí řádků pro tento výraz. To umožňuje transakcím READ COMMITTED zobrazit snímek dat, jak existují na začátku každého příkazu.

Poznámka

I když READ COMMITTED transakce pomocí správy verzí řádků poskytuje transakční konzistentní zobrazení dat na úrovni příkazu, verze řádků generované nebo přístupné tímto typem transakce jsou zachovány, dokud transakce nebude dokončena.

Chování při úpravě dat

Chování zápisů dat je odlišné s aktivovaným a bez aktivovaného optimalizovaného uzamykání.

Úprava dat bez optimalizovaného uzamčení

Ve transakci READ COMMITTED využívající verzování řádků se výběr řádků k aktualizaci provádí pomocí blokujícího skenování, kdy je zámek aktualizace (U) získán na datovém řádku při čtení hodnot dat. Je to stejné jako transakce READ COMMITTED, která nepoužívá verzování řádků. Pokud řádek dat nesplňuje kritéria aktualizace, zámek aktualizace se na daném řádku uvolní a další řádek se zamkne a zkontroluje.

Transakce probíhající v rámci izolace SNAPSHOT používají optimistický přístup k úpravám dat tím, že získávají zámky na data pouze za účelem vynucení omezení před samotnou úpravou. V opačném případě se zámky nezískávají u dat, dokud není potřeba data upravit. Když řádek dat splňuje kritéria aktualizace, transakce SNAPSHOT ověří, že datový řádek nebyl změněn souběžnou transakcí potvrzenou po zahájení transakce SNAPSHOT. Pokud byl datový řádek změněn mimo transakci SNAPSHOT, dojde ke konfliktu aktualizace a SNAPSHOT transakce se ukončí. Konflikt aktualizací zpracovává databázový stroj a neexistuje způsob, jak zakázat detekci konfliktů aktualizací.

Poznámka

Operace aktualizace spuštěné v rámci izolace SNAPSHOT se interně provádějí v rámci izolace READ COMMITTED, když SNAPSHOT transakce přistupuje k některé z následujících:

Tabulka s omezením cizího klíče

Tabulka, na kterou odkazuje omezení cizího klíče jiné tabulky.

Indexované zobrazení odkazující na více než jednu tabulku.

I za těchto podmínek však operace aktualizace nadále ověřuje, že data nebyla změněna jinou transakcí. Pokud byla data upravena jinou transakcí, SNAPSHOT transakce narazí na konflikt aktualizace a je ukončena. Aplikace musí zpracovat konflikty aktualizací a pokusit se o ně znovu.

Úprava dat s použitím optimalizovaného uzamykání

S optimalizovaným uzamčením a s povolenou databázovou možností READ_COMMITTED_SNAPSHOT (RCSI) a při použití výchozí úrovně izolace READ COMMITTED, čtenáři nezískávají žádné zámky a zapisovači získávají krátkodobé zámky na nízké úrovni, spíše než zámky, jejichž platnost vyprší na konci transakce.

Povolení RCSI se doporučuje pro maximální efektivitu s optimalizovaným uzamykáním. Při použití přísnějších úrovní izolace, jako je REPEATABLE READ nebo SERIALIZABLE, databázový stroj drží zámky na řádky a stránky až do konce transakce, jak pro čtenáře, tak pro zapisovače, což vede ke zvýšení zablokování a paměťové náročnosti na zámky.

Pokud je povolená analýza RCSI a pokud používáte výchozí úroveň izolace READ COMMITTED, zapisovače opravují řádky podle predikátu na základě nejnovější potvrzené verze řádku bez získání U zámků. Dotaz čeká pouze tehdy, pokud řádek splňuje podmínky a na daném řádku nebo stránce existuje další aktivní transakce zápisu. Kvalifikace na základě nejnovější potvrzené verze a uzamčení pouze kvalifikovaných řádků snižuje blokování a zvyšuje souběžnost.

Pokud jsou zjištěny konflikty aktualizací s RCSI a ve výchozí úrovni izolace READ COMMITTED, zpracovávají se a opakují se automaticky bez jakéhokoli dopadu na úlohy zákazníků.

S optimalizovaným uzamčením povoleným a při použití úrovně izolace SNAPSHOT je chování konfliktů aktualizací stejné jako bez optimalizovaného uzamčení. Aplikace musí zpracovat konflikty aktualizací a pokusit se o ně znovu.

Poznámka

Další informace o změnách chování funkce uzamčení po kvalifikaci (LAQ) v rámci optimalizovaného uzamčení naleznete v tématu Změny chování dotazů s optimalizovaným uzamčením a RCSI.

Chování v souhrnu

Následující tabulka shrnuje rozdíly mezi izolací SNAPSHOT a izolací READ COMMITTED pomocí verzování řádků.

Vlastnost	READ COMMITTED úroveň izolace s využitím verzování řádků	úroveň izolace SNAPSHOT
Možnost databáze, která musí být nastavena na ON pro povolení požadované podpory.	READ_COMMITTED_SNAPSHOT	ALLOW_SNAPSHOT_ISOLATION
Jak relace žádá o konkrétní typ verzování řádků.	Použijte výchozí úroveň izolace READ COMMITTED nebo spuštěním příkazu SET TRANSACTION ISOLATION LEVEL určete úroveň izolace READ COMMITTED. To lze provést po spuštění transakce.	Vyžaduje spuštění SET TRANSACTION ISOLATION LEVEL k určení úrovně izolace SNAPSHOT před zahájením transakce.
Verze dat, která byla přečtena příkazy.	Všechna data, která byla potvrzena před začátkem každého výroku.	Všechna data, která byla potvrzena před zahájením každé transakce.
Způsob zpracování aktualizací	Bez optimalizovaného uzamčení: Přepíná z použití verzí řádků na použití skutečných dat pro výběr řádků k aktualizaci a používá zámky aktualizací na vybraných řádcích dat. Získá exkluzivní zámky na skutečných řádcích dat, které se mají upravit. Žádná detekce konfliktů aktualizací. S optimalizovaným uzamčením: Řádky jsou vybrány na základě poslední potvrzené verze bez získání zámků. Pokud jsou řádky způsobilé pro aktualizaci, jsou získány výhradní zámky řádků nebo stránek. Pokud se zjistí konflikty aktualizací, zpracovávají se a opakují se automaticky.	Používá verze řádků k výběru řádků pro aktualizaci. Pokouší se získat výhradní zámek na datovém řádku určeném k úpravě, a pokud byla data upravena jinou transakcí, dojde ke konfliktu aktualizace a transakce snímku je ukončena.
Detekce konfliktů aktualizací	Bez optimalizovaného uzamčení: Žádné. S optimalizovaným uzamčením: Pokud jsou zjištěny konflikty aktualizací, jsou zpracovány a opakovány automaticky.	Integrovaná podpora. Nelze zakázat.

Využití prostředků pro verzování řádků

Architektura správy verzí řádků podporuje následující funkce databázového stroje:

• Spouště
• Více aktivních sad výsledků (MARS)
• Online indexování

Architektura správy verzí řádků podporuje také následující úrovně izolace transakcí založené na verzích řádků:

• Pokud je možnost databáze READ_COMMITTED_SNAPSHOT nastavena na ON, transakce READ_COMMITTED zajišťují konzistenci čtení na úrovni příkazů pomocí verzování řádků.
• Pokud je možnost ALLOW_SNAPSHOT_ISOLATION databáze nastavena na ON, SNAPSHOT transakce poskytují konzistenci čtení na úrovni transakcí pomocí verzování řádků.

Úrovně izolace založené na verzích řádků snižují počet zámků získaných transakcí tím, že eliminují použití sdílených zámků při operacích čtení. Tím se zvýší výkon systému snížením prostředků používaných ke správě zámků. Výkon se také zvyšuje snížením počtu případů, kdy je transakce blokována zámky, které byly získány jinými transakcemi.

Úrovně izolace založené na verzích řádků zvyšují prostředky potřebné pro úpravy dat. Povolení těchto možností způsobí, že budou veškeré úpravy databázových dat verzovány. Kopie dat před úpravou je uložena v úložišti verzí, i když neexistují žádné aktivní transakce pomocí izolace založené na verzích řádků. Data po úpravě zahrnují ukazatel na verzovaná data v úložišti verzí. U velkých objektů je uložena pouze část změněného objektu v úložišti verzí.

Použité místo v databázi tempdb

Pro každou instanci databázového stroje musí úložiště verzí mít dostatek místa pro uložení verzí řádků. Správce databáze musí zajistit, aby tempdb a jiné databáze (pokud je povolené ADR) měly pro podporu úložiště verzí více místa. Existují dva typy úložišť verzí:

• Úložiště verzí sestavení online indexu se používá pro sestavení online indexu.
• Společné úložiště verzí se používá pro všechny ostatní operace úprav dat.

Verze řádků musí být uloženy tak dlouho, dokud k nim aktivní transakce potřebuje přístup. Vlákno na pozadí pravidelně odstraňuje verze řádků, které již nejsou potřebné, a uvolňuje místo v úložišti verzí. Dlouhotrvající transakce zabraňuje uvolnění místa v úložišti verzí, pokud splňuje některou z následujících podmínek:

• Používá izolaci založenou na správě verzí řádků.
• Používá triggery, MARS nebo operace sestavení online indexu.
• Generuje verze řádků.

Poznámka

Když je aktivační událost vyvolána uvnitř transakce, verze řádků vytvořené triggerem se zachovají až do konce transakce, i když verze řádků už nejsou po dokončení triggeru potřeba. To platí také pro READ COMMITTED transakce, které používají verzování řádků. S tímto typem transakce je transakční konzistentní zobrazení databáze nutné pouze pro každý příkaz v transakci. To znamená, že verze řádků vytvořené pro příkaz v transakci už nejsou potřeba po dokončení příkazu. Verze řádků vytvořené jednotlivými příkazy v transakci jsou však zachovány, dokud transakce nebude dokončena.

Pokud je úložiště verzí v tempdba pokud v tempdb dojde místo, databázový stroj přinutí úložiště verzí zmenšit se. Během procesu zmenšení jsou nejdéle běžící transakce, které dosud nevygenerovaly verze řádků, označeny jako oběti. V protokolu chyb pro každou transakci oběti se vygeneruje zpráva 3967. Pokud je transakce označena jako oběť, již nemůže číst verze řádků ve verzovacím úložišti. Při pokusu o čtení verzí řádků se vygeneruje zpráva 3966 a transakce se vrátí zpět. Pokud proces zmenšení proběhne úspěšně, v tempdbbude k dispozici volný prostor. V opačném případě tempdb dojde k nedostatku místa a nastane následující:

• Operace zápisu se budou dál spouštět, ale negenerují verze. V protokolu chyb se zobrazí informační zpráva (3959), ale transakce, která zapisuje data, není ovlivněna.

• Transakce, které se pokoušejí získat přístup k verzím řádků, které nebyly generovány kvůli úplnému vrácení zpět tempdb, jsou ukončeny s chybou 3958.

Mezera použitá v řádcích dat

Každý řádek databáze může používat až 14 bajtů na konci řádku pro informace o správě verzí řádků. Informace o verzování řádků obsahují pořadové číslo transakce, která potvrdila verzi, a ukazatel na verzi řádku. Tyto 14 bajtů se přidají při první úpravě řádku nebo při vložení nového řádku za některé z těchto podmínek:

• možnosti READ_COMMITTED_SNAPSHOT nebo ALLOW_SNAPSHOT_ISOLATION jsou nastaveny na ON.
• Tabulka má spoušť.
• Používá se více aktivních sad výsledků (MARS).
• Na tabulce aktuálně probíhají operace sestavení online indexu.

Pokud je úložiště verzí v tempdb, odeberou se z řádku databáze tyto 14 bajtů při první úpravě řádku databáze za všech těchto podmínek:

• možnosti READ_COMMITTED_SNAPSHOT a ALLOW_SNAPSHOT_ISOLATION jsou nastavené na OFF.
• Trigger již v tabulce neexistuje.
• MARS se nepoužívá.
• Operace sestavení online indexu momentálně nejsou spuštěné.

14 bajtů se odebere také při změně řádku, pokud už není povolené ADR a jsou splněny výše uvedené podmínky.

Pokud používáte některou z funkcí správy verzí řádků, možná budete muset databázi přidělit další místo na disku, aby se přizpůsobilo 14 bajtům na řádek databáze. Přidání informací o správě verzí řádků může způsobit rozdělení indexové stránky nebo přidělení nové datové stránky, pokud na aktuální stránce není dostatek místa. Pokud je například průměrná délka řádku 100 bajtů, dalších 14 bajtů způsobí, že existující tabulka roste až o 14 procent.

Snížení výplňového faktoru může pomoct zabránit nebo snížit fragmentaci indexových stránek. Pokud chcete zobrazit aktuální informace o hustotě stránky pro data a indexy tabulky nebo zobrazení, můžete použít sys.dm_db_index_physical_stats.

Místo používané trvalým úložištěm verzí (PVS)

Pokud je povolené ADR, můžou se verze řádků ukládat v trvalém úložišti verzí (PVS) jedním z následujících způsobů v závislosti na velikosti řádku před úpravou:

• Pokud je velikost malá, uloží se celá stará verze řádku jako součást upraveného řádku.
• Pokud je velikost střední, rozdíl mezi starou verzí řádku a upraveným řádkem je uložen jako součást upraveného řádku. Rozdíl je vytvořen způsobem, který umožňuje databázovému stroji v případě potřeby rekonstruovat celou starou verzi řádku.
• Pokud je velikost velká, uloží se celá stará verze řádku do samostatné interní tabulky.

První dvě metody se nazývají v řádku úložiště verzí. Poslední metoda se nazývá úložiště verzí mimo řádek. Pokud už verze v řádku nejsou potřeba, odeberou se, aby se uvolnilo místo na stránkách. Podobně jsou z interní tabulky odstraněny stránky, které obsahují již nepotřebné mimofázové verze, a to procesem čištění verzí.

Ukládání verzí řádků v rámci řádku optimalizuje načítání dat transakcemi, které potřebují číst verze řádků. Pokud je verze uložená v řádku, není vyžadováno samostatné čtení stránky PVS, která se nachází mimo řádek.

DMV sys.dm_db_index_physical_stats poskytuje počet a typ verzí uložených v rámcích a mimo ně pro oddíl indexu. Celková velikost dat verze uložených v řádku je uvedena ve sloupci total_inrow_version_payload_size_in_bytes.

Velikost úložiště verzí mimo řádek je hlášena ve sloupci persistent_version_store_size_kb ve sys.dm_tran_persistent_version_store_stats zobrazení dynamické správy.

Prostor použitý ve velkých objektech

Databázový stroj podporuje několik datových typů, které mohou obsahovat velké řetězce o délce až 2 gigabajty (GB), například: nvarchar(max), varchar(max), varbinary(max), ntext, texta image. Velká data uložená pomocí těchto datových typů jsou uložená v řadě fragmentů dat, které jsou propojené s řádkem dat. Informace o verzování řádků se ukládají v každém fragmentu použitém k uložení těchto velkých řetězců. Fragmenty dat jsou uloženy v sadě stránek vyhrazených pro velké objekty v tabulce.

Při přidání nových velkých hodnot do databáze je přidělováno maximálně 8040 bajtů dat na fragment. Starší verze databázového stroje uložily až 8080 bajtů ntext, textnebo image dat na fragment.

Existující data ntext, texta image velkých objektů (LOB) se neaktualizují, aby se při upgradu databáze na SQL Server ze starší verze SQL Serveru uvolnilo místo pro informace o správě verzí řádků. Nicméně, při první úpravě dat LOB se tato dynamicky upgradují, aby se povolilo ukládání informací o verzích. K tomu dochází, i když se řádkové verze nevytvářejí. Po upgradu LOB dat se maximální počet bajtů uložených na fragment sníží z 8080 bajtů na 8040 bajtů. Proces upgradu je ekvivalentní odstranění hodnoty LOB a opětovné vložení stejné hodnoty. Obchodní data typu LOB se aktualizují, i když se upraví jenom 1 bajt. Jedná se o jednorázovou operaci pro každý sloupec ntext, textnebo image, ale každá operace může v závislosti na velikosti LOB dat generovat velké množství alokací stránek a I/O aktivit. Může také generovat velké množství aktivit protokolování, pokud je úprava plně protokolována. WRITETEXT a operace UPDATETEXT se protokolují minimálně, pokud model obnovení databáze není nastavený na FULL.

Pro splnění tohoto požadavku by mělo být přiděleno dostatek místa na disku.

Monitorování verzování řádků a úložiště verzí

Pro monitorování verzování řádků, úložiště verzí a procesů izolace snímků s ohledem na výkon a řešení problémů poskytuje databázový stroj nástroje ve formě dynamických pohledů správy a čítačů výkonu.

Zobrazení dynamické správy

Následující zobrazení dynamické správy poskytují informace o aktuálním stavu systému tempdb a úložišti verzí a transakcích využívajících verzování řádků.

• sys.dm_db_file_space_usage. Vrátí informace o využití místa pro každý soubor v databázi. Další informace naleznete v tématu sys.dm_db_file_space_usage (Transact-SQL).

• sys.dm_db_session_space_usage. Vrací aktivitu přidělování a uvolňování stránek podle sesí pro databázi. Další informace naleznete v tématu sys.dm_db_session_space_usage (Transact-SQL).

• sys.dm_db_task_space_usage. Vrátí alokaci a dealokaci stránek na základě úkolu pro databázi. Pro více informací viz sys.dm_db_task_space_usage (Transact-SQL).

• sys.dm_tran_top_version_generators. Vrátí virtuální tabulku pro objekty vytvářející nejvíce verzí v úložišti verzí. Seskupí prvních 256 agregovaných délek záznamů podle database_id a rowset_id. Tato funkce slouží k vyhledání největších spotřebitelů úložiště verzí. Platí jenom pro úložiště verzí v tempdb. Další informace naleznete v tématu sys.dm_tran_top_version_generators (Transact-SQL).

• sys.dm_tran_version_store. Vrátí virtuální tabulku, která zobrazí všechny záznamy verzí v úložišti běžných verzí. Platí jenom pro úložiště verzí v tempdb. Pro více informací viz sys.dm_tran_version_store (Transact-SQL).

• sys.dm_tran_version_store_space_usage. Vrátí virtuální tabulku, která zobrazuje celkový prostor v tempdb, jež je využíván záznamy úložiště verzí pro každou databázi. Platí jenom pro úložiště verzí v tempdb. Další informace viz sys.dm_tran_version_store_space_usage (Transact-SQL).

  Poznámka

  Dotazování sys.dm_tran_top_version_generators a sys.dm_tran_version_store může být nákladné, protože oba prohledávají celé úložiště verzí, což může být velké. sys.dm_tran_version_store_space_usage je efektivní a není drahé na provoz, protože neprochází záznamy úložiště jednotlivých verzí a místo toho vrací agregovaný prostor úložiště verzí spotřebovaný na databázi tempdb.

• sys.dm_tran_active_snapshot_database_transactions. Vrátí virtuální tabulku pro všechny aktivní transakce ve všech databázích v instanci SQL Serveru, která používá správu verzí řádků. Systémové transakce se v tomto DMV nezobrazují. Další informace naleznete v tématu sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

• sys.dm_tran_transactions_snapshot. Vrátí virtuální tabulku, která zobrazuje snímky pořízené jednotlivými transakcemi. Snímek obsahuje číslo sekvence těchto aktivních transakcí, které používají verzování řádků. Další informace naleznete v tématu sys.dm_tran_transactions_snapshot (Transact-SQL).

• sys.dm_tran_current_transaction. Vrací jediný řádek, který zobrazuje informace o stavu transakce v aktuální seanci související s verzováním řádků. Další informace naleznete v sys.dm_tran_current_transaction (Transact-SQL).

• sys.dm_tran_current_snapshot. Vrátí virtuální tabulku, která zobrazuje všechny aktivní transakce v době, kdy se spustí aktuální transakce izolace snímku. Pokud aktuální transakce používá izolaci snímků, tato funkce nevrátí žádné řádky. DMV sys.dm_tran_current_snapshot je podobné sys.dm_tran_transactions_snapshot, s tím rozdílem, že vrací pouze aktivní transakce pro aktuální snímek. Další informace naleznete v tématu sys.dm_tran_current_snapshot (Transact-SQL).

• sys.dm_tran_persistent_version_store_stats. Vrátí statistiku pro trvalé úložiště verzí v každé databázi použitou při povolení zrychleného obnovení databáze. Další informace viz sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Čítače výkonu

Následující čítače výkonu monitorují úložiště verzí v tempdbi transakce využívající správu verzí řádků. Čítače výkonu jsou obsaženy v objektu výkonu SQLServer:Transactions.

• volné místo v databázi tempdb (KB). Monitoruje množství v kilobajtech (kB) volného místa v tempdb databázi. V tempdb musí být dostatek volného místa pro zpracování úložiště verzí, které podporuje izolaci snímků.

  Následující vzorec poskytuje hrubý odhad velikosti úložiště verzí. U dlouhotrvajících transakcí může být užitečné monitorovat míru generování a úklidu, aby se odhadla maximální velikost úložiště verzí.

  [velikost úložiště běžných verzí] = 2 * [data úložiště verzí vygenerovaná za minutu] * [nejdelší doba běhu (minuty) transakce]

  Nejdelší doba běhu transakcí by neměla zahrnovat sestavení online indexu. Vzhledem k tomu, že tyto operace můžou u velmi velkých tabulek trvat dlouho, používají sestavení online indexů samostatné úložiště verzí. Přibližná velikost úložiště verzí sestavení online indexu se rovná množství dat upravených v tabulce, včetně všech indexů, zatímco online sestavení indexu je aktivní.

• Velikost úložiště verzí (KB). Monitoruje velikost v kB všech úložišť verzí v tempdb. Tyto informace pomáhají určit množství místa potřebného v databázi tempdb úložiště verzí. Monitorování tohoto čítače za určité časové období poskytuje užitečný odhad dalšího prostoru potřebného pro tempdb.

• frekvence generování verzí (KB/s). Monitoruje rychlost generování verzí v kB za sekundu ve všech úložištích verzí v tempdb.

• rychlost čištění verzí (KB/s). Monitoruje rychlost čištění verzí v kB za sekundu ve všech úložištích verzí v tempdb.

  Poznámka

  Informace z frekvence generování verzí (KB/s) a rychlosti čištění verzí (KB/s) lze použít k predikci tempdb požadavků na místo.

• počet jednotek úložiště verzí. Sleduje počet jednotek úložiště verzí.

• vytvoření jednotky pro úložiště verzí. Monitoruje celkový počet jednotek úložiště, které byly vytvořeny pro ukládání řádkových verzí od spuštění instance.

• zkrácení jednotek úložiště verzí. Monitoruje celkový počet jednotek úložiště verzí zkrácených od spuštění instance. Jednotka úložiště verzí je zkrácena, když SQL Server zjistí, že k spuštění aktivních transakcí nejsou potřeba žádné řádky verze uložené v jednotce úložiště verzí.

• Poměr konfliktů aktualizace. Monitoruje poměr aktualizačních snímků transakcí, které mají konflikty aktualizace, k celkovému počtu aktualizačních snímků transakcí.

• nejdelší doba běhu transakce. Monitoruje nejdelší dobu spuštění jakékoli transakce v sekundách při použití verzování řádků. To se dá použít k určení, jestli nějaká transakce probíhá déle, než je obvyklé.

• Transakce. Monitoruje celkový počet aktivních transakcí. Nezahrnuje systémové transakce.

• snímkové transakce. Monitoruje celkový počet aktivních transakcí snímků.

• Aktualizace snímků transakcí. Monitoruje celkový počet aktivních transakcí snímků databáze, které provádějí operace aktualizace.

• Transakce verze nesnímku. Monitoruje celkový počet aktivních transakcí, které nejsou snímky a generují záznamy verzí.

  Poznámka

  Součet transakcí Update Snapshot a NonSnapshot Version představuje celkový počet transakcí, které se účastní generování verzí. Rozdíl mezi snímkovými transakcemi a transakcemi aktualizace snímků představuje počet snímkových transakcí pouze pro čtení.

Příklad úrovně izolace na základě verzování řádků

Následující příklady ukazují rozdíly v chování mezi transakcemi izolace SNAPSHOT a transakcemi READ COMMITTED, které používají správu verzí řádků.

A. Pracujte s izolací SNAPSHOT

V tomto příkladu transakce spuštěná v rámci SNAPSHOT izolace čte data, která se pak upraví jinou transakcí. Transakce SNAPSHOT neblokuje operaci aktualizace provedenou druhou transakcí a nadále čte data z řádku verze a ignoruje úpravy dat. Pokud se však SNAPSHOT transakce pokusí upravit data, která již byla změněna druhou transakcí, SNAPSHOT transakce generuje chybu a je ukončena.

V relaci 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

V sezení 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

V relaci 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

V sezení 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

V relaci 1:

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. Práce s izolací READ COMMITTED pomocí verzování řádků

V tomto příkladu transakce READ COMMITTED pomocí verzování řádků běží souběžně s jinou transakcí. Transakce READ COMMITTED se chová jinak než transakce SNAPSHOT. Podobně jako transakce SNAPSHOT bude transakce READ COMMITTED číst verzované řádky i po změně dat druhou transakcí. Na rozdíl od transakce SNAPSHOT však transakce READ COMMITTED:

• Přečte upravená data poté, co druhá transakce potvrdí změny dat.
• Je schopen aktualizovat data upravená jinou transakcí, zatímco transakce SNAPSHOT nemohla.

V relaci 1:

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

V sezení 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

V relaci 1:

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

V sezení 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

V relaci 1:

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Povolení úrovní izolace založených na verzování řádků

Správci databáze řídí nastavení správy verzí řádků na úrovni databáze pomocí možností READ_COMMITTED_SNAPSHOT a ALLOW_SNAPSHOT_ISOLATION databáze v příkazu ALTER DATABASE.

Pokud je možnost READ_COMMITTED_SNAPSHOT databáze nastavená na ON, aktivují se mechanismy používané k podpoře této možnosti okamžitě. Při nastavování možnosti READ_COMMITTED_SNAPSHOT je v databázi povoleno pouze připojení, které spouští příkaz ALTER DATABASE. Dokud nebude ALTER DATABASE dokončeno, nesmí v databázi existovat žádné další otevřené připojení. Databáze nemusí být v režimu jednoho uživatele.

Následující příkaz Transact-SQL umožňuje READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

Pokud je možnost ALLOW_SNAPSHOT_ISOLATION databáze nastavená na ON, instance databázového stroje nespustí generování verzí řádků pro upravená data, dokud se nedokončí všechny aktivní transakce, které změnily data v databázi. Pokud existují aktivní transakce úprav, databázový stroj nastaví stav možnosti PENDING_ON. Po dokončení všech transakcí úprav se stav možnosti změní na ON. Uživatelé nemohou zahájit transakci SNAPSHOT v databázi, dokud není možnost ONdostupná. Podobně databáze prochází stavem PENDING_OFF, když správce databáze nastaví možnost ALLOW_SNAPSHOT_ISOLATION na OFF.

Následující příkaz Transact-SQL umožňuje ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

Následující tabulka uvádí a popisuje stavy ALLOW_SNAPSHOT_ISOLATION možnosti. Použití ALTER DATABASE s možností ALLOW_SNAPSHOT_ISOLATION nezablokuje uživatele, kteří aktuálně přistupují k databázovým datům.

Stav izolace SNAPSHOT pro aktuální databázi	Popis
OFF	Podpora izolačních transakcí SNAPSHOT není aktivována. Nejsou povoleny žádné izolační transakce SNAPSHOT.
PENDING_ON	Podpora izolace transakcí SNAPSHOT je ve stavu přechodu (od OFF do ON). Otevřené transakce musí být dokončeny. Nejsou povoleny žádné izolační transakce SNAPSHOT.
ON	Podpora pro izolaci transakcí SNAPSHOT je aktivována. SNAPSHOT transakce jsou povolené.
PENDING_OFF	Podpora izolace transakcí SNAPSHOT je ve stavu přechodu (od ON do OFF). SNAPSHOT transakce zahájené po této době nemají přístup k této databázi. Stávající SNAPSHOT transakce mají stále přístup k této databázi. Stávající transakce zápisu stále používají správu verzí v této databázi. Stav PENDING_OFF se nestane OFF, dokud nejsou dokončeny všechny SNAPSHOT transakce, které začaly, když byl stav izolace databáze SNAPSHOTON.

Pomocí zobrazení katalogu sys.databases určete stav obou možností databáze správy verzí řádků.

Všechny aktualizace uživatelských tabulek a některých systémových tabulek uložených v master a msdb generují verze řádků.

Možnost ALLOW_SNAPSHOT_ISOLATION je automaticky nastavená na ON v databázích master a msdb a nelze ji zakázat.

Uživatelé nemohou nastavit možnost READ_COMMITTED_SNAPSHOT na ON v master, tempdbnebo msdb.

Použijte úrovně izolace založené na verzování řádků

Mechanismus správy verzí řádků je vždy aktivní a využívají ho různé funkce. Kromě poskytování úrovní izolace založených na verzování řádků se používá k podpoře úprav provedených v triggerech a různých relacích MARS (multiple active result sets) a k podpoře čtení dat pro online indexové operace.

Úrovně izolace založené na verzích řádků jsou povoleny na úrovni databáze. Všechny aplikace, které přistupují k objektům z povolených databází, můžou spouštět dotazy pomocí následujících úrovní izolace:

• READ COMMITTED, která používá správu verzí řádků, nastavením možnosti READ_COMMITTED_SNAPSHOT databáze na ON, jak je znázorněno v následujícím příkladu kódu:

  ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
  

  Pokud je databáze povolená pro READ_COMMITTED_SNAPSHOT, všechny dotazy spuštěné na úrovni izolace READ COMMITTED používají správu verzí řádků, což znamená, že operace čtení neblokují operace aktualizace.

• Nastavte izolaci SNAPSHOT pomocí nastavení možnosti databáze ALLOW_SNAPSHOT_ISOLATION na ON, jak je znázorněno v následujícím příkladu kódu.

  ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
  

  Při použití dotazů mezi databázemi může transakce spuštěná v rámci izolace SNAPSHOT přistupovat k tabulkám v databázích s možností ALLOW_SNAPSHOT_ISOLATION databáze nastavenou na ON. Pokud chcete získat přístup k tabulkám v databázích, které nemají možnost ALLOW_SNAPSHOT_ISOLATION databáze nastavenou na ON, je nutné změnit úroveň izolace. Například následující příklad kódu ukazuje příkaz SELECT, který spojuje dvě tabulky při spuštění v rámci transakce SNAPSHOT. Jedna tabulka patří do databáze, ve které není povolená izolace SNAPSHOT. Pokud příkaz SELECT běží pod izolací SNAPSHOT, nepodaří se úspěšně spustit.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

  Následující příklad kódu ukazuje stejný SELECT příkaz, který byl změněn tak, aby změnil úroveň izolace transakce na READ COMMITTED při přístupu ke konkrétní tabulce. Z důvodu této změny se příkaz SELECT úspěšně spustí.

  SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
  
  BEGIN TRANSACTION;
  
  SELECT t1.col5, t2.col5
  FROM Table1 as t1 WITH (READCOMMITTED)
  INNER JOIN SecondDB.dbo.Table2 as t2
  ON t1.col1 = t2.col2;
  

Omezení transakcí využívajících úrovně izolace založené na verzích řádků

Při práci s úrovněmi izolace založené na verzích řádků zvažte následující omezení:

• READ_COMMITTED_SNAPSHOT nelze povolit v tempdb, msdbnebo master.

• Globální dočasné tabulky jsou uloženy v tempdb. Při přístupu k globálním dočasným tabulkám uvnitř transakce SNAPSHOT musí dojít k jedné z následujících situací:

  • Nastavte možnost ALLOW_SNAPSHOT_ISOLATION databáze na ON v tempdb.
  • Použijte vodítko pro izolaci k změně úrovně izolace pro příkaz.

• SNAPSHOT transakce selžou, když:

  • Databáze je nastavena jako pouze pro čtení po spuštění transakce SNAPSHOT, ale předtím, než transakce SNAPSHOT přistoupí k databázi.
  • Pokud dochází k přístupu k objektům z více databází, stav databáze byl změněn takovým způsobem, že k obnovení databáze došlo po zahájení transakce SNAPSHOT, ale před tím, než transakce SNAPSHOT přistoupila k databázi. Například: Databáze byla nastavena na OFFLINE a potom na ONLINE, databáze byla automaticky zavřena a znovu otevřena kvůli možnosti AUTO_CLOSE nastavené na ON, nebo databáze byla odpojena a znovu připojena.

• Distribuované transakce, včetně dotazů v distribuovaných dělených databázích, nejsou podporovány v rámci izolace SNAPSHOT.

• Databázový stroj neuchová více verzí systémových metadat. Příkazy jazyka DDL (Data Definition Language) u tabulek a dalších databázových objektů (indexy, zobrazení, datové typy, uložené procedury a funkce modulu CLR) mění metadata. Pokud příkaz DDL upraví objekt, jakýkoli souběžný odkaz na objekt v rámci izolace SNAPSHOT vede k selhání transakce SNAPSHOT. READ COMMITTED transakce nemají toto omezení, pokud je možnost READ_COMMITTED_SNAPSHOT databáze nastavena na ON.

  Správce databáze například spustí následující příkaz ALTER INDEX.

  USE AdventureWorks2022;
  GO
  ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
  GO
  

  Jakákoli transakce snímku, která je aktivní při spuštění příkazu ALTER INDEX, obdrží chybu, pokud se pokusí odkazovat na HumanResources.Employee tabulku po provedení příkazu ALTER INDEX. READ COMMITTED transakce používající verzování řádků nejsou ovlivněny.

  Poznámka

  BULK INSERT operace můžou způsobit změny v metadatech cílové tabulky (například při zakazování kontrol omezení). Pokud k tomu dojde, souběžné transakce s izolací úrovně SNAPSHOT při přístupu k hromadně vloženým tabulkám selžou.

Přizpůsobení zamykání a verzování řádků

Nastavte časový limit zámku

Pokud instance databázového stroje nemůže udělit zámek transakci, protože jiná transakce již vlastní konfliktní zámek na prostředek, první transakce je zablokována a čeká na uvolnění existujícího zámku. Ve výchozím nastavení neexistuje žádný časový limit pro čekání zámků, a proto může být transakce blokována neomezeně dlouho.

Poznámka

Pomocí zobrazení dynamické správy sys.dm_os_waiting_tasks určete, jestli je úkol blokovaný a co ho blokuje. Další informace a příklady najdete v tématu Vysvětlení a řešení problémů blokujících SQL Server.

Nastavení LOCK_TIMEOUT umožňuje aplikaci nastavit maximální dobu, po kterou příkaz čeká na blokovaný prostředek. Pokud příkaz počká déle než nastavení LOCK_TIMEOUT, blokovaný příkaz se zruší automaticky a vrátí se chybová zpráva 1222 (Lock request time-out period exceeded). Jakákoli transakce obsahující příkaz ale není vrácena zpět. Aplikace proto musí mít obslužnou rutinu chyby, která dokáže zachytit chybovou zprávu 1222. Pokud aplikace nezachytí chybu, může pokračovat, aniž by si uvědomila, že jednotlivý příkaz v rámci transakce byl zrušen, ale transakce zůstává aktivní. K chybám může dojít, protože příkazy později v transakci mohou záviset na příkazu, který nebyl nikdy proveden.

Implementace obslužné rutiny chyby, která zachytí chybovou zprávu 1222, umožňuje aplikaci zpracovat situaci s časovým limitem a provést nápravnou akci, například automatické opětovné odeslání blokovaného příkazu nebo vrácení zpět celé transakce.

Důležitý

Aplikace, které používají explicitní transakce a vyžadují ukončení transakce při přijetí chyby 1222, musí explicitně vrátit transakce zpět v rámci zpracování chyb. Bez tohoto se mohou jiné příkazy neúmyslně provádět ve stejné relaci, zatímco transakce zůstane aktivní, což vede k nekonečnému nárůstu transakčního logu a ztrátě dat v případě, že je transakce později vrácena zpět.

Pokud chcete určit aktuální nastavení LOCK_TIMEOUT, spusťte funkci @@LOCK_TIMEOUT:

SELECT @@LOCK_TIMEOUT;
GO

Přizpůsobení úrovně izolace transakcí

READ COMMITTED je výchozí úroveň izolace databázového stroje. Pokud aplikace musí fungovat na jiné úrovni izolace, může k nastavení úrovně izolace použít následující metody:

• Spusťte příkaz SET TRANSACTION ISOLATION LEVEL.
• ADO.NET aplikace, které používají obor názvů System.Data.SqlClient, mohou pomocí metody IsolationLevel zadat možnost SqlConnection.BeginTransaction.
• Aplikace, které používají ADO, mohou nastavit vlastnost Autocommit Isolation Levels.
• Při spuštění transakce mohou aplikace využívající OLE DB volat ITransactionLocal::StartTransaction s isoLevel nastavenou na požadovanou úroveň izolace transakcí. Při zadávání úrovně izolace v režimu automatického commit mohou aplikace, které používají OLE DB, nastavit DBPROPSET_SESSION vlastnost DBPROP_SESS_AUTOCOMMITISOLEVELS na požadovanou úroveň izolace transakce.
• Aplikace, které používají rozhraní ODBC, mohou nastavit atribut SQL_COPT_SS_TXN_ISOLATION pomocí SQLSetConnectAttr.

Pokud je zadaná úroveň izolace, chování uzamčení pro všechny dotazy a příkazy jazyka DML (jazyk pro manipulaci s daty) v relaci se řídí touto úrovní izolace. Úroveň izolace zůstane platná, dokud není relace ukončena, nebo dokud se úroveň izolace nenastaví na jinou.

Následující příklad nastaví úroveň izolace SERIALIZABLE:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

Úroveň izolace lze přepsat pro jednotlivé dotazy nebo DML příkazy zadáním hintu na úrovni tabulky, pokud je to nutné. Zadání nápovědy na úrovni tabulky nemá vliv na jiné příkazy v relaci.

K určení aktuálně nastavené úrovně izolace transakcí použijte příkaz DBCC USEROPTIONS, jak je znázorněno v následujícím příkladu. Sada výsledků se může lišit od sady výsledků ve vašem systému.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Tady je sada výsledků.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Tipy k uzamčení

Nápovědy k uzamčení lze zadat pro jednotlivé odkazy na tabulky v příkazu SELECT, INSERT, UPDATE, DELETE a MERGE. Rady určují typ uzamčení nebo správy verzí řádků, které instance databázového stroje používá pro data tabulky. Nápovědy pro uzamykání na úrovni tabulky lze použít, pokud je vyžadováno jemné řízení typů zámků získaných u objektu. Tyto rady pro uzamčení přepíší aktuální úroveň izolace transakcí pro relaci.

Poznámka

Pokud je povolené optimalizované zamykání, nedoporučuje se používat rady k uzamčení. I když jsou dodrženy nápovědy pro tabulky a dotazy, snižují výhodu optimalizovaného uzamčení. Další informace naleznete v tématu Vyhnout se zamykacím radám s optimalizovaným zamykáním.

Další informace o konkrétních tipech k uzamčení a jejich chování najdete v tématu Nápovědy k tabulce (Transact-SQL).

Poznámka

Doporučujeme, aby se nápovědy k uzamčení na úrovni tabulky používaly ke změně výchozího chování uzamčení pouze v případě potřeby. Vynucení úrovně uzamčení může nepříznivě ovlivnit souběžnost.

Databázový stroj může při čtení metadat získat zámky, i když zpracovává příkaz s nápovědou k uzamčení, který brání žádostem o sdílené zámky při čtení dat. Například příkaz SELECT spuštěný na úrovni izolace READ UNCOMMITTED nebo použití nápovědy NOLOCK nezíská sdílené zámky při čtení dat, ale při čtení zobrazení systémového katalogu může někdy požadovat zámky. To znamená, že takový příkaz SELECT je možné blokovat, když souběžná transakce upravuje metadata tabulky.

Jak je znázorněno v následujícím příkladu, pokud je úroveň izolace transakce nastavena na SERIALIZABLEa NOLOCK je použita s příkazem SELECT, zámky rozsahu klíčů, které se obvykle využívají k udržení SERIALIZABLE transakcí, nejsou získány.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

Jediný získaný zámek, který odkazuje na HumanResources.Employee, je zámek stability schématu (Sch-S). V tomto případě serializovatelnost už není zaručená.

Možnost LOCK_ESCALATIONALTER TABLE zabrání zámkům tabulek během eskalace zámku a povolí zámky HoBT (oddílu) u dělených tabulek. Tato možnost není nápovědou k uzamčení a lze ji použít ke snížení eskalace zámku. Další informace naleznete v tématu ALTER TABLE (Transact-SQL).

Přizpůsobit uzamčení indexu

Databázový stroj používá strategii dynamického uzamykání, která ve většině případů automaticky zvolí nejlepší členitost uzamčení dotazů. Doporučujeme nepřepsat výchozí úrovně uzamčení, pokud nejsou dobře pochopitelné a konzistentní vzory přístupu k tabulce nebo indexu a existuje problém s kolizemi prostředků, které je potřeba vyřešit. Přepsání úrovně uzamčení může výrazně ztížit souběžný přístup k tabulce nebo indexu. Například zadání zámků pouze na úrovni tabulky u velké tabulky, k níž mají uživatelé častý přístup, může způsobit úzká místa, protože uživatelé musí počkat na uvolnění zámku, než mohou tabulku znovu použít.

Existuje několik případů, kdy zakázání uzamčení stránek nebo řádků může být výhodné, pokud jsou vzory přístupu dobře srozumitelné a konzistentní. Například databázová aplikace používá vyhledávací tabulku, která se každý týden aktualizuje v dávkovém procesu. Souběžní čtenáři přistupují k tabulce se sdíleným zámkem (S) a týdenní dávkové aktualizace přistupují k tabulce s výhradním zámkem (X). Vypnutí uzamčení stránek a řádků v tabulce snižuje režii zamykání během týdne tím, že umožňuje čtenářům souběžný přístup k tabulce prostřednictvím sdílených zámků. Když se dávková úloha spustí, může aktualizaci dokončit efektivně, protože obdrží exkluzivní zámek tabulky.

Vzhledem k tomu, že týdenní dávková aktualizace blokuje souběžné čtenáře v přístupu k tabulce během spuštění aktualizace, může nebo nemusí být vypnutí uzamčení stránek a řádků přijatelné. Pokud dávková úloha změní jenom několik řádků nebo stránek, můžete změnit úroveň uzamčení tak, aby povolovala zamykání na úrovni řádků nebo stránek, což umožní ostatním relacím číst z tabulky bez blokování. Pokud má dávková úloha velký počet aktualizací, může být získání výhradního zámku na tabulce nejlepším způsobem, jak zajistit efektivní spuštění dávkové úlohy.

V některých úlohách může dojít k typu vzájemného zablokování, když dvě souběžné operace získávají zámky řádků ve stejné tabulce a pak se navzájem zablokují, protože obě potřebují uzamknout stránku. Zakázání zámků řádků vynutí jednu z operací čekat, čímž se vyhne deadlocku. Další informace o zablokování najdete v průvodci Deadlocks.

Členitost uzamčení použitého u indexu lze nastavit pomocí CREATE INDEX a ALTER INDEX příkazů. Kromě toho lze příkazy CREATE TABLE a ALTER TABLE použít k nastavení úrovně uzamčení pro omezení PRIMARY KEY a UNIQUE. Kvůli zpětné kompatibilitě může uložená procedura sp_indexoption systému také nastavit členitost. Pokud chcete zobrazit aktuální možnost uzamčení pro daný index, použijte funkci INDEXPROPERTY. Zámky na úrovni stránky, zámky na úrovni řádků nebo zámky na úrovni stránek i zámky na úrovni řádků je možné pro daný index zrušit.

Nepovolené zámky	Index, ke kterému se přistupuje
Úroveň stránky	Zámky na úrovni řádků a tabulky
Úroveň řádku	Zámky na úrovni stránky a tabulky
Úroveň stránky a úroveň řádku	Zámky na úrovni tabulky

Rozšířené informace o transakcích

Vnoření transakcí

Vnoření explicitních transakcí je možné. To je primárně určeno k podpoře transakcí v uložených procedurách, které lze volat buď z procesu již v transakci, nebo z procesů, které nemají žádnou aktivní transakci.

Následující příklad ukazuje použití vnořených transakcí. Pokud je TransProc volána při aktivní transakci, výsledek vnořené transakce v TransProc je kontrolován vnější transakcí a její INSERT příkazy jsou potvrzeny nebo vráceny zpět na základě potvrzení nebo vrácení zpět vnější transakce. Pokud TransProc provádí proces, který nemá nevyrovnanou transakci, COMMIT TRANSACTION na konci procedury potvrdí příkazy INSERT.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

Potvrzení vnitřních transakcí je ignorováno databázovým strojem, když je vnější transakce aktivní. Transakce je potvrzena nebo vrácena zpět na základě potvrzení nebo vrácení zpět na konci vnější transakce. Pokud je vnější transakce potvrzena, vnitřní vnořené transakce jsou také potvrzeny. Pokud je vnější transakce vrácena zpět, všechny vnitřní transakce jsou také vráceny zpět bez ohledu na to, zda vnitřní transakce byly nebo nebyly potvrzeny jednotlivě.

Každé volání COMMIT TRANSACTION nebo COMMIT WORK se vztahuje na poslední spuštěné BEGIN TRANSACTION. Pokud jsou příkazy BEGIN TRANSACTION vnořené, příkaz COMMIT se vztahuje pouze na poslední vnořenou transakci, což je nejvnitřnější transakce. I když příkaz COMMIT TRANSACTION transaction_name v rámci vnořené transakce odkazuje na název transakce vnější transakce, potvrzení se vztahuje pouze na vnitřní transakce.

Není povoleno, aby parametr transaction_name příkazu ROLLBACK TRANSACTION odkazoval na vnitřní transakci v sadě pojmenovaných vnořených transakcí. transaction_name může odkazovat pouze na název nejvzdálenější transakce. Pokud je příkaz ROLLBACK TRANSACTION transaction_name používající název vnější transakce proveden na jakékoli úrovni sady vnořených transakcí, všechny vnořené transakce se vrátí zpět. Pokud je příkaz ROLLBACK WORK nebo ROLLBACK TRANSACTION bez parametru transaction_name proveden na jakékoli úrovni sady vnořených transakcí, vrátí zpět všechny vnořené transakce, včetně vnější transakce.

Funkce @@TRANCOUNT zaznamenává aktuální úroveň vnoření transakcí. Každý BEGIN TRANSACTION příkaz zvýší @@TRANCOUNT o jeden. Každý příkaz COMMIT TRANSACTION nebo COMMIT WORK sníží @@TRANCOUNT o jednu jednotku. Příkaz ROLLBACK WORK nebo ROLLBACK TRANSACTION, který neobsahuje název transakce, vrátí zpět všechny vnořené transakce a dekrementuje @@TRANCOUNT na 0. ROLLBACK TRANSACTION, který používá název transakce nejvzdálenější transakce v sadě vnořených transakcí, vrátí zpět všechny vnořené transakce a @@TRANCOUNT zmenší na 0. Chcete-li zjistit, zda už jste v transakci, použijte SELECT @@TRANCOUNT, abyste viděli, zda je hodnota 1 nebo více. Pokud @@TRANCOUNT je 0, nejste v transakci.

Používejte vázané relace

Vázané relace usnadňují koordinaci akcí napříč několika relacemi na stejném serveru. Vázané relace umožňují dvěma nebo více relacím sdílet stejnou transakci i stejné zámky a mohou pracovat bez konfliktů zámků na stejných datech. Vázané relace je možné vytvořit z několika relací v rámci stejné aplikace nebo z více aplikací s oddělenými relacemi.

Aby se relace mohla účastnit vázané relace, volá buď sp_getbindtoken, nebo srv_getbindtoken (prostřednictvím Open Data Services) za účelem získání tokenu vazby. Token vazby je znakový řetězec, který jednoznačně identifikuje každou vázanou transakci. Token vazby se pak odešle do ostatních relací, které budou svázány s aktuální relací. Ostatní relace se navážou k transakci voláním sp_bindsessionpomocí vazebního tokenu přijatého z první relace.

Poznámka

Relace musí mít aktivní uživatelskou transakci, aby sp_getbindtoken nebo srv_getbindtoken uspěly.

Tokeny vazby musí být přenášeny z kódu aplikace, který vytvoří první relaci, do kódu aplikace, který následně váže jejich relace na tu první relaci. Neexistuje žádný příkaz Transact-SQL ani funkce rozhraní API, které může aplikace použít k získání tokenu vazby pro transakci spuštěnou jiným procesem. Některé z metod, které lze použít k přenosu tokenu vazby, zahrnují následující:

• Pokud jsou všechny relace inicializovány ze stejného procesu aplikace, mohou být tokeny vazby uloženy v globální paměti nebo předány do funkcí jako parametr.

• Pokud jsou relace provedeny z samostatných aplikačních procesů, mohou být tokeny vazby přenášeny pomocí komunikace mezi procesy (IPC), jako je vzdálené volání procedur (RPC) nebo dynamická výměna dat (DDE).

• Tokeny vazby mohou být uloženy v tabulce v instanci databázového stroje a být čteny procesy, které se chtějí připojit k úvodní relaci.

Aktivní může být kdykoli pouze jedna relace v sadě vázaných relací. Pokud jedna relace provádí příkaz v instanci nebo má výsledky čekající na vyřízení z instance, žádná jiná relace svázaná se stejným tokenem nemůže získat přístup k instanci, dokud aktuální relace nedokončí zpracování nebo zruší aktuální příkaz. Pokud je instance zaneprázdněna zpracováním příkazu z jiných vázaných relací, dojde k chybě, která značí, že se používá transakční prostor a relace by to měla zkusit znovu později.

Když spojujete relace, každá relace si zachovává nastavení úrovně izolace. Použití SET TRANSACTION ISOLATION LEVEL ke změně nastavení úrovně izolace jedné relace nemá vliv na nastavení žádné jiné relace vázané na stejný token.

Typy vázaných relací

Dva typy vázaných relací jsou místní a distribuované.

• Místně vázaná relace Umožňuje vázaným relacím sdílet transakční prostor jedné transakce v jedné instanci databázového stroje.

• distribuovaná relace vázaná Umožňuje vázaným relacím sdílet stejnou transakci mezi dvěma nebo více instancemi, dokud nebude celá transakce potvrzena nebo vrácena zpět pomocí programu Microsoft Distributed Transaction Coordinator (MS DTC).

Distribuované vázané relace nejsou identifikovány řetězcem znaků jako vazebním tokenem; jsou identifikovány identifikačními čísly distribuovaných transakcí. Pokud je vázaná relace zapojena do místní transakce a spustí RPC na vzdáleném serveru s SET REMOTE_PROC_TRANSACTIONS ON, místní vázaná transakce je automaticky zvýšena na distribuovanou vázanou transakci MS DTC a je zahájena relace MS DTC.

Kdy použít vázané relace

V dřívějších verzích SQL Serveru se vázané relace používaly především při vývoji rozšířených uložených procedur, které musí spouštět příkazy Transact-SQL jménem procesu, který je volá. Když volající proces předá jako parametr rozšířené uložené procedury token pro vazbu, umožňuje to proceduře zapojit se do transakčního prostoru volajícího procesu a tím integrovat rozšířenou uloženou proceduru s volajícím procesem.

Uložené procedury napsané pomocí MODULU CLR jsou v databázovém stroji bezpečnější, škálovatelné a stabilní než rozšířené uložené procedury. CLR-uložené procedury používají objekt SqlContext k připojení kontextu volající relace, nikoli sp_bindsession.

Vázané relace lze použít k vývoji třívrstvých aplikací, ve kterých je podniková logika začleněna do samostatných programů, které spolupracují na jedné podnikové transakci. Tyto programy musí být kódovány, aby pečlivě koordinovaly přístup k databázi. Vzhledem k tomu, že obě relace sdílejí stejné zámky, nesmí se tyto dva programy pokoušet upravovat stejná data ve stejnou dobu. Kdykoli může pracovat jako součást transakce pouze jedna relace; paralelní spuštění není možné. Transakci lze přepínat pouze mezi relacemi v dobře definovaných kontrolních bodech, například jakmile jsou dokončeny všechny DML příkazy a jejich výsledky jsou získány.

Kódování efektivních transakcí

Je důležité udržovat transakce co nejkratší. Při spuštění transakce musí systém pro správu databáze (DBMS) obsahovat mnoho prostředků až do konce transakce, aby chránil atomicitu, konzistenci, izolaci a stálost (ACID) vlastnosti transakce. Pokud jsou data upravena, musí být upravené řádky chráněny exkluzivními zámky, které brání jakékoli jiné transakci ve čtení řádků, a výhradní zámky musí být uloženy, dokud transakce nebude potvrzena nebo vrácena zpět. V závislosti na nastavení na úrovni izolace transakcí mohou příkazy SELECT získat zámky, které se musí uchovávat, dokud transakce nebude potvrzena nebo vrácena zpět. Zejména v systémech s mnoha uživateli musí být transakce co nejkratší, aby se snížil konflikt zamykání pro prostředky mezi souběžnými připojeními. Dlouhotrvající, neefektivní transakce nemusí být problém s malým počtem uživatelů, ale jsou velmi problematické v systému s tisíci uživatelů. Počínaje SQL Serverem 2014 (12.x) databázový stroj podporuje zpožděné trvalé transakce. Zpožděné trvalé transakce můžou zlepšit škálovatelnost a výkon, ale nezaručují odolnost. Další informace naleznete v tématu Trvanlivost transakce.

Pokyny pro kód

Toto jsou pokyny pro kódování efektivních transakcí:

• Během transakce nevyžadují vstup od uživatelů. Před zahájením transakce získejte všechny požadované vstupy od uživatelů. Pokud je během transakce vyžadován další uživatelský vstup, vraťte aktuální transakci a restartujte transakci po zadání uživatelského vstupu. I když uživatelé reagují okamžitě, jsou doby reakce člověka výrazně pomalejší než rychlost počítače. Všechny prostředky uchovávané transakcí se uchovávají po velmi dlouhou dobu, což má potenciál způsobit blokující problémy. Pokud uživatelé nereagují, transakce zůstane aktivní, uzamkne kritické prostředky, dokud neodpoví, což se nemusí stát po dobu několika minut nebo dokonce hodin.

• Při procházení dat neotevírejte transakci, pokud je to vůbec možné. Transakce by neměly být zahájeny, dokud nebude dokončena veškerá předběžná analýza dat.

• Udržujte transakci co nejkratší. Jakmile znáte změny, které je třeba provést, spusťte transakci, spusťte příkazy pro úpravy a pak okamžitě potvrďte nebo vraťte zpět. Neotevírejte transakci před tím, než je požadována.

• Pokud chcete omezit blokování, zvažte použití úrovně izolace založené na správě verzí řádků pro dotazy jen pro čtení.

• Inteligentní využití nižších úrovní izolace transakcí Mnoho aplikací lze naprogramovat tak, aby používalo úroveň izolace transakcí READ COMMITTED. Málo transakcí vyžaduje transakční izolační úroveň SERIALIZABLE.

• Inteligentně využívejte možnosti optimistické souběžnosti. V systému s nízkou pravděpodobností současných aktualizací může být režie spojená s občasným výskytem chyby, že "někdo jiný změnil vaše data po jejich přečtení," mnohem nižší než režie z neustálého zamykání řádků při jejich čtení.

• Přístup k nejmenšímu množství dat, které je možné v transakci. Tím se sníží počet uzamčených řádků, čímž se sníží kolize mezi transakcemi.

• Vyhněte se pesimistickým zamykacím nápovědám, jako je HOLDLOCK, kdykoli je to možné. Tipy, jako je izolace úrovně HOLDLOCK nebo SERIALIZABLE, mohou způsobit, že procesy musí čekat dokonce i u sdílených zámků a snižovat souběžnost.

• Pokud je to možné, nepoužívejte implicitní transakce. Implicitní transakce mohou způsobit nepředvídatelné chování z důvodu jejich povahy. Viz implicitní transakce a problémy souběžnosti.

Implicitní transakce a vyhnutí se problémům se souběžností a zdroji

Abyste zabránili problémům se souběžností a problémům se zdroji, pečlivě spravujte implicitní transakce. Při použití implicitních transakcí, další Transact-SQL příkaz po COMMIT nebo ROLLBACK automaticky spustí novou transakci. To může způsobit otevření nové transakce, když aplikace prochází data, nebo i když vyžaduje vstup od uživatele. Po dokončení poslední transakce potřebné k ochraně úprav dat vypněte implicitní transakce, dokud transakce nebude znovu vyžadována k ochraně úprav dat. Tento proces umožňuje databázovému stroji používat režim automatickéhocommitu, když aplikace prochází data a získává od uživatele vstup.

Kromě toho, když je povolena úroveň izolace SNAPSHOT, i když nová transakce nebude uchovávat zámky, dlouhotrvající transakce zabrání odebrání starých verzí z úložiště verzí.

Správa dlouhotrvajících transakcí

dlouhotrvající transakce je aktivní transakce, která nebyla včas potvrzena nebo zrušena. Pokud je například začátek a konec transakce řízen uživatelem, typická příčina dlouhotrvající transakce je uživatel, který spouští transakci a poté opouští, zatímco transakce čeká na odpověď od uživatele.

Dlouhotrvající transakce může způsobit vážné problémy databáze následujícím způsobem:

• Pokud je instance serveru vypnuta po provedení řady nepotvrzených úprav, může fáze obnovení následného restartování trvat mnohem déle, než je čas určený možností konfigurace serveru recovery interval nebo možností ALTER DATABASE ... SET TARGET_RECOVERY_TIME. Tyto možnosti řídí aktivní a nepřímé kontrolní body. Další informace o typech kontrolních bodů naleznete v tématu Database checkpoints (SQL Server).

• Je důležitější, že i když čekající transakce může generovat velmi málo záznamu, brání zkrácení transakčního logu na neomezenou dobu, což způsobuje, že transakční log může růst a případně se zaplnit. Pokud se transakční protokol vyplní, databáze nemůže provádět žádné další zápisy. Další informace naleznete v tématu architektury a správy transakčního protokolu SQL Serveru, Řešení potíží s úplným transakčním protokolem (CHYBA SYSTÉMU SQL Server 9002)a Transakční protokol.

Důležitý

V Azure SQL Database se nečinné transakce (transakce, které nebyly zapsány do transakčního protokolu po dobu šesti hodin), automaticky ukončí, aby se uvolnily prostředky.

Zjišťování dlouhotrvajících transakcí

Pokud chcete vyhledat dlouhotrvající transakce, použijte jednu z následujících možností:

• sys.dm_tran_database_transactions

  Toto zobrazení dynamické správy vrací informace o transakcích na úrovni databáze. U dlouhotrvající transakce zahrnují sloupce s určitým zájmem čas prvního záznamu protokolu (database_transaction_begin_time), aktuální stav transakce (database_transaction_state) a pořadové číslo protokolu (LSN) začíná záznam v transakčním protokolu (database_transaction_begin_lsn).

  Další informace naleznete v tématu sys.dm_tran_database_transactions (Transact-SQL).

• DBCC OPENTRAN

  Tento příkaz umožňuje identifikovat ID uživatele vlastníka transakce, takže můžete potenciálně sledovat zdroj transakce pro příslušné ukončení (potvrzení nebo vrácení zpět). Další informace naleznete v tématu DBCC OPENTRAN (Transact-SQL).

Ukončení transakce

Chcete-li ukončit transakci v konkrétní relaci, použijte příkaz KILL. Tento příkaz používejte velmi pečlivě, zejména pokud jsou spuštěny důležité procesy. Další informace naleznete v tématu KILL (Transact-SQL).

Zablokování

Deadlocky jsou složité téma související se zamykáním, ale liší se od blokování.

• Další informace o zámcích, včetně monitorování, diagnostiky a příkladů, najdete v průvodci zámky.
• Další informace o deadlocích specifických pro Azure SQL Database najdete v tématu Analýza a prevence deadlocků ve službě Azure SQL Database.

Související obsah

• Vysvětlení a řešení problémů blokujících SQL Server
• Vysvětlení a řešení problémů s blokováním služby Azure SQL Database
• Transakčně orientovaná dynamická správa pohledů a funkcí (Transact-SQL)
• režijní náklady na správu verzí řádků
• sys.dm_tran_locks (Transact-SQL)