Transaktioner

Transaktioner är händelser som ändrar data i databasen och alla dessa händelser registreras i transaktionsloggen. Om man t ex kör ett skript med någon av satserna UPDATE, DELETE eller INSERT, sker transaktionen direkt och det går inte att ångra. Det är dock möjligt att styra när transaktioner äger rum, genom att ge SQL-servern mer information än ovanstående satser. Alla ändringar efter BEGIN TRANSACTION kan ångras, men efter COMMIT TRANSACTION blir ändringarna permanenta. Om det uppstår några fel, kan alla ändringar raderas med satsen ROLLBACK TRANSACTION.

BEGIN TRANSACTION

BEGIN { TRAN | TRANSACTION }
[ { transaction_name | @tran_name_variable }
[ WITH MARK [ ‘description’ ] ]
]
[ ; ]

För att kunna kontrollera när transaktionen äger rum måste man först tala om för SQL-servern när den börjar. Då kommer inte efterföljande kod att köras automatiskt. Det går även bra att använda den kortare varianten TRAN i stället för nedanstående TRANSATION.

BEGIN TRANSACTION AddPerson
    INSERT INTO Personer
        (Name, Phone, City)
    VALUES
        ('Suzanne Apelqvist', '070-333 22 11', 'Örnsköldsvik')

Här kommer ingenting att skrivas till databasen.

Det går att namnge transaktionerna som jag har gjort ovan med ”AddPerson”. Detsamma gäller för nedanstående COMMIT och ROLLBACK. Det är en fördel att namnge transaktioner när de innehåller många händelser. Använd bara transaktionsnamn för de yttre paren av nästlade satserna BEGIN…COMMIT eller BEGIN…ROLLBACK. Observera också att transaktionsnamn är känsliga för små och stora bokstäver, även om instansen inte är det.

Om inte en plats i transaktionen har angetts för att spara eller om inget transaktionsnamn har angetts, ångras hela transaktionen. Vid nästlade transaktioner ångras alla inre transaktioner till den yttre satsen BEGIN TRANSACTION.

COMMIT

COMMIT [ { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]
[ ; ]

När du är säker på att du inte kommer att ångra dig och händelserna ska sparas permanent i databasen, avslutar du med COMMIT TRANSACTION. Transaktionsnamn ignoreras och är bara en hjälp för programmeraren. Inre nästlade COMMIT-satser frigör inte resurser och gör inte ändringar permanenta. Det sker först när du använder COMMIT-satsen i den yttre transaktionen.

BEGIN TRANSACTION AddPerson
    INSERT INTO Personer
        (Name, Phone, City)
    VALUES
        ('Suzanne Apelqvist', '070-333 22 11', 'Örnsköldsvik')
COMMIT TRANSACTION

Här kommer en rad skrivas till databasen.

Ångra en händelse

Fördelen med att använda BEGIN TRANSACTION är att man kan ångra sig. Det görs genom att skriva ROLLBACK TRANSACTION. För att se att raden verkligen läggs till och raderas, har jag med en SELECT-sats före och efter.

BEGIN TRANSACTION
INSERT INTO Personer
    (Name,Phone, City)
VALUES
    ('Suzanne Apelqvist', '070-333 22 11', 'Örnsköldsvik')

SELECT * FROM Personer WHERE Name = 'Suzanne Apelqvist'

ROLLBACK TRANSACTION

SELECT * FROM Personer WHERE Name = 'Suzanne Apelqvist'

Flödeskontroll

Den riktiga kraften med ROLLBACK är inte att manuellt välja om den ska äga rum eller inte, utan genom att använda flödeskontroll. Det går t ex att använda villkorssatsen IF…ELSE för det, även om det inte är den bästa metoden.

Felhantering

Det absolut bästa att använda när du vill styra transaktioner är felhantering med blocken TRY…CATCH. Om det uppstår ett fel och vi bara använder en enkel transaktion som avslutas med COMMIT TRANSACTION, kommer SQL-server automatiskt ångra hela transaktionen och generera ett felmeddelande. Även om det fungerar är det bättre att styra felhanteringen så att vi får lite mer användbar information.

TRY

För att lägga till felhantering behöver du tala om för SQL-server att du vill försöka köra lite kod och det gör du genom att börja med satsen BEGIN TRY. Sedan skriver du de satser som du vill testa och när du är klar med försöket avslutar du med satsen END TRY.

BEGIN TRY
    BEGIN TRANSACTION AddPerson 
        INSERT INTO Personer
            (Name, Phone, City)
        VALUES
            ('Suzanne Apelqvist', '070-333 222 11', 'Örnsköldsvik')
    COMMIT TRANSACTION AddPerson 
    PRINT 'Personen är tillagd'
END TRY

CATCH

Ett block med TRY fyller ingen större funktion utan ett block med CATCH.  Med CATCH fångar du upp alla fel som uppstår i försöket. Mellan BEGIN CATCH och END CATCH skriver du instruktioner om vad som ska ske när det blir fel. Här placeras också satsen ROLLBACK TRANSACTION, eftersom vi givetvis inte vill att ändringarna ska skrivas till databasen när de innehåller fel.

BEGIN CATCH
    ROLLBACK TRANSACTION AddPerson 
    PRINT 'Personen finns redan'
END CATCH

TRY…CATCH

BEGIN TRY
    BEGIN TRANSACTION AddPerson 
        INSERT INTO Personer
            (Name, Phone, City)
        VALUES
            ('Suzanne Apelqvist', '070-333 222 11', 'Örnsköldsvik')
    COMMIT TRANSACTION AddPerson 
    PRINT 'Personen är tillagd'
END TRY

BEGIN CATCH
    ROLLBACK TRANSACTION AddPerson 
    PRINT 'Personen finns redan'
END CATCH

Satsen innanför blocket BEGIN TRY och END TRY kommer att utföras om inget är fel. Annars körs satsen innanför blocket BEGIN CATCH och END CATCH.

Nästlade transaktioner

Det går att ha fler transaktioner inuti varandra.

BEGIN TRANSACTION Name1
    PRINT 'Huvudtransaktionen'
    BEGIN TRANSACTION Name2
        PRINT 'En nästlad transaktion'
    COMMIT TRANSACTION Name2
COMMIT TRANSACTION Name1

Här skrivs ”Huvudtransaktionen” från den yttre transaktionen ut och ”En nästlad transaktion” från den inre.

För att se hur många transaktioner som finns i en transaktion, kan man använda PRINT tillsammans med systemfunktionen @@TRANSCOUNT. Varje gång du börjar en transaktion ökar antalet transaktioner med ett och efter varje COMMIT minskar antalet med ett.

BEGIN TRANSACTION Name1
    PRINT @@TRANCOUNT
    BEGIN TRANSACTION Name2 
        PRINT @@TRANCOUNT
    COMMIT TRANSACTION Name2
    PRINT @@TRANCOUNT
COMMIT TRANSACTION Name1

Här blir resultatet i de tre PRINT-satserna 1, 2 och slutligen 1.

I exemplet ovan skriver den första PRINT-satsen ut 1 eftersom en transaktion har påbörjats. Andra PRINT-satsen skriver ut 2 eftersom två transaktioner har påbörjats, den yttre och den inre. I den tredje PRINT-satsen blir det plötsligt 1 igen. Det beror på att efter COMMIT har den inre transaktionen slutförts och då räknas den inte längre.

I nästlade transaktioner kan man inte använda namn för de inre transaktionerna i satsen ROLLBACK. Med satsen ROLLBACK ångrar du inte bara de inre transaktionerna utan alla ändras, ända tillbaka till den yttre. Antalet transaktioner i den sista PRINT-satsen, alltså efter ROLLBACK, kommer därför visa 0.

BEGIN TRANSACTION Name1
    PRINT @@TRANCOUNT
    BEGIN TRANSACTION Name2
        PRINT @@TRANCOUNT
    ROLLBACK TRANSACTION -- Här har jag tagit bort namnet på den inre transaktionen.
    PRINT @@TRANCOUNT
COMMIT TRANSACTION Name1

Här blir resultatet i de tre PRINT-satserna 1, 2 och slutligen 0. Dessutom blir det ett felmeddelandet.

I ovanstående exempel dyker felmeddelandet ”The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION” upp. Anledningen är att ROLLBACK ångrar alla transaktioner och då finns det inga transaktioner kvar att slutföra.

Om du i stället för ROLLBACK, avslutar den inre transaktionen med COMMIT och byter avslutar den yttre transaktionen med ROLLBACK, blir det inget felmeddelande. Det innebär dock inte att händelserna i den inre transaktionen har sparats, eftersom ROLLBACK i den yttre drar tillbaka allt som har hänt i hela transaktionen.

Den här informationen får mig att fundera på om det finns någon mening med COMMIT och ROLLBACK i inre transaktioner eller med nästlade transaktioner överhuvudtaget?

Savepoints

Det går att ångra bara vissa delar av transaktioner. Det görs genom att i stället för att börja transaktionen med BEGIN TRANSACTION använda SAVE TRANSACTION. Som jag nämnde tidigare är det inte tillåtet med namn för de nästlade transaktionerna i satsen ROLLBACK, men det gäller inte för savepoints. Då måste namnet vara. Savepoints börjar inte en ny transation, utan sparar alla händelser fram till den punkten. Så om du använder ROLLBACK efter en savepoint, så ångras allt tillbaka till den senaste sparade punkten.

BEGIN TRANSACTION Name1
    PRINT @@TRANCOUNT
    SAVE TRANSACTION Name2
        PRINT @@TRANCOUNT
    ROLLBACK TRANSACTION Name2 -- Här måste namnet på vara med.
    PRINT @@TRANCOUNT
COMMIT TRANSACTION Name1

Här blir resultatet i de tre PRINT-satserna 1, 1 och slutligen 1.

Resultatet i första PRINT-satsen i ovanstående exempel blir 1. Att det även blir 1 i den andra PRINT-satsen beror på att SAVE TRANSACTION inte börjar en ny transaktion och sedan ligger PRINT-satsen före ROLLBACK. Eftersom ROLLBACK bara ångrar alla händelser från och med den senaste punkten som sparades, är det fortfarande en transaktion kvar. Därför blir resultatet i första PRINT-satsen 1.

Kort om savepoints i lagrade procedurer

Om du börjar en transaktion i en procedur och sedan anropar en annan procedur, kommer transaktionen hållas öppen. Du kan skapa en savepoint inuti den anropade proceduren för att kunna ångra vilka händelser som helst som utförs inuti den.

Kommentera

E-postadressen publiceras inte. Obligatoriska fält är märkta *