Transactions

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

Dataset: bank transactions

customers

| customer_id | first_name | last_name | email                 | phone     |
|-------------|------------|-----------|-----------------------|-----------|
| 1           | Dylan      | Smith     | [email protected]   | 555888999 |
| 2           | John       | Antona    | [email protected]   | 555111222 |
| 3           | Astrid     | Harper    | [email protected] | 555000999 |
| 4           | Angus      | Brown     | [email protected]   | 555222012 |
| 5           | David      | Elcano    | [email protected]  | 555602314 |

Dataset: bank transactions

accounts

| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1          | 55555555551234567890 | 1           | 25000,00        |
| 2          | 55555555559876543210 | 1           | 200,00          |
| 3          | 55555555557070700707 | 2           | 1000,00         |
| 4          | 55555555558080808080 | 2           | 90000,00        |
| 5          | 55555555559090909090 | 3           | 35000,00        |

Dataset: bank transactions

transactions

| transaction_id | account_id | amount   | transaction_date       |
|----------------|------------|----------|------------------------|
| 1              | 1          | -100,00  | 2019-03-18 19:12:36.81 |
| 2              | 2          | 100,00   | 2019-01-18 19:12:36.91 |
| 3              | 1          | -9000,00 | 2019-02-18 20:20:36.41 |
| 4              | 3          | 9000,00  | 2019-02-18 20:20:36.51 |
| 5              | 4          | -50,00   | 2019-02-20 08:02:06.20 |

What is a transaction?

Transaction: one or more statements, all or none of the statements are executed

What is a transaction?

Transfer $100 account A -> account B

Subtract $100 from account A
Add $100 to account B

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

Transaction statements - BEGIN a transaction

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

Transaction statements - COMMIT a transaction

COMMIT [ { TRAN | TRANSACTION } [ transaction_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 transactions VALUES (1, -100, GETDATE());

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

Transaction - example

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

| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1          | 55555555551234567890 | 1           | 24300,00        |
| 5          | 55555555559090909090 | 3           | 35400,00        |

| transaction_id | account_id | amount  | transaction_date       |
|----------------|------------|---------|------------------------|
| 10             | 5          | 100,00  | 2019-06-07 18:26:27.46 |
| 19             | 1          | -100,00 | 2019-06-07 18:28:05.49 |

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 transactions VALUES (1, -100, GETDATE());

    UPDATE accounts SET current_balance = current_balance + 100 WHERE account_id = 5;
    INSERT INTO transactions VALUES (5, 100, GETDATE());
ROLLBACK TRAN;

Transaction - example

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

| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1          | 55555555551234567890 | 1           | 24400,00        |
| 5          | 55555555559090909090 | 3           | 35300,00        |

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 transactions VALUES (1, -100, GETDATE());

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

Transaction - example with TRY...CATCH

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

| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1          | 55555555551234567890 | 1           | 24300,00        |
| 5          | 55555555559090909090 | 3           | 35400,00        |

| transaction_id | account_id | amount  | transaction_date       |
|----------------|------------|---------|------------------------|
| 10             | 5          | 100,00  | 2019-06-07 18:26:27.46 |
| 19             | 1          | -100,00 | 2019-06-07 18:28:05.49 |

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 transactions VALUES (1, -100, GETDATE());

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

Transaction - example with TRY...CATCH

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

| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1          | 55555555551234567890 | 1           | 24400,00        |
| 5          | 55555555559090909090 | 3           | 35300,00        |

Transaction - without specifying a transaction

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

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 (500, 100, GETDATE()); -- ERROR!

Transaction - without specifying a transaction

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

| account_id | account_number       | customer_id | current_balance |
|------------|----------------------|-------------|-----------------|
| 1          | 55555555551234567890 | 1           | 24300,00        |
| 5          | 55555555559090909090 | 3           | 35400,00        |

| transaction_id | account_id | amount  | transaction_date       |
|----------------|------------|---------|------------------------|
| 10             | 5          | 100,00  | 2019-06-07 18:26:27.46 |

Let's practice!

Transactions and Error Handling in SQL Server