Transakce na serveru SQL Server je a sekvenční skupina příkazů nebo dotazů provádět jeden nebo více úkolů v databázi. Každá transakce může mít jednu operaci čtení, zápis, aktualizaci nebo odstranění nebo kombinaci všech těchto operací. Každá transakce se musí na SQL Serveru stát dvěma věcmi:
- Buď jsou všechny úpravy úspěšné, když je transakce potvrzena.
- Nebo se všechny úpravy vrátí zpět, když je transakce odvolána.
Transakce nemůže být úspěšná, dokud nebudou dokončeny všechny operace v sadě. Znamená to, že pokud některý argument selže, transakce transakce selže. Každá transakce začíná prvním spustitelným příkazem SQL a končí, když najde potvrzení nebo vrácení zpět, ať už explicitně nebo implicitně. Používá se SPÁCHAT nebo NÁVRAT příkazy explicitně i implicitně při použití příkazu DDL.
Níže uvedené obrázkové znázornění vysvětluje proces transakce:
Následující příklad vysvětlí koncept transakce:
Tento příklad bude používat bankovní databázový systém k vysvětlení pojmu transakce. Předpokládejme, že zákazník banky chce vybrat peníze ze svého účtu pomocí režimu ATM. Bankomat může tuto operaci provést ve třech krocích:
- The První krok je zkontrolovat dostupnost požadované částky na účtu.
- The druhý krok odečte částku z účtu, pokud je částka k dispozici, a poté aktualizuje zůstatek účtu.
- The třetí krok je zapsat operaci výběru peněz do souboru protokolu. Tento krok píše o tom, že transakce byla úspěšná nebo neúspěšná. V případě úspěchu zapište úpravu dat do databáze. V opačném případě bude transakce vrácena zpět do předchozího stavu.
Základní princip transakcí spočívá v tom, že pokud jeden z příkazů vrátí chybu, celá sada změn je vrácena zpět, aby byla zajištěna integrita dat. A pokud budou transakce úspěšné, všechny změny budou v databázi trvalé. Pokud tedy dojde k výpadku proudu nebo jiným problémům při výběru peněz z bankomatu, transakce zaručují, že náš zůstatek zůstane konzistentní. Tyto operace nejlépe provádí výpis transakce, protože čtyři klíčové vlastnosti transakce činí všechny operace přesnějšími a konzistentnějšími. Čtyři vlastnosti transakce se označují jako ACID.
Vlastnosti transakce
Vlastnosti transakce se označují jako vlastnost ACID (Atomicity, Consistency, Isolation, Durability), které jsou podrobně popsány níže:
Atomicita: Tato vlastnost zajišťuje, že všechny příkazy nebo operace zahrnuté v transakci musí být úspěšně provedeny. V opačném případě bude celá transakce přerušena a všechny operace budou vráceny zpět do předchozího stavu, když jakákoli operace selže.
Konzistence: Tato vlastnost zajišťuje, že se stav databáze změní pouze v případě, že transakce bude úspěšně potvrzena. Je také zodpovědný za ochranu dat před pády.
Izolace: Tato vlastnost zaručuje, že všechny transakce jsou izolované od ostatních transakcí, což znamená, že každá operace v transakci je provozována nezávisle. Zajišťuje také, že prohlášení jsou vzájemně transparentní.
Trvanlivost: Tato vlastnost zaručuje, že výsledek potvrzených transakcí zůstane v databázi trvale, i když systém zkolabuje nebo selže.
Transakční režimy na serveru SQL Server
Existují tři různé režimy transakcí, které může SQL Server používat:
Režim transakce automatického potvrzení: Je to výchozí transakční režim serveru SQL. Vyhodnotí každý příkaz SQL jako transakci a výsledky se podle toho potvrdí nebo vrátí zpět. Úspěšné příkazy jsou tedy okamžitě potvrzeny, zatímco neúspěšné příkazy jsou okamžitě vráceny zpět.
Režim implicitní transakce. Tento režim umožňuje serveru SQL Server zahájit implicitní transakci pro každý příkaz DML, ale explicitně vyžaduje použití příkazů potvrzení nebo vrácení na konci příkazů.
Explicitní režim transakce: Tento režim je definován uživatelem, který nám umožňuje přesně identifikovat začátek a konec transakce. V případě fatální chyby se automaticky přeruší.
Kontrola transakcí
K řízení transakcí se používají následující příkazy:
POZNÁMKA: Pro příkazy jazyka Transaction Control Language můžeme použít pouze příkazy DML (INSERT, UPDATE a DELETE). Nemůžeme je použít při vytváření nebo rušení tabulek, protože tyto operace se do databáze zadávají automaticky.
Stav transakce
Udává, jak transakce probíhají během jejich životnosti. Popisuje aktuální stav transakce i to, jak bude transakce zpracována v budoucnu. Tyto stavy definují pravidla, která určují, zda se transakce potvrdí nebo přeruší.
Popišme jednotlivé stavy transakcí na serveru SQL Server:
Aktivní stav: Transakce je v aktivním stavu, zatímco se provádějí instrukce transakce. Změní se na „částečně angažovaný stát“ pokud jsou všechny operace 'čtení a zápis' dokončeny bez chyb. Pokud některá instrukce selže, změní se na „stav selhání“.
Částečně odsouhlaseno: Po dokončení všech operací čtení a zápisu se změna provede v hlavní paměti nebo místní vyrovnávací paměti. Stát by šel do 'oddaný stát' pokud jsou změny v databázi trvalé. V opačném případě přejde do „stavu selhání“.
Stav selhání: Transakce přejde do neúspěšného stavu, když selže jakýkoli pokyn transakce nebo selže trvalá úprava v databázi.
Stav přerušení: Transakce se přesune z a 'neúspěšný stav' do an 'aborted state' když dojde k jakémukoli selhání. Změny jsou odstraněny nebo vráceny zpět, protože tyto změny byly provedeny pouze v místní vyrovnávací paměti nebo hlavní paměti v předchozích stavech.
Zavázaný stát: Transakce je dokončena a přejde do tohoto stavu, když jsou změny v databázi trvalé a ukončeny v „ukončený stav“.
Ukončený stav: Pokud nedojde k vrácení zpět a transakce je v 'zavázaný stát,' systém je konzistentní a připravený na novou transakci, zatímco stará je ukončena.
Implementace transakce v SQL Server
Vezměme si několik příkladů, abychom pochopili, jak můžeme implementovat transakci na SQL Server. Zde použijeme 'Produkt' tabulka pro demonstraci všech stavů transakcí.
Následující skripty SQL vytvoří tabulku Produkt ve vybrané databázi:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Dále spusťte níže uvedené skripty pro vložení dat do této tabulky:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Provedením příkazu SELECT ověřte data:
Příklad transakce COMMIT
Příkazy SQL použité v transakci je vhodné rozdělit do více logických částí. A pak se můžeme rozhodnout, zda data potvrdit nebo vrátit zpět. Následující kroky ilustrují vytvoření transakce:
- Zahajte transakci pomocí ZAČNĚTE TRANSAKCI příkaz.
- Napište SQL příkazy a rozdělte je podle našich potřeb
- Použijte SPÁCHAT pro dokončení transakce a trvalé uložení změn.
Níže jsou uvedeny příkazy, které vysvětlují operace COMMIT v SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Pokud není nalezena žádná chyba, uvidíme následující výstup, kde se každý příkaz SQL transakce provede nezávisle:
Příkazy INSERT a UPDATE nelze po potvrzení transakce vrátit zpět. Když ověříme tabulku po operaci potvrzení, uvidíme následující data:
řetězec formátu java
Příklad transakce ROLLBACK
Pomocí příkazu ROLLBACK vrátíme zpět všechny transakce, které ještě nebyly uloženy do databáze, a vrátíme se do bodu, kde transakce začala. Následující příklad vysvětluje operaci ROLLBACK na serveru SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Jakmile provedeme výše uvedenou transakci, můžeme vidět, že bude úspěšně provedena. Neovlivní to však žádné změny v databázi, protože dokud neprovedeme příkaz COMMIT nebo ROLLBACK, změny se nemohou stát trvalými. Proto máme možnost pomocí příkazu ROLLBACK transakce vrátit zpět všechny operace databáze. Zde je úplný výpis transakce:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Použití globální proměnné @@Error v transakcích:
Tato proměnná je slouží ke kontrole, zda došlo k chybě nebo ne. Níže uvedený příklad vysvětluje jeho koncept. Zde nejprve zahájíme transakci pomocí příkazu BEGIN a poté napíšeme dva příkazy insert. Dále použijeme globální systémovou proměnnou @@CHYBA v prohlášení IF zkontrolovat chybu. Pokud je hodnota větší než 0, znamená to, že došlo k nějaké chybě. Nyní se transakce vrátí zpět; jinak je transakce potvrzena.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Po provedení výše uvedené transakce si všimneme, že byla vrácena zpět. Je to kvůli našemu pokusu vložit duplicitní hodnotu do sloupce Primární klíč.
Transakce automatického vrácení zpět
Většina transakcí obsahuje více než jeden dotaz. Pokud při provádění transakce některý z příkazů SQL způsobí chybu, v databázi nedojde k žádným změnám a zbývající příkazy se neprovedou. Tento koncept je na serveru SQL Server známý jako transakce automatického vrácení zpět. Pro demonstraci tohoto procesu použijeme jednoduchý příklad.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Tato transakce vytváří následující výstup:
V tomto výstupu můžeme vidět, že příkaz insert byl úspěšně proveden. Při provádění příkazu aktualizace však došlo k chybě kvůli problému s převodem datových typů. V tomto případě SQL Server nepovolí žádné změny v databázi, což znamená, že operace vložení nepřidá žádnou hodnotu a příkaz select se neprovede.
Bod uložení v Transakcích
Bod uložení vloží do transakce speciální značku, která nám umožní vrátit zpět všechny změny provedené po bodu uložení. Používá se také k vrácení jakékoli konkrétní části transakce, nikoli celé transakce. Můžeme to definovat pomocí ULOŽIT TRANSAKCI sp_name prohlášení. Následující příklad vysvětlí použití bodu uložení v transakcích, které potvrdí příkaz insert a vrátí příkaz delete.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Podívejte se na níže uvedený výsledek, kde vidíme, že produkt id 116 je odstraněn a 117 je vložen do prvního výstupu. Ve druhém výstupu je však operace odstranění vrácena zpět kvůli bodu uložení.
Jak uvolnit bod uložení v transakci?
Uvolnit bod uložení se používá k odstranění pojmenovaného bodu uložení z aktuální transakce bez vrácení výsledků dotazů provedených po bodu uložení. MySQL má tento příkaz, ale SQL Server neposkytuje žádný příkaz k uvolnění bodu uložení. Místo toho jsou automaticky uvolněny na konci transakce potvrzení nebo vrácení zpět, takže se o ně nemusíme starat.
Implicitní transakce v SQL Server
Implicitní transakci můžeme definovat povolením volby IMPLICIT_TRANSACTIONS. Následující příklad tento koncept snadno vysvětlí:
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
V této transakci jsme použili dvě možnosti @@OPTION a @@TRANCOUNT. @@OPTOPN poskytuje informace týkající se aktuálních možností SET a @@TRANCOUNT poskytuje příkaz BEGIN TRANSACTION v aktuální relaci.
Nyní provedení transakce vrátí níže uvedený výstup:
Explicitní transakce v SQL Server
Explicitní transakce musí být definována pomocí příkazu BEGIN TRANSACTION, protože identifikuje počáteční bod explicitní transakce. Můžeme definovat explicitní transakci v SQL Server, jak je uvedeno níže:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
V syntaxi volba trans_name označuje jedinečný název transakce. The @trans_name_var označuje uživatelem definovanou proměnnou, která ukládá název transakce. Konečně, OZNAČIT nám umožňuje označit konkrétní transakci v souboru protokolu.
Explicitní transakce prostřednictvím příkazu BEGIN TRANSACTION získala zámek v závislosti na úrovni izolace zdrojů souvisejících s transakcemi. Pomáhá snížit problémy se zámkem. Viz níže uvedený příklad:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Zde je výstup:
Označená transakce v SQL Server
Označená transakce se používá k přidání popisu ke konkrétní transakci v souborech protokolu. Můžeme jej použít jako bod obnovy místo data a času při obnově databáze do předchozího stavu. Musíme vědět, že značka je přidána do souborů protokolu pouze tehdy, když označená transakce modifikuje databázi. Jeho koncept pochopíme na následujícím příkladu.
Předpokládejme, že jsme omylem upravili databázi a neznáme přesný okamžik změny dat; v takovém případě může obnova dat trvat dlouho. Pokud však použijeme označené transakce, může to být užitečný nástroj pro určení přesného načasování změn dat.
Následující syntaxe ilustruje označenou transakci na serveru SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Zde musíme definovat název transakce a poté přidat možnost WITH MARK. V níže uvedeném příkladu odstraníme záznamy a přidáme značku do souboru protokolu:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
The logmarkhistory tabulka je součástí databáze msdb a ukládá informace týkající se každé označené transakce, která byla potvrzena. Provedením níže uvedeného příkazu získáte podrobnosti z tabulky logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Pojmenovaná transakce na serveru SQL Server
Můžeme také poskytnout název pro naši transakci v SQL Server. Při práci s mnoha transakcemi v jednom dotazu se vždy doporučuje použít pojmenovanou transakci. Níže uvedený příklad vysvětluje, jak přejmenovat transakci:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Zde je výstup:
Závěr
Tento článek poskytne úplný přehled transakce v příkazech SQL Server. Transakce jsou užitečné v relačních databázových systémech, protože zajišťují integritu databáze.