1. Welcome to Transactions and Error Handling in PostgreSQL
Welcome to the course. I'm Jason Myers, a data scientist, and engineer. I'm also a huge fan of PostgreSQL! I've used it through many different jobs and roles, and I've found it to be a fantastic database to use.
2. Learning objectives and datasets
In this course, we're going to learn about using transactions to preserve the integrity and credibility of the data we store.
We'll also take a look at how multiple database operations occurring simultaneously, also known as concurrency, affects transactions, and how to handle error conditions that can arise while using PostgreSQL.
We'll take a look at all those concepts in more detail, but for now, let's focus on transactions
3. Motivation for using transactions
Let's imagine we've got a table for patient intake that stores patient names and priorities of their ailments, as shown here. If we need to swap Prisha and Oscar in priority, we could do that using the SQL statements presented here. However, between our two statements executing, we lost connection to the database server. What do you think the result would be?
4. Errored table results
As you can see here, it resulted in both patients being priority one, which is not what we intended at all and could result in an improper course of action. Errors like this are an instance where transactions can help us.
5. Using a transaction
As you can see in our example here, it's the same ordinary SQL statements with an explicitly defined beginning and end. Another way to think of this is as a wrapper that represents a unit of work that we want to succeed or fail coherently and independently of other work in progress on our database server.
You can see that we begin the transaction with the `BEGIN` keyword, execute our statements, and commit our transaction.
Starting a transaction tells PostgreSQL that we want the work from BEGIN to COMMIT clauses of the transaction to succeed or fail as a whole. The statements execute as usual in this protected transaction space; however, they do not immediately update the database in a manner other transactions can see until we commit, which ends it and closes the transaction block. Now, if we have the same database connection issue between our two update statements, we will not update the table at all. While this isn't what we had intended, it will prevent the data from being bad and potentially causing other issues related to duplicate record priorities. We'll cover ways to make sure we know when this happens later in the course.
6. Another reason to use transactions
Apart from making sure that multiple statements either succeed or fail as a group, another important use case is .. concurrent operations. Don't worry about concurrency for now, as we'll come back to it at a later time during the chapter.
7. Transaction blocks
Looking back at our example, the two update statements that we need to succeed or fail as one are what we call a statement group. By surrounding these statements in a transaction, anything that might cause an error between the BEGIN and COMMIT statements would result in neither of the statement's actions being saved, commonly called committed, to the database. Wrapping multiple statements in this way is often referred to as a transaction block. Once a transaction is committed, it can not be rolled back.
8. Any number of statements
Transaction blocks can have as many statements as you desire in them. Here we are changing up the whole order of the table with three statements. Nonetheless, focus on keeping transactions small and containing just the statements that need to succeed and fail together.
9. Let's practice!
Now it's time for us to build a few transactions ourselves, and maybe go find a cookie.