Transactions

[00:00:00]
>> So we just talked about sub queries. We are gonna talk about transactions. So we're gonna pop back over to the recipe database just for just a hot moment. If you hit \L, you should see, recipeguru here like I do. If you don't, let me just show you how to recreate it just say CREATE DATABASE recipeguru: like that, right?

[00:00:33]
You'll hit Enter, I think mine is gonna say, I already have one, so I'm not going to, but yours was should would say, okay? You would hit \c recipeguru, okay? If you hit \d, you would see nothing. If you do see something then you're fine, don't do anything.

[00:00:53]
If you see nothing there, then come back over here. If you remember what section was it in JSONB, maybe know the project. So under joins and constraints project there's two projects the second one. There this link to this big SQL file, if you click on that, that will download, that's my fourth copy of it.

[00:01:22]
Just copy and paste that and you can past at here, I'll do it, because it just recreate everything from scratch and there you go. Now you should see this. SELECT COUNT(*) FROM recipes:, you should see I have 56 something around there, good enough. Okay, now that we're that you should see recipeguru here, we should be good to go.

[00:01:58]
If you see OMDB there, you're still came to the wrong database \c recipeguru. Let's talk about transactions cuz these are actually really important particularly for certain classes of database activity. Particularly if you're in financial tech like me. So imagine you are writing banking software. You have Bob, who was trying to give $10,000 to Alice.

[00:02:31]
You have two queries there, right? You're gonna have to subtract $10,000 from Bob's account and you're going to have to add $10,000 to Alice's account. Those are two separate queries, you're not gonna do them in one query. What happens if you subtract $10,000 from Bob's account and then in the middle of that before you can run the second query, your database crashes?

[00:02:57]
Well, you have a massive problem. Bob has lost $10,000, Alice has not gained $10,000. The bank has robbed Bob, I'm sure Bob is not happy about that. Furthermore, Alice doesn't have her $10,000 and if you did it in the opposite order, where you gave Alice the $10,000 first in your database crashed.

[00:03:21]
No, the bankers giving away money, right? Both of those are wholly unacceptable outcomes. So we live in this situation now where both of those have to succeed or both of them have to fail. If both of them fail, they might be annoyed that the money didn't get moved to the way that they wanted it to.

[00:03:41]
But no one's gonna call a lawyer just yet, right? So we need what's called an atomic transaction. Atomic means the Greek word, indivisible, right? You cannot divide this either all works or none of it works, nothing in the middle. This is something that SQL provides for lots of banks use SQL and all sorts of flavors and varieties.

[00:04:05]
And so transactions are absolutely essential. So you need to think about transactions when you have something where multiple things need to happen in succession and they cannot afford to fail, right? Or like you cannot have partial failure, right? Partial failure is unacceptable, this is where transactions are useful.

[00:04:27]
So what will happen with a transaction is you'll say begin. You'll do a couple of things and then you'll say end and then it'll execute all of them. So let's say we're making a new recipe, and we wanna add things into ingredient. And for whatever reason, we cannot afford or tolerate ingredients without recipes, just like fattening that other situation there.

[00:05:03]
So what you need to insert into ingredients? We will need to insert into recipes. And then we will need to insert into recipe ingredients all of the correct IDs, right? All of the correct connections. So it's a bit of a long query. So we're just gonna copy and paste it from the site, but we'll go through it together.

[00:05:27]
Essential, we're gonna say begin. Begin basically means we're about to start a transaction. You can also say, begin transaction or you can say begin work, they all mean the same thing. You can also just say, begin because we're lazy, we'll do it that way. Insert into ingredients, right?

[00:05:48]
So we got whiskey and simple syrup, you can replace this with like I don't know soda if you don't wanna talk about alcohol, that's fine with me too. We're gonna insert into recipes old fashioned, right? And I have the very descriptive molded fashion cuz again I should not be writing courses about food.

[00:06:11]
And then we're going to INSERT INTO recipe_ingredients. And we have to use subqueries because we have to go get those ids, right? So the first value is going to be the id, the recipe_id from 'old fashioned' and the id from 'whiskey'. The second one that we're going to add in here is the id FROM recipes old fashion again and the id FROM simple syrup.

[00:06:31]
Then once we have finished all of that, once we're happy with the state of our transaction. We can say COMMIT, commit transaction, commit work, those are all the same thing and then it'll actually go and do all of those. If any part of these fails, if this part fails everything is canceled, this is canceled, this is canceled, all of its canceled.

[00:06:51]
But if everything succeeds, then the entire thing is committed in one atomic transaction. So you now avoid the problem of what happens if my database fails in the middle of this, or what second queries doesn't work, right? That's what all of this does. So we're gonna do this, I'm gonna do BEGIN I am not going to do COMMIT.

[00:07:17]
So I'm going to do this, all of that. I have not typed COMMIT yet, even though I see all these basically means that it's inserted. I'm still in the middle of a transaction. If I say SELECT * FROM ingredients WHERE name, is it name, it's title, right, title.

[00:07:43]
title = simple syrup it will come back, right? The reason why it comes back is that according to this session of this person like this one P SQL session that has happened, right? However, if I open a brand new tab and I do docker exec and I connect to it again and I'm connecting to recipeguru.

[00:08:13]
And I say SELECT, I'm gonna run the exact same * FROM ingredients WHERE title = simple syrup. Nothing's shows up because that transaction hasn't happened yet. So I'm gonna go back here, if I wanted to just blow it up and not have anything happen, what I would do is I'd say, ROLLBACK and now we'll just cancel my transaction immediately, I don't want to.

[00:08:52]
So I'm gonna say COMMIT. If I do it again, from here again, it's still there. But now if I go back to this other new session and say SELECT * FROM ingredients simple syrup. Now this shows up, because that transaction has been committed. Again, there's not really a big performance consideration here.

[00:09:13]
I wouldn't worry so much that. The consideration here is, your tolerance for failure and partial failures, that's where transactions are essential. So I'm trying to think if there's anything outside I wanted to tell you about there. Yeah, I mean, I wanna copy this, I just wanna show you what ROLLBACK back looks like We can even just do this And don't need that, will say this is like I don't know.

[00:09:52]
Something else it doesn't matter. Okay, so we'll do this again. I've done two inserts here, and if I'm unsatisfied with how this is going, you can just say ROLLBACK. And all of a sudden everything is back to where it was that these two things are not in the database because I rolled them back Make sense?

[00:10:22]
And again you can say begin work, you can also say begin transaction if those makes more sense to you, beginners just short for that. Sometimes it's helpful for BEGIN because BEGIN means several things in SQL contextually. Whereas BEGIN transaction is obviously very specific to beginning a transaction that can be useful sometimes.

[00:10:41]
The question I ask myself is like this is really kind of burdensome doing these subqueries here. You actually can do this with the variables, so that I can get the ID back when I do this insert, and the ID back when I do this insert. And then instead of doing the subqueries, we can actually put the IDs from its stored in variables rather than doing subqueries.

[00:11:06]
You have to do it in PL PG SQL, there it is sorry, it takes a second for me to remember how to say that always. So I wanted to just show you how to do that, you can't do a transaction inside of a function, but you can do it outside of it.

[00:11:30]
So let's say I wanted to add mimosas, the way that you would do that is you would begin work here. Again, you could just say BEGIN here I just did work. So you could say that this BEGIN is different than this BEGIN, this is the beginning of a function, this is the beginning of a transaction.

[00:11:46]
I declare variables, champagne, orange juice and mimosa. I insert into this and then I return ID into champagne. So that's how you store this into a variable. Return ID into orange juice, ID into mimosa. And I do those for all of my inserts here. And then here into recipe ingredients, I can refer to recipe ID ingredient ID, mimosa, champagne mimosa, orange juice, and then at the end here I can COMMIT.

[00:12:19]
Just to show you that all worked. Here the do that this is probably the operative thing that you're looking for. You're telling Postgres, I'm about to run a function. So you give it this body, it immediately executes as opposed to like creating a function and storing that like we did.

[00:12:41]
Where we are actually just creating a function and invoking it immediately. Because we wanna do something like run JavaScript or run the Postgres programming language directly. So ask yourself which one of these do you like better? I'm gonna say I probably like this one better, even though the sub queries are a little annoying.

[00:13:04]
I'm getting fooled into a turing complete programming language in mySQL not my favorite thing to do personally. That's really it for transactions, it's just BEGIN and COMMIT or BEGIN and ROLLBACK, everything else here I'm kinda showing you just for fun. And again, you cannot have a function do a transaction.

[00:13:22]
However, you can use functions inside of transactions.