Jako transakci označujeme v SQL logickou množinu nebo sekvence operací, která je do této transakce patří. SQL Transakce můžeme použít pokud nad databázemi provádíme nějaké změny. Všechny prováděné změny jsou poté do této transakce zahrnuty. Můžeme je rozdělit na systémové a uživatelské transakce.

Výhodou používání transakcí je to, že provedené změny jsou odvolatelné a transakci musíme vždy potvrdit přes příkaz COMMIT nebo odvolat přes příkaz ROLLBACK.

Proč je používání transakce výhodné?

Vezměte si situaci, kdy provádíte několik UPDATE a INSERT operací za sebou. Během průběhu operací se může vyskytnout chyba nebo máte chybu ve skriptu. Pokud jste nepoužili transakci (např. v kombinaci s TRY/CATCH) budete v některých případech složitě dohledávat které části skriptu byly provedeny a které ne. Můžete se dostat do takových problémů, že budete potřebovat zálohu db.

Všechny operace, kde provádíme nějakou změnu v databázi mohou být součástí transakcí:

• DML operace (data manipulation language) – INSERT, UPDATE, DELETE
• DDL operace (data definition language) – např. CREATE TABLE, CREATE INDEX, atp.

Pokud definujeme sql transakci a v rámci ní provedeme nějakou DML operaci, dochází k uzamčení objektu nad kterým DML provádíme – row lock, page lock, table lock

Transakce je definována v rámci sql relace, pokud je relace otevřena, tak je transakce (a locky) stále aktivní

Syntaxe uživatelské SQL transakce

BEGIN TRANSACTION --definice transakce

   --DDL nebo DML operace

END TRANSACTION;
COMMIT; --potvrzení transakce
ROLLBACK; --odvolání transakce

Transakce mohou být vnořené takže můžete vytvořit transakci v transakci (nested transaction).

Funkce @@TRANCOUNT a XACT_STATE()

Tyto funkce slouží pro zjištění existence, stavu a počtu vnoření transakcí.

@@TRANCOUNT – počet aktivních transakcí v SQL

Pomocí této systémové funkce zjistíme počet transakcí v rámci relace

• 0 znamená, že neexistuje aktivní transakce
• >0 znamená, že existuje aktivní transakce
• >1 existuje aktivní vnořená transakce

Syntaxe TRANCOUNT:

SELECT @@TRANCOUNT

XACT_STATE() – test jestli existuje otevření transakce

tato funkce je podobná, ale dává jiné informace

• 0 neexistuje aktivní transakce
• 1 existuje nepotvrzená transakce, která může být potvrzena (počet vnořených transakcí se nezjišťuje)
• -1 existuje nepotvrzená transakce, která nemůže být potvrzena z důvodu chyby

Syntaxe XACT_STATE():

SELECT XACT_STATE()

Savepoints v transakci

Záchranné body jsou užitečná funkce. Umožňují v rámci transakce nadefinovat místa, ke kterým se můžete použitím ROLLBACK vrátit. Nemusíte tak odvolávat celou transakci, ale vrátit se zpět pouze o pár kroků.

Syntaxe vytvoření sql SAVEPOINT

BEGIN TRANSACTION --definice transakce

   --DDL nebo DML operace 1

  SAVE TRANSACTION <název záchranného bodu 1>

   --DDL nebo DML operace 2

  SAVE TRANSACTION <název záchranného bodu 2>

END TRANSACTION;
COMMIT; --potvrzení transakce
ROLLBACK <název záchranného bodu 1>; --vrátí se k savepoint 1