Europe 2022
What can go wrong with data...will!
Or ACIS transactions & database consistency.
JW
Jim Walker
Principal Product Evangelist, Cockroach Labs
Transcript
Hello, and welcome to my session today. Uh, we're titled this, what can go wrong with data will, because basically it will, uh, the subtitle here is asset transactions and database consistency. So we're gonna get into, you know, a little bit about, you know, kind of, uh, what, what isolation levels are in a database and why they are important, uh, for your applications. And then we're gonna talk a little bit about this in the context of, uh, distributed systems as well. And, you know, what are the challenges that we see when we start to actually, you know, have data living in lots of different locations. So, you know, hopefully this is, uh, you know, educational more than anything. I, I hope I'm at least entertaining for y'all. Um, but I, I hope this is a, is a useful session for everybody today. So first of all, hi, my name is, uh, Jim Walker.
I am principal product evangelist, uh, at cockroach labs. Uh, my Twitter following or Twitter handle is just at James. Uh, my email is jim@cockroachlabs.com if anybody ever wants to reach out and, and today's session is kind of, it it's beginner too immediate, because I think some of these, these are some of the core concepts that I think we should all be familiar with, um, as we kind of deal with databases and as they, I mean, they ultimately are the foundation of our applications. In fact, I was talking to a guy by the name of Dan Goodman, who was the CTO at a company called ultimate tournament. And Dan, Dan said something interested to me. He said, all applications are simply the interface between humans in a database. Eh, I, I like to think that, but then again, um, I work at a database company, so I'm a little bit slanted in that one, but, but it's, it's interesting.
And, and the database is kind of one of these, these core concepts. It is fundamental. I, I, I think of it as infrastructure, uh, and it is really what drives kind of, you know, the, the, the basic capabilities of our, of our apps, because ultimately everything stores something to dis I mean, this is the whole point that we're doing these things. Sure. There's some stateless things, but, but, you know, um, you know, the database is actually pretty, pretty important here, so, so let let's get into it. Um, and, and hopefully you find this useful. Great. So, um, let's just talk about a database transaction. So first of all, a, a transaction with the database really is, is it happens in three phases, right? There is begin, there is execute the transaction, and then there's a commit, or if something failed a rollback of that transaction.
So we actually have to roll back what executed or didn't now for a simple, you know, select this execution phase is, is pretty simple, but if you're gonna do, you know, a more complex transaction, well, the, the transaction, the phase two here, it could be two lines. It could be three execute, three tra you know, three kind of lines of a, of a transaction. Uh, it could be 20 or 30. They could get extremely complex. And for years, you know, we've done a lot of work in the database world to kind of, you know, optimize how these things work. Um, you know, for us at, at cockroach, how do we do these things, uh, in a distributed fashion, right? How do we actually distribute the, the, the comp the computations to, to multiple different nodes and have lots of different people, or, or nodes actually participate in this?
You know, there's the whole world of, of transaction kind of optimization. There's cost based optimizers out there. And, and so really this phase two, uh, is, is much long. It, it, it can be quite long, right? The beginning is just like a marker. I've started, I'm doing all these things. And then three is a kind of marker at the end saying it worked, or it didn't, if it doesn't roll back. Okay. So great. That's all really good. Seems pretty simple. Um, but what could possibly go wrong? Uh, well, maybe only half your transaction was successful. Um, maybe you're executing a transaction and at the same time, another transaction tries to write data that is going on at the same time. Uh, maybe one has to wait for the other, maybe if, uh, one of your transactions, you know, does need it fails. And there's a, there's a rollback.
Um, will it cause another rollback? You don't have this concept of like these, these cascading rollbacks, like, honestly, like databases seem really easy, but these, these are the, the, the crazy corner cases that, that drive database engineers crazy. Uh, and this is the stuff that I think, you know, really where the, the, the, the rocket science and databases really happens, you know, how do you actually implement this, this isolation of a transaction, right. Uh, and, and what does that actually mean for your application? And I think it's, it's important for developers to, to have a handle on kind of what these things are, because ultimately it could affect, you know, something in your application and where you once thought was something was a bug. It could just be, you know, an isolation level that was set in your database. Right. And so I'm gonna go through some of those things now.
Okay. So there's this word called acid acid transactions. You know, we, we hear a lot of database vendors, you know, talk about this, oh, we have asset transactions as if it's like some binary understanding of, of what this term is. And, and it quite honestly, it's simply not. I think that concepts are the concepts. However, when you get into the details of how these things are implemented, there's actually levels of, of, or, or spectrum, if you will, in terms of correctness of data, when we think about acid transactions, well, I wanna get into that a little bit. Right. Okay. So at the concept of acid, uh, was first, first appeared in 1983. Um, and it was a set of properties of a database transactions to guarantee validity in events of errors slash power failures. Um, you know, proved some pretty smart people on here, including Jim gray, but, but acid is a moniker and it just stands for ity, consistency, isolation, and durability.
Right? So ity is, is the first concept. And ity just says, basically the transaction itself is gonna complete in total. So in, in, in a relational database and SQL you say, like select star from customer or select star from customer where X equals Y whatever that is. Right. And so ity just means that the whole thing is gonna commit or not, right. Insert these 15 records into addresses table. Okay, great. I can't just submit or insert three. I have to do all 15. Right. And so ity says that the transaction is gonna happen in its completeness. Right. And, and that's what it is. The atomic unit for that is actually that transaction. Now action. Now consistency often gets kind of confused with the next, uh, letter. I, but consistency, basically, we're not gonna violate the integrity of the database, right. So we're gonna leave it in a VE state now in, in the no SQL world.
This is, um, you know, this is a little bit different, right? Because I, it is funny when, when no SQL databases talk about acid, because, you know, things like referential integrity and foreign keys and, and, and, and, and data types and these sort of things that actually control what the data is in, in a particular database. That's what consistency all is all about. Right? So the data is actually gonna be valid now in no SQL databases, often you have to kind of like overlay referential, integrity, or do checks on what the data types are and these sort of things. And so the, the database on the relational side takes care of these things. Like I'm just pure SQL, right? So the original concepts of, of consistency was really about is the data correct and valid? Is it in a good state, right? Uh, when that transaction completes now, the I is where I'm gonna spend a lot of time today.
Um, it really is isolation. So it ensures that when many transactions are going on at the same time, each one of them will have isolation. So they're gonna be able to complete in their own way, and they're gonna be executed kind of one after the other, or maybe not, right. Because there's in each database, there's different isolation levels. Right. And so we'll talk a little bit about those, and I think it's actually pretty important to explore what these are with whatever database you're using. Uh, lots of documentation out there, um, on, on these sort of things. We're gonna talk about that. And, and how do you actually implement isolation and distributed system? Hopefully that's valuable to your, and then finally there's durability. And so no matter what, you know, the transaction is gonna commit or not, no matter what the, what the circumstances are. Right?
So like, this is the whole, like, there's a power failure that, that once that thing is committed and there's a power failure, it's gonna stay in that state. Right. And so, no matter what happens around it, is it durable? Um, if the commit happens, the commit happens and, and that's, that's it. Right. And so that's really kind of the, the last concept. So, you know, altogether that, that really comes together as asset. And those that is really what, what the term means. Right. So let's just start there. Correct. Okay, great. So there's lots of different database isolation levels. Let's get into that eye. And I, I talked a little bit about that before it's a setting. Um, there are default levels within each database, but ultimately it's pretty important to kind of understand the ones on the, on the, on the left hand side of this tree.
Um, if you really want a great seriously, an amazing kind of outline of the various different isolation levels and kind of what they mean, um, Kyle, who, who runs a Jefferson and Jefferson kind of, they investigate kind of these distributed systems and distributed data systems that are out there, and they do a great job. Um, if you go to jeffson.io/consistency, you know, it talks a lot about, you know, what recommitted means, what repeatable reads, it's snapshot, isolation, serializable, strict serializable, right? And so if you go up basically from the bottom to the top is levels of isolation and, and, and really re the restrictive levels. Right. And so, um, you're gonna be guaranteed. Data is correct at the top, at the bottom, lots of weird things can happen and read committed. I'm sorry, but like transactions could overlap each other and, or read UNC uncommitted.
I mean, you know, I could read data before it's even committed. Like, and, and so the, the database actually has a setting in which you do that. Now people will change isolation levels, uh, in a database often. Um, maybe it's a performance thing. They don't wanna wait around for transactions, this sort of stuff. But, you know, ultimately there, there's a default set of, of things that, that, that these things are set to as well. And we'll, we'll walk through that. So week isolation levels really result in a lot of common issues. Um, like I was talking about before a dirty read, you know, you know, a transaction, you know, this phase two lots of stuff is happening. Another transaction comes in a select comes in, looks at that it's uncon uncommitted data. Um, you know, I, I can have these non repeatable reads where I read something from a, a table and I read it again, and it's not the same thing.
Uh, I can have a right skew, which is a little bit more kind of, um, it's, it's a little bit more of kind of, um, I guess, I guess academic in terms of, of how you actually understand it, but it's kind of like, it actually kinda overlaps how you use that data. Um, and then, you know, these Phantom reads as well. And, and, and look, there's a lot of information out there about some of these, these, these common issues that are out there. I know Wikipedia goes into great explanations about each one of these, these issues that are caused by a levels and database, but every database has a default isolation level. Um, by default, you know, these are the various different levels that, that these solutions are, um, you know, here at cockroach DB, we, we are serializable. In fact, you can't relax serializable for us.
Um, but you could do things like follower reads, and there's some other things that you could do on a database to, to relax this on a query by query basis or whatever you wanna do. But, but at the da at the database level, we are absolutely with, you know, you know, serializable, uh, and, and, and so if you look at the various different kind of, you know, isolation levels, it shows you the different on the right hand side, the different data issues that could happen with your data. And so, based on what that isolation level is, you can have, you know, whatever your issues, these, these sort of things are. So being familiar with the issues that you, you might have with your data, and really quite honestly, how important are these issues to your application? You know, look, if you're doing kind of like financial ledger, crazy transactions where you require like nuclear codes.
Oh my God, man. Yeah. It's gotta be serializable because I can't have any issues with my data. Um, but if you're just doing kind of some, I don't know, like a, I don't know, like a birthday card application, you know, is the dirty, right. Okay. With you dirty read, it really, really comes down to really what you want to accomplish for your workload and, and the importance of your data now, system of record type stuff. You know, I always air on making that stuff, correct. Because look at we're running analytics on these things. Maybe it's money, maybe it's inventory these things actually, you know, they're, they're tracking things that are, that are important to the business. And so often a lot of times in say like an analytical flow, you know, you get junk in junk out. So make sure your data is, is correct, uh, on what you want to accomplish.
Right. So let's look at a, a, a transaction we're gonna update account balance. Um, we're gonna have two simultaneous transactions due. It's just to give a quick overview of one of these, right? And so we're gonna read the account balance. We're gonna update the balance to reflect a deposit. The first transaction's gonna deposit a hundred bucks, the second transaction's gonna be deposit 50. And at the end of this, we expect $150 after the two transactions. Right. Cause at transaction, one happened after transaction two, that would happen in serializable. You're guaranteed that that's going to happen. And if they overlap and one tries to do the other, you may get a, a, a transaction retry, right. So you have to put, try, catch blocks around things. That's just good coding. Right. And so ultimately what we want these things to do is happen in order. Right.
And so let's look at it at recommitted, right. So we're gonna begin this. So here we go, begin and commit, right? The, and the phase in the middle is, is the stuff right? Select balance from accounts where ID equals one, um, update my account, set balance to hundred where ID equals one. Okay. Update and then select, you get, it's gonna be a hundred, but I haven't committed yet. Right. So, and what happens before that commit has happened? Another transaction starts transaction two, and it's gonna update that account balance to 50. Right. And so before that commit happens, these two state, oh, excuse me. These two statements in my, uh, in, in this second transaction have actually happened. And so at the end of this commit on, on, on number one, the balance that we're gonna give back is 50. Right. And so we haven't actually updated that balance.
Right. And so this is kind of one of those things that can go wrong. Um, with Reed committed now with serializable isolation, what you're gonna do, um, you know, there's gonna be, uh, you know, there's gonna be basically a block put in place so that, that you, aren't gonna get these things done. And honestly, transaction two is not gonna happen because ultimately this other transaction was happening. And it would just actually say back to the application, I couldn't do this because there was a concurrent update going on. So I would get an error. And this is what, I mean, you gotta put a try catch block around something so that you can actually catch this error and then retry that application in your code logic. Right. And so this would happen. Okay, great. It committed in the meanwhile transaction two was trying to happen. It failed and the try catch block would actually have it happen at another time.
And you would add another $50 and you're selected, the end would be 150. Right. So that's, what's gonna happen in, in a serializable instance, right? So this, this right queue is a bit difficult to actually understand. You kind of gotta understand the data. And so what we're gonna go through here is we're not gonna use this account balance setting. We're gonna use like a vacation request, right. And so we have two Vaca. We have two employees that wanna actually take, uh, vacation. Uh, both of them are, uh, asking the system, can I, can I, can I take time? And basically there's only two employees and somebody has to be working at all times. Right? And so these are kind of weird situations where it comes down to the data and this right skew can happen. And if, if these things were serializable, this wouldn't happen.
Right. But in, in certain kind of isolation levels, this can actually happen. So in, in, in, in like I have client one, the transaction, one saying, look at, I wanna take vacation request. And it goes in, it confirms that employee two is, is on the calendar is on call. And that application is gonna allow employee one to take leave. Right? Cause look, somebody's got covering the help desk, whatever that is, right at the same time, client two also request leave. Um, and at the time it says, Hey, that employee one doesn't have vacation time, so they're on call. And so that happens before that commit has happened. And so basically the applications and I was saying, both of my employees can take vacation. Right. And so basically the, the time of the right is made on the premise that the decision is no longer true.
Um, and so rights skews are a little bit more difficult to understand, but actually pretty important, um, in, in the overall kind of context of how these things work. And so these are some of the things that can go wrong. Um, there's some much more kind of really crazy stuff out there in terms of what people can do in terms of exploiting isolation levels. Um, this, oh God acid rain by the, by the, you know, by Peter Ballas at, uh, at Stanford lab, we at this article, it's pretty scary stuff. And what people can actually do in terms of, you know, fraud as they, you know, exploit these isolation issues too. So it's not just kind of what your app can do. It's really a security issue in many ways in, in some, in some systems as well. Right. So check that out. So we talked about isolation levels, kind of what those things mean.
Now let's talk about this in a distributed environment. And I, you know, I bring this up because, you know, yeah. We're here at cockroach labs. We're, we're trying to kind of solve these things, um, at broad mass in, in distributed systems. Right. And so, first of all, in distributed systems, there's a thing called distributed consensus and distributed consensus basically is, is, are these algorithms we use raft. And basically what it says when I write to a system and I'm gonna write three times. Right. And so I have an odd number of replicas and every right is gonna get quo, like two of three have to actually commit. Right. And so basically that's kind of the, the nature of the distributed system in the background. Now this has to, this has to do with transactions. So when I write a transaction, it has to write it three times, right?
Not just once now it uses the concept of a raft leader. And we'll talk about that a little bit more. If you wanna learn more about raft, you go check out, um, you know, the, the secret lives of data.com, but, but raft is actually pretty interesting. And the raft leader is really, what's gonna make sure that the atomic right happens. The whole transaction is gonna happen are not in all the places. If it doesn't, it's gonna roll back across the replicas and say, Hey man, it's not good. So we're gonna be guaranteed. That data is correct. Right. So go check out the secret lives of data. Um, it'll talk more about raft. It's pretty cool. Okay. Now transactions are gonna happen in order now, how do we make sure that these things are gonna actually not overlap? Like I'm not gonna have two transactions overlap and I'm gonna actually implement isolation.
So there's this thing called MVCC multi version concurrency control. Now really again, like actually Wikipedia does a great job of describing what this is, but just check it out. Right? So there's three things we're gonna track here, a transaction itself, a timestamp for that transaction and a row of data that I wanna update. Right. And so basically let's just go through basically a transaction. So at time zero, this transaction has a transaction timestamp, the Ts one of zero right now, my object has never been touched, you know? So I, I haven't even created, so baby, an insert. So I'm gonna write this thing. So at time 0, 0 1, I'm gonna change the right timestamp on my object to zero one to the first second. Now what we do is we create a temp object in the background because we don't wanna actually update the object. We're gonna have this temporary thing.
So we can do a rollback if something goes wrong. Right. And so it takes maybe a second to actually do that. It comes back. It's not, time is number three. And I'm gonna say back to the transaction, Hey, at time number three, the read timestamp is good. So I have a read timestamp and a right timestamp on that object. Right. And so I've now acknowledged that that thing works. That's cool. Right? So let's kind of clever, right? I can look at read and write timestamp. I'm gonna understand basically the status of that object, if it's actually undergoing some sort of transaction or not now in distributed system, that object is dealing with lots of stuff in the background. Right. So you can imagine that that raft leader is managing these things for the complexity of the transaction. That's happening in the background. Now let's try this with a conflict.
Okay. So at times zero, my transaction is starting. Okay, great. Right. Tat stamp goes to one second. Right. I create my temporary object at times zero two. Right. And it may create a whole bunch of craziness in the background. Right. And so I'm creating this, I'm gonna do a bunch of work. I'm gonna execute all that. You know, the phase two stuff going on in that transaction. Right. And while I'm doing that, another transaction is coming in saying, I wanna write. And my timestamp right now is two. Right. It happened at two seconds really? After transaction one is stop it. Right. So what it does is it really looks at the read timestamp of that object. And it says, wait a second, my timestamp is two. The read timestamp is zero. It's greater than that. So basically right now, I'm gonna get a, a, a bad state of what's going on.
It's gonna look at the right timestamp, all these things. So basically what we're doing is we're, we're comparing timestamps to make sure that things, things are gonna happen in order. And it's really a combination of the transaction, three timestamp and the right timestamp to make sure that these things are correct or not. Right. And so ultimately it's kind of like standing in line at the store. You can only have one person go through the, the, the, you know, at a time, uh, through, through the, through the checkout line. Right. So, so that's, that's kind of the basis of kind of VCC. Hopefully that's helpful. I check it out. It's pretty cool stuff in a distributed execution environment. How does it kind of physically look again? So we have a transaction that's gonna happen in, in cockroach. Basically any node can be an endpoint. So you can imagine any one of these, these four nodes actually servicing a transaction.
So I'm gonna create a transaction. I'm gonna go through one of those and I'm gonna go find that RA leader, right? I'm gonna say, Hey, RA leader, I wanna write this transaction. So the RAF leader says, Hey, wait, I'm pending here. And it's gonna, we're gonna insert two records. So I'm gonna write son, it's gonna go talk to the followers. Remember this is that object doing a bunch of work. It's gonna go talk to my two replicas, say, Hey man, write a temporary record. Is this cool? Right. Um, and as soon as one of those come back, I have two of three, right? Two of three of the copies I could say, having I'm pretty good with sunny. Great. Temporary commit that thing. Great. Hold it. Now I'm gonna do it right on Ozzie. Right. And so it's gonna go out and find the RAF leader for Ozzie.
Ozzie's gonna go out, put temporary placeholders for Ozzie, actually being inserted into this database. And when it gets an acknowledgement back two of three for that actually op for that, for that row, it's gonna come back and acknowledge back. Actually, the, the RA, leader's gonna acknowledge back saying, Hey man, everything looks good. It's gonna commit that transaction. And it's gonna both do two things. It's gonna return back to the requesting application. Everything went fine, but it's gonna take those temporary records, those yellow things and say, no, no commit. 'em, they're good because if anything had gone wrong, it can actually roll back all that information. Right. So we're kind of using the context of, or, or the concepts of VCC here, right. Where, you know, if another trans transaction had come in and I was trying to do these things, and the raffle leader said, Hey, wait, I'm busy.
You know, it would, it would fail. And I would, I would have a conflict, right. We'd have to have a transaction retry. Um, and I'm also using kind of raft now to make sure that this atomic commitment, the, the a and asset happened. Right. And so that's kind of how we do it in a, in a, in a distributed environment, that's us for a database. Uh, you could think about this in the context of your own applications and, and how this might be applicable. Um, but that's really kind of the, the core concepts of, of like the two kind of technical components of what makes this interesting in, in a distributed environment. So that, that's basically all I wanna talk about. I mean, um, you know, I, I work at cockroach, uh, database and cockroach labs. If you want to go try our database right now, you can go to cockroach labs.com, spin up an instance.
Uh, you get a serverless instance for free, uh, right now. So you get a, a nice instance of database gonna be guaranteeing database correction. So, um, and you can go start that now. So, um, with that, I wanted to thank everybody for joining this session. I hope it was valuable. Um, you know, we walk through kind of isolation levels and what asset means. I think asset is a, a spectrum of values in terms of what that actually means, especially the consistency and the, the isolation levels in a database. Um, you know, we talked through kind of, um, you know, what isolation levels are, how to, how to investigate those and then how to use that in distributed systems. So, um, with that, I just wanted to thank everybody for taking the time, uh, and have a great day.