Get startedGet started for free

Transactions

1. Transactions

In this lesson, you will learn what a transaction is and how to combine transactions with error handling.

2. Dataset: bank transactions

We will practice concepts related to transactions with a dataset based on bank account transactions. The dataset contains information about customers,

3. Dataset: bank transactions

accounts,

4. Dataset: bank transactions

and transactions.

5. What is a transaction?

So, what is a transaction? A transaction is the execution of one or more statements, such that either all or none of the statements are executed.

6. What is a transaction?

Let's see an example. Imagine we want to transfer $100 from account A to account B. First, we have to subtract $100 from account A and after that, add those $100 to account B. If the second operation fails, we can't subtract $100 from account A. These two operations must behave as an atomic operation, we execute both of them or none of them. They need to be in the same transaction.

7. Transaction statements - BEGIN a transaction

SQL Server defines some transaction statements. BEGIN TRAN or TRANSACTION is used to set the starting point of a transaction. You can optionally add a name for the transaction and the WITH MARK option. These options won't be covered in this course.

8. Transaction statements - COMMIT a transaction

COMMIT, optionally followed by TRAN or TRANSACTION, marks the end of a successful transaction. When executed, the effect of the transaction cannot be reversed. You can optionally add the name of the transaction and set to on or off the WITH DELAYED_DURABILITY option. These options won't be covered in this course.

9. Transaction statements - ROLLBACK a transaction

ROLLBACK TRAN or TRANSACTION reverts a transaction to the beginning of it or a savepoint inside the transaction. After ROLLBACK TRAN or TRANSACTION, you can optionally add the transaction or the savepoint name. We will define savepoints in the next lesson.

10. Transaction - example

Let's see all these statements. In this example, we try to transfer $100 from account 1, with $24,400, to account 5, with $35,300. We mark the start of the transaction with BEGIN TRAN. The first operation updates account 1 subtracting $100. Then, we register that movement into the transactions table. After that, we add $100 to account 5 and then register the movement into the transactions table. Finally, COMMIT TRAN confirms the changes.

11. Transaction - example

By executing this code, we can see that account 1 decreases by $100 and account 2 increases by $100. Besides, two rows are inserted into the transactions table.

12. Transaction - example

Let's change the previous example using ROLLBACK TRAN instead of COMMIT TRAN to revert all the changes as if nothing had happened.

13. Transaction - example

Accounts 1 and 5 have the same balance as they had before the execution of the script. Besides, no rows are inserted into the transactions table.

14. Transaction - example with TRY...CATCH

Let's surround the previous code with a TRY...CATCH. We start the transaction within the TRY block. If everything between BEGIN TRAN and COMMIT TRAN executes without errors, the changes will be confirmed. However, if there is an error, the CATCH block will take the control and revert the changes with the ROLLBACK TRAN statement.

15. Transaction - example with TRY...CATCH

Executing this code, as there are no errors, account 1 decreases by $100 and account 2 increases by $100. Besides, two rows are inserted into the transactions table.

16. Transaction - example with TRY...CATCH

Let's introduce an error in the previous script, inserting $100 into the transactions table for account 500, which doesn't exist. If we execute this code, as there is one error, the CATCH block will take the control, executing the ROLLBACK TRAN statement. All the changes are reverted as if nothing had happened.

17. Transaction - example with TRY...CATCH

Accounts 1 and 5, have the same balance as they had before the execution of the script.

18. Transaction - without specifying a transaction

Finally, let's see what happens if we don't explicitly specify a transaction. SQL Server considers each statement as an individual transaction. The three first statements are executed correctly, but the last statement fails, and this row is not inserted.

19. Transaction - without specifying a transaction

Account 1 decreases by $100, and account 2 increases by $100. However, only one row is inserted into the transactions table, resulting in an inconsistent state.

20. Let's practice!

Let's practice!