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!