NASTAVIT ÚROVEŇ IZOLACE TRANSAKCÍ (Transact-SQL)

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

Řídí chování uzamčení a správy verzí řádků Transact-SQL příkazů vydaných připojením k SQL Serveru.

Transact-SQL konvence syntaxe

Syntaxe

Syntaxe pro SQL Server, Azure SQL Database a databázi SQL v Microsoft Fabric.

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Syntaxe pro Azure Synapse Analytics a paralelní datový sklad

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Poznámka:

Azure Synapse Analytics implementuje transakce ACID. Výchozí úroveň izolace je READ UNCOMMITTED. Můžete ji READ COMMITTED SNAPSHOT ISOLATION změnit tak, že při ON připojení k READ_COMMITTED_SNAPSHOT databázi změníte master možnost databáze pro uživatelskou databázi. Po povolení se všechny transakce v této databázi spustí v rámci READ COMMITTED SNAPSHOT ISOLATION a nastavení READ UNCOMMITTED na úrovni relace není dodrženo. Další informace naleznete v tématu ALTER DATABASE SET možnosti (Transact-SQL).

Arguments

ČTENÍ NEPOTVRZENÉ

Určuje, že příkazy mohou číst řádky, které byly změněny jinými transakcemi, ale dosud potvrzeny.

Transakce spuštěné na READ UNCOMMITTED úrovni nesdílejí sdílené zámky, aby zabránily jiným transakcím v úpravě dat přečtených aktuální transakcí. READ UNCOMMITTED transakce nejsou blokovány exkluzivní zámky, které by zabránily aktuální transakci čtení řádků, které byly změněny, ale nebyly potvrzeny jinými transakcemi. Pokud je tato možnost nastavená, je možné číst nepotvrzené úpravy, které se nazývají špinavé čtení. Hodnoty v datech lze změnit a řádky mohou být zobrazeny nebo zmizí v sadě dat před koncem transakce. Tato možnost má stejný účinek jako nastavení NOLOCK u všech tabulek ve všech SELECT příkazech v transakci. Toto je nejméně omezující úroveň izolace.

Na SQL Serveru můžete také minimalizovat kolize uzamčení a současně chránit transakce před nezašpiněnými čteními nepotvrzených úprav dat pomocí těchto možností:

• Úroveň READ COMMITTED izolace s možností databáze nastavenou READ_COMMITTED_SNAPSHOT na ON.

• Úroveň SNAPSHOT izolace. Další informace o izolaci snímků naleznete v tématu Izolace snímků na SQL Serveru.

PŘEČTENO POTVRZENO

Určuje, že příkazy nemohou číst data, která byla změněna, ale nebyla potvrzena jinými transakcemi. Zabráníte tak nezašpiněných čtení. Data mohou být změněna jinými transakcemi mezi jednotlivými příkazy v rámci aktuální transakce, což vede k nerepeatable čtení nebo fantomových dat. Tato možnost je výchozím nastavením SQL Serveru.

Chování READ COMMITTED závisí na nastavení READ_COMMITTED_SNAPSHOT možnosti databáze:

• Je-li READ_COMMITTED_SNAPSHOT nastavena na OFF (výchozí na SQL Serveru), databázový stroj používá sdílené zámky, aby zabránil jiným transakcím v úpravách řádků, zatímco aktuální transakce spouští operaci čtení. Sdílené zámky také blokují příkaz číst řádky změněné jinými transakcemi, dokud se druhá transakce nedokončí. Typ sdíleného zámku určuje, kdy se uvolní. Zámky řádků se uvolní před zpracováním dalšího řádku. Zámky stránek se uvolní při čtení další stránky a po dokončení příkazu se uvolní zámky tabulek.

• Pokud READ_COMMITTED_SNAPSHOT je nastavena na ON, databázový stroj používá správu verzí řádků k prezentaci každého příkazu s transakčním konzistentním snímkem dat, jak existoval na začátku příkazu. Zámky se nepoužívají k ochraně dat před aktualizacemi jinými transakcemi.

  • READ_COMMITTED_SNAPSHOT ON je výchozí na Azure SQL Database a SQL databázi v Microsoft Fabric.

Důležité

Volba úrovně izolace transakcí nemá vliv na zámky získané za účelem ochrany úprav dat. Transakce vždy získá výhradní zámek u všech dat, která upraví, a uchovává tento zámek, dokud transakce nekončí, bez ohledu na úroveň izolace nastavenou pro danou transakci. Kromě toho aktualizace provedená na READ COMMITTED úrovni izolace používá zámky aktualizací na vybraných řádcích dat, zatímco aktualizace provedená na SNAPSHOT úrovni izolace používá verze řádků k výběru řádků, které se mají aktualizovat. U operací čtení úrovně izolace transakcí primárně definují úroveň ochrany před účinky úprav provedených jinými transakcemi. Další informace naleznete v tématu Transaction Locking and Row Versioning Guide.

Izolace snímků podporuje data FILESTREAM. V režimu izolace snímků jsou data FILESTREAM přečtená libovolným příkazem v transakci transakční konzistentní verzí dat, která existovala na začátku transakce.

Pokud je READ_COMMITTED_SNAPSHOTmožnost ON databáze , můžete použít nápovědu READCOMMITTEDLOCK tabulky k vyžádání sdíleného uzamčení místo správy verzí řádků pro jednotlivé příkazy v transakcích spuštěných READ COMMITTED na úrovni izolace.

Poznámka:

Když nastavíte READ_COMMITTED_SNAPSHOT tuto možnost, povolí se v databázi pouze připojení, které ALTER DATABASE příkaz spouští. Do dokončení databáze ALTER DATABASE nesmí existovat žádné další otevřené připojení. Databáze nemusí být v režimu jednoho uživatele.

OPAKOVATELNÉ ČTENÍ

Určuje, že příkazy nemohou číst data, která byla změněna, ale dosud potvrzena jinými transakcemi, a že žádné jiné transakce nemohou upravovat data přečtená aktuální transakcí, dokud aktuální transakce nedokončí.

Sdílené zámky jsou umístěny na všechna data přečtená každým příkazem v transakci a jsou uloženy, dokud transakce nebude dokončena. Tím zabráníte jiným transakcím v úpravě všech řádků, které byly přečteny aktuální transakcí. Ostatní transakce mohou vložit nové řádky, které odpovídají podmínkám hledání příkazů vydaných aktuální transakcí. Pokud aktuální transakce pak opakuje příkaz, načte nové řádky, což vede k fantomovým čtením. Vzhledem k tomu, že sdílené zámky se uchovávají na konci transakce místo uvolnění na konci každého příkazu, souběžnost je nižší než výchozí READ COMMITTED úroveň izolace. Tuto možnost použijte pouze v případě potřeby.

SNÍMEK

Určuje, že data přečtená libovolným příkazem v transakci jsou transakční konzistentní verze dat, která existovala na začátku transakce. Transakce může rozpoznat pouze úpravy dat, které byly potvrzeny před zahájením transakce. Změny dat provedené jinými transakcemi po spuštění aktuální transakce nejsou viditelné pro příkazy spuštěné v aktuální transakci. Účinek je, jako kdyby příkazy v transakci získaly snímek potvrzených dat, jak existovaly na začátku transakce.

S výjimkou případů, kdy se databáze obnoví, SNAPSHOT transakce při čtení dat nevyžadují zámky. SNAPSHOT transakce, které čtou data, nezablokují zápis dat jinými transakcemi. Transakce, které zapisují data, nezablokují SNAPSHOT transakce čtení dat.

Během fáze vrácení zpět obnovení databáze transakce požadují zámek, SNAPSHOT pokud je proveden pokus o čtení dat, která jsou uzamčena jinou transakcí, která se vrací zpět. Transakce SNAPSHOT se zablokuje, dokud se transakce nevrátí zpět. Zámek se uvolní hned po udělení.

Možnost ALLOW_SNAPSHOT_ISOLATION databáze musí být nastavena na ON před zahájením transakce, která používá SNAPSHOT úroveň izolace. Pokud transakce používající SNAPSHOT úroveň izolace přistupuje k datům ve více databázích, ALLOW_SNAPSHOT_ISOLATION musí být nastavena na ON každou databázi.

Transakci nelze nastavit na SNAPSHOT úroveň izolace, která začala s jinou úrovní izolace. Tím dojde k přerušení transakce. Pokud transakce začíná na SNAPSHOT úrovni izolace, můžete ji změnit na jinou úroveň izolace a pak zpět na SNAPSHOT. Transakce se spustí při prvním přístupu k datům.

Transakce spuštěná pod SNAPSHOT úrovní izolace může zobrazit změny provedené danou transakcí. Například pokud transakce provádí v UPDATE tabulce a pak vydá SELECT příkaz proti stejné tabulce, upravená data jsou zahrnuta do sady výsledků.

Poznámka:

V režimu izolace snímků jsou data FILESTREAM přečtená libovolným příkazem v transakci transakční konzistentní verzí dat, která existovala na začátku transakce, nikoli na začátku příkazu.

SERIALIZOVATELNÝ

Určuje následující podmínky:

• Příkazy nemohou číst data, která byla změněna, ale dosud potvrzena jinými transakcemi.

• Žádné jiné transakce nemohou upravovat data, která byla načtena aktuální transakcí, dokud aktuální transakce nedokončí.

• Jiné transakce nemohou vložit nové řádky s hodnotami klíče, které by spadají do rozsahu klíčů přečtených libovolnými příkazy v aktuální transakci, dokud aktuální transakce nedokončí.

Zámky rozsahu jsou umístěny v rozsahu hodnot klíčů, které odpovídají podmínkám hledání každého příkazu spuštěného v transakci. Tím se zablokují další transakce v aktualizaci nebo vložení všech řádků, které by mohly být způsobilé pro kterýkoli z příkazů spuštěných aktuální transakcí. To znamená, že pokud se některý z příkazů v transakci spustí podruhé, přečte stejnou sadu řádků. Zámky rozsahu se uchovávají, dokud transakce nebude dokončena. Toto je nejvíce omezující úrovně izolace, protože uzamkne celé rozsahy klíčů a uchovává zámky, dokud transakce nebude dokončena. Vzhledem k tomu, že souběžnost je nižší, použijte tuto možnost pouze v případě potřeby. Tato možnost má stejný účinek jako nastavení HOLDLOCK u všech tabulek ve všech SELECT příkazech v transakci.

Poznámky

Najednou je možné nastavit pouze jednu z možností úrovně izolace a zůstane pro toto připojení nastavená, dokud se explicitně nezmění. Všechny operace čtení prováděné v rámci transakce pracují s pravidly pro zadanou úroveň izolace, pokud v klauzuli příkazu není FROM uvedeno jiné chování uzamčení nebo správy verzí pro tabulku.

Úrovně izolace transakcí definují typ zámků získaných při operacích čtení. Sdílené zámky získané pro READ COMMITTED nebo REPEATABLE READ jsou obecně zámky řádků, i když zámky řádků lze eskalovat na stránku nebo tabulku zámky, pokud čtení odkazuje na velký počet řádků na stránce nebo tabulce. Pokud transakce upraví řádek po přečtení, transakce získá výhradní zámek pro ochranu daného řádku a výhradní zámek zůstane zachován, dokud transakce nebude dokončena. Pokud má transakce například REPEATABLE READ sdílený zámek na řádku a transakce pak upraví řádek, je zámek sdíleného řádku převeden na výhradní zámek řádku.

S jednou výjimkou můžete kdykoli během transakce přepnout z jedné úrovně izolace na jinou. K výjimce dochází při změně z jakékoli úrovně izolace na SNAPSHOT izolaci. To způsobí, že transakce se nezdaří a vrátí zpět. Transakce spuštěná v SNAPSHOT izolaci však můžete změnit na jakoukoli jinou úroveň izolace.

Když změníte transakci z jedné úrovně izolace na jinou, prostředky, které se čtou po změně, jsou chráněny podle pravidel nové úrovně. Zdroje, které jsou přečtené před změnou, budou nadále chráněny podle pravidel předchozí úrovně. Pokud se například transakce změnila z READ COMMITTED na SERIALIZABLE, sdílené zámky získané po změně se nyní uchovávají až do konce transakce.

Pokud vydáte problém SET TRANSACTION ISOLATION LEVEL s uloženou procedurou nebo triggerem, když objekt vrátí řízení úrovně izolace je resetována na úroveň, která se projeví při vyvolání objektu. Pokud například nastavíte REPEATABLE READ dávku a dávka pak zavolá uloženou proceduru, která nastaví úroveň izolace na SERIALIZABLE, nastavení úrovně izolace se vrátí, REPEATABLE READ když uložená procedura vrátí řízení do dávky.

Poznámka:

Uživatelem definované funkce a uživatelem definované typy CLR (Common Language Runtime) nelze spustit SET TRANSACTION ISOLATION LEVEL. Úroveň izolace ale můžete přepsat pomocí nápovědy tabulky. Další informace naleznete v části Tipy pro tabulku (Transact-SQL).

Když použijete sp_bindsession k vytvoření vazby dvou relací, každá relace si zachová nastavení úrovně izolace. Použití SET TRANSACTION ISOLATION LEVEL ke změně nastavení úrovně izolace jedné relace nemá vliv na nastavení žádné jiné relace, které jsou s ní svázané.

SET TRANSACTION ISOLATION LEVEL se projeví při spuštění nebo spuštění, a ne v době analýzy.

Optimalizované operace hromadného zatížení blokovaných dotazů hald, které běží na následujících úrovních izolace:

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED používání správy verzí řádků

Dotazy, které běží pod těmito úrovněmi izolace, naopak blokují optimalizované operace hromadného zatížení na haldách. Další informace o hromadných operacích načítání naleznete v tématu Hromadný import a export dat (SQL Server).

Databáze s podporou FILESTREAM podporují následující úrovně izolace transakcí.

Úroveň izolace	přístup k Transact-SQL	Přístup k systému souborů
Nepotvrzené čtení	SQL Server	Nepodporované
Potvrzené čtení	SQL Server	SQL Server
Opakovatelné čtení	SQL Server	Nepodporované
Serializovatelný	SQL Server	Nepodporované
Čtení potvrzených snímků	SQL Server	SQL Server
Snapshot	SQL Server	SQL Server

Examples

Následující příklad nastaví TRANSACTION ISOLATION LEVEL relaci. Pro každý Transact-SQL příkaz, který následuje, SQL Server uchovává všechny sdílené zámky až do konce transakce.

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

Související obsah

• ALTER DATABASE (Transact-SQL)
• DBCC USEROPTIONS (Transact-SQL)
• SELECT (Transact-SQL)
• Příkazy SET (Transact-SQL)
• Rady k tabulce (Transact-SQL)