Hey, everyone, welcome back. In this video, part eight of topic six in our database class, I'm going to discuss acid transactions and the four different transaction isolation levels. All right, this takes us to a discussion of consistent transactions, and this is a little odd, I'm always still odd, because we say that consistent transactions are acid transactions, and one of the parts of acid is consistent, so I don't know where this idea comes from, but anyway, and acid transaction is a desirable thing in the database world. So a transaction is a meets this acid standard, if it is atomic, consistent, isolated, and durable. So let's see, returns what they refer to. Now, the atomicity of a transaction, that is, if it is atomic, this is an old Greek word when the Greeks used to think that, to the years ago, their philosophy was that the atom was the smallest indivisible unit of matter, and that's what we're thinking of here. It's treated as one thing. Of course, now we know that atoms are made up of smaller and smaller particles and so on, but hey, give credit to the Greeks. It was 2000 years ago. So anyway, the idea here with when we're talking about atomic is that's nothing related to nuclear energy or anything like that, but it's that we treat the steps in a transaction as a single unit. So it's all treated as one thing. So the entire thing succeeds or the entire thing fails. I mentioned this last time. You might have a transaction that contains a hundred steps, and the first 99 might be completed successfully, but if that 100th step fails, the entire transaction fails. Okay, so this is what we're referring to when we're talking about the atomicity of a transaction. Every step in the transaction must be successful for the entire transaction to be successful. That is the only time, only situation in which the changes made by the transaction will be saved will be in a situation where every single step was successful. Right, if any step fails, none of the proposed changes are saved to the database. Instead, the transaction fails, and we need to decide if we want to try to run it again. So the result then is everything that the transaction needs to do or that it intended to do, it will do if the transaction succeeds. So we know then that if a transaction succeeds, every single step of that transaction was successful. Right, there wasn't a single part of it that failed, the whole thing worked. And this is obviously a good thing because as we saw with some of the examples from last time, but if we make a few changes and then something goes wrong, and we don't undo the changes that we had previously made, that leaves our database containing inaccurate data. And obviously that's bad. So the C and acid is consistent, and the consistency refers to what we call, well, a statement level consistency, because we want to have a guarantee that no other users or no other transactions are going to be able to change the data that we're working with until we have finished what we were doing. Okay, so this way with this consistency guarantee, when we start a transaction, we can feel comfortable that nothing sneaky or shady is going to be happening behind the scenes to the data that we're working with, while we are still working with them. Okay, it may be possible for some of the transaction to read the data values, but if they are reading those values, they won't be reading any of the proposed changes that we made. And indeed, we might just lock down the whole thing so they can't even read them. You just have to wait. So in this way, we can ensure that our, the result of our transaction is consistent with our expectations. And in this case, our expectations are based on what we would expect the outcome to be if we were the only person using the database. Okay, so we want the outcome to be consistent with that standard. And to do that, we have to have this consistency guarantee where the transaction can be carried out without having to worry about changes being made in the background that may affect whatever it is this transaction is trying to accomplish. So that's the C in acid that I refers to isolation. Okay, and this is an interesting part of transactions because the standard sequel gives us four different transaction isolation levels. Okay, now remember the idea here is that we're considering what happens in a multi user database environment. And we have lots and lots of users that may want to use the same data at the same time. And we therefore need to make some choices about how isolated we want each transaction to be. That is, do we want every transaction to be entirely isolated from every other transaction? Do we want it to be entirely unisolated or somewhere in between? And again, this is a performance decision. We can lock things down very, very tightly. But in so doing, we are choosing to reduce the performance of our database. So broadly speaking, we have these four sequel transaction isolation levels read uncommitted, read committed, repeatable read and serializable. And as we move from read committed down to serializable, we are introducing more and more isolation or more and more protection for our transactions from other transactions. However, we are also decreasing the performance of the database if it's in a heavy multi user environment. So serializable is really locked down tightly, read on committed is very open. And then somewhere in between would be read committed or repeatable read. All right, so let's learn a little bit more about each of these transaction isolation levels. So when we choose and remember just to connect this to the previous slide, as we move from read uncommitted down towards serializable, we're locking down the transactions more and more. That is, we're isolating them or protecting them from the influence of other transactions to a greater and greater extent. So we're, same time, our performance is also decreasing due to that greater and greater level of isolation. So with that in mind, let's take a look at these four different transaction isolation levels. The first is read uncommitted. And when we choose to use a read uncommitted isolation level for our database, what we're saying is that dirty reads are fine. And I know it's been a while since we last talked about dirty reads. So just as a reminder, the idea with a dirty read is that one transaction may be working with some data and may have changed those data or propose some changes to them, but they haven't been saved yet. They haven't been made a permanent part of the database. So that's what's going on and a dirty read occurs when another transaction is allowed to see those uncommitted changes. And this can, it might be okay, but it might not be okay because if the original transaction that has proposed these changes ultimately does not commit them. Is it ultimately does not save those changes to the database, then the second transaction, which was looking at those changed values changed but not saved values may have done something based on those on saved values, such that when it commits its changes. It is now introduced incorrect data into the database. So read uncommitted means that transactions can basically see what each other are doing even if those changes have not yet been made permanent in the database. And that this is high performance, right, because we're allowed to we're not like locking these things down. I can see what you're doing. You can see what I'm doing. We're not protecting our work from each other, but it's with the understanding that that work is still in progress. So if we save those changes yet. So if we act on this work that's in progress and ultimately whoever was doing that work decides not to save it, then we may be making a mistake based are given that we are are basing our actions on those on committed work. So slightly more stringent than that is our second type of isolation for transactions and this is called a read committed. So in a read committed transaction isolation level dirty reads are not allowed. So this is good. So we don't have to worry about other transactions looking at our work while it's still in progress. So dirty reads are not allowed. That is data in the database are only going to be visible to a transaction if they are in a committed state at the time when the data are being read. And then if the data are committed will the transaction be able to see them. However, and this is a big big consideration with read committed data can be changed. We could add new rows to a table update existing rows in a table or delete existing rows in a table. Well, some other transaction is still working with those rows. So maybe you're working with some data and you're going through and carrying out the various steps of your transaction. And well, you're busy doing that I come in and delete some of the data that you are working with on your transaction. Okay, so that is possible with read committed. So dirty reads are not allowed, but changes to the data are still allowed with read committed. So slightly more stringent than that is repeatable read. And now this is similar to read committed in the sense that dirty reads are not allowed. However, with repeatable read in addition to dirty reads not being allowed. Data that are being read by a transaction are not allowed to be updated or deleted while the transaction is still in process. So if you remember up here on read committed data can be inserted updated or deleted. Right. So repeatable read is very similar except that we don't allow those steps. Right. So dirty reads are not allowed updates are not allowed and deletes are not allowed. That is only inserts. So if we have repeatable read you can see new data can be inserted well our transaction is still in process. And so let's say that we're just doing something simple calculating our total sales thus far for the day. And we're doing that just by adding together all the transactions. So we start our process our transaction begins we start adding together all of the sales the individual sales. And well we're in the midst of that. A new sale is recorded. Right. So a new row a new sales row is inserted into the database. That's allowed with repeatable read. Right. So we will get a result but the point is that our result will be only accurate as of the time that the transaction began. Because we cannot guarantee that additional data were not inserted while we were still busy doing our work. So that's repeatable read. And finally, serializable is our most stringent transaction isolation level. And with serializable our isolate our transactions are wholly isolated from other transactions. Okay. So it's like repeatable read. But inserts are not allowed. So you can see we build as we go. Here dirty reads are allowed inserts are allowed updates are allowed deletes are allowed on read on committed. Right. As we move to a slightly more stringent level of read on committed. And this case dirty reads are not allowed. But. Other transactions can insert or update or delete the data that we're working with well we are still working with them. We move down another layer to repeatable read. And here. Dirty reads are not allowed and data cannot be updated or deleted. Well, we are currently working with them. But new data can be inserted by other transactions. And finally, with serializable. No dirty reads. No inserts updates or deletes for the data that we're working with while we are still busy working with them. Now, you can see how they become more and more stringent as do go. Right. As you move down from read on committed towards serializable. But hope you can also see how the performance of the database can be expected to decrease as you go. Right. So here it's wide open. Right. If I'm busy working with some data. You can read my on save changes. You can insert update or delete the data that I'm working with while I'm still busy working with them. So it's wide open. Right. There's no bottleneck in performance there. You can basically do anything you want with the data while I'm still busy using them. If we move to read committed, you can do most of the things that you might want to do with the data. Insert update or delete. But you won't be able to read. Any data values that I'm currently working with. And what does that mean from a performance perspective? Well, it means you have to wait. Right. You need to wait until I'm finished before you'll be able to read those data values. Right. And then as we move to repeatable read, we disallow updates and deletes. Okay. So again, if you are trying to update or delete some of the data that I'm currently working with, you will need to wait. Right. Your transaction will just have to wait until I'm done before your updates or deletes can be processed. You can see it's lowering the performance some more. Right. With repeatable read. And then ultimately with serializable, you cannot read data that I'm working with. You cannot update, insert or delete any data that I'm working with. So you have to just completely wait. Right. You can't do anything with any of the data that I'm using until I'm finished. So hopefully you can gain an intuitive understanding here as to why. Performance tends to decrease as our transactions become more and more isolated from each other. Because other transactions are not allowed to a greater and greater extent. They're not allowed to work with the same data. Cool. So as a database administrator, we need to make smart decisions about which of these transaction isolation levels we should use for our database or for specific tables. That means we have to be aware of usage patterns, current see the types of conflicts that might occur. And the balance that with our need to protect the data. So if you're something like a bank and your financial transactions like calculation of interest, right payments, etc. Must be absolutely correct. You can have no possibility of making mistakes. Then you're probably going to be forced into using a serializable transaction isolation level. And that would lower your performance given the same hardware. So if you need better performance and you're forced to use serializable, your only choice is to try to buy better hardware. However, if you're in a situation where say there are almost never any updates, if you do like a million reads for every one update, then you can probably use something like read on committed, I wouldn't recommend that under any certain, maybe read committed. Because it's going to deliver much higher performance. We're not locking down all these resources. Multiple transactions can use the same data at the same time. And if we're unlikely to do any inserts updates or deletes anyway, well, why not have the data more accessible. So usually by default enterprise level relational database management systems will choose one of these two types of isolation levels. So we'll get somewhere in between. It's not entirely open. It's not entirely closed somewhere in between. But we can change that based on our particular use case and usage patterns. And then finally, the D in acid is for durable. And the idea here is once we commit changes is once we hit save on our word document, if you want to think of it that way, that becomes the official version. There's no undoing that anymore, at least under normal database circumstances, you may be able to undo it with a roll back, but that would only occur if some mistake were made, if someone accidentally deleted all the customers, then we have to do a roll back. But basically, if I go in and change your email address and I commit that change, that is now the only version of your email address stored in the database. We don't have the previous one available anymore. So anybody that looks at your email address from that point in time forward would see the version of it that I just stored. It's the official version. So changes become official. That's the durability and acid.