What can go wrong with data...will!

Hello, and welcome to my session today. We titled this "What Can Go Wrong with Data Will," because basically it will. The subtitle is "ACID Transactions and Database Consistency." We are going to get into what isolation levels are in a database and why they are important for your applications. Then we are going to talk about this in the context of distributed systems as well, and what challenges we see when data lives in lots of different locations. Hopefully this is educational more than anything. I hope I am at least entertaining, but I hope this is a useful session.

My name is Jim Walker. I am Principal Product Evangelist at Cockroach Labs. My Twitter handle is @jaymce. My email is jim@cockroachlabs.com if anybody ever wants to reach out. Today's session is beginner to intermediate, because these are some of the core concepts we should all be familiar with as we deal with databases, and as databases ultimately are the foundation of our applications. I was talking to Dan Goodman, the CTO at a company called Ultimate Tournament, and Dan said something interesting: "All applications are simply the interface between humans and a database." I like to think that, but then again, I work at a database company, so I am a little slanted on that one. Still, the database is one of these core concepts. It is fundamental. I think of it as infrastructure, and it really drives the basic capabilities of our apps, because ultimately everything stores something to disk. Sure, there are some stateless things, but the database is pretty important here.

Let's talk about a database transaction. A transaction with a database happens in three phases: begin, execute the transaction, and then commit, or if something failed, rollback. We actually have to roll back what executed or did not. For a simple select, this execution phase is pretty simple. But if you are going to do a more complex transaction, phase two could be two lines, three lines, twenty, or thirty. They can get extremely complex. For years, we have done a lot of work in the database world to optimize how these things work. For us at Cockroach, that includes how we do these things in a distributed fashion: how we distribute computations to multiple nodes and have lots of different people or nodes participate. There is a whole world of transaction optimization and cost-based optimizers. Phase two can be quite long. Begin is just a marker saying, "I have started." Then three is a marker at the end saying it worked or it did not; if it does not, roll back.

What could possibly go wrong? Maybe only half your transaction was successful. Maybe you are executing a transaction and, at the same time, another transaction tries to write data that is going on at the same time. Maybe one has to wait for the other. Maybe if one transaction fails and there is a rollback, it will cause another rollback, the concept of cascading rollbacks. Databases seem really easy, but these are the crazy corner cases that drive database engineers crazy. This is where the rocket science in databases really happens: how do you implement isolation of a transaction, and what does that mean for your application? It is important for developers to understand these things, because something you thought was a bug could just be an isolation level that was set in your database.

There is this word called ACID: ACID transactions. We hear a lot of database vendors say, "Oh, we have ACID transactions," as if it is a binary understanding of what the term is. Quite honestly, it is not. The concepts are the concepts, but when you get into implementation details, there are levels, or a spectrum, in terms of correctness of data when we think about ACID transactions.

The concept of ACID first appeared in 1983. It was a set of properties of a database transaction to guarantee validity in events of errors or power failures. Some pretty smart people were involved, including Jim Gray. ACID stands for atomicity, consistency, isolation, and durability. Atomicity says the transaction itself is going to complete in total. In a relational database, in SQL, you might say "select from customer" or "select from customer where X equals Y." Atomicity means the whole thing is going to commit or not. If you insert fifteen records into an addresses table, you cannot just insert three; you have to do all fifteen. The atomic unit is that transaction.

Consistency often gets confused with the next letter, I, but consistency means we are not going to violate the integrity of the database. We are going to leave it in a valid state. In the NoSQL world, this is a little different. When NoSQL databases talk about ACID, things like referential integrity, foreign keys, data types, and other controls over what the data is in a database are what consistency is all about. The data is going to be valid. In NoSQL databases, you often have to overlay referential integrity or do checks on data types. In relational SQL databases, the database takes care of those things. The original concept of consistency was really about whether the data is correct and valid, and whether it is in a good state when the transaction completes.

The I is where I am going to spend a lot of time today. It is isolation. It ensures that when many transactions are going on at the same time, each one has isolation, so they can complete in their own way and will be executed kind of one after the other, or maybe not, because each database has different isolation levels. We will talk about those. It is important to explore what these are with whatever database you use. There is lots of documentation on these things. We are going to talk about isolation and how you implement it in a distributed system.

Finally, there is durability. No matter what, the transaction is going to commit or not. If there is a power failure after something is committed, it is going to stay in that state. No matter what happens around it, if the commit happens, the commit happens. Altogether, these concepts come together as ACID.

There are lots of different database isolation levels. Isolation is a setting. There are default levels within each database, but it is important to understand the levels. Kyle, who runs Jepsen, investigates distributed systems and distributed data systems, and Jepsen does a great job. If you go to jepsen.io/consistency, it talks about what read committed, repeatable reads, snapshot isolation, serializable, and strict serializable mean. From bottom to top, the levels become more isolated and more restrictive. At the top, you are guaranteed the data is correct. At the bottom, lots of weird things can happen. With read uncommitted, I could read data before it is even committed. The database has a setting for that. People often change isolation levels in a database, maybe for performance, because they do not want to wait around for transactions. But there are also defaults, and we will walk through those.

Weak isolation levels result in common issues. A dirty read is when another transaction reads uncommitted data during phase two of a transaction. Non-repeatable reads are when I read something from a table, read it again, and it is not the same thing. Write skew is more academic in how you understand it, but it overlaps how you use the data. There are phantom reads as well. There is a lot of information about these common issues caused by database isolation levels. Every database has a default isolation level. CockroachDB is serializable, and you cannot relax serializable at the database level, though you can do things like follower reads or relax some behavior query by query. At the database level, CockroachDB is absolutely serializable.

The different isolation levels show the different data issues that could happen with your data. Being familiar with the issues you might have with your data, and with how important those issues are to your application, matters. If you are doing financial ledger transactions or something that requires nuclear-code-level correctness, it has to be serializable because you cannot have issues with the data. If you are doing a birthday card application, maybe a dirty write or dirty read is okay. It comes down to what you want to accomplish for your workload and how important the data is. For system-of-record work, I always err on making that stuff as correct as possible.

Let's look at an example where two simultaneous transactions update an account balance. The account starts at zero dollars. Transaction one deposits one hundred dollars. Transaction two deposits fifty dollars. We expect the account to contain one hundred fifty dollars after the two transactions. At read committed, transaction one begins, selects the balance where ID equals one, sees zero, updates the account balance to one hundred, and selects again, seeing one hundred before it has committed. Before that commit happens, transaction two begins and updates that same account balance to fifty. Before the first commit happens, the statements in transaction two have happened, and at the end, the balance we get back is fifty. We have not actually updated that balance the way we expected. This is one thing that can go wrong with read committed.

With serializable isolation, there is basically a block put in place so you do not get these things done in that order. Transaction two is not going to happen, because another transaction was happening. The database would say back to the application, "I could not do this because there was a concurrent update going on." You get an error. That is what I mean when I say you need to put a try/catch block around something so you can catch this error and retry that application in your code logic. Transaction one commits. Meanwhile, transaction two was trying to happen, it failed, and the try/catch block would have it happen another time. Then you would add another fifty dollars and your select at the end would be one hundred fifty. That is what happens in a serializable instance.

Write skew is a bit difficult to understand. You have to understand the data. Instead of using the account-balance example, use a vacation request. Two employees want to take vacation. Both ask the system, "Can I take time?" There are only two employees, and somebody has to be working at all times. Client one asks to take vacation, and the app confirms that employee two is on call, so it allows employee one to take leave. At the same time, client two also requests leave, and it sees that employee one does not have vacation time, so employee one is on call. That happens before the commit. The application now says both employees can take vacation. The write is made on the premise that the decision is no longer true. Write skews are harder to understand, but important in the context of how these things work.

There is more crazy stuff people can do by exploiting isolation levels. "ACID Rain" by Peter Bailis at Stanford is scary stuff about what people can do in terms of fraud as they exploit isolation issues. It is not just what your app can do; in some systems, it is a security issue as well.

We talked about isolation levels and what they mean. Now let's talk about them in a distributed environment. I bring this up because at Cockroach Labs, we are trying to solve these things at broad mass in distributed systems. In distributed systems, there is a thing called distributed consensus. Distributed consensus is an algorithm. We use Raft. When I write to a system, I am going to write three times. I have an odd number of replicas, and every write is going to get quorum; two of three have to commit. That is the nature of the distributed system in the background. This has to do with transactions: when I write a transaction, it has to write it three times, not just once. Raft uses the concept of a Raft leader. If you want to learn more about Raft, go to thesecretlivesofdata.com. The Raft leader makes sure that the atomic write happens. The whole transaction is going to happen or not in all the places. If it does not, it rolls back across the replicas.

Transactions have to happen in order. How do we make sure they do not overlap, so we can implement isolation? There is this thing called MVCC: multi-version concurrency control. There are three things to track: the transaction itself, a timestamp for that transaction, and a row of data that I want to update. At time zero, the transaction starts with timestamp zero. The object has never been touched, so this is an insert. At time one, we change the write timestamp on the object to one. We create a temp object in the background because we do not want to update the object directly; we want a temporary thing so we can roll back if something goes wrong. It comes back, now time is three, and we say back to the transaction that at time three, the read timestamp is good. We have a read timestamp and a write timestamp on that object. We can understand the status of that object if it is undergoing a transaction or not. In a distributed system, that object is dealing with lots of stuff in the background, and the Raft leader is managing the complexity.

Now try MVCC with a conflict. At time zero, the transaction starts. The write timestamp goes to one second. We create the temporary object at time two, and it may create a whole bunch of work in the background. While we are doing that, another transaction comes in saying, "I want to write." Its timestamp is two, after transaction one has started. It looks at the read timestamp of the object and compares timestamps to make sure things happen in order. It is a combination of the transaction timestamp, the read timestamp, and the write timestamp to make sure these things are correct or not. It is like standing in line at the store: only one person can go through the checkout line at a time. That is the basis of MVCC.

In a distributed execution environment, any node can be an endpoint, so any one of four nodes can service a transaction. I create a transaction, go through one node, find the Raft leader, and say, "Hey, Raft leader, I want to write this transaction." The Raft leader says it is pending, and we insert two records. It writes Sunny and talks to the followers, asking replicas to write a temporary record. As soon as one follower comes back, I have two of three copies and can say Sunny is good. Then I write Ozzie. It finds the Raft leader for Ozzie, puts temporary placeholders for Ozzie being inserted into the database, and when it gets acknowledgement from two of three for that row, the Raft leader acknowledges that everything looks good. It commits that transaction and returns to the requesting application that everything went fine. It also takes those temporary records and commits them. If anything had gone wrong, it could roll back all that information.

We use MVCC so that if another transaction comes in while the Raft leader is busy, it would fail, create a conflict, and require a transaction retry. We are also using Raft to make sure that the atomic commit, the A in ACID, happens. That is how we do it in a distributed environment for a database. You could think about this in the context of your own applications and how it might be applicable. Those are the core concepts of the two technical components that make this interesting in a distributed environment.

That is basically all I want to talk about. I work at Cockroach Database and Cockroach Labs. If you want to try the database, go to cockroachlabs.com and spin up an instance. You can get a serverless instance for free. With that, thank you for joining this session. I hope it was valuable. We walked through isolation levels and what ACID means. I think ACID is a spectrum of values in terms of what it actually means, especially the consistency and isolation levels in a database. We talked through what isolation levels are, how to investigate them, and how to use them in distributed systems. Thank you for taking the time, and have a great day.