Transakce SQL je sekvence jedné nebo více operací SQL (např.INSERT UPDATE DELETE) provedené jako jeden celek práce. Transakce zajišťují, že buď všechny operace budou úspěšné, nebo se nepoužije žádná při zachování integrity dat.

Klíčové vlastnosti SQL transakcí: ACID

Integrita transakcí SQL je řízena vlastnostmi ACID, které zaručují spolehlivé databázové transakce. Tyto čtyři vlastnosti spolupracují, aby zaručily, že databáze zůstane konzistentní a spolehlivá.

• Atomicita: Výsledek transakce může být buď zcela úspěšný, nebo zcela neúspěšný. Celá transakce musí být vrácena zpět, pokud jedna její část selže.
  
• Konzistence: Transakce zachovávají omezení integrity přesouváním databáze z jednoho platného stavu do jiného.
  
• Izolace: Souběžné transakce jsou od sebe izolovány, což zajišťuje přesnost dat.
  
• Trvanlivost: Jakmile je transakce potvrzena, její změny zůstávají v platnosti i v případě selhání systému.

SQL příkazy řízení transakcí

V příkazech řízení transakcí SQL spravujte provádění operací SQL zajišťujících integritu a spolehlivost databázových transakcí. Tyto příkazy pomáhají spravovat počáteční potvrzení a vrácení změn provedených v databázi. Níže jsou vysvětleny klíčové příkazy pro řízení transakcí v SQL se syntaxí a příklady pro každý z nich.

1. Příkaz BEGIN TRANSACTION

TheBEGIN TRANSACTIONpříkaz označuje začátek nové transakce. Všechny příkazy SQL, které následují po tomto příkazu, budou součástí stejné transakce, dokud aCOMMIT nebo ROLLBACK se narazí. Tento příkaz neprovede žádné změny v databázi, pouze spustí transakci.

Syntax:

BEGIN TRANSACTION transaction_name ;

Příklad transakce SQL se scénářem bankovního převodu

Podívejme se na příklad bankovního převodu mezi dvěma účty. Tento příklad ukazuje použití více dotazů v jedné transakci.

BEGIN TRANSACTION;  
  
-- Deduct 0 from Account A  
UPDATE Accounts  
SET Balance = Balance - 150  
WHERE AccountID = 'A';  
  
-- Add 0 to Account B  
UPDATE Accounts  
SET Balance = Balance + 150  
WHERE AccountID = 'B';  
  
-- Commit the transaction if both operations succeed  
COMMIT;

Pokud dojde k nějaké chybě, jako je problém sUPDATEdotaz, který můžete použítROLLBACKpro vrácení všech změn provedených během transakce:

ROLLBACK;

Tím je zajištěno, že systém neskončí v nekonzistentním stavu, jako je odečítání peněz z jednoho účtu, aniž by byly přidány na jiný.

BEGIN TRANSACTION TransferFunds;

2. Příkaz COMMIT

TheCOMMITpříkaz se používá k uložení všech změn provedených během aktuální transakce do databáze. Jakmile je transakce potvrzena, změny jsou trvalé. 

Syntax:

COMMIT;

Příklad

Zde je ukázkaStudenttabulka, která bude použita k provádění operací v tomto příkladu. Tato tabulka obsahuje základní podrobnosti o studentovi, jako je věk ID jména a další relevantní informace, se kterými se bude manipulovat pomocí různých transakční kontrolní příkazy.

Následuje příklad, který by odstranil ty záznamy z tabulky, které mají věk = 20, a poté COMMIT změny v databázi. 

DELETE FROM Student WHERE AGE = 20;  
COMMIT;

Výstup

vložení řazení java

3. Příkaz ROLLBACK

TheROLLBACKpříkaz se používá k vrácení všech změn provedených v aktuální transakci. Používá se, když dojde k chybě nebo když požadované změny nelze dokončit. Databáze se vrátí do stavu, ve kterém byla před BEGIN TRANSACTION byl popraven.

Syntax:

ROLLBACK;

Příklad

Odstraňte z tabulky ty záznamy, které mají věk = 20, a poté VRTULUJTE změny v databázi. V tomto případě DELETE operace se vrátí zpět a změny v databázi se neuloží.

DELETE FROM Student WHERE AGE = 20;  
ROLLBACK;

výstup:

4. Příkaz SAVEPOINT

ASAVEPOINTse používá k vytvoření a kontrolní bod v rámci transakce. Můžeme se vrátit ke konkrétnímuSAVEPOINTmísto vrácení celé transakce. To nám umožňuje vrátit zpět část transakce celou transakci.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Příklad

SAVEPOINT SP1;  
//Savepoint created.  
DELETE FROM Student WHERE AGE = 20;  
//deleted  
SAVEPOINT SP2;  
//Savepoint created.

výstup:

Vysvětlení:

Z výše uvedeného příkladu Ukázková tabulka1 Smažte z tabulky ty záznamy, které mají věk = 20, a poté VRÁTNĚTE změny v databázi zachováním bodů uložení. Zde je SP1 prvním SAVEPOINT vytvořeným před vymazáním. V tomto příkladu došlo k jednomu vymazání. Po opětovném smazání se vytvoří SAVEPOINT SP2. 

5. NÁVRAT NA ULOŽENÝ BOD

TheROLLBACK TO SAVEPOINTnám umožňuje vrátit transakci zpět do určitého bodu uložení a efektivně vrátit změny provedené po tomto bodu.

Syntax:

ROLLBACK TO SAVEPOINT SAVEPOINT_NAME;

Příklad

Proběhlo vymazání, předpokládejme, že jsme změnili názor a rozhodli jsme se VRÁTIT SE do SAVEPOINT, který jsme identifikovali jako SP1, který je před smazáním. Takže v tomto případěDELETEoperace se vrátí zpět a transakce se vrátí do stavu, ve kterém bylaSP1savepoint.

ROLLBACK TO SP1;  
//Rollback completed

výstup:

6. Příkaz RELEASE SAVEPOINT

Tento příkaz se používá k odstranění SAVEPOINT, který jsme vytvořili. Jakmile byl SAVEPOINT uvolněn, již nemůžeme ROLLBACK používat příkaz ke zrušení transakcí provedených od posledního SAVEPOINT. Používá se k zahájení databázové transakce a používá se ke specifikaci charakteristik transakce, která následuje. 

Syntax:  

RELEASE SAVEPOINT SAVEPOINT_NAME;

Příklad

Jakmile je záchranný bodSP2je propuštěn, už se k němu nemůžeme vrátit.

RELEASE SAVEPOINT SP2; -- Release the second savepoint.

Proč používat transakce v bankovnictví?

V tomto případě bez transakce riskujete scénáře, kdy jsou peníze odečteny z jednoho účtu, ale nejsou přidány na druhý, čímž systém zůstane v nekonzistentním stavu. Transakce zajišťují, že se těmto problémům zabrání tím, že zaručí, že obě operace budou úspěšné nebo neúspěšné společně.

Typy SQL transakcí

Existují různé typy transakcí podle jejich povahy a konkrétních operací, které provádějí:

• Přečtěte si Transakce : Používá se pouze ke čtení dat obvykle pomocí SELECT dotazy.
  
• Napište transakce : Ty zahrnují úpravu dat v databázi pomocí INSERT UPDATEneboDELETEoperace.
  
• Distribuované transakce : Tyto transakce zahrnují více databází a zajišťují konzistenci mezi nimi.
  
• Implicitní transakce : Automaticky spouští SQL Server pro určité operace.
  
• Explicitní transakce : Ručně řízené transakce, kde uživatel začíná a ukončuje transakci pomocíBEGIN TRANSACTION COMMITaROLLBACK.

Monitorování a optimalizace transakcí SQL

Chcete-li zachovat výkon a předejít problémům, zvažte následující techniky:

1. Zámky monitoru : Sledujte chování zamykání a upravujte dotazy, abyste minimalizovali konflikty zamykání.

2. Omezte rozsah transakce : Omezte počet řádků nebo záznamů ovlivněných transakcí, abyste urychlili zpracování.

3. Použijte dávkové zpracování : Pokud zpracováváte velké množství dat, rozdělte operace na menší transakce nebo dávky, abyste zabránili zahlcení systému.

Který z následujících scénářů nejlépe popisuje porušení vlastnosti 'Isolation' v ACID?

• A

  Transakce opustí databázi ve stavu, který porušuje omezení primárního klíče.

• B

  Dvě transakce běžící současně čtou a zapisují do stejných dat, což vede k nekonzistentním výsledkům.

• C

  Uživatel úspěšně aktualizuje záznam, ale selhání systému tuto změnu vymaže.

• D

  Transakce selže uprostřed a všechny její změny jsou vráceny.

Toto je klasický příklad narušení izolace, kdy je mezistav jedné transakce viditelný pro druhou.

V bankovní aplikaci převod prostředků zahrnuje odepsání částky z jednoho účtu a připsání částky na jiný. Která vlastnost ACID zajišťuje dokončení buď obou operací, nebo žádné?

• A

  Izolace

• B

  Atomicita

• C

  Trvanlivost

• D

  Konzistence

Atomicita zajišťuje úspěšné dokončení všech operací v rámci transakce; jinak bude celá transakce vrácena zpět.

Transakce je provedena a je vydán „COMMIT“. Ihned po výpadku napájení. Která vlastnost ACID zaručuje, že změny provedené transakcí jsou stále přítomné i po restartu systému?

• A

  Konzistence

• B

  Atomicita

• C

  Trvanlivost

• D

  Izolace

Trvanlivost je vlastnost, která zajišťuje, že jakmile je transakce potvrzena, zůstane taková i v případě výpadku napájení nebo zhroucení systému.

Jaký je primární účel příkazu 'SAVEPOINT' v transakci?

• A

  K provedení části transakce.

• B

  Označení bodu v transakci, ke kterému se můžete později vrátit.

• C

  Chcete-li trvale uložit stav transakce.

• D

  Ukončit transakci a provést všechny změny natrvalo.

'SAVEPOINT' umožňuje částečné vrácení zpět v rámci transakce.

Zvažte následující transakci: 'START TRANSACTION; INSERT ...; SAVEPOINT A; AKTUALIZACE ...; ULOŽENÍ BOD B; VYMAZAT ...; NÁVRAT DO ÚLOŽNÉHO BODU A;' Jaký je stav transakce po příkazu 'ROLLBACK'?

• A

  Celá transakce je vrácena zpět.

• B

  Pouze 'DELETE' je vráceno zpět.

• C

  Došlo k chybě, protože se nemůžete vrátit zpět k bodu uložení, který není nejnovější.

• D

  'INSERT' je uloženo, ale 'UPDATE' a 'DELETE' jsou vráceny zpět.

Návrat zpět na 'SAVEPOINT A' zruší všechny změny provedené po tomto bodu uložení, kterými jsou 'UPDATE' a 'DELETE'.