 All right, again, thank you DJ Drop Table for keeping things fresh. All right, so we have a lot to talk about real quickly, again, for the assignments. Project three has been put out. That'll be due on Sunday November 17th. And I'll talk about that briefly at the end of this class. And then, homework four should be four, not three. Four will be released next week. And that will be then, that'll be due on the 13th, OK? And as I said, my wife is going, we're going to the hospital tonight. It's going to happen like nine hours from now. So I'm going to be gone for two weeks. I won't have office hours next week or the following week. And then next week, we will still have classes. My PhD students will be taking care of those classes. Then the following Monday will be again, one more PhD student and then we'll have no class on October 30th. And the schedule correctly reflects this, OK? So any questions about any of this? Then I will make arrangements with my admin about having all the midterms in her office. And then we'll figure out some time you could go come to her office and check out your midterm. Again, just bring your student ID so she knows who you are, OK? And then if you have, if you want something regraded, take a photo. You can't take your midterm with you. Just take a photo of the page. You want me to read it and email me. And we'll take care of it, OK? All right, so we're at now in the course is that we've covered the entire stack. We've covered how to store things on disk. We've covered how to put things in our bubble. The access methods do scans, how to execute operators, and how to do query planning. So now we're going to actually look at for the next four weeks, we're going to go back and look at the entire architecture all over again. And now consider two important components, concurrently control and recovery. And these concepts actually permeate all throughout the entire system. We need to understand the basics first, and that's why we went through without discussing any of these things. And now we're going back and seeing how we, if you want to enforce a concurrency tool or make sure that our database can be stored on disk safely, how do we modify what we've already talked about to account for these things and take care of it. So again, concurrently control and recovery are all, they're not necessarily separate things on the side. Like the bubble manager or an index, the entire system needs to be aware of how it's going to be durable, how things are going to be, how transactions are going to run safely. And so that's why we're again, we're covering this at the second half of the semester. And I would say also too, once we have these two things, then you can go off the world and build your own database system. These are the last two things we need to actually build a database system that can run transactions correctly and make sure that everything is safe. So we're almost there. So to motivate why we want to talk about concurrently on recovery, let's look at two simple scenarios. So let's say that I have an application where I want to have two threads try to update the same record in the same table at exactly the same time. How to make a decision about which one should succeed? What should be our final change? There's a race condition here. I would have wanted to come slightly before another. The other scenario is that let's say that I have an application for my bank and I want to transfer $100 out of my account into your account. But let's say after I take the money out of my account, but before I can put it into your account, the building, the data center gets struck by lightning, we lose all power, our machine crashes, our database system crashes. So when we come back, what should be the correct state of the database? What should we actually see? So the first problem that I'm talking about here at the top, this is an example of a lost update. If I have two transactions, two threads trying to make an update to the same record at the same time, I couldn't end up missing one. How to make sure that doesn't happen? And the way we're going to ensure that these things happen correctly is through a concurred show mechanism, a concurred show protocol. For the second scenario where my data center catches on fire and I lose power and my machine crashes, we're going to use the recovery mechanisms in the database system to ensure durability. So these two concepts, concurred show and durability, are one of the main selling points of a database management system. This is why if you're building an application, whether it's in the cloud or on a cell phone or on a desktop, you don't want to be in the business of doing these things yourself in your application, because you're probably going to get it wrong. And you can end up losing data or have incorrect data. This is why you want to use a database management system because they have really smart people that have spent a lot of time to make sure that these things happen correctly. If you also think about it too, like if you're a startup, if you're shipping an application, it doesn't matter, at the end of the day, what's not going to sell your product is, oh, I can recover the database after a crash. You need that as a feature you absolutely have to have. But that's not a differentiating aspect of your application versus your competitors. So you don't want to be in the business of writing a database management system yourself unless that is your job. For everything else, people should rely on high quality software, database system software that is vetted to do these things. So the core concept that we're going to use for the next four weeks discussing our system to make sure that things are running in the correct order or that all our changes are still durable is this idea of transactions that are going to run with asset guarantees or asset properties. So there's a quick show of hands. Who here has heard of the acronym ACID before? All right, about half. OK, so we'll cover that. So before we can talk about ACID, let's talk about what a transaction is. So in our world today, for what we're talking about in this lecture, the transaction is going to be the execution of a sequence of operations on a database system before some higher level function. And so these operations you can sort of think of as SQL queries or the reads and writes we're doing to the database. And by higher level function, I mean something like that one for our application. Some feature we want our application to perform or to steps. Like transfer money from my account into your account, that would be a high level function. Because that's something we would program in a transaction in our application. No database system is going to have that feature, that single function you can call move money. This is something you would write in your application up above. So transactions are going to be the basic unit of change in our database management system. Meaning this is how all changes are going to occur in the wrapped inside of a transaction. Whether it's multiple queries or a single query, it's always going to be a transaction. I suppose you can have a zero query transaction, but that doesn't really mean anything. But it's assumed that it's one or more operations that we want to do. And so the key concept, though, about our transactions is that we're not going to allow for partial transactions, or transactions are always going to be atomic. What that means is that if I have a sequence of five updates I want to do, either all five occur or none of them occur. I can't have maybe the first three out of the five succeed and the other two fail. It's either all or nothing. And even if you have a single query transaction, a single operation transaction, say I have an update query that updates five tuples, still one query, but within that I'm updating five things, all five have to get updated, not some subset of them. So the transaction example would be that the one I talked about before, where I want to move $100 out of my bank account into my shady promoters account. So the database system doesn't provide this functionality. In my application code I would write the steps to perform this. So in the first step I would say, well, check to see whether Andy has $100. He probably doesn't. But then if I do, then you can take the $100 out of my account and then put the $100 into his account. Again, these are step-by-step. There's no magic way to just materialize money in a single at the lowest level of the hardware to automatically update something and another thing at the exact same time. There's a bunch of extra stuff we'll have to do to make sure that this happens atomically. But from the application perspective, they evoke this transaction and this will all happen or none of it happens. So let's talk about a really simple database system we could build that could do this for us. So let's say we have a database system that only supports a single thread, meaning only one transaction and only one query can run at a single time. And if multiple queries or multiple transactions show up into the system, it just puts them in a queue and there's one thread pulling things off that queue and running them one by one. So now before transaction starts executing, what they're going to do is they're going to copy the entire database file or a set of files, however it's architecting it. It's going to make a second copy of the database, make all the changes it wants to make to that copy, and then if it succeeds and we want to save all our changes, then we just flip a pointer to say now the new version of the database is the second file I just created. So this guarantees that atomicity property I just mentioned because if I make the copy to the database and then I'm doing five writes but then the first three happen and then I crash, when I come back, I still have my original copy of the database because I didn't affect that, so everything is still correct there. That's fine. Things are being written to disk, so if I crash I could come back and once my disk didn't die, all my data is still there. So would this actually work? It says yes. Would this be fast? It says no, why? You said no, so why? So many updates that just pop out and then copies would take up so much. So the updates doesn't matter, right? The amount of updates I'm doing in my transaction doesn't matter because I'm copying the file every single time. So by copying the entire file and make one change versus 1,000 changes, that copy cost is always the same. But you're right, the copy part is expensive. If it's a four kilobyte page for my database, who cares, that's one hardware read and write. I can do that pretty quickly. But if I have one petabyte of data, now for every single transaction I'm copying one petabyte every single time, making my changes, and then updating the pointer. So this is a good example of where we can design a system that has the properties that we want, in particular the asset properties that we'll talk about, but this is going to be super slow to do it this way. The other issue is that we're also running with a single thread. So I didn't say anything about whether the database is in memory or not. So now if I'm running with a single thread and try to touch data that's not in memory, but it's in disk, I have to stall my thread until I go fetch it. And I can't run anything else because I only have one thread that can do this at a time. So what we're going to talk about today, and for the next couple weeks, is a potentially better approach where we're going to allow transactions to run simultaneously at the same time. And then we're going to come up with a way to try to potentially interleave their operations in such a way that we maximize our parallelism, but still get all the safety guarantees that we want and correctness guarantees that we want in our database system. And again, it's obvious why we want to do this because we talked about it before when we talked about latching, we talked about query execution. If we can allow multiple queries to run at the same time, we're going to get better utilization of our hardware, better throughput, meaning we can do more work in the same amount of time. And then the system is going to look more responsive and snappy because now I don't have to wait in that single queue until my transaction gets to that front and then I can run, I could potentially start running right away. But now, of course, the tricky thing is going to be is that how do we actually do this interleaving in such a way that we don't violate any of our correctness guarantees of our system and that we don't starve any one transaction from taking all the resources and other transactions can't do anything? So concurrently, what we're talking about today is an old concept that goes back to the 1970s when IBM built System R. This is one of the first things they also invented. And so in a disk-based system, back then, of course, because memory was limited and any time a transaction could charge data that's on disk and not in memory, and therefore it would stall, then now you can let other transactions run at the same time. In modern systems today, usually for OLTB applications, they're not that big. The databases aren't that big. So we have enough memory where we could put the entire database in memory. For analytics, you still go to disk, but we're not doing transactions there. So in a modern system, most of the OLTB databases can fit in memory, but now Intel is giving us more and more cores. So now we're going to allow transactions run on different cores at the same time, and then we still need to guarantee all of these things. So even though the hardware is different from how people first invented and could actually go back in the day, we still have the same problems. We still want to maximize parallelism. And as I said a couple times already, this is going to be really hard to do. And as I said last class, this is probably the second hardest thing to do in database systems, to do concurrency control. And this is part of the reason why the NoSQL guys, when they first came out 10 years ago, they were like, well, we're not doing transactions. That's too hard, because they want to run faster. So it's going to be super hard for us to guarantee correctness, which transactions? So what happens if I have $100 in my bank account, and I try to give money to two people at the exact same time, what should happen? Because I don't want to, assuming that the bank's not going to let me overdraft, I don't want to be giving out money I don't actually have. And then it's also going to be hard to execute this very efficiently. Because again, if I do the serial execution case that I talked about in the beginning, then that's going to be always correct, because only one transaction is running out of time. And then I don't know how many worries about any interleaving. But for now, I do want to interleave them. I want that to be as efficient as possible to be to figure out whether I'm running correct still correctly, because there's going to be some overhead to figure these things out. So what we're essentially trying to do today, and for the next three or four lectures, is allow for these interleaving and the operations of transactions. And what we see is that when we start doing these interleaving, we can end up with inconsistent databases. And sometimes it's OK, sometimes it's not OK. So some inconsistencies will be OK, because they're temporary. So for example, if I'm taking money out of my account and putting in your account, again, I can't do that atomically at the hardware level. I have to do that with multiple instructions and multiple operations. So there will be a brief period in time where I take the $100 out of my account, and then before I put it in your account, that $100 doesn't exist anywhere. So that's OK, because that's temporary. It's unavoidable. The outside world will potentially not see that inconsistency. And we'll do some protection mechanisms to make sure that they can't see this. And so because we're going to allow this, this is going to allow us to actually make this all work. But the thing we want to avoid are permanent inconsistencies, where again, if I take the $100 out, and then I crash, and I come back, that $100 better not be missing. It better be in the other account or my account. It can't just disappear. So in order for us to understand whether we're doing the right thing, whether we're coming out with the interleaving of our transactions that are actually correct, we need a more formal definition of what actually means to be correct. Because it's sort of obvious right here, if I take $100 in my account, and before I put it in your account, we crash, that's sort of obvious. We know that we don't want to lose $100 or any amount of money. But from the database systems perspective, it doesn't know that it's operating on money. It just sees a bunch of bytes, and it's moving them around. So we need a way for us to reason about whether we're doing the correct thing. So the first thing we need to find out what are these operations that we're actually doing? So as I said already, a transaction is in the prize of one or more operations, but at a high level, the application could be update this, insert that, make these changes. But from the database systems perspective, it doesn't know about those high level queries. It just knows that I'm doing low level reads and writes. And so the only thing that we can reason about are the things that happen to our database. So that means that if our transaction involves additional steps, or procedures, or operations that aren't reads and writes on the database, this is outside our purview. This is outside our control. And we can't do anything about it. So to give an example, let's say that, again, I take $100 out of my account. I put it in your account. And then I send an email to you to say the transfer succeeded. And we want that to happen in the transaction. But then before I can go commit and save my changes, there's a crash. So I've sent the email, but then I crash before I can save all the changes. That email is going out on the network because it's outside the database now, and it's going out in the real world. We can't retract that. So we can only reason about and roll back and persist things that are these low level reads and writes to our database. If we make a call to an outside system, or whatever, that's beyond us. No system can handle that, at least from what we're talking about here so far. OK? So the database that we're going to be worried about today is going to be defined as a fixed set of arbitrary data objects that are each going to have a label or a name. So in this case here, we're going to use ABCD, or we're just going to use alphabet characters. So the two things to point out here are, one, I'm not defining what a database object is. It could be an attribute. It could be a tuple. It could be a page. It could be a table. It could be a database. It doesn't matter. All the same things that we'll talk about today and for the next couple of classes, they all still work at different granularities. In practice, most of the time it's going to be based on a tuple. But we'll see in some cases you can take locks. You can try to protect databases and tables. Nobody actually tries to protect single fields. That becomes too expensive. The other thing to point out too is that I'm saying the database is a fixed size. So that means that the only operations we're going to do are reads and writes, reads or updates of existing things. We're not going to talk about inserts today. We're not going to talk about deletes. The database always has the same number of things. Because that's going to complicate things. And we'll cover that on Monday next week. So for today, just assume that we always have the same number of objects. And so now what the database is going to see is just the sequence of read and write operations on these named objects up above. So we're going to say the function r for a read and the function w for a write. So this is the only thing that we can see in our database system. We can't see anything else, any program logic that the application may be running for the transaction. And that's going to limit the amount of parallelism we'll be able to get. Because we don't understand some kind of high level meaning of what the transaction is actually trying to do. We'll see one case where if you do know this, you can get better parallelism. But in practice, nobody does this. And we'll get to that later. So now from a practical standpoint, how do you actually implement or use transactions in applications and database systems today? So in the SQL standard, you have these extra keywords begin, commit, and abort. Some systems use rollback instead of abort. I think PostgreSQL and my SQL support both. So we're going to explicitly start a new transaction with the begin keyword. And then what happens is any queries we then execute are a part of that transaction. And then they either call I want to commit or abort. So if the user says I want to commit, then two things can happen. Either the transaction does commit, the database saves all the changes that you made, and returns back an acknowledgment to say that they're successful. Or the database system can say, you can't actually commit. I'm not going to let you make those changes. And I'm going to go ahead and shoot you and abort you and you have to roll back. And you get a notification that your transaction failed. So just because the application calls commit, doesn't mean you're actually going to commit. Again, that's a very important concept that we can rely on later on. If the transaction gets aborted, then any changes that we made since we called begin will get rolled back. And it will appear as if the transaction never ran at all. So that's how we guarantee if I'm moving $100 out of my account to your account, if the thing fails before we put the money in your account, the transaction gets aborted, come back. And then we go back to the state we were before we started our transaction. This is how we guarantee that there's no partial transactions. Yes? Why would we want to tell the begin us to abort? This question is, why would we want to tell the database that you want to abort? So a lot of times there's application code where you say take, for example, take the money out of my account. I'm transferring money. So I go look at my account first. I read that. Do I have $100? Yes. Now I take $100 out of my account. But then I'll go read your account and your bank's, your account's been flagged broad. So now I want to abort and roll that back, right? The simple reason. I don't know how often that occurs. I would say, I mean, most code I want to commit and they want to go to commit, right? But we have to be able to support this. So again, the main thing to point out here is this abort could either be self-inflicted, meaning we tell the self if we want to abort, the data system tells you you have to abort. And then if you tell the system to come back, it says you have to abort or you got aborted, then it's up for you in the application code to catch that. You get like an exception back and says, you know, your transaction failed, and it'll suggest that you retry it. You have to go back in the application code if you actually care about this and retry again, right? So the correctest criteria we're going to use now for this lecture and going forward through the rest of the semester is going to be defined in terms of this acid acronym. So acid stands for admissivity, consistency, isolation, and durability. So admissivity is what we already talked about, where we say all the operations of a transaction have to occur or none of them occur, right? No partial transactions. Consistency is sort of a weird one. I'll briefly talk about it, but it's very hand-wrapped how it actually means, at least for a single node database system. So it just says that if the transaction is consistent, it's like doorbell, sorry. Ah, fuck it. If the transaction is consistent and the database system is consistent, then when the transaction executes, then the database and state will be consistent. So now you're like, what does consistent mean? Well, at a high level, it means correctness. But then what does that mean? So we'll cover this in a few more slides. This one, again, as it was originally defined by the guy that invented this, this acronym, this one was always a really hand-wrapped one. Some people feel like he sort of forced this one in here in order to get the acronym to work out. The other thing, too, is that the database lore is that he made this thing up to make fun of his wife. Because his wife didn't like candy, or she was like a bitter woman or something. So he named it after her. I don't know whether that was true. He's German, so maybe. But there's another one called base, which is for distributed systems or no-SQL systems. And we'll cover that in a few more lectures. So there's asset is what we'll care about here. Base we'll cover later. Isolation is another important one. That means that when our transaction executes, it should have the illusion that it's running by itself, even though other transactions may be running at the same time. And the data system will provide that illusion for it. And then durability is where, if our transaction commits, and all our changes get saved, and we get back at an acknowledgement that our transaction committed, then no matter what happens to the database, whether the machine crashes, the OS crashes, machine catches on fire, then all our changes should be persisted. If we should always be able to come back and see our changes. Our changes may get overwritten, that's okay. But for at least for our transaction, we know that all those changes got persisted. So another sort of shorthand way of looking at these things is you would say, adivicity just means all or nothing, no parts of transactions. Consistency means it looks correct to me, and correct will be in quotes. Isolation means you're running as if you're alone, and then durability means that you're gonna survive all failures. So for today's class, we're gonna go through each of these one by one and describe it at a high level what it means to determine whether we are achieving the asset guarantee or the given property of each letter. We're gonna mostly focus on adivicity and isolation. I'll briefly talk about consistency here. It doesn't really make that much sense for a single node system. It matters more for distributed systems. And then for durability, we're also not really gonna talk about it too much because we'll spend that whole two lectures after I come back on checkpoints and logging because that's how they're gonna achieve that. Okay? And I'll say also too, asset is what you would get in a, if a relational data management system says they support transactions, this is typically what we mean. The NoSQL systems that don't do transactions, they're typically going to sacrifice often adivicity, isolation, in case it's actually, some of them do get rid of everything, but we'll take that offline. All right, let's talk about adivicity. So as I said already, there are two outcomes of our transaction. Either it commits and all our changes get applied to the database all at once, or it gets aborted because of some, either data says so or application says so. So again, what we're providing, the guarantee we're providing to our application is that any transaction that we execute, all the changes will be atomic, meaning that all appears if they happen exactly at the same time. So again, it just means that either everything happens or none of it happens. So no matter what happens, if I say I commit, then I know everything got saved. So let's look at two scenarios where we could have problems with adivicity and then we'll see how we actually wanna solve it. So again, my beloved example of taking $100 out of my account and putting it to another account, but then we take the money out of my account, but then the transaction gets aborted. The machine doesn't crash, the data system doesn't crash, we just get aborted. The second scenario is when you can take the $100 out, but now there's a power failure and everything that the data system is running is lost. We come back and what should be the correct state of the database? Right? So there's two ways we could possibly handle this. The most common approach is to do logging. So when I say logging, I don't mean like the log debug messages you're using for your projects, right? I mean something like write ahead logging where we're actually recording our file on disk. Here's all just that we're making. So what'll happen is the data system is gonna run and as it runs a transaction, for every change I make to the database, every update or write I do to the database, I'm gonna make a copy of what the old value was that I'm overwriting. And then that way if I crash or my transaction gets aborted, I had the old value sitting around and I can go back and put it back in place. So that when my transaction gets cleaned up after an abort, all the original values were still there. And so the way this is gonna work is that we're gonna maintain these undue records both in memory and on disk. And that way again, we crash while if the transaction gets aborted while we're running, then if it's in memory, we just go reverse things real quickly. But if stuff gets written to disk and then we crash, then we have our log records on disk that we can then load back in when we turn the database system on and reconcile, put us back in the correct state. So at a high level, you can sort of think of the log as the black box in an airplane. Like if there's a major, any airplane crash is a major crash. But if an airplane crashes, the government goes and looks at the black box, because that's gonna record information about what actually is, what happened in the plane at the moment that it crashed. And then it tries to figure out what was the error, what was the malfunction. Now in the airplane case, they can't put the airplane back together. In the database case, we can put it back together. That's what we're gonna use that for. So logging at a high level will be used by almost every single data system that's out there. Any database system that says that they're adorable at the disk, chances are they're using logging. So in addition to having the ability to roll back things and guarantee animicity, logging is gonna provide us additional benefits in terms of both performance and high level concepts or high level criteria we may have for application or organization. So it's gonna turn out that when we start talking about logging, since disks are expensive to write to, we can turn random writes into sequential writes through a log, right? And that'll make the system run faster. And then for other applications, the log's actually going to be an audit trail, every single thing your application did, and then you can use that to figure out what was happening if you ever have an audit or have to have questions about, my application did this at this time because then there was a breach, what data got read or what data got written. So in a lot of financial companies, they have to maintain the log that data systems generate for the last seven years because of a government regulation. So this is a good example where they can use the log for animicity, but also get additional benefit from it. So the other approach to guarantee animicity that's less common is called shadow paging. And this is actually the example that I mentioned in the beginning of the class where I said for every single transaction, I'm gonna make a copy of the database file on disk. All my changes go to that copy and then when my transaction commits, I just swing a pointer and say, this is now the master version. So that's essentially what shadow paging is. But instead of copying the single file every single time, they'll just copy the individual pages that the transaction modifies when it runs. And then when the transaction commits, again, you swing a pointer and say, all right, all of these shadow copy pages are now the master copy pages. So this is one of the oldest ideas in database systems. This was invented by IBM in the 1970s in System R. This turns out to be super slow and problematic for managing data on disk. And when IBM went to go build DB2, which is the second relational data system they built after System R, they didn't do any of this. They went with the logging approach. Because you end up with fragmentation, you end up with unordered data sets and it gets slower. So as far as I know today, the only two database systems that actually do this shadow paging approach is CouchDB and LMDB. They say it's for performance reasons, it's not that common. Everyone else is gonna do logging. So this is, question? Yes. Oh, this. This one? Keep going. This one. There are two scenarios, but I didn't see the difference between why they're... So for this one, so this one is, it's the same operation. Take money around account, put it in your account. This is like we get aborted. Like the user says abort my transaction. Everything's still in memory. How do I roll that back? This is like a hard crash. How do I come back from that? And so the point I was trying to make here was the log information is gonna reside both in memory and eventually also get written out to disk because if it's in memory, then I can quickly go get it and flip back the old back if I abort. If I do a hard crash, if it's on disk, then I can reverse things potentially when I load the system back up. Because again, after a hard crash, all the contents that are buffer for are gone and we need to figure out what was happening at the system at the time at the crash to put it back in the correct state. Yes. So this question is, does this require writing the disk for each transaction? Yes. If you care about this, if you care about not losing data, yes, we'll cover that later. LMDB. This question is, why would you ever actually want to do this? It doesn't work, right? So we, a few years ago, one of my first PhD students, he and I started building a new system using the new Intel non-volta memory devices. And we thought at the time that with really fast storage, like non-volta memory is almost as fast as DRAM, with really fast storage to do random access, that shadowpaging would actually turn out to be a better approach, like taking an old idea from the 70s and running it on today's hardware. It doesn't work. Right-hand logging is always me faster because you can do these sequential writes. You can batch a bunch of things together and then shove them on all the disk at once. So shadowpaging, it's all this fragmentation. You're copying things every single time. It becomes very expensive. We'll see multi-version current control, which is sort of like this, but instead of copying an entire page before I make a change, I maybe just copy a tuple or a subset of the tuple. So shadowpaging is sort of how multi-version current control works, but it's shadowpaging as defined by IBM as nobody does except for these guys. Yes? So the first approach is it not possible that you fail after you do an action but before you actually log in? To log in stuff? Yeah, so I don't spend too much time writing out the disk, but the question is, is it the case that if I do a bunch of changes, I create some undue records that are in memory, but then I crash before it's written out the disk? Is that a problem? No, because when I come back, all my memory's gone and therefore I'm going to load the database back up based on how it was on disk, and so because those changes never got persisted to disk, they're as if they never happened. So his question, which is a good point, is that, does this mean I have to do, if I want to say my transaction is committed, do I have to do an S-link? Do I have to do a flush? Every single time my transaction commits. The answer is yes, but you don't really do it on every single commit. You batch a bunch together and then do a group commit when you flush them out all together. And that amortizes the F-sync cost over time. But if you want to guarantee that your data is actually durable, you have to write the disk. And so, but the tricky thing is going to be, in what order you write the disk, it's going to matter a lot too. So you have to make sure you write the log record that corresponded to a change to a data page first before you write the data page to disk. We'll send a whole day on this as well. And at the point I was checking, I made about like, oh, well, the NoSQL guys don't always provide acid. Some of them would actually not even flush the disk. If they had transactions, they would not flush the disk exactly when you say complete my transaction. They would sort of do it every, I don't know, 60 seconds. So that means you could crash and lose the last 60 seconds of data. Some systems are even worse than this. Or just say it straight up, Mongo, right? The early version of Mongo was when you do it right, it would immediately come back and say, yeah, I got your right. But it didn't actually need to do the right. At the network layer, I said, yeah, I got it. And if you wanted to make sure that your right actually occurred, you had to come back a second time and say, did you actually do that? And that was the default for them for like four or five years. And their early benchmark numbers were amazing because they would do these writes. And of course it's like, yeah, yeah, I did it, no problem. But it didn't actually do it. Mongo fixed that, it's not the default anymore. Okay, so any questions about atomicity? Again, we'll cover how we actually guarantee this in a second. So consistency, as I said before, is this nebulous term about correctness of the database. So at a high level of the way to think about this, what a database actually is, is trying to model some concept or aspect of the real world. Like my database for my bank is trying to model the old days of a bank where somebody would sit in a ledger and record how much money you actually had in your account. It's modeling some process in the real world. So we're gonna say that if we have our database be logically correct, meaning we don't care how it's actually physically stored, but the data integrity, the referential integrity, all those things are correct, then any questions we ask about that our database will produce correct results. And again, that sounds very vague, so let me go into more detail. There's two types of consistency we can possibly have. We have database consistency and transaction consistency. The spoiler would be database consistency is the one we actually care about. We can't do the second one and we'll see why in a second. So again, our correctness criteria is that our database actually reflects what the real world looks like. And so how do we actually enforce that? Well, we provide the data system with integrity constraints to say this is what it means for us to have correct data. So for example, if I have a table of people or students and I'm keeping track of their age, I can have an integrity constraint that says nobody's age could be less than zero. There's no negative ages. And so the data system could enforce that and sometimes insert somebody with a negative age and say, you can't have that in the real world. I can't let you insert that data. The other way to think about it too also is that the... So now, in addition to the integrity constraints, now as transactions start making changes to the database that any transaction that executes in the future should be able to see the changes, the correct changes that a transaction in the past made. So what does that mean? So if I have a transaction, say, I run a transaction right now and I make some change to the database, if you now run a transaction one minute later, as long as nobody has overwritten my changes, you should be able to see my updates. So in a single node database, this is not that big of a deal, right? So if my transaction commits, I get back the acknowledgement that I committed, then you come along and now do another transaction on that same machine and you should be able to read my rights right away. So for a single node database, this is not really an issue. When this matters more is the distributed databases. So now if I'm trying to guarantee strong consistency in my distributed database, if I do a write and I update some account and then you come one millisecond later on another machine, for the same logical database, but on a separate physical machine and you start, you now do a read, you should be able to see my change. If I told the outside world that my transaction committed, right? So this will matter more than for the distributed databases because the NoSQL guys will have this thing called a ventricle consistency where I'll say, I'll propagate changes eventually and not guarantee that everyone sees the exact same state of the database at the exact same time. But for our purposes today, a single node database, it doesn't really make sense. It won't be an issue. So the other type of consistency is transactional consistency. And this one again is very hand-wavy, but it basically says that if the database is consistent before transaction runs and our transaction is consistent, then after we run our transaction, the end state of the database should be consistent. So what does it mean to be consistent or correct? That's a higher level concept that we can't reason about in our database, right? We can try to enforce some integrity constraints and we prevent the transaction from doing, making some changes. But if my application says there should be no customer over the account that has at cmu.edu email address and my transaction goes ahead and actually tries to do that, I can't stop that in my database. That's not a good example because I, you know, it's, let me phrase that. Let's say there's, the application says that nobody taking this class is allowed to have an account on this one system. But my database doesn't have access to whether you're enrolled in this class or not. So the transaction is allowed to go ahead and do that. And the data says, okay, sure, we want to do this answer, I'm allowed to do that. But that's the higher level concept, this higher level constraint that the data doesn't know anything about. So therefore the transaction is acting consistent and therefore we can't stop that. So again, this is something that we can't, simply just can't do in our database system. We can enforce integrity constraints, the referential integrity constraints, we can't enforce these high level things because we just don't know. Because it's a human value judgment that we can't codify in our system. So there's nothing really else to say about this. So like, if you understand the high level what I'm talking about, then that's it, right? That's all that matters, okay? All right, so the one, the other one we care about today is also isolation. So isolation again is saying that if our transaction, if we have our users submitting a bunch of transactions, we want each of them to run assuming that they're running by themselves. And the reason why we want to provide this guarantee is that it makes it way easier to program our application or our logic in our transactions if that's the case. We assume that we have exclusive access to the database. We don't have to worry about any intermediate data we could be reading from other transactions. Then we just write our single-threaded code and that's fine. It makes our life easier. So we can achieve this by doing, again, my straw man approach in the beginning where I just have a single-threaded extra transaction one by one, but I said that we want to be able to interleave transactions to achieve better parallelism and concurrency. And so if you want to be able to guarantee this illusion of property, but we still want to interleave this, that becomes difficult. And so the way we're going to provide this, the way we're going to do this is through a concurrent show protocol. So we've already talked about concurrent show protocol slightly when we talked about index latching, right? We're going to have a single data structure and allow multiple threads to access it at the same time and we use our latches to enforce the correctness of our data structure. So now we're going to do the same thing, but for our database objects. And this is why I was making the distinguish between locks and latches. So latches are protecting the internals of the data structure. Locks are going to protect these database objects. So you can think of a concurrent show call as like the traffic cop for the database system, right? It's sitting saying, okay, we can let this operation go. This operation has to wait or this operation has to abort. It's trying to figure out how to interleave things in a way that we end up with a correct state. So there's two categories of protocols that we're going to care about. And then this will cover on next week, right? It'll be both of these. So the first one is a pessimistic protocol where we're going to assume that our transactions are going to conflict to cause problems. So we require them to acquire locks before they're allowed to do anything, right? You assume that, you know, you're pessimistically assume that there's going to be problems, so you make sure that there's, you make sure that things go in the correct order by using locks. Optimistic occurred to show is where you assume that the conflicts are rare. Most of the time, my transactions aren't going to conflict. So rather than making them stall and acquire locks at the very beginning, I just let them run and do whatever they want. And then when they go to commit, go back and figure out whether that was actually the right thing to do, whether there was a conflict. So Monday's class next week will be on two-phase locking. That's a pessimistic protocol. Wednesday's class next week will be on timestamp ordering. That's considered an optimistic protocol. An optimistic occurred to go protocol was actually invented here at CMU in the 1980s. All right. So let's look now at some real examples and start understanding what it actually meant to have conflicts. So again, this is my bank account example where we have two accounts, A and B. It's Andy and his bookie. And so we're going to transfer $100 out of my account into my bookies account. But then at the same time, the bank's going to run an transaction where it's going to update the monthly interest of all the bank accounts. So we're going to update every account with an add 6% interest. So transaction one is take $100 out of A, put $100 in B, and then transaction two is just computing, incrementing both of the accounts by adding 6%. So we assume that, again, both bank accounts have $1,000. And we want to execute these two transactions. What are the possible outcomes we could have for the state of the database? Assuming we have arbitrary interleavings. Well, many, right? Because we could have T1 maybe run one query and then switch over to T2, then back and forth, right? There's a bunch of different ways we could end up with these interleavings. But the important thing to point out though is that at the end of the day, after we execute transaction T1 and T2, in any possible order, to know that our database state is correct, the final result when we add both the accounts together should be $2120. Because I have $1,000 in A and $1,000 in B, add that together, that's $2,000. And then the second transaction wants to add 6% interest. So we want to guarantee that no matter how we order or interleave our operations, we always at the end, after executing T1 and T2, we end up with $2120. So now this is a very important property about transactions and database systems. That's gonna be slightly different than maybe how you know or have experienced parallel programming before. So in a database system that we're talking about here, even though T1 may be submitted to the data system first, followed by T2, there's no guarantee the database system is gonna run T1 first, right? And the reason why we're gonna do this is because we can have any possible interleaving or any possible ordering, then this is gonna allow us to open up more opportunities to do interleavings to get better parallelism. If I cared, my application absolutely had to care, say, well, T1 absolutely has to execute first, then followed by T2, the way you would write that code is you would submit T1 and then only when you get back to the acknowledgement that T1 committed, then you submit T2, because you can't guarantee that. Now in practice, if you submit T1 and it takes a minute, then you submit T2, that's basically the same thing. But if I submit them at exactly the same time, then the data system could interleave them in any way it wants. But what we're gonna care about though is that for any arbitrary interleaving, we want the end state of the database to be equivalent to one where we actually executed these transactions in serial order with a single thread. Either T1 followed by T2 or T2 followed by T1. The end state of the database has to look like that. So now that means that the number of possible outcomes we could have are, for the state of A and B, could be different, right? So if I have, say, T1 go first followed by T2, I'll have $9.54 in A and $11.66 in B, but when I go in the other order, I'll have $9.60 and $11.60. But again, if I add both of these together, I always get $21.20. And that's equivalent to one where they executed in serial order. So this clear. All right, so let's actually look at what the database sees. So for this, this is called a schedule for our transactions. And the way to read this is that going from the top to the bottom for time, and then for each of these columns here, we have the transactions, and we have the operations that they're actually doing. So I call begin on T1, I take $100 out of A, take put $100 in B, and then I call commit. And then now, next in time, I do a context switch over here, and now I call T1, and it can use the interest in these guys. So for this, assume that we only have a single thread with a single program counter, and we can only execute one operation at a time. We can interleave them with these different transactions, but at any given time set, we can only do one thing. So in this case here, if we execute T1 followed by T2, we end up with this, this amounts for A and B. If we execute T2 first followed by T1, we end with these amounts here. So again, A doesn't match, A is 954 over here and 960 over here. So they're technically different from a finite exact amount, but from a database assistance perspective, for what we're caring about with transactions, you add them both up and you always get 21, 20. So both of these interleavings, both of these orderings are still equivalent to each other. They're correct. Yes? Is that ordering? Is it going to be able to do the A and B? So I think your question is, if you know that you have these two transactions exactly the same time, could you do it where you combine them? Yes, but nobody does it that way. And I think that would complicate things right now. Let's just assume that this is the case. Also say too, what I'm showing here are like, yeah, here's why you can't really do that. So what I'm going to talk about here today are the schedule is fixed, meaning I know ahead of time exactly what all the transactions actually want to do. In a real system, it's not like that. In a real system, you have transactions are showing up, they're calling, a client opens the connection, calls begin, and then it starts executing a bunch of queries and you don't know what the next query is. In this case here, to reason about correctness, you see everything all at once, right? So on Monday, when we talk about two-phase locking, that's a dynamic concurrency protocol where you don't know what the queries are going to be ahead of time. Now, there's some cases where if you have some introspection about what the application is actually trying to do, then you can actually do what you propose, but that's hard and nobody actually does that. So for each of those operations, is the client essentially calling get a compute a minus 100, decided to a minus 100? Yeah, we'll get to that. So his question is correct. I said before, the data system only sees reason rights, what are the levels of this A equals A minus one, right? Yes, that will get translated to a read followed by a right. I'll see that in a sec. Yes. Yes. Does that mean that like, since these can be interleaves, somehow like you imagine that like in T2, T2 has to have its own separate copy of what A was when T2 started and T1 has to have its own separate copy of what A was when T2. Yeah, we'll get to that. So his question is, so this A equals A minus 100, what is that actually gonna look like? Well, in the program logic, I would say, do a get on A, do a read on A, have a copy of my local variable, then I can manipulate it, and then write it back to the database. So each of these two transactions would have their own local variables that aren't shared. So we can't get everything. His question is, can you interleave the operation between transactions? Yes, we'll get there in a sec. Yes. If T1 and T2 are touching the completely different troubles in completely different database, then does the database still need to serialize those two? The question is, if I have two transactions that are touching completely different objects, not tuples, objects, do I need to still serialize this? I mean, so for this one, I'm just trying to show equivalency, if they touch completely different things and there's no conflicts, then you can interleave them any way you want. Absolutely, yes. The question is, how do I know whether another transaction is touching the same thing I'm touching? Again, this is a high-level example. The database sees and reads and writes. So I do a read on object A, you do a read on object A, in order for me to serve your read request, it's like it asks me to read it for you. So I see everything. But I don't see high-level things like, I don't see that you're gonna take the value of A and then add 6% to it. So again, what everyone's sort of, we're getting up to now is we have to interleave these transactions, or interleave the operations. And we've already covered this, we wanted to do this because this is slow and we have a lot of CPU cores. And so the idea here is again that, instead of having the, if we have to go to disk to get something, or wait to acquire a latch on something, we could have one transaction stall, another transaction keep on running and still make forward progress. So we're trying to figure out a schedule or interleaving such that we maximize the performance of the system and we get the best utilization of our hardware. So if we go back here now to our example, so now I'm interleaving our transactions, right? One starts, takes $100 out of A, then there's a context switch, T2 starts, put compute 6% on A, then we go back, put the $100 back on B, then we go back here and compute the engines on that, and then we go commit. So now again, now it's not one transaction running in its entirety at a time, right? We're now able to actually interleave things. And this example here, this is correct. This is fine because this is equivalent to a serial ordering of our transactions, right? The end state of the database is equivalent, right? And so the key thing to point out here is that the reason why this worked out okay, and then we end up with equivalent is that we always make sure that we did the operations on T1 first on a given object before we did that operation on T2 for that same object. So I took $100 out of A, and then I computed the interest on A, and then I put the $100 back on B, and then I computed the engines on B, right? So for this interleaving here, we violate that. So I take $100 out of A, I compute the engines on A, then I compute the engines on B, then I put the $100 back on B. So now in this case here, when I add up these two values together, I don't get 21, 20, I get 20, 14. So the bank lost $10, or $106, right? Now you can guess it's $106, but if it's a billion dollars, if it's your account, $100 is a lot, but like, this is why we wanna guarantee that we always have correctness for transactions, especially when you're doing anything that involves money. There's a famous example a few years ago where some Bitcoin exchange, I forget where in the world was running on MongoDB. MongoDB at the time didn't have transactions, and so some hacker figured out that you can have, you can manipulate the API and have it drain out everyone's account. So they wiped out the Bitcoin exchange in a single day because MongoDB wasn't doing transactions. It's part of the story, but they didn't have transactions, that's a bad idea. So again, back to his point, what is the database system actually seeing? Again, it doesn't see these high-level operations, it just sees these reads and writes. And so essentially what we're trying to do is make sure that for any object that does a read or does a write or a read on the object, if another transaction is doing the same thing, we're always going in the right order to determine whether our schedule is correct. So the way we're going to figure this out, the way we're going to find correctness for what we're talking about here today is we'll say that a schedule of any arbitrary ordering of operations is correct if it's equivalent to one of a serial schedule. So the serial schedule we've already talked about, serial schedule is just saying that we execute transactions one after another and no interleaving. And then the equivalency policy says that if the final state of the database is of the objects is equivalent, or it has actually the same values of another database state, then they are equivalent. So a ordering of a schedule could be equivalent to at least one, exactly one serial ordering, and not exactly one, one or more serial orderings if the database is still the same state. But a given schedule could be correct, it could still be serializable by being equivalent to any possible serial ordering. So this is the former property that we're going to care about for our schedules, serializability. Okay, it just says that a schedule that is equivalent to some serial execution doesn't matter which one, it has to be one of them. If that it's equivalent to that serial ordering, then whatever schedule we're looking at is considered to be serializable. And this is the gold standard of what you want to get in a database management system. This is guaranteeing, almost guaranteeing all the protections you could ever want. The only one it doesn't guarantee is that if your transaction, if T1 shows up first followed by T2, T1 will commit first. That's called strict serializability or external consistency, we don't care about that here. Most systems don't provide that. The only system that provides that that I'm aware of is Google Spanner. And they need it for some global ads thing. Systems don't do that. Those systems, if they say they support serializability, they're getting what I'm defining here. So again, if you come up with a background from parallel programming, this might seem kind of weird that I could say, all right, well I can have the, there's not one single state of the database that I can say this is what it exactly should be to determine whether it's correct. It could be any possible thing. And the reason why we want to do this is because if we have multiple choices for how we want to interleave our operations and multiple serial orderings that we get aspired to achieve, then that gives us more options to decide how we want to schedule our operations for our transactions. So to understand this bit better, we now need to form a way to determine what it means to have a conflict that could violate the serial ordering or serializability of a transaction. So what we're gonna say is that two transactions or two operations are gonna conflict if they're being run by different transactions at the same time, and they're both operating on the same object. And at least one of those operations is a right. So there's three types of anomalies we're gonna care about. We have read, write, read, and write, write. Okay, why no read, read conflicts? Yeah, it doesn't matter, who cares? Like if you read something and I read something, we read the same thing, that's fine, who cares? It's when we have rights, and when it's one of the operations as a right is when we have problems. So let's go through each of these one by one. So the first one is read, write conflicts. This is also sometimes called a repeatable read. So let's say I have two transactions, T1, T2. T1 is gonna do a read on A, and then read on A again. T2 is gonna read on A, followed by a write on A. So let's say I actually run this. T1 starts, does the read on A, gets $10 out of my bank account. Then there's a context switch. We start running T2, T2 reads on A, sees $10. That's fine, but then it writes back $19. Then it goes, heads and commits. And we get back the acknowledgement from a database system to say that our transaction committed. But then now our transaction switches back, or context switch back over to T1. T1 now does a read and it gets back 19. But the first time I read A, I got 10. So I'm trying to read the same object and I'm not getting the same value. So that's a repeatable read. If I try to read something, I'm not seeing the same thing over and over again. And again, if we're trying to guarantee isolation for our transactions, it should not see this change over here because if we're running this in serial ordering, T1 should have just run completely and then finished and then T2 would have ran. So that couldn't happen under a serial order. Next conflict is write read, also sometimes called dirty reads. So now T1 is gonna do a read on A, followed by a write on A, and then T2 is also gonna do a read on A, followed by a write on A. T1 reads A, gets these $10, then it writes back $12. Now T2 reads A and it sees the $12 that the first guy put in, right? But then now it writes back $14. Then it goes ahead and commits and then we get back to acknowledge it from the data system and say, yeah, you committed, you're good to go. But now when we do our context switch, we come back over here and now our first guy aborts. So he said there's no partial transactions. So this guy has to abort, so we need to roll back our update. But the problem was that this other transaction here already read my update and then it committed and it told the outside world that yeah, when I read A, I saw $12. But that shouldn't have happened because again, we can't, that's a temporary inconsistency. We made a change to A that we have to make the change in order to actually apply the change to the database, but nobody should have been able to see that change because our transaction didn't commit. And furthermore, because we aborted, now we leak something into the outside world that shouldn't have appeared. The last conflict is write rights and this is overwriting uncommitted data. So T1 does a write on A, puts in $10. T2 does a read, a write on A, puts in $19 without reading it. Then it updates B and puts in Andy and then over here, T1 starts running again and it writes in Beaver. So now when we go to commit, what's the issue? Well, I have two objects, A and B. And so for A, I'm seeing the write that the T2 put in there, it put in $19. But for object B, I'm seeing just in Beaver because that's what T1 put in. So I have a torn update, right? I have data that, I have two objects that have been modified by two different transactions when they both should have been modified atomically by one transaction. And this occurred because this guy got to go running while this guy was still running. And that shouldn't happen if we were trying to guarantee serial ordering. So now, given these conflicts, we need to understand more formally how to actually prove whether something's actually serializable. So in the sake of time, we're gonna skip conflict serializability, or sorry, you definitely know conflict serializability. I'm gonna skip view serializability. Conflict serializability is what you need to know for the homeworks. I'll post on Piata the link to the lecture from last class last year that talks about view serializability. It's the same material that I would have covered but we're out of time. Right, so most data systems are gonna try to give you this. If they say they support serializable execution transactions or serializable isolation level, they'll give you this. Nobody does this one. Because this requires high level information about what the application's trying to do and we can't get that automatically. All right, so now we're gonna find a new term and say that two schedules are considered to be conflict equivalent, if and only if they are involved in the same set of operations and transactions running at the same time. And then every pair of conflicting transactions are ordered in the same way. So again, conflicting transactions when they're actually trying to update turn to a read or write on an object. Two transactions, one's either a read or write and one's either a read or write. They always have to have at least one write. So let's say a schedule S is conflict serializable if it's conflict equivalent to some serial schedule. So the way we're gonna figure out how to determine whether something's conflict serializable is by just swapping the order of non-conflicting operations. The idea is that we can do these swapping steps on operations and that'll push a bunch of operations to the top for one transaction, push a bunch of operations to the bottom for another transaction until we end up with a serial order. So going back to this example here, so we do a read on A, write on A, and read on B, write on B for T1 and T2. So we have here, we wanna start swapping here. So we have a read on B and a write on A. So this case here, they're not touching the same object. So I can go ahead and swap their order. I can make read on B happen before the write on A. Same thing with the next one here. The read on B can happen before the read on A. I can swap their order, that's fine. Now I can do the same thing with this other one. The write on B can happen before the read on A, write on A, swap that. The write on B can happen before the read on A can swap, so I can do that. So now I end up with a serial ordering. It's equivalent to this one here. So this is the one where you can't do this. So in this case here, I have a write on A, followed by a write on A, I can't swap their order. So therefore, it's not equivalent to a serial ordering. This is pretty straightforward. But of course now this is kind of like stupid to do. Like what if I have a lot of transactions and I have a lot of operations, this is gonna be very expensive for me to do. So we need a better way to figure out to determine whether something is gonna be serializable or not, without having to do the swapping thing. And so the way we can do this is through a dependency graph. And the textbook I think calls this a precedence graph. So dependency graph is just gonna say, we're gonna have a node for every single transaction in our schedule, and that will have an edge between two transactions if there's some operation in one transaction conflicts with another operation in the other transaction, and the first operation occurs earlier in the schedule than the other transaction, right? And so if I look at my entire schedule and I generate my dependency graph, if I see a cycle, then I know that it's not serializable because I won't be able to swap their ordering. But if there's no cycles, then it is conflict serializable. So let's look at this example here that we have before. So in this case here, I have a write on A followed by a read on A. So the write on A conflicts with the read on A, obviously. And the write on A in T1 happens before the read on A in T2. So I'll have an edge from T1 to T2, and I label it for the object A. Same thing here, I have a write on B followed by a read on B. So I have an edge from T2 to T1 on B. And now at this point, I have a cycle, so therefore I know that this ordering is not conflict serializable. Again, just looking at the code, this is the Justin Bieber example I had before. I do a read on A followed by a write on A here, then read on A followed by a write on A, read on B followed by a write on B, then read on B followed by a write on B. So I would have the update to B occur from T1, and the update to A would occur to T2, and that's a torn update. So let's get even crazier now. We can have an arbitrary number of transactions. So in this case here, I have a read on B and a write on B. So I have an edge from T2 to T1 on B, and just keep going down the line one by one, right? And then we end up something like this. So the question is, is this considered equivalent? Yes, because we can take any arbitrary, we take the ordering T2, T1, T3, and that'll end up with the correct serial order and what we want it. So if we generate the dependency graph, just look to see whether we have a cycle that'll tell us whether we're conflict serializable or not. Okay? All right, actually we have a little more time. We keep going. Okay. Let's look, let's bring back now the operations we had before these high level things that the application may want to do. So now we still have our reason rights, but now I'm showing you what the program actually, the program logic is actually doing. So the reason rights are always going to the database, but any of these other operations like B equals B plus 10, these are happening within the transaction state. So any change I make to B is not written to the database until I actually do the right on B, all right? So in this case here, the other thing to point out too is what we're doing in this one here, we're taking $10 out of one account and then putting $10 in this account. The second transaction, I just want to compute the sum of the total amount of money that are in A and B, and then this is like this fake echo command. This is not a real thing. I'm just showing this for demonstration purposes. This is just printing out to you, returning the actual sum of the two values. So in this case here, right? We want to generate a dependency graph. We have a right on A and a read on A, so we have an edge from T1 and T2 on A, but then down here, we have a right on B and a read on B, so we have an edge going in the other direction on B, so we have a cycle, so therefore this is not conflict serializable, but there is actually a way for if we can potentially modify this application and do something different than just computing the sum this way, where we could end up with something that would still follow the same state as a serial ordering of a transaction, but it would actually not be conflict serializable because of this cycle up here. So instead of just me going reading A and then adding to my sum and reading B, what if instead of actually giving them the exact sum for both accounts, what if I just want to know what are the total number of accounts that have more than zero dollars? I'm computing a counter of the number of accounts that are greater than zero. So if I rewrote my application to say if A greater than equal to zero, add one to my counter, then in that case, when I print out my count, or even though I interleaved my transaction while T1 was still running and that money was missing, assuming that I did have exactly $10 and didn't go negative, then this thing would actually still produce the correct result. So what's up with that? That's kind of weird, right? I said conflict serializability says that I need to end up at the same state of the database or the same result as if I was running a serial ordering, but in this case here, I'm not conflict serializable, but I am still getting the same result on the same state of the database as if I was running a serial ordering. So this is what view serializability is. And again, the spoiler is that nobody actually does this because this requires us to have some reasoning about what the hell is the application and transaction that you're trying to do here to know whether it's okay for me to interleave them by plopping these guys in the middle of these other ones here. So this is what I was saying, nobody actually does this, but it's actually a really interesting concept and eventually, maybe 50 years from now, people have better programming models and programming application frameworks where we could actually do view serializability, but it doesn't exist today, it's only theoretical. So let's look at another example here. So we have three transactions running, T1 was to a read on A followed by a write on A and then T2 and T3 are doing what are called blind writes on A. So we're writing to A without actually reading it first which we could do. So again, if I just go through and generate my dependency graph, we're gonna generate a bunch of edges and we have a cycle, so we therefore we know that it's not conflict serializable. But if I just actually look at the transaction a little bit, well I see, well, assuming that these are the only transactions that are running at this right now, which we said was the case, T1 does a read on A followed by a write on A, T2 does a write on A, but in the end of the day, the end state of the database, the only thing that matters is this last write on T3. So this ordering here is actually view equivalent to one where I executed them in this order like this. So as long as T3 is the last write that gets persisted to the data is A, who cares how these other ones up here got interleaved? But I need to know that in my application it's okay for this thing to be the last writer. And as I said, next class when we start talking about two-phase locking, transactions are showing up in arbitrary orderings at different times during execution, and you don't know exactly what they're gonna be doing ahead of time, so you can't guarantee this. So just real quick, let's skip all this. The way to think about these schedules that we talked about is that you have this universe of all possible orderings you could have for any transaction in your application. That's this giant space here. Any possible ordering, but you're not even considering what it means to be correct or not. Then you have a smaller portion here of serial ordering. These are ones where executing transactions one after another. And then around that would be conflict serializable, where again, these are some interleavings that may not be serial. It includes all serial orderings, but it includes the ones that aren't. And then around that will be the view serializable orderings, yes? In the previous example? Yes. Going back. Why actually did you just consider about the last write in deeply and not about the order in human and people? Because again, so say, this is what I'm given. And I wanna say, run these transactions. Again, I'm not worried about a transaction showing up arbitrarily. I'm saying, this is what I have, I wanna run this right now. What's the end, there's only one object in the database. So the only thing I'm gonna see after I run these transactions is what's the value of A? And so who cares that T1 and T2 wrote A because T3 is gonna overwrite it? So at the end of the day, all that matters is whatever this guy wrote, who cares what these guys actually did? So this won't work with the first example that you gave me? Correct, this will not work in the first example. What is the first example? Money getting deducted. Yeah, taking money out of one account, put in another account. And at the same time, the... Computing the interest, yes. This is just meant to illustrate the concept. Okay, so I'm gonna skip all this. For transaction durability, we've already covered this. That's the logging stuff. Again, we'll spend a whole week on this. The asset properties we've already covered, add-insistency, and isolation. Today, we mostly focus on these two. We'll cover this in more distributed databases. We'll cover this for logging and checkpoints. But for next week, we're gonna focus on these two further. So the last thing I just sort of say, too, is that... Concurrential is hard, it's hard to get correct, hard to get to perform well. This is why a lot of newer database systems that come along, except for more recently, they initially did not support transactions because they said they wanna get best performance and they didn't worry about running transactions. They wanted to make sure the system was always available and they didn't wanna burden themselves with transactions. And so the thing I always like to point out, though, is Google in 2004 or 2005, they were sort of the harbinger or the vanguard of the new SQL system. They put out this thing called Bigtable. They said joins are slow, SQL slow, transactions are slow, we're not gonna do any of that. And then, like, seven or eight years later, after everyone's sort of copied what Google had done, like Asandra, Mongo, a bunch of other key value stores, HBase, they came up with this paper called on Spanner, which is their transactional database system that they use internally. And there's this great line in the paper that says that for their programmers, they think it's better to provide an abstraction or programming model that uses transactions because that's easier for them than the reason about the correctness of the program and whether they're actually doing the right operations in the right order. It's better for to have the unwashed message, your average, you know, Rando, JavaScript program or whatever they're using. You have them all programmed to using the transaction programming model. And then instead, you have a bunch of really smart people that can do the systems development, like Jeff Dean. Their job is to worry about how to make those transactions go faster. So transactions are super important. Every system that they didn't have them before is now trying to start to add them. Because again, it provides all these nice guarantees that you wanna have in your system, okay? Again, I'm rushing, I apologize, but I quickly wanna get through project three real quickly. All right, so project three is out. What you're building is the query execution engine for bus tub. So what you're gonna end up implementing is the executors for the actual query plan operators. So say you have a query plan like this, then for each of these operators, you're gonna generate this executor that's gonna follow the iterator model that we talked about, where they all have a next function that shoves up, you know, you call next on the executor and it gives you back either the next tuple that it has or no pointer to say that it's done, okay? So you guys are doing, it's gonna be single-thread execution, no exchange operators, and you don't have to worry about transactions, you don't have to worry about doing updates or deletes. So what do you have to build? So the first thing we're asking you to do is build out the catalog. There's a lot of you to install tables into the database and then go back and get those tables back from the catalog using the name or the internal identifier. And then you're building executors for insert, sequential scans, hash joins, and hash aggregation. For the hash join, you can implement it first using a memory hash table that will provide you, but the ultimate goal is that you wanna use your linear probe hash table that you built from project two, because that'll allow you to do joins on tables that don't fit in disk. So you can do the first two tasks without having to have a working linear probe hash table, but the last one will require you to have that one working. So, implement the catalog in certain executor first because obviously you can't do sequential scans unless you have data in your database. You don't need to worry about any transactions and then when I posted this on Piazza and I'll just emphasize this again, grade scope is not meant to be for debugging. If you submit it and it takes a half an hour before it starts running, we can't fix that. That's up to grade scope and there's a queue of other students with 100 students in the class, you're not gonna run right away. So we provide you some basic tests as a framework to figure out how to write more tests, but you should be doing as much as all the development you want to figure out what your problem should be done locally. And then if you find your thing timing out because it's running too slow, you should figure out why your system's running slow locally. Don't make a bunch of minor changes and keep submitting them to grade scope and try to measure how long grade scope takes. You can figure these things out locally. So we've already covered this. Don't change any file and then once we give you, you wanna rebase over the bus stop master, there's instruction on Piazza how to do this and then come to office hours if you have questions. I always have to say this, don't plagiarize, we will destroy you, because what'll happen is if you do plagiarize or you copy from other people, the university comes back to me and say, hey, did you tell your students not to plagiarize? And I showed them the video and it's like, yes, here's me telling you not to plagiarize. Don't plagiarize, and that's evidence against you, you're screwed, okay? All right, next class, again, I will be gone. Yes, it'll be gone, it's not gonna be good, right? We'll recover two days locking and that'll build upon what we talked about today, okay? All right guys, enjoy your weekend. Oh yeah, coming through with my shallon crew, two cent for a case, give me St. I's crew, in the midst of broken bottles and crushed up cans, met the cows in the jam, oh, I'll drive. He's with St. I's in my system, crack another unblessed, let's go get the next one, then get over, the object is to stay sober, lay on the sofa, better hit down my show, follow me, Tim, stretch out, could never be sun, Rick and say jelly, hit the deli for a part one, naturally blessed, yes, my rap is like a laser beam, the pawns in the bushes, St. I's been like a team. Crack the bottle of the St. I, sip it through those, you don't realize, you're drinking it only to be drunk, you can't drive, keep my people still alive, and if the St. don't know you're from a can of pain, pain.