Transactions

• Transactions

  • What is a transaction?

    • Transaction: one or more statements, all or none of the statements are executed.
    • Example: Transfer $100 account A -> account B
      1. Subtract $100 from account A
      2. Add $100 to account b

    Operation 2 FAILS -> Can’t subtract $100 from account A!

  • Transaction statements - BEGIN a transaction

      BEGIN {TRAN|TRANSACTION}
      [
          {transactino_name|@tran_name_variable}
          [WITH MARK ['description']]
      ]
      [;]
    

  • Transaction statements - COMMIT a transaction

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

  • Transaction statements - ROLLBACK a transaction

      ROLLBACK {TRAN | TRANSACTION}
          [transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable][;]
    

  • Transaction - example

    • Account 1 = $24,400
    • Account 5 = $35,300

        BEGIN TRAN;
            UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
            INSERT INTO transaction VALUES(1,-100, GETDATE());
                      
            UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
            INSERT INTO transaction VALUES(5,100, GETDATE());
        COMMIT TRAN;
      

  • Transaction - example with TRY…CATCH

    • Account 1 = $24,400
    • Account 5 = $35,300

        BEGIN TRY
            BEGIN TRAN;
                UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
                INSERT INTO transaction VALUES(1,-100, GETDATE());
                          
                UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
                INSERT INTO transaction VALUES(5,100, GETDATE());
            COMMIT TRAN;
        END TRY
        BEGIN CATCH
            ROLLBACK TRAN;
        END CATCH
      

---

@@TRANCOUNT and savepoints

Number of BEGIN TRAN statemetns that are active in your current connection.

• Returns:
  • greater than 0 -> open transaction
  • 0 -> no open transaction
• Modified by:
  • BEGIN TRAN -> @@TRANCOUNT + 1
  • COMMIT TRAN -> @@TRANCOUNT - 1
  • ROLLBACK TRAN -> @@TRANCOUNT = 0 (except with savepoint_name)

• Nested Transactions

        SELECT @@TRANCOUNT AS '@@TRANCOUNT value';
        BEGIN TRAN;
            SELECT @@TRANCOUNT AS '@@TRANCOUNT value';
            DELETE transactions;
            BEGIN TRAN
                SELECT @@TRANCOUNT AS '@@TRANCOUNT value';
                DELETE accounts;
            -- If @@TRANCOUNT > 1 it doesn't commit!
            COMMIT TRAN;
            SELECT @@TRANCOUNT AS '@@TRANCOUNT value';
        ROLLBACK TRAN;
        SELECT @@TRANCOUNT AS '@@TRANCOUNT value';
  

• @@TRANCOUNT in a TRY…CATCH construct

    BEGIN TRY
        BEGIN TRAN;
            UPDATE accounts SET current_balance = current_balance - 100 WHERE account_id = 1;
            INSERT INTO transactions VALUES (1, -100, GETDATE());
          
            UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
            INSERT INTO transactions VALUES (5, 100, GETDATE());
        IF (@@TRANCOUNT > 0)
            COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF (@@TRANCOUNT > 0)
            ROLLBACK TRAN;
    END CATCH
  

• Savepoints

  • Markers within a transaction
  • Allow to rollback to the savepoints

      SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
      [ ; ]
    

    BEGIN TRAN;
        SAVE TRAN savepoint1;
        INSERT INTO customers VALUES ('Mark', 'Davis', 'markdavis@mail.com', '555909090');
          
        SAVE TRAN savepoint2;
        INSERT INTO customers VALUES ('Zack', 'Roberts', 'zackroberts@mail.com', '555919191');
          
        ROLLBACK TRAN savepoint2;
        ROLLBACK TRAN savepoint1;
          
        SAVE TRAN savepoint3;
        INSERT INTO customers VALUES ('Jeremy', 'Johnsson', 'jeremyjohnsson@mail.com', '555929292');
    COMMIT TRAN;
  

---

XACT_ABORT & XACT_STATE

To be updated…