 So as hopefully you've noticed, I am not Andy. Andy and KB welcomed their first child last week. Everyone's doing well. And we're extremely thrilled to see the Andy Pavlo parenting experiment. So the clicker stopped working. So last class, Andy talked a lot about acid properties of transactions, focusing a lot on isolation in particular. Reasoning about whether schedules are serializable, and we talked about conflict serializability and view serializability. And with conflict serializability, we were able to verify whether a schedule was serializable based on being able to swap operations around or looking at the dependency graph and being able to say, yes, this schedule is serializable or whether it would violate the serializability guarantee. And then there was this other notion of view serializability, which most applications are actually no systems really support, because it's this concept of being able to produce results to the application that look right to the application, but actually aren't serializable as we've defined them. Or it wouldn't be conflict serializable. So I think he gave a couple examples. One would be like counting all the number of accounts that had a positive balance while he was shuffling money around between the accounts. And because the accounts never dropped negative, we got the correct result. But it wouldn't technically be a conflict serializable schedule. I think another one was just doing blind rights into accounts. And because the last right that we cared about was the one that got executed, it counts as view serializable, but it's not actually conflict serializable. So there was a lot of time spent on like, how do you verify these schedules? But what now we want to care about is how do you do this stuff in real time? Because he's got an example here. So an example of this is not conflict serializable because we have this right on A and then another read on A. Unrepeatable read, this is not conflict serializable. So that's basically what Andy talked about in the last lecture. I'm going to try to make up for time here because we're starting a little late. So as I was saying, we need a way to do this on the fly in a real system. We don't get the full schedules of transactions when they're submitted. When clients connect to the database and they start a transaction, we don't know what the queries are going to be a priori. So we can't look at what the client's going to do and predict whether their operations are going to be serializable or not. So we need a protocol, we need a system that's going to allow us to execute these transactions in parallel because we want parallelism in the system, but we still need to guarantee isolation between these transactions. So I think Andy briefly mentioned this idea of there are pessimistic approaches and optimistic approaches. Today we're going to look at two-phase locking. We're going to use locks in the system. This is a pessimistic approach because basically you're going to have to ask for permission from the system before every operation. So before you can do a read or a write on a tuple, you're going to have to ask for a lock on that object. We'll get to optimistic stuff in the next couple of lectures, but today it's all about pessimism with locks. So this is just a high level what I was just describing. An example of before we can do a read on a, we're going to go to a lock manager and say, I want a lock on a. This lock manager is sort of your central authority on whether you're allowed to access a tuple or not. It maintains metadata about who holds locks and enforces the lock protocol in the system. So T1 is going to begin. It's going to request a lock from the lock manager on a. It gets the lock as no one else holds it. T2 begins, let's imagine we're in a single-threaded environment, so we're only executing one of these transactions or we're only making progress on one of these transactions at a time. So T1 begins, gets the lock on a. We switch to T2, it begins. It wants the lock on a because it's eventually going to do a read and a write down here. And it's denied by the lock manager because T1 already holds that lock. The only thing T2 can do here is stall. What the behavior is is kind of up to the implementation. But there's going to have to be some sort of stall because you can't get the lock that you want. T1 proceeds, does its right, does its read, releases its lock. The lock manager is now going to basically tell T2, you can get the lock that you want on a. It's going to grant it. T2 is going to do what it needs to do. And then it's going to release the lock. So conceptually, that's how we're going to use locking in the system. There's a little bit more nuance to it than that. And we'll go through that through the rest of the lecture. Makes sense? We're good? Cool. So today we're going to look at the lock types first. We're going to start with two basic lock types. And then we're going to get into our first proper concurrency control protocol, two phase locking. We're going to talk about how we reason about deadlocks and how we're going to solve that problem. And then we're going to introduce the notion of hierarchical locking, which is going to allow us to take fewer locks in the system to be a little bit more efficient about how we use locks. And then the last thing that actually is a mistake. We're not going to get to isolation levels today. He didn't make it last year. And they're not in these slides anymore. So we'll get to isolation levels. I think we'll handle them in the next lecture. So I think you've seen this slide before. Andy presented this. This is from work by Goetz Greifah. He showed it when you were talking about protecting concurrent data structures with latches. So I think concurrent B plus tree indexes, hash indexes, things like that. And I'm pretty sure this slide exists just to get Dave Anderson to stop making fun of us because he says database people are weird for calling locks latches. But they are distinct terms in our world. Before Andy was talking about, like I said, latches to protect threads from trashing the memory of your concurrent data structures, today we're talking about locks, which are protecting logical constructs in the database. So things like databases, tables, tuples, they're designed to protect transactions from conflicting with each other. And probably the key distinguishing part is that with latches, we avoided deadlocks with programming discipline. We were writing the logic on how these latches were acquired with things like latch-crabbing in the B plus tree, how we could reason about what the operations were doing. So we knew how to safely acquire latches in one order and then make sure we respected that order and release them in the correct order as well to avoid deadlocks. With locks, we can't do that because we don't know what the transactions are going to do, what queries they're going to run. So we need to be able to handle the clients issuing queries to the system that would result in a deadlock and we need to talk about what are we going to do then. Because it's pretty simple to, as we'll show in a demo a little bit later in the lecture, it's pretty easy to put the system into a deadlock. So like I said, we'll start with two basic lock types. We have shared locks and exclusive locks. Shared locks are used for reads. Exclusive locks are used for writes. These are kind of similar to the read-write latches, I think, that were talked about in the concurrent data structures. The compatibility matrix kind of represents, if you already have a shared latch, can you hand out more shared latches to that? Yes. Exclusive latches are more or less incompatible with any other latch types. You can't hand out multiple exclusive latches. You can't hand out an exclusive latch to something that already has a shared latch. And that's the basic compatibility of these locks. I shouldn't call them latches, because Andy will get mad at me. So the basic semantics of how we're going to work with locks is, as I was describing before with that example, you're going to go to a lock manager and you're going to say, hey, can I get a lock on this object? The lock manager is going to decide, based on its internal metadata, whether you're allowed to have that lock or not. And then it's up to the transaction to release the lock when it's done. The transaction managers, or excuse me, the lock manager is not necessarily putting any sort of constraints on how long necessarily you have that lock for. So it's up to the transaction to make sure they come back, release the locks when they're done with it. And then it updates its internal metadata. I don't think there's a, Andy's not doing a lock manager project this semester. We've done one in the past. But the basic implementation is there's an internal table, probably a hash table, that keeps track of all the possible locks that are taken in the system. And then as requests come in, you put them into a queue, you process them off the queue, and you go forward from there. So it's a cool project, but I don't think you're doing one this year. So let's look at an example with these shared and exclusive locks that we defined before. So T1 begins, it wants to do both a read and a write on A. So it's going to go straight for the exclusive lock. So it's going to go to the lock manager, give me the exclusive lock, lock manager's like, sure, no one else has a lock on A. It's all yours. T1 completes the operations it needs to do on tuple A, releases the lock because it's done. T2 comes along and says, I want to do a write on A. So it wants the exclusive lock now. Lock manager's like, sure, no one's got that lock. It's all yours. Do whatever you want. If it does its right, it unlocks A. And then T1 wants to come back and just do a read, so we only need a shared lock. Once again, ask the lock manager, give me the lock, does its operation, releases the lock. So as we described in the last slide, transactions are responsible for locking and unlocking. The lock manager's just kind of here to kind of direct traffic and make sure everyone's doing what they're supposed to be doing. Does anything jump out as a problem with this schedule? Right, it's not isolated. So in particular, when T1 at the end does another read, it's getting an unrepeatable read. It read tuple A, it did a write on A. But then it goes back and it reads A. And it's actually seeing the value that T2 wrote. So we have an unrepeatable read anomaly. We can't have that. So we're going to need to be a little bit smarter with what we do with our locks. We're going to need a little bit more discipline and a protocol applied to what we're doing here. Right, so Andy added one more cool animation to show, this is a problem, here's your unrepeatable read. So what we're going to use is a protocol called two-phase locking. And it's going to allow the database system to hand out locks in a manner that always guarantees conflict serializable schedules. This is a big deal because now we don't have to try to limit the parallelism in the system. We can try to run multiple transactions at the same time. And we don't need to know what the transactions are going to do ahead of time to be able to decide if they're conflict serializable. The system can run, we can do operations, and we can hopefully get high throughput on transactions without generating un-serializable schedules or violating our isolation requirements. This is all work that came out of IBM in mid-70s for the System R project. It was led by Jim Gray, who later went on to win a Turing Award. So this is pretty groundbreaking stuff. At the time, they were building one of the first relational database management systems. So there was no textbook on how to do this stuff. Two-phase locking is what they came up with. Turns out it's a pretty good idea. Most systems use two-phase locking now. So you're probably not surprised to see that two-phase locking has two phases. There's the growing phase, and then there's the shrinking phase. So in the growing phase of a transaction, it's allowed to acquire whatever locks that it needs. It goes to the lock manager, gets everything that it needs, and then it performs all of its operations. And then the second it releases a lock, it tells the lock manager, I'm done with this lock, the transaction is now in a shrinking phase. It is no longer allowed to acquire any more locks. So in an example, we'll see how this helps us out, how this prevents the anomalies we're concerned about. And I think, yeah. So another way to think about it is this visualization. x-axis is time. This is the lifetime of a single transaction. y-axis is the number of locks held by that transaction. So in the growing phase, it can continue to accumulate as many locks as it needs. The second it releases a lock, it's going to go into the shrinking phase. You get no more locks. So this is an example of violation of two-phase locking. Because the transaction released some locks, and it went back and acquired some more. We can't have that. It would cause the same sort of problems we saw in the previous example, where we had an unrepeatable read, where you released a lock, and then went back, acquired a lock, and you get anomalies because of that. So here's an example with two-phase locking. Transaction one is going to begin like before. It needs an exclusive lock, because it wants to do both a read and a write on the tuple. It could start with a shared lock and upgrade it. There was a notion on an earlier slide of upgrading. We'll talk about upgrading a little bit more later in the lecture, but we're going to start for now with an exclusive lock. Goes to the lock manager, gets the lock. T2 wants the exclusive lock. It can't have it, because T1 still holds it. So we're going to keep holding onto that lock until we're done with all of our operations. We're going to do the extra read that we wanted in T1. Then we're going to release the lock. T2 has been stalled this whole time, not able to make any progress, because it didn't get the lock on A. Lock manager's going to say, OK, T2, you get what you want. Do your write. And then T2 is going to unlock and then commit. So the great thing about two-phase locking is, if you follow this protocol, you will get conflict serializable schedules. The dependency graphs are guaranteed to be acyclic. This is great. This is exactly what we want. It does have a slight problem, though, and that is cascading abort. So we'll give an example on the next slide. But the idea is, because two-phase locking alone does not guarantee that you don't get dirty reads, you can lead to cascading abort. So this is the same schedule as before. We're going to access two different tuples. We're going to get the exclusive lock on A and the exclusive lock at B. That's because all the way down here, we're going to do operations on B. And we have to get all of our locks before we start unlocking anything, because we're in two-phase locking. So T1 does the operations it needs, releases lock on A. T2 gets the lock on A, does a read, and then also does a write. And then T1 aborts. But there's this problem now that T2 read a value from T1 that is now not actually in the system because the transaction aborted. It didn't commit. So we have this dependency now that because T1 aborted, you now have to abort T2 as well, because it read a value that isn't valid anymore. So that's what we mean by saying two-phase locking is susceptible to cascading abort. And the reason this is a problem is because you could be wasting a lot of work. So T2, because it read a value in T1, it could have done a bunch of operations on the system. And now because T2 has to abort as well, you're increasing the complexity of your rollback logic. More and more stuff needs to be rolled back in the system just because one transaction aborted leads to another transaction aborted. You can kind of see how this could domino through the system if you have a bunch of transactions running that are simultaneously reading values from each other and then one aborts. So another thing worth pointing out is this is a valid schedule under two-phase locking. Basically, kind of what I stated already, you can't let information from T1 leak to the outside world because it aborted, but it would be a permissible schedule under two-phase locking. So we're going to have to solve this somehow. And yeah, like I said, this is all wasted work. T2 could have done a bunch of complicated logic, done a bunch of more writes into the system, and because we have to abort it, we wasted a bunch of time. So a couple other things about two-phase locking that we want to point out. There are schedules that are serializable that two-phase locking actually won't allow. Two-phase locking is a little bit more, I guess I'll say, conservative or, yeah, we'll use that word. Because there are serializable schedules that two-phase locking would not be allowed. And because of the locking, we're going to limit the concurrency of the system slightly. As I mentioned, we have this problem with dirty reads that can lead to cascading aborts. We're going to solve that with a modification to two-phase locking called strong strict two-phase locking. It's also called rigorous two-phase locking. The terms are kind of used interchangeably. I think the textbook refers to both. And we have this other problem which is common with a lot of locking protocols that we can lead to deadlocks. So we're going to need to solve that problem with either a detection mechanism where we can detect when we enter into a deadlock state or some sort of extension of the protocol where we prevent ourselves from ever being able to go into a deadlock state. So again, going back to what we did with latches, with concurrent data structures, we make sure we acquire the locks in a very specific way that makes it so that we can guarantee we're never going to enter into a deadlock scenario. So we're going to tackle the first one first. We're going to talk about strong strict two-phase locking. It's sort of a misnomer because the second phase doesn't really exist anymore. All strong strict two-phase locking says is you don't release any of your locks until the end of the transaction when you're going to commit. The growing phase is exactly the same. You keep acquiring locks as you need them. But you don't release any locks until commit time. And this is going to allow us to prevent any unrepeatable, or excuse me, any dirty reads from propagating across transactions, which is also going to solve the cascading aborts problem for us. So yeah, the figure is kind of updated. But as you can see, the shrinking phase, wherever you want to define it as the last lock you acquired is the start of the shrinking phase. But basically, there is no shrinking phase. Everything gets released at once at the end of the transaction. And the word strictness does have a specific meaning in when we're talking about concurrency control. It basically means anything that you wrote, none of your rights are going to be visible to any other area of the system, any other transactions, things like that, until you commit. So in this context, strict has a very specific meaning. And like I said, that solves our cascading aborts problem because no other transactions are going to see values that aren't committed to the system yet. So they're only looking at committed data. Everyone's happy. And like I mentioned before, this simplifies your abort logic because aborted transactions only have to put back their values. You don't need to worry about all these transactions in the system potentially reading uncommitted data. And then they each kind of have to store their own metadata almost or methods to be able to roll back the work that they've done. This simplifies things a lot by not having to reason about multiple versions in the system. You only have one undo to do if you wrote a value. So we're going to look at a simple example. Andy owes his bookie money, I guess. So he's going to move $100 to his bookie's account. And then the second transaction is just going to compute the sum. This echo command is made up. It's not a real statement. He just wants to demonstrate that you're reading these values out. We could have done something more complicated, but we want to keep the example fairly simple. First, we're going to look at it with, I think, just basic locking, then two-phase locking, and then strong-strict two-phase locking. So with the two locks that we defined at the beginning, and if we're not using two-phase locking, we can see, spoiler alert, you're going to get a wrong output from this. So we're going to start with $1,000 in each account. T1 already gets the exclusive lock, does a read because it needs to decrement his balance by $100. T2 wants to get the shared lock because it's trying to compute the sum. It can't get it, so it just starts waiting. T1 finishes its operation to decrement, unlocks A. At that time, T2 gets the lock on A and performs its read, and then unlocks it because, again, we're not in two-phase locking here. So you're free to acquire and release locks as you need them. T2 also gets the shared lock on B because it needs to do a write on that, which leads T1 to stall because it can't access that tuple yet. Eventually, T2 releases the lock. T1 gets the lock on B, finishes moving the money over to the bookies account, unlocks, commits. And T2 gives us a wrong output because it read an inconsistent state. It read part of the work that T1 had done, and T1 leaked that information to the rest of the system and said, OK, this bank account balance is $900, but that $100 was missing at that point when T2 came along and got the locks that it needed. So with two-phase locking, the key thing to notice here is it starts the same. T1 gets its exclusive lock on A, performs the operation it needs, but before it unlocks A, which would put it into the shrinking phase, it acquires the lock it needs on B all the way down here. So it gets the exclusive lock on B. T2's stuck waiting around, waiting for the locks that it needs. Because this isn't strong strict two-phase locking, T1 actually unlocks A. Well, yeah. If this were strong strict, it would be down here at commit time. So it unlocks A, finishes its operation on Object B, and then releases the locks. And this actually gives us a correct output. Same example with strong strict two-phase locking. Like I said, the unlocking happens at the end right before commit. So yeah, T1 gets its lock. T2 has to wait the entire time. And you can sort of see how strong strict two-phase locking is effectively forcing a serial ordering for these transactions. By basically acquiring all your locks, holding on to them until you get to commit time, you're guaranteeing that any of the operations that T2 would have that would conflict are going to be forced into serial ordering. Make sense? I'll go with yes. And then the correct output again as well. So I think Andy showed this slide before in the universe of schedules. He showed serial schedules, conflict serializable schedules, view serializable. And then I don't know if he had cascading of boards in there. So this slide actually I think is, he mentions it last year is actually incomplete. What he meant to show is where two-phase locking and strong strict two-phase locking live in this hierarchy. And I'll see if I can at least use the laser pointer because the clicker doesn't seem to be doing anything. Nope. So two-phase locking is going to live in this area right here. It's guaranteed to generate conflict serializable schedules, but it's going to be susceptible to cascading of boards. I'll see if we can get this slide updated for the ones that actually get published on the site so we actually see better than just a laser pointer on the video. But yeah, two-phase locking would be here. And then inside of this box and around serial you would have strong strict two-phase locking because it's guaranteed not to have cascading of boards. We get conflict serializable schedules. We're happy. Make sense? So now let's talk about the other problem with two-phase locking, which is that it leads to deadlocks. And like I mentioned before, there's a couple different ways we can try to solve this problem. We can be kind of lazy about it and use a detection algorithm to find a deadlock. Or we can kind of be a little bit more proactive about things and try to prevent them from ever happening in the first place. So you've probably seen deadlocks before. It's a fairly common concept in computer systems. But we'll give a basic example anyway. So T1 wants an exclusive lock on A. Lock managers like, sure, you can have that. No one else has that lock. T2 gets the lock on B because no one else has that lock. Now T2 says, I want the lock on A. Lock managers like, no, you don't get that. But T2 is going to sit around and wait for that lock. And T1 now wants the exclusive lock on B and it's going to wait as well. So we have a problem now. Both these transactions are waiting for locks that the other transaction holds. So we need to break this somehow. Nice animation. We have a problem. So like I said, deadlocks are a, when you have a dependency cycle between transactions where they're holding locks. And we have a couple of ways of dealing with them. Detection and prevention. Let's see what we got here. So with deadlock detection, what the system is going to do with like a background thread is you're basically going to look at the lock manager's metadata and you're going to build a wait's for graph. So the idea is every node is a transaction and every edge is pointing to another node that holds a lock that that transaction wants. This is all going to be done in the background. You can balance out how frequently this gets done. We'll talk about that in a minute. But the idea is it's a background task that inspects the state of the lock manager and says, hey, are there any deadlocks? Use your favorite cycle detection algorithm and you have to decide what you're then going to do with that deadlock. So let's look at a simple example of what these wait's for graphs look like. We'll start with T1 wants a shared lock that T2 already holds an exclusive lock for. So T1 gets an edge pointing to T2. T2 is going to end up with an edge pointing to T3 because, as you can see here, it wants an exclusive lock that T3 already holds a shared lock on. Those are incompatible, so we're just stuck waiting. And then lastly, T3 is going to end up with an edge pointing to T1 because it wants an exclusive lock that T1 has a shared lock on. We have a deadlock. So we have to do something about this. Questions? What's that? So deadlock handling is kind of simple. Pick a victim, kill it. You choose a transaction and you roll it back. How far you roll that back is kind of implementation defined. It's possible you don't. I think this is a later slide. But you may not have to abort the entire transaction. You may not have to undo all the queries that it did. You may only need to partially roll back some of the queries to release the locks that you need to remove the deadlock and make forward progress in the system. The last point here is basically saying you have a trade-off in the system with how frequently you're going to build these wait for graphs. If the way you're dealing with deadlocks is detection, you have this background task. It's building these graphs, checking for deadlocks. It's up to you how frequently you want that task done. You could do it every microsecond if you want. But you're going to burn a bunch of CPU cycles, constantly building these graphs, and potentially not finding any deadlocks. So what you may want to reason about is, OK, maybe I want to check for deadlocks less frequently. And if I do enter in a deadlock state, how long do I want to make those transactions wait? What's an acceptable timeout that I could sit in a deadlock state without detecting it right away? So these are always going to be tunable parameters in your database system, because different workloads are going to manifest different deadlock behaviors. So we want to make sure we're not being too aggressive. It may be fine just to leave the system in a deadlock state for 10 seconds even. It depends on what the responsiveness of the system needs to be. So victim selection, there's a lot of different things you can look at here. And certain systems get very elaborate with what they do. But there's all sorts of different heuristics you can look at. The first is, look at the age of the transaction when you're trying to find a victim that you're going to kill. Because at the end of the day, you have this cycle. You've got to pick one. You can start with the timestamp. You can look about how much work it's done. How many queries has it executed? Progress is a, you don't know how close it is to being done, but you can at least reason about how much work it's done. How many locks it already holds. Let's see. It's possible that you would have to look at the number of transactions. You have to roll back. That would be in the case of if you have cascading of boards. I don't think I mentioned before. You don't have to do strong, strict two-phase locking. Some systems may just do two-phase locking and live with the possibility of a cascading of board. Again, it's going to be workload dependent. You can decide if the workload is not very susceptible to cascading of boards or dirty reads. You can just say, two-phase locking is fine. I can release my lock sooner. That may result in higher throughput in the system. Again, that's often going to be something that's configurable. Why would you make about the transaction the lowest time? Shouldn't you about the one with the highest time? How has that in the least work been done? Yeah, so we'll talk about a couple different ways to decide a little bit later on about which transaction gets killed. There are a couple different solutions, but the other one is typically going to be a combination of things. It's not necessarily always going to be just the age of the transaction. It's going to be what determines whether you get killed or not. Often, one of the biggest things is going to be the number of times you've already been killed, which is the last one says there. Because you do want to make sure you make progress in the system. You need to prevent starvation for these transactions. And I'm not saying any one of these is exactly what you're going to use to kill a transaction. The commercial systems get rather elaborate and can build predictive models even to try to figure out which transaction they should try to kill when they enter a deadlock scenario based on all these different heuristics. So it's typically a combination. It's not just the time stamp, I guess. It's a long way of answering, hopefully, your question. My question is, why would you always want to kill? Should it be considered the high? Even if you're considering only the time stamp, then why not the highest? Like, the one where it's been for the least time? If someone is working for like one hour and that's almost everything and just wants to write on the answer variable, answer it open, and it is just waiting for that lock. If you kill that, then all that one hour will be wasted. OK, so your question is, why would you? Sorry, I want to make sure I repeat it back for the video. Your question is, why would you always want to kill? In what scenarios would you want to kill the transaction with the lowest time stamp, always? Yeah, I suspect it's still always going to depend on the workload, depending on what's causing the deadlock, what sort of situation is causing it. But yeah, we'll formalize a little bit like in a few slides on what gets killed and why. But I think in the case of Postgres in the example, we'll see the highest time stamp gets killed. I think it's just an example of just saying, like, one heuristic you could look at. And then like I mentioned before, it's possible you don't need to abort the entire transaction. You could completely abort it, or you could do some sort of minimal abort, where you only roll back the number of queries in that transaction that will allow you to release the deadlock and make forward progress in the system. Again, this is something that's going to depend on the workload, how much work you're throwing away, and whether it just makes sense to have transactions resubmit all their queries, or if you can slowly unwind parts of it in order to free the system up from the deadlock and make forward progress. Let's see if we can get a demo working. So pardon my typing on this Surface keyboard. Looks like it's working. So we'll start with my SQL. We'll have two terminals open. We have a pretty basic table set up called transaction demo. That's horrible. You can't see what's happening there. But basically, we have two tuples in it, primary key ID 1 and 2, and the values 100 and 200. Pretty simple. We have two tuples. This should be really easy to put us into a deadlock state. What's up? I have no idea which ones those are. Usually, if you press all the buttons, it kind of works. I'm glad I was standing next to you. We got that on tape. Producer won, everyone. So we've got these two tuples in our table transaction demo. First thing we're going to do is set our timeout here. So like I was saying before, you can adjust things like how frequently you're going to detect for deadlocks. So in this case, we're going to try to change the lock weight timeout here. In ODB is the storage engine for my SQL as of, I don't know, my SQL 5, I think, or something like that. So the first terminal will begin a transaction. The first thing we're going to do is try to update the value of tuple 1. Actually, I missed a step. We need to explicitly tell my SQL we want to run in the serializable isolation level. So that's going to give us complex serializable schedules. We haven't talked about isolation levels yet. Like I said, I think that's going to be in the next lecture. But we need to tell the system we want serializable isolation level. We'll go back. We'll begin our transaction. And we're going to, like I said, update the value on tuple 1. Switch to our other terminal. We're going to start a transaction. We're going to update the value on tuple 2. So so far, no conflicts yet. T1 holds a lock on tuple 1. T2 holds a lock on tuple 2. We're going to go back to transaction 1 here. And we're going to say, OK, I want to update the value on where ID is 2. And it just waits. All it can do is stall. It's waiting for the lock. And then we can go back to T2 and say, I want to update the value on 1. T1 holds that lock already. We should get a deadlock. And sure enough, we get a deadlock. MySQL yelled at us rather quickly. Not a ton of debugging information, but deadlock found we're trying to get the lock. Try restarting the application. And you'll notice that T1, which was waiting for the lock on value 2, as soon as the system detected a deadlock with T2, it killed that transaction. T1 got the lock and made the progress that it needed. This is not strong fit, right? Because there was no isolation. 41. What do you mean by there was no isolation? It used that updated value on T2. We can check. So I'm just going to roll both of these back. And we'll begin. And we're going to update the value in ID1. We'll go down to T2, begin a transaction, update the value where ID is 2. And then we're going to just do a select star from transaction demo in T1 so it can't get the lock. And it's just going to hang there. If we do a rollback, we see that the value of T2 was never written to the table because it didn't commit. And T1 was able to make its forward progress by getting the shared lock. Cool? Good question. Yeah? There is no isolation. Yeah, I mean, in practice, you're probably going to see strong strict two phase locking because the system can't reason about whether it should sort of eagerly start releasing locks because it doesn't know what the client's going to do next. So you could, but I don't think any systems do this, you could speculate about starting to release locks early, but you might get it wrong. And we can't be wrong. We have to make sure we guarantee these isolation guarantees. Oh, yeah, I guess so. OK, isolation can stay in some boxes. Sure. Well, that's not actually true. So like I said, we'll talk about isolation levels in the next lecture or two. There are scenarios where you may be willing to run with lower isolation guarantees. And you can start to introduce certain anomalies back into the system if you are comfortable with them. Or if at the application layer you have your own logic to reason about that sort of stuff, you can lower isolation guarantees if it improves the performance of your system. But if we want to guarantee that, well, I think that way is through the stronger component of the big lock. Yeah, so strong strict two-phase locking or rigorous two-phase locking is the one we need to use if we want to guarantee we don't get any dirty reads that could result in cascading of warts as well. OK, cool. So that's my sequel. We can take a look at Postgres real fast. And we'll do a similar demo. Postgres, there is one other cool thing we can do here. So we're going to set the deadlock timeout to 10 seconds. The demo itself is, let me just make sure I created this table. Cool, same table exists, two values. With Postgres, we're going to begin each transaction by saying we want isolations level serializable. It's not necessarily a session-wide setting. You can actually, at a transaction granularity, define which isolation level you want to run at. And again, I keep saying isolation levels. We'll talk about that in a future lecture. T1 is going to begin. Oh, very begin. That's right. We're just going to do the same operation. We're going to update value where ID is 1. We're going to go down here. Do the same thing where value equals 2. Go back to T1. Try to update 2. We wait, looking for the lock that we don't have yet. And then we're going to force a deadlock by asking T2 to update the value related to 1. And there was a slight lag there. I don't know if you saw it. But compared to MySQL, which instantly was like, this is a deadlock, Postgres waited a little bit before it yelled at us and said, hey, you have a deadlock in the system. So like I said, these are tunable parameters. You can adjust how aggressive the database system is going to be with looking for deadlocks. Postgres gives us a lot more information. Tells us exactly which process was waiting for what kind of lock held by another transaction, which is another process. Postgres, I think this is a big reason Andy loves referring to Postgres, because it's basically a textbook implementation. The wording you get back out of the system is exactly what you would see out of a database textbook and kind of the behavior that would help us kind of understand what's going on here. There's one more thing we can try. I'm getting low on time, but I will do one more thing. So I'm going to roll both of these back. I'm going to set the timeout to 20 seconds, because there's one more thing we want to try to see. Let's see if I can get one more terminal. Hopefully this still plays. It may be tough. The deadlock timeout, we'll do the same thing. We can transaction in serializable on two of the terminals. This is going to be a slight race against the deadlock detection algorithm. So what I'm going to try to do here is demonstrate some of the internal metadata that Postgres presents to you and how it's tracking these sorts of things. Do that. And did my Tmux session just die? It died. Does this still have a connection? No. Interesting. Well, that's that demo. Cool, we're low on time anyway. But the idea is if we had been able to put the system into a deadlock state, I was going to query the catalog, and it was going to be able to show which transactions were holding locks, which locks they're waiting on, which transactions and processes those belong to. Postgres is great. Let's go back to this stuff. So like we talked about before, the alternative to doing a detection algorithm is to prevent yourself from ever going into a deadlock state in the first place. If we can come up with some sort of system that prevents this from happening, we don't never need to build weights for graphs. We don't need this background task that needs to make decisions about which process we're going to kill or which transaction we're going to kill. And I suspect this is probably what my SQL was doing, because my SQL immediately yelled at us that we had a deadlock, or immediately detected it, didn't prevent the deadlock, but it immediately detected it as opposed to Postgres, which kind of had a bit of a lag to it with the detection. So a very simple way to do this is to assign priorities based on timestamps. So older transactions are considered higher priority. And then you kind of have two different protocols you can follow. You have this weight die system, which basically just means old transactions wait for young transactions. So if the requesting transaction has a higher priority, it's going to hold a transaction. So a holding transaction is younger than it, but the older transaction wants its lock, then the requesting transaction is just going to sit around and wait. Otherwise, the requesting transaction is going to abort. The other system is called wound weight. You can think of it as young weights for the old. Requesting transactions, if they have a higher priority, then the holding transaction, the holding transaction abort. So basically, the requesting transaction comes in, steals your lock, and makes forward progress. Alternatively, the requesting transaction just waits. So this is probably confusing. It's a little bit clearer, I think, on an example on the next slide. So we have these two scenarios, these two schedules we want to look at. We'll look at the first one first. T1 and T2. T1 begins first, so T1 is going to have the higher priority than T2. But T2 gets the lock first. So T2 gets an exclusive lock on A. T1 wants the exclusive lock on A. It's the older transaction. It has higher priority. So under weight die, T1 is going to wait for that lock. And under wound weight, T1 is basically going to come in, kill T2, steal its lunch money, and T2 has to restart or whatever it needs to do, whatever the application needs to do with that transaction. In the second schedule, T1 once again begins before T2. So it has an older timestamp. It's higher priority. But it got the lock first. T2 wants that lock under weight die. T2 is going to abort. And under wound weight, T2 is just going to wait. So what these two policies are doing is they're basically defining the same sort of logic that we used for latching in concurrent data structures where this concept of we only take latches in one direction and we release them in a disciplined manner as well. If you can apply some sort of ordering to how you acquire your locks, which is effectively what this is doing, you can avoid deadlocks entirely. So weight dies basically saying, OK, we're going to order our timestamps in one direction and how we hand out locks. And then in wound weight, it's basically just the opposite. As long as we're consistent, you can't mix these things. If you start mixing them, you're going to get deadlocks in why you're even doing this, because then you would need a deadlock detection algorithm and you didn't solve any problems. Why is it called wound weight instead of deadlock? So the question is, why is this called wound weight as opposed to some sort of opposite of weight die? I have no idea. I really don't like the naming of this. I find it very confusing. Yeah, if you're confused by the naming, I'm with you. I think that's why Andy kind of adds this sort of simplification of old weights for young or young weights for old, because yeah, weight die, wound weight, it's a weird nomenclature. Any other questions? So this is sort of what I was talking about before. These schemes guarantee no deadlocks, because you're basically enforcing locks being handed out in a single direction or locks even being able to be stolen in a single direction. So yeah, we've talked about this already. So when a transaction restarts, what would its new priority be? Any guesses? What would its time stamp be? It's got to be the original. Any guesses why? So then you maintain the consistency of whether or not it gets wounded or died or weighted, whichever one. Essentially, if you have a transaction you start and then it gets killed by another transaction coming in, let's say because its priority will increase, the older it is, you want to maintain its true age as opposed to just resetting its age. Right, so that's right. In one word, starvation. We can't have transactions being starved. So when you restart the transaction, we want to make sure it's still used with the same time stamps so that eventually, whichever deadlock prevention ordering we're going with, we want to make sure progress gets made at one point or another. So we have 10 minutes. So we'll see how far we get. So far, this doesn't seem very efficient. What if we have a transaction that needs to update a billion tuples? Are we going to the lock manager a billion times? Going to the lock manager is not cheap. You're not implementing one this semester, but you can envision if you had to implement this in a system, you're going to need data structures that are protected by latches. It has to be concurrent. It's on the critical path of what every transaction is doing. We can't make all these trips to the lock manager. So what we're going to try to do is introduce some sort of hierarchy or change the granularity or allow different granularity of locks in the system. So we can apply locks to tuples, pages, tables. If you knew you needed to do a billion updates on a single table, maybe it makes sense to just ask for the single exclusive lock on the entire table, you are going to prevent other transactions from accessing it, but maybe that's what you want to do. The goal is to reduce the number of total trips to the lock manager possible by using this hierarchical model. So to do that, we're going to have to introduce a few more lock types and then this notion of a hierarchy. So conceptually, like I was describing, you can now take locks at different levels in the system. So a database can have multiple tables, different tables can have multiple tuples, and within different tuples, you can have multiple attributes. So you want to acquire the fewest number of locks possible to do the work that you need to do. So if T1 takes the lock on table one, if it explicitly takes a lock on table one, it now implicitly locks everything below it in this tree. This tree's, again, it's a slightly abstract concept. This isn't like a B plus tree or something like that. It's just in the hierarchy of the database system of all the tables and tuples you've inserted into the system. It's sort of a conceptual hierarchy in the system. So Andy's trying to get the balance of his shady offshore bank account and he's giving me 1% interest at the same time. Cool. This is sort of demonstrating in this conceptual tree that we have, what sort of locks do we need to take? We have all sorts of different types of locks. We need exclusive locks because my bank account balance is changing, Andy's going to need shared locks. And then there's this notion of an intention lock, which I think is defined on the next slide. You basically give hints in this tree. You basically take intention locks at higher levels to give hints to other transactions about what you're doing in lower levels of the system that's going to try to increase the parallelism of the system. So like I said, an intention lock is a hint to other transactions and it'll get clear with an example, but the basic idea is if you have an intention shared lock in this tree, the subtree rooted at that node, there is an explicit shared lock somewhere underneath that. Same thing with an intention exclusive lock. Somewhere in that subtree rooted in that node, there's an explicit exclusive lock. We're gonna add three more lock types, intention shared, which is like I described. Somewhere in the subtree rooted at that node, there's an explicit shared lock. Intention exclusive, same idea. Somewhere in that subtree there's an explicit exclusive lock and then there's one kind of tricky one, shared intention exclusive. This is an explicit shared lock on this node, which means that you have shared lock on everything below it in its subtree. And somewhere in that subtree, you also have an explicit exclusive lock. So you can imagine if you wanted to do a read on an entire table and then maybe update one value, you would get a shared intention exclusive on that table because you're gonna take a shared lock on the entire table and say, I'm reading all the values of this table, but I'm only gonna update one value. So you have an exclusive lock on one tuple further down. It'll make more sense with an actual example, I think, but are there any questions first? Example. Our compatibility matrix gets a little bit more complicated. Not a lot to say about this other than I think the tree actually seeing an example is a little bit more clear, but basically, the semantics are similar to shared and exclusive lock compatibility just apply to it to a tree. The lock protocol is basically just saying in order to get a shared lock, you have to hint at least in the parent node that you have an intention shared lock. Same thing with exclusive, to get an exclusive, intention exclusive, shared intention exclusive. You have to at least hold an intention exclusive on the parent node. Again, let's do an example because I think that's gonna make things a little bit clearer, we have five minutes. Very simple table, or a very simple example. Two levels, there's a table, there's a bunch of tuples. We wanna check Andy's bank account. He wants to do a read on tuple one. So he wants, he's gonna want just a shared lock on this one to do a read. But we're gonna have to take an intention shared at the parent node first. Basically as a hint to say, hey, below this node, I'm gonna take an explicit shared lock. T2 comes along, we wanna update my bank balance by 1%. So we want an explicit exclusive lock on this tuple. So we're gonna try to get, yeah, we get our intention exclusive lock on the parent node and we get our exclusive lock on the individual tuple. Now things will get a little bit more interesting. I think T1's basically doing the scenario I described before where you're gonna do a bunch of reads and then you're gonna update one tuple. T2's gonna read a single tuple, T3's gonna scan all of them. I apologize for going a little fast. I realize this is probably a little confusing, but we're getting a long time. So T1, like I said, it wants to read all the tuples and do an update on one. So it's gonna get a shared intention exclusive. This means I'm taking a shared lock on the entire table so I can read all the attributes and all the tuples in this table. And the intention exclusive part means I'm gonna update at least one of these tuples down there. In this case, it's tuple n. So because this is shared intention exclusive, all these tuples are implicitly locked in shared mode and then this is the only one we actually have to take an exclusive lock on because that's the only one being updated. T2 wants to read a single tuple. We're gonna need the shared lock on this guy which means we need intention shared at this level. That's all good, we can do that. The last transaction is the one that's gonna have problems. It wants to do a read on all of them. It's gonna want a shared exclusive shared lock, or excuse me, explicit shared lock on the table. Can't get that because it's not gonna be compatible with the shared intention exclusive because there's a right happening lower in the table. T3 has to wait. So basically it wants this explicit shared lock on the table. Can't have that, all it can do is wait. Yeah. I am confused with this example. Like two operations are understood. First was to read tuple one, to read the bank account. And the second operation was to increase one percent, was to add it to the tuple length. How did this thing, reading all the tuples came into the picture? So I may have gone too quickly. The example operations changed when I got to this one with three transactions. This is no longer doing like the read Andy's bank account, bump my account balance by one percent. T1's reading all the tuples and then modifying one. T2's just doing a read on a single one. And then T3 is the one that's trying to read the entire table. So yeah, it's a different example. I'm sorry about that if I went kind of quickly. So in practice, it seems complicated, but it's actually pretty helpful because you can reduce the number of locks that go, or the number of trips to the lock manager. You reduce the number of lock requests dramatically. And like we mentioned before, there's this concept of lock escalation. So if you already have locks in shared mode and you want to bump them to exclusive locks because you've decided you want to do it right on the tuple, you can do that. Once again, this is designed to reduce the number of trips to the lock manager and also doesn't violate two page locking because you can upgrade your locks, you don't actually release the lock. So in practice in real systems, you're not sitting there telling it which tuples to lock. You can give hints, like I said, if you know you're going to do a bunch of operations on a table and you want to hold the lock the entire time, you can explicitly lock the table. So it's not part of the SQL standard, but here's examples on how to do it in like Postgres Oracle, DB2, SQL, MySQL. These guys all use the nomenclature that we're learning about now, which is shared and exclusive. And because MySQL loves to be different, they call them read and write locks because they want to be different. There's also this notion of select for updates. So if you're doing a read on a tuple that you eventually want to update, you can give a hint to the database system that says, look, I know because you're doing a read, you're going to request a shared lock. I'm going to do a write later on, just take the exclusive lock now and hold it for me. So you can do select and add this for update that basically tells the system take the write lock or excuse me, take the exclusive lock right now. And you can also tell it just to take a shared lock. I don't know why you would ever do that because by default, it probably should just take a shared lock if you're doing a read. To finish things up, like the slide says, it's used in almost every system out there, at least most widely deployed commercial systems, SQL server, MySQL, Postgres. Two-phase locking is a big deal, but it's also not too difficult to implement and it gives us exactly what we want. It gives us our serializable schedules. We just have to be disciplined about whether we're going to try to detect our deadlocks and handle them, or we're going to try to prevent them entirely in the first place. Next class, I think it's going to be Dana talking to you guys about timestamp ordering, which is good. Cool. Oh yeah, coming through with my shell and crew. Two cent for a case in me, St. I's crew. In the midst of broken bottles and crushed up can. Met the cows in the gym or I'll drive. It's with St. I's in my system. Crack another unblessed. Let's go get the next one and get over. The object is to stay sober. Lay on the sofa. Better yet, damn I'm sure. Come here to follow me, Tim. Stressed out. Could never be sun. Rick is a jelly. Hit the deli for a part one. Naturally blessed. Yes. My rap is like a laser beam. The puns and the bushes. St. I's been licking the king. Crack the bottle of the St. I's. Sipping through gold. You don't realize. I'm drinking it. Only two we drunk. You can't drive. Keep my people still alive. And if the St. don't know, you're full of can of pain.