 This is the second lecture now on crack recovery. Again, on Monday we talked about how every recovery algorithm of a database system is what it uses to ensure that all the transaction changes that commit successfully are durable, that we assure that all the changes are atomic, we don't have any partial transactions, and that we can restore the database and come back in the correct state. We said that these recovery algorithms have two parts. On Monday we talked about what we did at runtime, the data we had to collect and store it out the disk that we could then use for the second part of these algorithms was what we're going to talk about today, of when you come back after a crash, after restart, and you look at the data that you set aside or collected during normal operations, you use that to figure out what the hell was going on and how to go back to the correct state. Today we're focusing on the second part here. We're assuming we've crashed, we've got to come back and figure out what was going on and put us back to where we should be. So the protocol or the methods we're going to talk about today come from this seminal paper called ARIES, the algorithms for Recovery, Isolation, and Exploiting Semantics. So I don't know if the textbook actually refers to this as ARIES, but this is sort of the gold standard, this is sort of the Bible if you will of how to do database recovery. Even though the textbook may not talk about it in terms of calling it ARIES, this is what they're doing. This is pretty much what everyone's doing. So ARIES was developed at IBM Research in the late 1980s, early 1990s by a tech fellow at IBM called Mohan. He said it goes by just Mohan. This paper that we're going to talk about today is like 70 pages, it's very, very dense. It's if you want to fall asleep and you need help, read this. But it's also amazing, I'm not trying to disparage it because it really lays down the exact steps of everything you need to do to make sure your database can recover after a crash, including all possible corner cases. So if you're building a database from scratch, you said, how do we actually do recovery? You could turn to this, if you understood it and implement it, then you'd have the guarantees that you can recover in any possible failure, other than the machine catching on fire, because no software can do that. So it's not to say that no database system in the world was doing recovery before this paper came along. This thing codified the exact rules and mechanisms you had to do to ensure recovery. So the various databases out there that are doing redhead logging, they may not implement ARIES exactly, because there may be some variation of when you actually flush things or what metadata you actually store. But at a high level, they're essentially doing some variant of ARIES. So if you say your database does ARIES, everyone knows what essentially what you mean. You're doing redhead logging with fuzzy checkpoints and can do recovery with CLRs. So that's what we're going to be focused on today. So as I said, the author of this paper was this guy Mohan. He's super friendly. This is a picture of him and me. Sorry, there we go. This picture of him and me a few years ago at SIGMOD just hanging out the bar and he's a really, really nice guy and he's super fun. In this picture here, he's going out the party. The reason why you know he's going out the party, because he's got his shirt open and he's fluffed out his chest hair. So you ever see Mohan doing that? You're looking for a good time. So I love Mohan a lot and this paper is great. So let's try to get through it. So ARIES are going to have three main ideas. The first one is that we're going to do right-of-head logging in the same way that we talked about last class. We have to have this guarantee that we don't flush out any dirty disk page that was modified by a transaction until the corresponding log record that modified that page has been written out the disk. We're doing this under the steel no-force buffer pool management policy. Steel says that we're allowed to have dirty pages created by uncommitted transactions, written out the disk before that transaction has been committed. So to ensure that we can always recover and come back and figure out what was going on in that page, we have to make sure the log record for that transaction is written out first. No force just says that when a transaction commits, we don't have to flush all those dirty pages, but we have to flush all of its log records. In order for us to say that it's completely durable, we can tell the outside world that our transaction has committed. So now the second two parts of areas is during the recovery process. So the first thing is that during recovery, we're going to replay all our actions and restore the database back to the correct state. This is essentially by redoing all the actions that occurred in the log when we were running normally. We see the redo operations and we can apply them. The second thing is that anytime now we do an undo, both during recovery and normal execution, we're going to log those undo operations. This is going to be really important because if we crash while recovering, you need to recover from a recovery. By logging all the undo records, this guarantees this. So the first one should be pretty obvious because we covered that last class. The second one will become more apparent as we go along, and then the third one will see this when we talk about CLR. So you'll understand what I mean by this. Again, this is super complicated. Even I'm even going at a high level areas, I'm not going into the real nitty gritty details of the 70-page paper, but it's really complex. So again, stop me and ask questions as we go along. All right, cool. So today we're going to start off talking about log sequence numbers. We're going to add them to our redhead log records so that we know the order that we applied operations to our database or to our pages. Then we'll talk about what we actually going to do now at runtime when we have a commit and abort operations as we execute transactions. Then we're going to extend the checkpointing protocol that we talked about at the end of last class and now talk about fuzzy checkpoints, where we're going to allow transactions to keep running while we take the checkpoint because last class we didn't do that. Then we'll put this all together and say all the metadata we've collected from these three previous steps, we'll see how we actually can use them in our recovery algorithm. All right. So the first thing we're going to do is we're going to extend our log records from last time to now include additional information. And the first thing we need to include is something called the log sequence number, the LSN. And think of the LSN as just a logical counter that's always monotonically increasing. We're just adding one and every time we add a new log record. And we're going to use this to ensure that we know the order of the operations we're applying to the database because they'll have to match up how they apply to the log. And then various other parts of the system will use these log sequence numbers to figure out whether the log record that modified the particular page has actually been written to disk or not. So these log sequence numbers are not just the log, so the log manager and the right-hand log aren't the only things that know about it. Now these log sequence numbers have to permeate all throughout our entire system. So the first thing we're going to have is called the flush LSN. And we're going to record this in memory. And this is just the last LSN that we flushed out the disk in our right-hand log. So we know at up to that LSN point, we know that everything else is durable prior to that. They're inside of a page now. We're going to have a page LSN and a rec LSN. The page LSN is just the last LSN of the last transaction that modified the page. And then the rec LSN is the oldest LSN that modified the page since it was last flushed to disk. So page LSN will be pretty obvious, because it's like if I have a transaction, I'm going to update the page, I have an LSN for that update record, and then I apply it to my page LSN. The rec LSN will make sense later on when we start doing recovery. The next thing is the last LSN, and this is just recording what for every transaction, what was the last LSN that it created in our right-hand log, and we use that to be able to jump back in time and just skip over other log records to find the ones that correspond to our transaction. And then the last one is a master record, and this is just a location on disk where we record, here's the last checkpoint that I took for my database. Because remember, when we take checkpoints, we add log entries for them as well, so we know where to jump ahead to. OK? So again, every data page is going to have a page LSN, and that's going to be both when it's in memory and on disk. It's just in the page header, so if I modify it in memory, my buffer pool, when I write it out, the page LSN, the rec LSN go out with it. Now anytime I flush my right-hand log, the tail of the log that I have in memory, I write it out to disk, I update my flushed LSN to keep track of the last log record I just wrote out. And again, all these log sequence numbers we're tracking always have to go forward in time. You can't go back because that would mess everything up. And then when now the buffer manager wants to decide that whether he can write a page out to disk, it wants to evict a page to free space because some other page needs to come in, we have to check to see whether the page LSN is less than equal to our flushed LSN. So the page LSN is the LSN of the last transaction that modified our page, and the flushed LSN is the last log record that we flushed out. So long as the page LSN is less than equal to the flushed LSN, then we know every possible log record that could correspond to a change to my page has been safely written to disk, and therefore it's safe to evict that page and write it back. Let's go through an example. All right, so the first thing we see in memory, we have our buffer pool, and we have the tail right head log, the tail means we only need to keep track of the last so many number records, and then as we write them out to disk, we discard those entries and we reuse the memory. And then on disk we have our database, which is just for now it's a single page, and then we have a right head log. So the first thing you see is now we're adding log sequence numbers to our log records. Again, these are always just increasing, just think of a logical counter, you atomically update as you generate each new log record, and then the contents of the right head log in memory is gonna match the contents of the right head log on disk if they have the same format. Then we have our page LSN, and again, this is just the LSN of the last transaction that modified our page. Same thing, we have it on memory on disk, the rec LSN is the oldest in memory LSN that modified our page, and then the flush LSN just corresponds to the end of the right head log that's been safely written out to disk, right? And then the master record is just the LSN of the last successful checkpoint that we took, okay? All right, so let's say that the page LSN points to log sequence number 12, and say we wanna write this page out to disk. Are we allowed to do that? What did I say in the last slide? What's the criteria we have to have to say that it's safe for us to write a page out to disk? Flush LSN, the page LSN has to be less than or equal to flush LSN. So in this case here, since the page LSN is 12, the flush LSN is 16, so that's safe for us to write that out, so we can do that. What about this one? What if the page LSN is 19? No, right, because again, it's obvious, right? This thing is sitting in memory, so we can't flush that out. So again, the bufferful manager's gonna use that as part of this eviction policy to decide whether it's allowed to write something out to disk. This is in addition to whether something's pinned or not. This is sort of all parked the same thing, right? Again, this one's not safe for us to flush. Right, so again, just to recap, all log records are gonna have LSN. Every time we're gonna update a page, we update the page LSN with the log record that we degenerated, so this is why you have to generate the log record first, then you apply the change because you need to know what your LSN is before you comply that change. And then every single time we wanna flush out the log because we're running out of memory or there's a timeout, then we update the flush LSN after we've successfully written out the tail of the log and then we can reclaim that memory, okay? All right, so these LSNs are gonna be an important primitive and building block we're gonna use throughout all these algorithms because it's gonna help us figure out where we're at in our, in the recovery process for what log records we've looked at and what's dirty, what's not dirty. So if you understand basic LSNs, then we can proceed and start doing more complicated things. All right, so now we wanna talk about what happens when we execute transactions. So again, transactions in our world are just a bunch of sequence of reads and writes. We actually ignore the reads because they don't show up in the log. It doesn't make sense to the log reads so we don't actually care. So we do a bunch of operations and then there's a commit or abort. And so for the examples we're gonna show throughout this lecture, we're gonna make the following four assumptions. So just to simplify things, right? This is sort of what, again, if you read the Aries paper, they cover all these things beyond what we're talking about here. So the first most obvious thing is that we're gonna assume that our disk writes are atomic and therefore all log records can fit into a single page. So we don't have to worry about some giant update to a single record and the log record spans two pages and we may only get one page and not the other. We're gonna ignore that issue. We're also gonna assume that everything we're doing is on a single version database using strict two phase locking, right? If you understand it in the context of this environment you can see how it can be easily extended to multi-virgining or other concurrency protocols. And of course we're gonna assume that we're gonna do steel and no force with right ahead logging, right? That's a core principle of Aries. So when a transaction goes to commit, we do whatever validation we want or it doesn't matter what concurrency protocol we're gonna do, we write out a commit record to the log. And then we have to make sure when we write that commit record, when we flush that commit record, all log records for that transaction have to come before that commit record, right? So this assumes that we're writing them out sequentially, synchronously out the disk, right? Because you don't want to have, this transaction did two updates and then it commits and then I write the commit record out the disk but before I write the update records, right? I'm screwed because I crash now before I come back before I flush out the update records, I lost them, right? So all the operations, all the log records for any updates have to occur, have to get written out of the disk prior to the commit record. Now they can be written out together if they're all in the same, sorry, the right-hand log page, they all fit in a single page, that can be done atomically but I can't reorder them, I always have to go out sequentially. So then now when the commit succeeds, we're gonna add a new type of log record called transaction end. And this is an eternal bookkeeping thing for the database system, this is not exposed to the outside world. As soon as I write the commit record, successfully the disk, from an application standpoint, the transaction is considered committed. So we can flush this out and immediately tell the outside world, your transaction is safe, your transaction is durable, right? This transaction end thing will come up later on because this is just internal for us to say, we've done everything we needed to do for this transaction, so any internal bookkeeping we're doing about this transaction, we can throw away, right? And this thing doesn't need to be flushed out immediately. The commit does, right? If you want your transaction to be truly durable before we tell you your transaction committed, we have to flush this thing. This thing again is internal, we'll flush it out just as it goes along eventually. Okay? Again, this will come up later on when we do aborts. So for committing a transaction, it's pretty straightforward, right? I commit, that's flushed, I'm done, now I can write transaction end and then that eventually gets written out. So let's look at an example, right? So again, we have our transaction here, it's doing two updates, we have our commit record, now at this point we have to flush out the log, right? So we take everything that's in memory, everything that comes before this commit record has to be written out. Once that's now durable on disk, then we can update the flush LSN to say now, it's now log record 15, right? Then at some point we do later processing, doesn't matter what we're doing, but we'll add our transaction end record and then at this point here we know that everything about this transaction is done, we'll never see any update ever again, right? So we just discard anything we know about it. We also can discard at this point too, right? After we do this flush on the commit record here, we know it's out on disk, so for all intents and purposes, we can just remove it out of memory because we're never gonna need to go back and use it, right? If we ever needed it because it could be crashed, right? Having it in memory doesn't help us because the memory's gonna get blown away after the crash and it's out on disk safely so we can go out and get it. So we can reclaim that memory and reuse it. Yes? Who keeps track of the LSN? So he says who keeps track of the LSN? Which LSN? Sorry, there's a ton of them. So like, so you do have to turn back. You can have like, you turn on the transaction and you can use it in the, in the, in the, who is issuing the LSN? So his question is who's generating these LSNs on the log records? So it's like the log manager. You go to the log manager and say here's, here's my update information, right? Or here's the, here's the update information that I want to apply to the database. It says, okay, I got it. And by the way, here's your log sequence number. And in turn, the log manager just has a counter incrementing one by one. So in this example here, I only have one transaction. So, you know, 12, 13, 14, 15, these are all log sequence numbers going in, in that order for that one transaction, but I can interleave them with other transactions. Right? So, so transaction T4 might have 12, but transaction T5 might have 13. I don't care. And it is possible that the transaction you're still running, but part of the red head allows you to collect the disk. Yeah, so he is, excellent point. So he says, it may be the case that a transaction is still running. We haven't committed yet, but the portion of its log, of its log has been written out the disk because it got piggybacked on some other log, right? Absolutely, yes. Right? And that's fine. Because if we come, if we crash, if we abort that transaction, we're gonna log our boards anyway, so we'd have to write out all the changes anyway. So this is a good point. It's actually the, in the next slide. For our boards, just because the transaction aborted doesn't mean we can discard its red head log records. Right? We wanna log everything, even for the transaction that are aborted. Right? And so the way to think about this, the abording on transaction at runtime is gonna basically be the same thing we do during recovery to undo a transaction. It's all the same steps. And so this is gonna be different than what I've talked about before when we talked about undo, because when we talked about undo before, I've been really vague. I just said, yeah, there's some in-memory state, and you just roll that back and whatever, right? Now we need to be very explicit. And what we're gonna do when we undo a transaction, we're actually now gonna add new log records for when we reverse those changes. So the first thing we need to do is figure out, keep track of how we can go back and undo the operations of a transaction. So now we're gonna add a new field to our log record called the previous LSN. And this is gonna be on a per-transaction basis, the, for each log record, the LSN of the previous log record for that individual transaction. Because that may, again, we may be interlating other transactions, so it may not be always in sequential order. We wanna know how to jump ahead and say, where's that log record we need to undo next? And essentially, this again, think of this as a link list. We don't need this for correctness. We need this for performance, right? Because we don't want this to do a sequential scan on a huge log just to find the next one we wanna undo. We know how to jump and find what we want, right? So we go back to our entue here. Again, now we're in our header of each log record, we prefixed it with the LSN and the previous LSN. And then say that this transaction aborts. We now need to figure out how to undo its changes, right? So this transaction did two updates. We need to figure out in this middle part here, how do we undo its changes and keep track of that log? Because what we don't want is we don't want to, we need to know that we're applying changes to reverse some other changes we've made because we don't know whether that other change has been written out the disk yet. We don't know if the original change has been written out the disk yet. So we're gonna introduce a new type of log record called the compensation log record of the CLR. And this is just like an update record, but it's explicitly telling you that I'm undoing a previous update record, right? And so it's gonna have all the same fields as an update record like the before value and the after value. But now we're gonna have a new one called undo next. It just tells us the next one we need to undo in our list. So we're gonna add these as we undo the changes to a transaction just as we would during an update. But we know the transaction is in this special abort mode. So now I'm gonna show a different sort of example or illustration of what the logs are gonna look like because we're adding a lot of fields and we're running out of space. So we have a transaction T1, it begins and we have a log sequence number for that, a log record for that and then it does an update on A, then some other stuff happens and then some later point we get an abort operation. So this is what we've seen so far in our write a log and memory and now we need to go back and undo this. So the first thing we recognize is that we have to undo this update operation we did before. So we're gonna append a new compensation log record that says I'm undoing this change here, right? All right, so some internal metadata to say oh, I know I'm undoing log sequence number O2. Then the before and after value again are just to reverse what we did before. So it used to be 30 and when we did the update the first time, I went from 30 to 40. Now if I'm undoing, I'm going to 40 to 30. I'm just putting this back where we were before. And then we have the undo next is just a pointer to say what's the next, what's the next log record for this transaction we need to undo, right? And for this one here, it points back to the begin log record and its previous LSN is nil or null. So we know we don't need to look at anything else prior to this, right? So at this point, we reversed all the changes of the transaction, right? But now we need to put a log record in there that says we've undone everything we need to do to truly abort this transaction. We can't put commit because that makes it look like the transaction actually succeeded, right? So this is where the transaction n comes in, right? This is why we have this additional log record type. We add transaction n, we said we've undone everything you need to do for this transaction and we can blow away any internal state we're maintaining for it. Now when a transaction aborts, right? Either because the user told us to abort or the data system aborted you, right? Like at this point here, we tell the outside world your transaction's aborted, right? It's okay because the, it doesn't actually care, the application doesn't care that I've undone all my changes and written log records out the disk to know that I've fully aborted. I just told them I'm aborted and the data system will make sure that you don't come back and try to read anything. That should have been aborted. So that's sort of clear. So when you commit, I can't commit until I flush all the log records. And for I abort, I can actually tell that the application, yes, you're aborted before I even flush this log record out because it doesn't matter. So yes. So when you're recovering from a committed, if you're redoing a transaction, you think that you're going forward but if you're undoing that record. Right, so he said, if I'm redoing a transaction during recovery, I'm going forward. If I'm going to undo a transaction, I'm going in reverse direction. Yes, that'll be the recovery mechanism we use later. Yes. Is that it again? Why do we start with 0, 0, 1? So we're here. We start here, right? We have to abort this transaction. So the newest update that this transaction did, there's only one, but the newest update is 0, 0, 2. So that's the one we want to reverse first, right? And this is just saying, okay, well, if this matches up with this and say if you undo this thing, if you undo 0, 0, 2 in this here, then the next one you want to undo is 0, 0, 1. Because at this point, you don't know what 0, 0, 1 is. It's just telling you, hey, where to go to look at it. So then when we do our lookup in 0, 0, 1, we would see that, oh, it's the begin transaction. There's no other update that comes after this. So we know we have, we've undone everything this transaction did when it ran the first time. So now we can write our transaction end. Is that clear? All right, cool. All right, so again, when you do an abort, you first have to write the log record, and then you're going to play back in reverse order all the updates that the transaction applied when it ran. And you're going to first write a CLR entry to say here's the reverse of the operation for that particular update. Then you restore the old value and you keep doing that into the change until you get back to the begin of the transaction. And then that point, you've undone everything the transaction did, and then now you can write out the transaction end. So CLRs are never going to be undone because they're essentially looking like, they are essentially like updates, but they're logically tied to an update from before. And it's an additional metadata to keep track of if we want to not update. Yeah, you'll never generate a CLR for the same update multiple times because you know what the CLR corresponds to. And so when we later on, when we talk about recovery, we'll see that we can replay transactions and apply the CLRs and we never have to undo them because this is occurring after we've known the transaction aborted. All right, so we've covered log sequence numbers, we've covered the CLRs, and now we start getting more complicated and talk about how to take checkpoints because again, the idea of a checkpoint is that the log's gonna run forever, or it's gonna grow forever. And so if we had to replay the entire log every single time we restarted, that could take a long time. So the checkpoints are a way to tell the log record all your pages that were in your buffer pool at this point in time have been written out the disk. And therefore I don't need to go start at the beginning of the log, I can start at that point there in the database or in the log. So let's talk about two ways to do checkpoints that are bad and then we'll see how to do the better way, which is the fuzzy checkpoints. So the easiest way to take a checkpoint, again we want our checkpoint to essentially be a consistent snapshot. That's sort of could be one goal. The easiest way to do this is you stop accepting any new transaction and you let whatever transactions that are currently running right now, you let them finish. Then at some point there'll be no actual transactions in your system, then you just scan through your buffer pool and write out your checkpoint, right? Super easy, super bad, right? Because you could have a transaction that could be running for hours. And I need to wait until it finishes, I'm not accepting any new transactions. So my database system looks like it's gonna be down or it's unresponsive during this waiting for this one transaction to finish. And I don't know how long it's gonna take because it could be issuing transaction requests or query requests over and over again. So again, this is easy for us to implement but in practicality of it is not good because it'll make our database look unresponsive. So the slightly better technique is what I talked about last time, but still not that good, where we'll just let any, we don't have to stop any accepting any new transactions. We'll just pause any actual transactions while they're running, while we take the checkpoint and then once the checkpoint's done, they can resume and start up again. So what this would look like from the application standpoint, it'll look like your query is just taking, it just took a little bit longer than maybe you expected because it paused it while it was writing things out. So the issue that we're gonna have to deal with though is that this is gonna have inconsistent checkpoints because we may not get all the updates of a transaction that may have been active at the moment we took the checkpoint. So let's say that we have three pages in memory and then we have one thread that went through a checkpoint, one thread wants to do a transaction. So say the transaction's already running first and it's gonna update two pages. It's gonna update page three and update page one. It's gonna go in sort of reverse order. So to say at the beginning, it updates page three. Then now we pause all our transactions because we're gonna take the checkpoint and the checkpoint goes ahead and scans through and writes out the pages to disk. So it's gonna get one and two as they existed before the transaction started but then it's gonna get page three with the modification that the transaction made before the checkpoint started. We write that out the disk, now we unpause all our transactions, then the transaction keeps going and now updates page one. So now in disk we have, again, we have the state of page one and two before the transaction started but page three after the transaction started but if you want to exist in snapshot, you need the update to page one as well. The issue for this is because there's no extra information we have in our checkpoint about what was going on in the state of the system. For this sort of simple example. All we did is say let's just pause everybody and we'll write out our checkpoint and then we can come back and do whatever they want. So to be able to recognize these issues, to recognize that oh we actually missed page one from this transaction, we need to actually store some additional metadata about what was going on in the system at the time we took the checkpoint. And now we're actually gonna keep track of the boundaries of the time we were taking the checkpoint. So before I said I take a checkpoint, I pause everybody and there's a single log entry for that. Now I want to say I start my checkpoint and I end my checkpoint. So now what we're gonna record in our checkpoints are something called the actor transaction table or the ATT. So these are all the transactions that were active at the time the checkpoint was running. And then we'll have the dirty page table of the pages that were modified since I started my checkpoint. And that'll help me figure out, oh by the way there's this transaction here and they were active when I was running and they modified this page so I missed that. So the actor transaction table was gonna be this internal memory hash table or table. That doesn't have to get rid of the disk except when it goes written out with checkpoints. And for every single transaction that's active during the checkpoint, we're gonna keep track of its transaction ID, its status, whether it's running, committing or a candidate for undo. Meaning we don't know what outcome it's gonna be so we think we're gonna have to undo it. And then we're also gonna have the LSN for the most recent log record that was created by this transaction. Any time a transaction commits to abort, we can remove it from our ATT. The dirty page table is gonna keep track of what pages in the buffer pool at the point I'm taking the checkpoint and which one of them were modified or the log record for the transaction that modified them. So for every entry of a dirty page in the dirty page table, we have the rec LSN, which is the oldest record, oldest log record for any active transaction that calls this page to get dirty. We have the difference between the page LSN and the rec LSN. Page LSN is the newest log record for the transaction that made the page dirty and the rec LSN is the oldest one. So that's where this comes into play. So let's look at an example. So running out of space in our log, that's fine. So in this example here, we have two checkpoints. At the first time we take the checkpoint, we're gonna see that in our active transaction table, T2 is still active, because it begins up here. And then we have two dirty pages. We have P11 and P22 because P11 and P22 were modified here. We don't know whether they'll be written out the disk yet. So then now when we take another checkpoint, at this point, our active transaction table only contains T3, because during this time, since then, T2 committed. So T3 is active during this checkpoint, because we paused it while we were running. So we keep track of that there. And then our dirty pages is P11, P33, because these guys were modified up here since the last checkpoint. So this is better, because now we know if we have that torn update problem where the transaction update two pages and we wrote out one but not the other, we will be able to see that because we know what pages are being dirty and what active transactions are running. But this is still not ideal because we still have to pause all our transactions while we did this. And again, depending on how big our buffer pool is, how many dirty pages we have, how fast our disk is, this might be a couple of minutes while we write everything out. If you have a one terabyte buffer pool, this could take a long time. So the way to get around of not having to pause transactions is to do what's called our fuzzy checkpoints. So this is where we're gonna still, we're gonna take active checkpoints, so we're gonna take a checkpoint, but we're gonna let transactions keep on running while we take the checkpoints and still update the database. And then we're gonna rely on the ATT and the DPT to be able to figure out what pages were modified during this time. So now we're gonna introduce two types of new log records called checkpoint begin, checkpoint end. Checkpoint begin to says, here's the start of my checkpoint, I start doing my sequential scan on my buffer pool in memory and I start writing those pages out the disk. And the checkpoint end is where you say, all right, I've done my checkpoint and oh, by the way, here's now the ATT and the DPT for things that were running or active and modified pages while I took the checkpoint. And I may or may not have written out those pages out the disk, you don't know. So we go back to our example here. Now we see that we have, again, we have a checkpoint begin and we have a checkpoint end. So in the checkpoint begin, right, when this occurs, we actually go and update our master record on disk to say, here's the last checkpoint I took for my database. So then when we crash, we can come back to that and figure out whether we actually got everything we wanted. Now maybe the case, the checkpoint actually doesn't complete because he crashed before it finishes and but we would know that because he would scan forward and not see the checkpoint end. But for our purposes, let's keep it simple. Assume we're not gonna crash during checkpoints. So then we see now in our checkpoint end entry we have again the ATT and the DPT. So at this point here in the ATT, the only active transaction that was running at the moment our checkpoint started was T2, right? Because T1 committed here, T2 began here, T3 started after the last checkpoint, but I don't care because again, when I do recovery, my starting point is gonna be checkpoint begin. So I'll scan forward in the log and see T3 get started. But I need to know about T2 because I may not, you know, I'm not starting at that point in the log. I need to know, oh, by the way, above my checkpoint begin, there's a transaction T2 that you need to know about. And then for the dirty page table, sort of keep it simple, we say in between during the checkpoint we wrote transaction T2 updated page 11. So we just have that in our dirty page table. And it doesn't mean that it was, it doesn't mean that it was exactly, it was or was not written out the disk during the checkpoint, it's just saying it was modified during this time. It may not have been. All right, so now, now with fuzzy checkpoints, log sequence numbers and CLRs, now we can actually do recovery. So Aries is gonna have three phases. The first phase is doing analysis. And this is where we're gonna read the log going forward in time from the last checkpoint that was successful. And we're gonna figure out what are all the transactions that were running during this time, whether they've committed or not, and how far back we actually need to go in the log beyond the checkpoint. Because we can look at the active transaction table and say, well, there's a transaction that started above where the checkpoint started. So you need to know about it. So you scan through once to do analysis, then you're gonna go back and do scan through again to do a redo. And where you start to do the redo may actually be after the last checkpoint. Because you would use the active transaction table to tell you this. And you're gonna replay all the log entries until the very end in the redo phase. Even if you know the transaction is gonna go into abort because you would see it, it's gonna abort in the analysis phase. You don't care, we're gonna replay everything. Then now, after we finished all those redo, now essentially what we're at is the state of the database is exactly as it was at the moment that it crashed. Including all transactions that were actively running but then ended up aborting. So then in the final phase, you go back now in reverse order and you're gonna undo all the changes from transactions that you know didn't commit before you crashed or before the log got finished. And then when that's done, then you know the database is in the exact state that it should have been at the moment of the crash without any partial updates or partial transactions. It only contains the changes from transactions that successfully committed before the crash. So is that clear? Yes. Why do you want to redo everything including the CLR records? Why do you have to have a backcode in the CLR records? This question is if you want to redo everything, if you want to redo everything including the CLR records, why do you need to have a backward link? Because if I'm undoing a transaction, I say I have two updates, I need to undo them. So I have the first CLR for the first updated reversed. Then before I get now to the second one, I crash. So there's no second CLR. So when I come back, I'm gonna redo that and keep going where I left off. I want to know where to jump next. So you don't need it for correctness. So a bunch of those extra, those LSN pointers are just to jump more quickly to where you want to go. Yeah, but like when you're playing the CLR, you're actually... The statement is if you're playing the CLR you're still in a forward manner. For recovery, yes, for like the runtime process now. Cause you're going in reverse order for that. So you don't need it for the redo, but you need it for the runtime operation. Yes, you still need to undo, sorry, say it again. I think you're saying that if you know you have to undo them in phase three, why even bother with redoing them in phase two? This is for, they're being super anal about correctness, right? So there's a bunch of optimizations you can do, say, oh, I know I'm not, for example, I know this transaction is gonna abort cause I saw this, I saw it, it didn't commit in the analysis phase. And I know that it only modified a page that no other transaction will modify. So long as my page on disk at the start of the redo phase is where it should be and not any changes, but not with any changes from this transaction I know that's gonna abort later on, then I can just ignore everything that transaction does cause they never made it out of the disk, I don't care. So yes, there's a ton of optimizations you can do like this. We're not gonna do any of them because we just wanna understand the basics of this. And I'm saying that like, to understand how to get database coverage, it's super important cause you don't wanna lose any data. So we're gonna be super cautious, overly conservative and make sure the database is exactly this data should be. Even though we know there's some things we could throw away. We're not gonna do any of that. So visually it's gonna look like this. So again, starting from the last begin checkpoint we have that we found via our master record, right? That's gonna tell us where in the law we wanna start. And we do our analysis phase, go forward in time and just build out the ATT and the DPT, right? Then we go look at the, in the redo phase, we're gonna jump to some other point in the log, which may or may not be before the checkpoint. And we're gonna replay all those changes for every transaction. Even for ones that possibly the page is actually made out of the disk, we don't care, you know, since the checkpoint, we don't care, we're gonna replay everything cause we don't wanna miss anything. Then we're gonna do our undo, we have to go back in time and undo the effects of any failed transaction, any transaction that didn't commit by the end of the log, okay? And again, I'm showing you that these are sort of like the max bounds of how far you'd have to maybe go back and look at each of these things, right? So maybe the case that there's no transaction that you have to undo past the last checkpoint. But there could have been a transaction that was, you know, you opened five days ago, right? And it's just sitting open and made a bunch of changes that you have to go back and reverse. Now again, the key is that you don't have to redo everything to get back up there, but you have to undo it, okay? All right, so we'll go through these one by one. So the idea of the analysis phase is the goal is that we wanna basically reestablish the knowledge of the internal state of the database in regard to the DPT and the ATT at the moment that we crashed. So we're gonna jump to the last successful checkpoint, scan forward and look at every single log record. So anytime we find a transaction and record, then we go ahead and remove it from the ATT because we don't need to, we don't care about it anymore, right? There's nothing else that's gonna come after it later on. For all the records we see, if the log record corresponds to a transaction that we haven't seen before, then we need to add it to our ATT, but we're gonna set its state to be a candidate for undo. Because at that point, the first time you've ever seen this transaction, we haven't looked it down the rest of the log, so we don't know what's gonna come later on. So we think it's probably gonna have to abort. We're gonna assume that it's gonna have to abort. But then if we see a commit record, then we can flip its status to be commit because we know it has successfully finished prior to the crash, before the crash. If we see any update record, then we're gonna know what page it modified. So if the page is not in our dirty page table, we're gonna go ahead and add it, and then we're gonna set the rec LSN for that page to be our LSN for our log record. And again, so now the way to think about this is we're keeping track of all the pages that were modified since from the last checkpoint to the crash, some of them may have written out the disk, some of them may not have been. We don't know at this point, so that's why we're just building this internal metadata to keep track of what we think should have happened. So now at the end of the analysis phase, we have on our ATT tells us what are the transactions that were active at the time of the crash, and it tells us, the DPT tells us what are the dirty pages that may or may not have been written out the disk before the crash. So let's look at an example. So here we have now a simple redhead log, and then this is the internal state we're building up as we do our analysis. So in the first, we jumped to our, the first checkpoint begin, there's nothing to record for this point, in our ATT or DPT, we don't know anything yet. Then we get to now to this first update record here as there are 20, so in our actual transaction table, we're gonna add entry to say transaction 96, and we set its status to be undo, undo candidate because at this point, we don't know anything else that comes down the log, we're scanning this for the first time. So we think it's gonna have to get a board and undo everything. They're also gonna record that it modified page 33, so we have an entry in our dirty page table to say, page 33 has been modified and the oldest log record that modified it is 20. Then now we get to the checkpoint begin, sorry, the checkpoint end, and now we set a bunch of additional metadata that we can record about what were the actual transactions that we had at the time and what are some of the other dirty pages we had at the time. So here's the point out is I knew about transaction 96 since my last checkpoint, since the checkpoint begin because I saw an update record for that, but I also have transaction 97 here, right? So I didn't see any update record from this transaction because whatever it did, it's up above this checkpoint begin. So this is what the ATT is telling us. Hey, by the way, there's another transaction you need to know about but it did something before you did your checkpoint. It also modified page 20, so again, same thing, we have an entry for that and it has some log scene with some or that comes before us. Then now we do our commit and now here transaction 96 is gonna commit. So now we change our status in the ATT for transaction 96 to be committed, right? And then what we can ignore the dirty page table for this because we're not flushing anything out, right? We don't have to flush the log record. Then we have the transaction end and now at this point, we know that all the changes that for this transaction I've been successfully written out so we can go ahead and just remove that here, right? So now after an analysis phase, we go to the redo phase. Again, the idea here is we're gonna replay all the entries with all the log records we have in our log both for the updates and the reversals with the CLRs and we're gonna reply all our changes and we're gonna do this for even the transactions that are bored. Yes? Yeah, so it's 33 and 32. Our question is if we remove T96 here, why do we still have to maintain information about this page table here and those pages? So this is just telling us that this transaction is active or whether it's finished or not. We don't know anything about whether these pages have been written out to disk or not. They're independent of each other, right? But it modifies some pages, right? Were those pages written out to disk? You don't know, right? I still keep track of like oh for say it modified what, 20? So I know for modified page P33, I know page P33 was modified and the first log record that made that change was 20. So I know if I come back and I'm gonna replay this, I know I need to see at least whatever this log record was because I don't know whether that modification made out to disk or not. Yes? Our question is is it true that DPT always increases? During the analysis phase, yes. Right, as we flush things out, we can remove them. Okay, so now again, the redo phase, we're basically repeating the history to put us back in the exact state we were in in our database system at the moment of the crash. So again, we're gonna replay everything even for abortive transactions because again, we wanna be exact about this. So as I said to her before and she asked about, well, why do I have to redo transactions that I know are gonna abort later on? Again, there are techniques to avoid these things. We're gonna ignore all that for now. Okay? All right, so what's gonna happen is we're gonna scan forward in our log from the entry in the dirty page table with the smallest rec LSN. Again, the rec LSN is the oldest LSN corresponding to an update to a page that made it dirty. So we gotta go back in our log and find that log record. And then, because we know that anything prior, anything that comes before that, all those pages have been written out the disk. Right, because my rec letter, my LSN is the oldest one. So we're gonna scan forward now and replay all the log records that come after this. And for every single update log record or CLR we see, we're gonna reapply our change to the database unless one of the three criteria are met. So if the effective page is not in the DPT, then we know that it got written out the disk, so we're fine. Or if the effective page is in the DPT, but it's the log records LSN is greater than the rec LSN, then we know it's also been written out in the disk as well. Or if we go bring the page in and we see it's page LSN, and the newest, the LSN of the newest log record that modified it, if that is greater than our log records LSN, then we know that whatever modification that our log record had to that page was written out the disk because it got later written out by when this thing got flushed out. Again, we can use these LSNs to figure out what's actually being written, what's the order of these things happening. Yes. Effective page is in the DPT, but that records LSN is greater than the smallest record of LSN. Yeah, actually, this should be, I think this should be the page LSN, right? Yeah, that should be the page. So I have my log record. I have a rec LSN that corresponds to it when it was last modified, but then my LSN comes after this. It should be the other way. My rec LSN comes, the rec LSN is on disk is newer than my LSN. Then again, I know all my changes got written. It's the opposite of this. Okay, yeah, I'll fix that afterwards. Again, the reason why we're starting here because we know everything else prior to this in our log, all the changes that made to the pages have been successfully written out the disk. Yes? So if you change that, why are you always setting control? Okay, so I have, so I'm going through my log. I have LSN here for my log record. I am looking at a page and its rec LSN comes after mine. Here for my log record. Then I know in order for anything to get written out for that page, it has to, all any changes that came before it hasn't been applied to it. So everything else up to that point has been written out, so I don't need to reapply it. Anything that it hasn't like after your... Yes, so yeah, it's actually very clear. So at this point I'm going through, I have nothing in memory. I have no pages in memory. I don't know anything about it. So I bring the page in memory and I say, aha, I have a page LSN and a rec LSN. Now I look at all the log records I want to reapply and I see whether my change occurs after that thing was last written out the disk. I should rewrite that and make it more clear. So you said it occurs after the LSN, the rec LSN, we have to replay it. Correct, yes. Because whatever changes you have, we're not applied to it. Yeah, at this point you know it not because you fetched it from disk, you know. Right, so to redo an action, all we have to do is just reapply the action again, update the page LSN to be whatever our LSN was for our change and then that's it. We don't have to do any additional logging, we don't have to do any additional forcing of flushes because it's just almost the same thing we were doing at runtime. Instead of running SQL queries, we're replaying these log records. So now, at the end of the redo phase, for any transaction that is still in our, any transaction in the DPT with the commit status, we can go ahead and add its transaction end entry if we hadn't seen it. Actually, if it's still in the DPT, then we didn't see the transaction end. So we can go ahead and add it and remove from the ATT. Right, this is saying we saw a transaction that committed but we didn't see the transaction end entry before we crashed but we know it actually committed because we saw the commit entry. So we can go ahead and remove it. But now for the undo phase, any transactions that are still in the DPT, sorry, in the ATT with the status of undo candidate, those are the ones we need to reverse and undo. So now what we're gonna do is again, we're gonna go in reverse order and we're gonna process the each log record and undo them in the opposite order that they appear, from the real wall clock time. And then every single time we undo an entry, we're gonna add a CLR because it could be the same process you would have at runtime when you boarded the transaction. Okay, so that's the full end-to-end example of this. Right, so we'll do a really simple log here. What's sort of not shown is that there's previous LSNs that allow us to bounce through for every single transaction but we can ignore that for now. I'm trying to make it as compact as possible because there's a lot to cover. Let's say here after log sequence 60, we crash. So now we need to recover the database after this. So say we go through now our analysis phase and we do our analysis phase and we update our ATT and we see that we have two entries here for transactions that did not finish before we crashed. So then when we go to the redo phase for T1, we already have actually the transaction end entry here. So there's no changes we actually have to make. So now the first thing we're gonna do is go back to reverse order. So the first thing I'm gonna do is undo this change that T2 made on this page here. So in our CLR, we're gonna say we're gonna undo for transaction T2 and we're reversing the operation that occurred at LSN 60. And then we have now just a pointer in our undo next to say here's the next operation I need to undo for this particular transaction. It's not the next operation I'm going to physically apply. It's just for this particular transaction, it's the next one I should apply. Again, we don't need this for correctness. We'll need this if we crash during recovery, right? So we crashed and we're covering and now we're gonna crash again during recovery. We wanna know how to jump ahead and find the next thing we actually should reverse. So let's say now the next thing we do was the undo update on T3. Same thing on update undo T3 and the log sequence number this corresponds to is 50. And at this point here, we know that there's no other update for T3 in this transaction. So we can go ahead and put a transaction end message in there to say that it's fully done and fully committed. So now the next thing we need to undo is this one here on T2. But let's say, you flush the log and now everything's out to disk. To now say before we undo the second update on T2, we go ahead and crash and restart. So now our ATT and TPT that we generated from the analysis phase is blown away. So now when we come back, we're gonna do our, again, same thing, do our analysis, then do our redo and now we're gonna figure out which ones we actually need to undo. So the only transaction that's still sitting around in the ATT after we've done this analysis here is transaction T2, because it has had this undo record here but we didn't undo the other one. So now this is where those undo LSN pointers undo next LSN pointers help us, because now instead of having to scan and look at through every single log record, we know that we only need to look at the log records for this one transaction. So we can just use this as a link list to jump through and find the entries that we want. Right, this seems sort of trivial because I'm only showing a small number of log records. But again, think of like, you know, database system doing a million transactions a second. And so you're gonna have these really large logs and you don't want to have to scan and look at everything because reading from disk is expensive. So this allows you to jump through more quickly. So at this point here, again, I recognize that when I try to start undoing this transaction, as I redid the first time, I would see, all right, while I was able to undo this entry here, the next one I need to undo is up here in 20. So I would add the next CLR for that, reverse the change, that's the only one I have left for this transaction T2 and then I write out the transaction end message and I flush that. Now this point here, when I do this, when I finish this one here, I've covered all the transactions I have in my actual transaction table. I don't have to flush out the dirty page table because that'll happen again in the normal process as to execute new transactions. But at this point here, if I've reversed all transactions that were still sitting in the ATT, then recovery is done. And now I can turn the system on or I can sort of start listening on connections and start accepting new requests and new transactions and going back into a normal mode. Is everybody falling asleep or is everyone dying? Right, good, this is hard, right? This is why you don't want to write recovery for yourself in your shitty application, right? You want to use a database system because it's people are just, really smart people will spend a lot of time making sure that this works correctly and the average JavaScript programmers not gonna be able to do this. Right? This is why database developers get paid a lot of money because nobody wants to lose data. So you pay people, they know what the hell they're doing to make sure you don't lose data, right? Okay, so, yes. Can we say that the CLR records will never be played backwards? His statement is, will the CLR never be played backwards? I mean, do you, I mean, there are just like two legal operations for CLR. The first thing is like when you're constructing a CLR, you're playing back the original record. Yes. The second option is like you get a CLR and you took the previous CLR and jump to the... Correct, yes, so, to be more concretely, you don't undo an undo, right? So you don't, it doesn't make sense. It doesn't undo a CLR. So when I'm doing, so I would replay them, I crashed here. All in recovery, I would replay the CLR and apply all the changes, but then recognize, oh, I don't see a transact and end message for T2. So what was the last LSN I saw for it? 70. So I know this is, I'm in the undo phase here, so I use undo next to tell me where to jump through, right? And then again, you're using points to say, well this thing updated, this thing reversed 60. So the next thing I need to undo is 20. And then I add the CLR for that. So you only redo an undo, but no undo an undo. Correct, so he said, you only redo undo, you never undo undo, yes. All right. Yes. I'm just, so I'm just saying that like, yes, so his statement is, why am I showing this sort of chain here, right? Why can't you just use this and jump to that? Yes, I'm just saying that this thing corresponds to this and I know the lineage of what things I need to look at. All right, so we talked about what happens if you crash during undo, what happens if you crash during the analysis phase? What do you have to do? Anything special? No, right, because you didn't do anything, right? You didn't write anything out? You don't do anything, you just run the analysis again. What if you crash during redo? Nothing, exactly, yes, because I'm gonna come back and just redo everything all over again and pick up where I left off, right? So it's sort of only in the undo phase you need to be a little bit careful about recognizing I've undone this transaction so far and I need to figure out how to jump back and undo the rest of it. All right, so this came up a couple of times. It seems like we're gonna be kind of slow when we're doing this because we're doing maybe more work than we actually need to. So that's one way we can improve performance during the redo phase. So what I didn't really talk about is that we were actually gonna be super anal and flush everything as we go along. But if you assume you're not gonna crash during the redo phase, then you just flush your changes out the disk asynchronously and just keep going. Right, again, if I crash while I'm doing this, I'm gonna redo everything anyway, so who cares? So maybe I'm not so super careful about making sure everything gets flushed. When I'm done, I gotta make sure everything's flushed but not while I'm doing it. What about the undo phase? So one optimization you could do other than not undoing things that you know you don't need to undo or undoing things that you shouldn't have redone in the first place. You actually can lazily apply undo operations only when transactions actually go to try to read them. So there's the paper from Gertz-Graphy, the guy that did the volcano stuff that we talked about before. He had a technique where you basically keep track of, oh, I have a page but I haven't applied all the changes to put it back into the correct form and if anybody tries to go read it, then I'll go ahead and do that. So that way you can turn the database on right away and it's almost like it instantly gets recovered even though underneath the covers it actually didn't. The alternative also too is just to rewrite your application to make sure you don't have any really, really long transactions because that'll reduce the amount of, how far you have to go back in the log. But that, again, that requires a major rewrite. All right, so to finish up, the main ideas of areas are again, right ahead logging with steel and no force. We use fuzzy checkpoints and it takes snapshots of all the dirty pages and maintain the ATT and DPT about what was going on in the system at the time we did this. We're gonna, during the recovery operation, one pass-to-do analysis, figure out what was going on, redo, pass-to-do redo, redo, and we apply all the changes since the last page we know that was not possibly written out the desk. And then we undo for any transactions at the end that we know that didn't have commit to reverse all the changes, right? And as we're doing undoes, we make sure we log everything in CLRs. So the log sequence numbers are essentially what we're gonna use to figure out what the hell's going on in the system, right? It's gonna basically link these things together to keep track of, for my transaction, here's all the log records I generated. So if I need to do anything for a transaction, like undo, to undo it, I know how to jump over to the log records that are in between those log records and jump to the ones I just need for that single transaction, okay? And the page LSN and records LSN allows us to figure out what's out on disk and what's in the log. All right, any questions about Aries? So let's do a live demo. I've never tried this. Yeah, so I wrote the code, I haven't even tested it. All right, so we're gonna run my SQL 5.7. And what we're gonna do is we're gonna make a single table called wall demo and it's gonna have a single tuple with 10 fields, right, zero to nine. And I'm gonna write a really simple Python script that is just gonna go into a for loop or infinite loop and it's going to try to update in a transaction. It's gonna update every single field one by one. So I have 10 fields in a transaction of 10 updates and it's just gonna add one to the counter. And so we said that the database should not have any partial updates, right? So this is my code here, really it's hard to read, but like while forever loop through and in each iteration of the while loop, loop through and execute this query here that's just gonna take whatever each field and add one to it one by one in transaction and then the transaction commits here. So then while this is running, I'm gonna go ahead and do a hard kill on the database server, right? And assuming the database system is running fast enough, I hopefully it'll be running in the middle of a transaction and then we'll turn the system back on, look in the system log and see whether it, when it comes back online, whether it's correct that each entry is always one more than the previous one, right? Because if it's not, then we know we have a partial update. Like if it crashed while it was updating value five, so this thing got updated to six and then we crashed here before we could update this thing to seven, we would come back online, see that this was six and this was six. So if any two fields that are next to each other are the same, then we know it didn't recover this correctly, okay? All right, live demo, see what happens. So our thing is running, oops, sorry. I can do a select here, right? We see it's running, doing something. So then now in for my SQL, they had this thing called the PID file that basically tells you what the current PID is for the database over here. So I'm just gonna do a k-9 on this, right? And then Python came along and says I lost my connection, right? So let's go see what's in the log. All right, so this is gonna be not the right ahead log, this is gonna be like the debug log for my SQL. It basically tells you what's going on when you start up. And actually let's do it up here. So we can see it better, right? So this was running and then it got killed. So now what we'll do is service my SQL status should be down, active. Let's try that restart. Oh, perfect, all right. So the top guy started up again. So up here, we turned the thing on, my SQL booted up and someone here was just saying recover from the log. Yeah, much of roll 7.7, that's not the same thing. Non-redual roll back 7.7s are active. All right, it did something, right? But I killed it. So now let's go back and catch our database. And voila, there we go. Right, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51. There you go. Now, presumably I didn't kill it while it was in between the transaction and the for loop, but it was unlikely that it maybe did. So again, this just showed you that if I didn't have write ahead logging and doing this correctly, I would have come back and saw a partial transaction. Because it knew to undo changes from a transaction that did not commit before I killed it, then it knows to reverse that change. Which is what I should have done, I should have had this thing printed out, like what update it left off with. We could have seen that the last one I did, but that's fine. Take my word for it, my SQL has write ahead logging, okay? All right, so at this point in the semester, you're awesome, right? You can go off and build your own single node database system. You can do transactions, you can do logging, you can do indexes, right? So now that we understand the basics of single node databases, we can now start talking about distributed databases. All the same techniques and same problems we talked about on single node systems up to this point, they exist in distributed databases. But now there's other that we have to worry about, consistency becomes a big problem now. How do you keep sure track that the different nodes in your database are gonna be in sync? So for the next three lectures, we're gonna talk about transactional distributed databases, transactional analytical databases, and then we'll do a new lecture on these cloud serverless databases that are sort of coming more prominent. Okay? Any questions? That's my favorite all-time job. Oh my God. No. What is it? Yes, it's the SD Cricut IVES. I make a mess unless I can do it like a Gio. Ice cube with the G to the E to the Tio. Now here comes Duke, I play the game where there's no roots. Homies on the cusp of yama, focus on drink brook. With the bus a cap on the ice, bro. Bushwick gonna go with a flow to the ice. Here I come, Willie D, that's me. Well G, and St. Iva 4. Six pack for the act, Joe, I drink it by the 12. They say bill makes you fat. But St. Iza straight, so it really don't matter.