 All right, so let's get started. So today we're going to start a discussion of databases, relational databases, which is going to last for a couple of lectures. First of all, though, let's review the material from last time that Anthony covered. So first of all, flow control is responsible for detecting packet loss and retransmission. Is that correct or incorrect? False. All right. Yeah, so congestion control deals with loss and retransmission. Flow control always allows a sender to resend a lost packet. Actually, the flow control may allow the sender to resend. The idea is to get the packet through. If the receiver fails to acknowledge the packet, then it has to be resend. OK, well, flow control is associated with the network layer with TCP, not with IP. All right, so next one, with TCP, the receiving OS can deliver data to the application out of sequence. False, no. Right, it goes out of sequence, possibly, to the receiver. But then when it's rebuffered, it's going to go up in order. All right, and flow control makes sure the sender doesn't overflow the receiver. What? No, that's right. I mean, that's the idea of flow control. It's managing the receiver's capacity, trying not to overload the receiver. OK, all right, so let's review what databases are. We'll do that first, and then we'll look at transactions. Really, the core of relational databases. And as part of that discussion, without going deeply into all of the aspects of ACID transactions, we're going to talk about how we can, in a fairly simple way, decide how to get reasonably good concurrency but still consistent transactions. And the slides are drawing a lot from previous presentations by Mike Franklin. All right, so what is a database? So a database is a large, organized collection of data. The organization makes it possible to access the data in particular ways. So we think usually about electronic collections, but actually the Library of Congress is considered a database. It's indexed in such a way that you can retrieve all of it, which is critical. And without necessarily being in electronic form, it supports certain types of query, albeit slowly. So but more familiarly, we have data that's stored in enterprises and consists of certain objects that we care about, people, businesses, products, and so on, and the relationships between them. So for instance, Cal playing against Stanford. That's a relation. And the big game is part of that same relation. It's the relation of playing in a certain venue. So we can define a predicate that captures all of those aspects. And that's what a relation is, mathematically. So let's look at some large databases to get a sense of where we are and why relational data is still very important. Yahoo has a collection of user data that's formally reported around 700 terabytes. AT&T has famously kept customer records back virtually forever. And it's currently running at about 300 terabytes. And the Australian Bureau of Statistics has 250 terabytes of something, and I don't know what it is, because there's only about 23 million of us. And somehow they have megabytes on all of us. And we hate filling out census forms, so I don't know where they get all this stuff. But still, it's a massive amount of data. Each of these data sets are large, and they're representative of similar data. So Google and Amazon obviously have large customer record data sets. Yahoo's a little bit better reported. So what matters for these data sets? What kinds of questions do we want to ask of those data sets? Right, so what about for Yahoo? What does Yahoo care about? Was that? Yeah, yes, definitely. So what questions are they gonna ask of that data? What kind of data they want in there and what questions would they ask? How does Yahoo make money? Yeah, right, yep. So that user data comprises anything that would give them a hint about what kinds of ads they can serve. So Yahoo has a lot of diverse properties that indicate the interest of their users. They also have history of which ads, if any, have been clicked through before and so they can, on top of that data, build models of what's gonna work. All right, good. Yeah, so what about AT&T? What do they care about? How do they make money? What's that? They bill you, right, exactly. So therefore, what kind of data do they care about? Yeah, just exactly the calls that you made, when you made them. I mean, they also want to optimize their networks and so on, avoid congestion. So as well as the caller and the callee, they want to know where you were called, where you were calling, when and so on, so they can manage the capacity of the network. Australian Bureau of Stats, well, you know, the usual stuff, far more stuff than I think they probably then would they should care about. So all right. So the things that are important for Yahoo's data are gonna be availability and timeliness because that information's being used in real time to serve ads to people. As soon as somebody shows up on the site, they have to decide what ads to show. And they always have a very large portfolio of advertisers. So matching the advertiser to this subject is a big part of making revenue. So availability of that database is critical. It should be up as much as possible. And also with as low a latency as possible because as users are cruising around Yahoo, they may in one page do something that tells the system a lot about their interest. They might be in a automotive interest site and naturally from there you can tell that you should be serving them ads about cars, potentially or perhaps accessories. And that can happen in the click, the very next click. So a fraction of a second you may wanna serve them an ad that's appropriately categorized. So both of those are important. AT&T, well, you want your bill to be accurate, right? You don't wanna be charged for things that you didn't do. And people wanna sort of be able to figure out when and where they were making calls as well. So accuracy is important since they have this information distributed across their network, right? There's gonna be machines in different places gathering your call information depending on where the calls are originating and ending. So that data has to be collected and made consistent. And finally, they want the data to be durable. So if their system failures, they don't wanna lose some of the records because that's just lost revenue. All right, what about the census data? What kinds of things are important there? Any ideas? Yeah, the idea is to be able to run a lot of rich queries on the data. Searchability is, well, perhaps filterability would be a better way to put it because normally you don't want individual data. You want groups of people. So yeah, so you want fast rich queries. And in fact, you often want them on aggregates of data. So rather than going through every record, typically the trick is to preform aggregates of similar users that you can use later on in queries. All right, so, okay, so the set of properties that are important for consistency, accuracy and durability, those are the, in captured in the acid axioms, which we'll talk about this time. So those are the most important things for a traditional database, customer database like AT&T. For Yahoo, we'll just mention this briefly later. The emphasis is not so much on consistency, it's really on timeliness and availability. And a common name for that style of database is a base, base, base, database. And finally, some technologies are commonly used for statistics or OLAP and ROLAP, which involves precomputed aggregates and various axes of a data table so that you can quickly compute aggregate statistics by slicing and dicing the dataset. All right, so anyway, so today we're gonna focus on the relational style of database. So describing databases starts with a data model, which is abstractly this collection of entities and relationships, specifically we're gonna be interested in the relational model. Then to deal with a particular question or a set of data you wanna define a schema and that defines what the entities are, what their relationships are abstractly without looking at specific instances and how the data is organized. All right, so the relational model is the most used data model. It comprises relations. So relation, we'll define on the next slide, but intuitively it's a table and the relations comply with schema which specify what are the columns, what are the types of the columns, what are relationships between indices across tables and so on. All right, so to be more specific, if we have some information about students on a campus, the schema could comprise these three relations and within each relationship, within each relation we're gonna have an index, a unique index, in this case it's a student ID, then the student's name, email, age, and GPA and each of these attributes is gonna have a type. Courses similarly have a unique index, a name and finally let's say a credit which is an integer value. So in the third relation is the enrolled relation which is now indexed by both the student ID and course ID and comprises the student's grade for that string and also includes a integrity constraint or relation constraint that this SID must reference the student's table. So this constraint here ensures that the student table is kept consistent with the enrollment table and if there's an entry for a given student here, there actually is an entry in the student table. And finally, whereas these two have a unique key which is just a single field, though each instance of the SID returns a single row in the table, the enrolled table has a composite key which is student ID and course ID because these individually can occur in multiple locations in that relation. All right, so as well as the, I guess primary tables in this database, you can have virtual tables which are views which are defined by queries but which otherwise function as though they were real tables. So here, excuse me, we have a course information virtual table which is indexed by the course ID and just returns the enrollment. And it's createable by a SQL query that just says from the, let's see, no, this is the course info view that's being created. This is the name of the table that's being created and as is definition of that table and it's just retrieving the ID and then account associated with that ID. So it's the number of records that match a given ID. You have to group it by ID in order for this to work and the count will be an integer that counts the number of entries in that table that match the course ID and therefore that's the enrollment in that course. All right, so views are often useful. They can simplify the representation of the data set by taking common derived relationships and then making them without creating new table and using new storage. You could find a view which will create a shortcut and sometimes get materialized to accelerate queries that involve those fields. All right, so a relation, the name relation comes from elementary mathematics. So if you remember the first time you saw a formal mathematics it was expressed in terms of functions and relations. So functions are things that take arguments and return usually unique values. Relations generalize the idea of functions in a relation you can have values for X and Y but also multiple values for Z and therefore it's richer. You can, there's something like multiple valued functions that they allow you to express arbitrary relations between things or attributes that individuals have. So informally they are actually predicates on the fields or attributes of entities or equivalently and the relations themselves can be thought of either as the mathematical predicate or as the set of tuples that satisfy the predicate. If you wanna think more concretely it's just a list of tuples. So the tuples, the usual name for the rows in the table. The entirety of the tuples in the table is equivalent to defining that mathematical function which is true only of the rows in the table. Okay, so a database management system is a software system designed to store, manage and facilitate access to a database. And databases comprise these two major language components. The data definition language which is where the schema are expressed and potentially relations between fields and the data manipulation language which is the query language that allows you to do, to run queries and also do updates to the data. And for most systems there's a variety of constraints that are enforced by the system that guarantee you durability, consistency and atomic updates. All right, so starting off with a query here we have a simple SQL query to select some fields from a database that satisfy a given constraint. So the select query first of all lists of fields. This is a MySQL syntax here which is the students is the name of the table, S is an alias for that table. So and then the where clause specifies a constraint on one of the columns of that table. So here the GPA field must be greater than three. So we're gonna do a couple of things. We're gonna project projection is pulling out specific columns. Selecting is checking this predicate and only returning tuples that satisfy that predicate. The originating table is down here. So the idea of the database system is among other things that includes a query optimizer that's gonna take this abstract specification and map it into a series of operations which you can think of as a data flow on this really a stream of data coming from the originating table. In this case it's doing the select first so it's filtering by that predicate there and then it's doing a projection of the full width tuples. In this case those operations are commutative. Being careful if you're careful to check that your predicate is one of the retained columns if it's not you can you should add it to the list of columns in the projection. But in this case it's there anyway so those two steps are commutative and the query optimizer's gonna figure out where the projection should come before selection or vice versa. Either way it's gonna give you a correct execution in this case. And you can see the stream conceptually the data's flowing upwards. Control wise sometimes databases use kind of a recursive iterator scheme that propagates down. And basically the iterator here calls select and says calls next and then applies a filter function select when next is called on select it calls next on the student database. And that arrangement has some advantages in certain situations. So they're actually two different styles of processing of this data flow. One of them is purely bottom up one of them is top down. So select is doing the filtering by GPA and projection is just pulling out the desired columns. All right so here's a more complex query. Here we're trying to count distinct entries and we have in this case two tables and a join condition and an additional filter constraint which is on the credits field. So joining you can specify different types of join hash join or sorted joins. If the data is already sorted it'll almost always do a sorted join. But the join is going to pull tuples from these two tables somehow match the keys efficiently. If it's a sorted table then it can do that with a merge that's gonna be linear time. And then pass up matching tuples that have corresponding course IDs from these two data sets. And the join tuples will comprise all of the columns from the union of the two tables. Then the selection is going to happen I'll just bring that up in a second. And then finally the count distinct is going to need to be sorted I think. Well never mind. Okay so the columns coming up from the two tables are listed here. The join is gonna produce a union of all the columns with just one CID field because they've been matched between the two tables. That's gonna go up to the select which is gonna apply the predicate filter. And count distinct it's going to actually need them to be sorted by SID so that it can count contiguous blocks. So there's gonna be an implicit sort in here in order to do that. Anyway after that's been done the count is gonna return a number it's gonna collapse each distinct record based on SID it'll just count how long the runs of SID are and return an integer. And finally you'll get the number of students with distinct IDs from that list. Okay so an important idea in query processing is the idea of a transaction. So this is the fundamental way that you can make many different clients accessing the same database avoid trampling on each other's transactions. So transaction is an atomic sequence of actions reads and writes that takes the database reliably from one state to another. In both cases that state has to be consistent which means it satisfies relational constraints integrity constraints involving keys from one table to the next and also any consistency constraints such as fields being positive, balances being non-negative and so on. So the transaction both transforms the data in an all or nothing way and then also verifies that after the transformation all of those constraints are satisfied. And if it can't verify that it's gonna roll back and undo the transaction. So a simple case involves, let's see two accounts here and a transfer of money from a saving to checking. So if you have these two accounts before the transaction the balances are 1200 your total balance or your total assets are $1,200. After the transaction you've devided the savings account and bumped up the checking account. Your total assets are still 1200 which is critical. You don't wanna lose any of that money. And we want it to be consistent. So the consistency here is that consistency is that the balance, total balance is 1200. Now, checking the total balance is actually not one of the consistency constraints that you can write down. So in this case it's up to the writer of the transaction to make sure that a debit from one account matches a credit to the other. The database provides some facility to check the results of queries but it's limited to single rows of the table. So if things like balances are being non-negative that can be added as an integrity constraint but not that two rows somehow match each other or have a common sum. Nevertheless, any reasonable transaction is going to enforce that kind of constraint and making the transaction atomic guarantees that when the writer does the right thing, the outcome is gonna be consistent. So in other words, the writer is responsible for ensuring that an invariant holds such as the sum of these two fields should be the same before and after and then atomicity makes sure that all of the steps necessary to make that happen actually do happen. Okay, so it's not as easy as it sounds because we wanna have high performance in the system which means we want concurrency which means we want these individual updates hopefully to be not purely serialized but let's look at the serialized case first. We do want the illusion of users running their transactions as though they own the database and you could do that by using a locking system and basically having users lock either the entire table or parts of it that such that they can guarantee that all of the steps in their transaction complete. But on the other hand, you have lots of users hitting a database and you want all of them to have a reasonable response time even in AT&T's case and certainly in the case of banks, you want updates to your accounts to propagate reasonably fast. All right, so we have many transactions happening once we wanna have a reasonable throughput. So we have to somewhat interleave these steps in all of the transactions. So how do databases help with this? So the asset steps as we'll see in a second provide the necessary protection against interaction between the different users trying to access the same database. So a transaction is the abstract atomic series of actions that the user wants to see. It can be broken down as a series of reads and writes but should be executed all or nothing. All right, so there is a notion of locking here that's important for consistency and databases do use locks. We'll see in a minute though that they also interleave the individual steps but for right now let's focus on the serialized version how that works. It works using locks just as we did with process coordination earlier. All right, so but it's important to understand that locks in databases are different animals from locks in the operating system. So a database lock is a higher level notion. It's closer actually to a monitor. And in fact it's normally designed as a reader's writer's monitor. So in other words it supports there's an asymmetry between reads and writes. Why would that be? Yeah, all right, that's exactly right. So reading and not mutating a field is a safe operation. It's a safe shareable operation. So many different clients can read the same information. So there's a lock involved because you wanna prevent someone writing while the readers are reading. But on the other hand they can share the information. They can share the information concurrently. So reading locks are shareable or at least they're I suppose you can have multiple reading locks but only a single write lock. So it's essentially the same setup as the reader's writer's monitor. So as soon as somebody gets a write lock though they prevent all the readers from accessing the same data. On a DBMS though the locks are managed because the DBMS is typically distributed or at the very least it's got many processes running if it's on one machine. There's an explicit process called a lock manager that it's just responsible for handling acquisition and release of locks and keeping a consistent table of who has which lock. That lock table has to be highly available because clients are trying to access various parts of the database. And so this process may in fact spawn separate threads but in any case it's devoted specifically to handling acquisition and release of locks. So we're not gonna say more about this. It's one of the major components of a database system. It's one of the five major building blocks. But it is different conceptually from what we've talked about before. It really involves updating the state of a separate table so it typically takes much longer than simply setting a traditional lock using a semaphore or something similar. Databases typically do support low-level locking as well but the locks there are called latches. Latches are short pieces of code. Calls to the lock manager are often in the process calls and they take a long time. And so a fundamental question when designing the system is to think about the granularity to lock at. And most databases, probably all of them actually support locking down to the row level. So you can either lock the entire database. If for instance your transactions are typically updating many different tables it may make most sense just to lock the whole database to make sure that things can't possibly interact. Transactions can't interact. You can also lock at the table level. That's an easy proposition if you're only making updates to one table. But a lot of the time tables are shared by many users and so it's undesirable to lock at that level. And ideally you'd like to just lock rows that you're actually changing. So the more granular the locking the better generally speaking in the sense that you can have higher potential concurrency because other clients can access other rows. But keep in mind that the cost of actually getting the locks and releasing them is quite high. So if your client is doing a lot of transactions it's making a lot of updates. Let's say it's one bank machine talking to another bank server. It's going to make actually a lot more sense to lock either the whole database or a bunch of tables because then you're avoiding the overhead of dealing with the lock manager for every record. You only have to deal with it once. And you'll have exclusive access to the table which is going to slow the other clients down but overall the throughput's going to be higher that way. So the most common case is to lock as granularly as possible but there's a few cases where you don't want to do that. Okay, so it's very easy to come up with scenarios where things can go wrong in databases. Let's say we have a simple two column table, a key value store and two clients that want to update it. First of all, if the client A is trying to do an update, there we go, it's going to first read the contents of the row, the rows index by the key. And yeah, go ahead. Test and set, it somehow has to be attached to a specific part of the database. So it sounds a bit more, it sounds like, okay, so it sounds like you're suggesting sort of pushing the update instead of getting the data. You're trying to do an atomic transaction sort of at the data itself, is that the idea? That is a model that's used. Basically, if you push a piece of code close to the data, you can execute the code usually very fast. It's locked, but very locally. You can sometimes push atomic operations all the way to the tables, to individual rows of the tables. That's actually, there's some newer systems which support that in hardware to get good throughput without, yeah, basically they have essentially locking really at the memory location or small vector level, but it's pretty rare. And really the difficulty with test and something like test and set, there's something close to test and set anyway involved with updating the lock table because those interactions have to be atomic, but they also have to be visible. Everyone has to be able to see which rows or tables are locked. So it really ends up being more like a monitor. Also, you wanna have a sort of a distinction between read locks and write locks. The read locks are a lot easier to share than the write locks. So it ends up pushing it really into that monitor pattern. Yep, yeah. Yeah, I mean, I don't know if that's supported in hardware, it seems a reasonable idea. I mean, I know access, there are access control domains that are typically organized as subsets of tables. So just in terms of access control, that is actually pretty common. Whether you can lock up a group of tables, I'm not sure. It's a reasonable idea. All right, so let's continue with this example. So, client A is trying to update the contents of the key value store at key number 17. So they first have to retrieve the value. Before they finish their update, let's say client B grabs the value that's there, they're gonna get the same value back. If client B is able to update it quickly, they're gonna push a new value, which is their value. In the meantime, client A is still working on their update. Eventually they finish doing it, they actually decremented or decreased the balance. So the decreased balance goes in there, but the correct answer should have been, from either ordering, one transaction's decreasing the amount by 75, the other one's increasing by 25. So the net result, if these happen sequentially, should be 50. But in fact, one transaction completed successfully and the other one basically was overwritten. So it's as though client B's right never happened. So unless we're careful about locking, just as with locking on a sequential machine or on a single machine, you can get problems. All right, so client B's update was lost, it was overwritten. The simplest solution is to use a lock to acquire a lock from a lock manager. So first of all, we grab, in this case, it's gonna be a right lock. It's gonna last this long, we'll see in a minute. Client B's gonna try to acquire a right lock as well, but they'll be rejected the first time. Client A is gonna grab the value of the key value store, get it back, do their update and then finally put their value into the store. So once they get it back, they probably wanna do a get there to verify that it happened. Once they're satisfied that it's happened, they can release their lock. It'll be clear and now client B can acquire it. So how does B figure out though that this is all happened? Two ways. Okay, well first of all, what could they do? How could they figure out that the lock is free now? What's the simplest possible way? Yeah, I could just keep asking. It could poll the lock manager. The other way is to maintain a list of waiting clients at the key value store, actually at the lock manager, the key value store. So then they'll be, the lock manager can send a message to the members of that waiting queue in order and telling them that the lock is free now. Okay, so because this is a distributed system, we have nowlier issues of reliability than we would have in a typical OS. What happens if the client holding the lock crashes? It's really a two-part question. The answer is bad things, but what can we do? The real question is what do we do to avoid that? Yeah, all right, so well, timeout is one option. Timeout's a tricky though, because database transactions can be really expensive. So that's sometimes an option, but it's a very difficult option. What's a, or another option that you can think of? Yeah, so the suggestion was detecting connection loss. So something that's detecting, that's continually checking that the client is actually still running is important. So yeah, it's called a heartbeat. It's a message, some kind of message back and forth that just says are you still going? You're still running this queried. You still need the lock and the client responds. If the client does respond, you keep on going. If the client fails to respond after a timeout, then you should release the lock and allow somebody else to get it. Yeah, so, but two problems. One of them is that the transaction itself can take a long time. Secondly, latencies can be really large for distributed systems. And so we want to avoid, you know, it's good to have this critical section effectively, this distributed critical section in terms of integrity, but it's bad in terms of performance because there's a good chance for small transactions that we're basically waiting for the network all the time. And the clients monopolize this particular piece of data and we get low throughput because of that. So this is a good first cut solution that we're gonna strive to improve performance by allowing more concurrency. All right, so, and again, we talked a bit about lock granularity earlier and some of the trade-offs. So let's just keep going. So a better solution, a more practical solution is to try to interleave the individual steps of the two transact or two or more transactions. We still don't want to guarantee that if we do interleave operations that the end result is the same as though they were completely not interleaved. So a transaction is the result of performing a sequence of updates one after the other as though there were no other updates happening. So we perform the sequence of reads and writes as dictated by the transaction, ignoring the other, what's actually happening. When we implement the transaction, we may actually interleave the operations but we're gonna guarantee that the result would be the same. Okay, so here's a classic example of a transaction where basically we're doing a transfer of funds. So we're gonna update Alice's balance, debit $100 from Alice in the accounts database, also update the branches, the bank branch that Alice has her account at. That's this nested query here is returning a branch that houses Alice's account. So the balance for that branch has to also decrement by this transaction amount. Alice is transferring funds to Bob. So in the accounts database, we're also gonna increment Bob's balance by $100. And finally, for Bob's branch, that branch is also acquiring an extra $100. So for consistency with this transaction, we have to update all of these four fields in these two tables. All right, the net effect is to transfer $100 from Alice's account to Bob's account. Also transfer $100 from Alice's branch to Bob's branch. Okay, so to do this safely, we turn this into a transaction. So the beginning of the transaction is here and all four steps have to be completed successfully before we commit. So if any step fails, the result of the transaction is a nothing. The state of the two of all of the tables goes back to the state before we started. All right, so now acid properties. Acid, so first property is atomicity. All actions in the transaction happen or none of them happen. Consistency, transactions maintain integrity constraints. Balances can't be negative. The tables that include the account information, if there's records for individual users, then for every user ID in one of the columns of one referencing table, that element has to exist in the customer list table. Isolation, isolation says that the steps that one user does or one client does must not interfere with the steps that another client is doing. The net result of isolation is that however the steps in the transactions are actually executed, the end result is going to be as though they were executed independently one after the other, in some order. And finally durability says that if you actually do successfully complete a transaction, then the effects persist. Somehow there's a way to store enough information that the transaction won't be undone or partially undone by some system failure. All right, so acid is one extreme, as we suggested in the list of large databases, it's one extreme in the spectrum of different styles of performance in databases. So what's the opposite of acid? I'm already sure, we saw it on one of the other slides. Yeah, base. All right, so base, it comes from a Berkeley paper from Armando Fox and a few others. And base is basically available soft state and eventual consistency. So it contrasts with acid, which emphasizes consistency and reliability of the data, but is extremely conservative in terms of availability. So acid databases and acid systems generally are in a sense inconsistency intolerant. They're like ATM machines. If anything goes wrong, they eat your card and shut down. So for an acid system, it's more important for it to be in a consistent state. So normally if part of the system fails, it'll often, if it can't recover, it'll just stop working. In a base system though, the emphasis is on providing availability for applications where the user experience involves being able to still make queries or to be targeted with advertisements. So availability is really a critical aspect. There's also less emphasis on recoverability. Base systems generally can work fine with part of the database missing. So there's less of an emphasis on durability and recovering exact state after a failure than on just having the system keep running. So the notion there is a soft state, which is the opposite of durability or roughly the opposite of durability. And finally, instead of having an emphasis on consistency at every time step. So an acid database is consistent before a transaction and the state at the point of a commit instantly changes to the state after the transaction. Base systems support inconsistent state for some period of time. So consistency is lower here and in fact the system can be an inconsistent state's part of the time. But that matches the use models for those kind of systems. All right, so atomicity. The idea is that the commit effectively instantly changes the state of the system as seen by other clients at the point of the commit. If there's some kind of failure during the transaction, then things roll back to the state beforehand. So atomic transactions are atomic in the sense that all of the updates that are occurring as part of the transaction transform the system in such a way that it's either doing the complete transaction or no part of it. Part of making that work is that the system has to log actions that it's been doing. So that it can undo the effects of partial updates that it's done to some of the tables and allow it to go back to the previous state before the transaction. All right, so consistency in acid involves integrity constraints. We've mentioned a few of those. So basically foreign keys have to really match. The key from one table to another has to be matched by appropriate indices in the other table and predicate constraints such as values being non-negative and so on have to also be satisfied. The system will actually check the integrity constraints after a transaction and it will fail the transaction if they're not satisfied. Okay, but again, a last point to reiterate is that the database itself doesn't understand the semantics of those transactions. So it's not able to tell that debiting, that you should always debit from one account and add the same amount to a different account. That's beyond the scope of consistency. So isolation is the important idea that transactions aren't interfering with each other. So each transaction should transform the databases though it were running by itself. And techniques of providing isolation include pessimistic ones, basically preventing trouble and optimistic ones that take risky steps and then try to undo the effects of failures. All right, so finally, durability is the idea of making sure that crashes or failures of parts of a database don't cause partial failure of transactions. So you have to make sure after a commit that basically the database doesn't become unrolled if some particular disk or node goes down. It should be the case that when the system comes back it's coming back in the state after the transaction. So if the transaction hasn't really propagated throughout the system there must be enough log data to make sure that when it comes up before it's actually available it's redone those updates and propagated them completely. Okay, so let's review, yeah, question? Yeah, I mean, base consistency normally relates only to the state of different nodes in the system, not really to consistency in terms of, in the ACID sense. So, and normally it's not providing the same kind of atomic update either necessarily for, yeah, I mean that exactly is what happens in name servers in DNSs. They're often inconsistent in the sense that if a dynamic DNS server updates in some domain somebody changes an IP address. The other DNSs will have old information and provide old addresses for a while until those updates propagate. And there's not a lot you can do about that other than shutting down, you know, if there was an update you could also shut down the other nodes as soon as the update or before the update was made actually. You'd have to first of all lock before the update happened but that's a really bad idea obviously. So it's better to do the update, keep both of the servers running and just deal with the cases of nodes that get the stale information. You know, the worst case is they try to access something that's not where they think it is. They have to go back to get the correct address but the information propagates then usually after not too much time and then eventually the system is consistent. In fact, there's a few different mechanisms for updating the DNS data. So anyway, it matches well with the use case and the idea there is that having the stale information is not very destructive and it's typically not very destructive in the other usage scenarios for base systems. Okay, so quickly let's review some of these ideas. So let's see, a relational data model is the most used data model. Say something about that on one of the slides. Yes, it's a slightly tricky question. I mean, I think it's certainly most of the data tables in the world are relational data tables. It is the case that there are massively large data bases now that are not relational. So in terms of volume of tuples, perhaps this is not true but anyway, almost all of the actual database entities are relational. Transactions are not guaranteed to preserve the consistency of a storage system. True or false? Well, it's a double negative, yeah. So it's a false, yeah. That's the point. The point is that they do guarantee consistency, otherwise they're gonna roll back. Database, DBMS is used a log to implement atomicity. True or false? Well, we didn't really talk about this but they can use the, yeah, we should resolve this next time. They can use the log to implement atomicity. We really talked about it in the context of durability but they can also use it to make sure that the transactions are fully committed. All right, durability isolates reads and writes from all other transactions. Right, it's not the durability, it's isolation. All right, so let's take a short break and finish up the last few topics. All right, so in the time that's remaining, let's look in a bit of detail about how we implement isolation. Well, one mechanism for implementing isolation and we'll get into some of the other constraints next time. So let's imagine that we have a couple of transactions. So transaction one is to move $100 from account A to B and transaction two is to move $50 back from account B to account A. You can see them represented there as two steps. So, and each of the updates, these individual expressions is comprising a read and then an addition or a subtraction and then a write. So if you wanna decrement A by $100, we've gotta read it first then perform the subtraction and then finally write the updated value. You know, the actual operations on the database are reads and writes. So transaction one, here are the steps. What the database sees is the read of A, the write of A, it doesn't see the update. And then a read of B and a write of B and similarly for the next transaction. So if we start with, say $1,000 in A and 500 in B, what's the legal outcome? What's the outcome we want from running those two transactions, right? So A, we're moving 100 from A to B and then 50 back. So we should just in effect move 50 from A to B. So A should drop by 50 and B should be incremented by 50. But these steps are gonna occur in general if we allow them to be asynchronous. Let's assume first of all that they're ordered in the same order from each client. But the order between the two clients, between the two transactions could be different. Well, what do you think would happen with this order? It's basically a serialization of the two transactions. We can work through it. We're gonna decrement A first, increment B, then decrement A, increment, sorry, decrement A and decrement B and it'll give us the right answer. If we flip the order, you can work through it again. This time we're adding the 52 A, removing it from B, finally removing 100 from A and adding it to B. So the result's the same in both cases. And we've really just done two different orderings of the transactions. The ordering's not, doesn't matter with these transactions. They're additive transactions, so they're commutative operations. All right, what about this execution here? Let's trace it through. So we updated A first, we incremented A, then we read A and incremented it with a $50 as part of the second transaction. We decremented B by the same amount. And finally, we incremented B at the end of the first transaction. Is that good or bad? It's, you can sort of see that it's good because we're in each step, doing a pair of steps on current data. We don't have any outstanding rights that are pending. So that's the right answer. But now look here. Well, let's trace it through if we read A first and then do the second transaction. We're updating A and B. So we've done the increase of A and the decrement of B. But now A has an incorrect value. It got updated here by the second transaction. So that second transaction is interfered with the first one. And we no longer have integrity of this first transaction. So it's likely to cause trouble and in fact it does. So we end up with basically $50 missing. So the correct, the increment of A down here got erased in effect by the fact that we had stale data for A in the first transaction. So it's serious, it actually causes a net loss of money. So we can avoid this by running one transaction at a time. But as we said, that's gonna slow down the system. We're gonna have to complete all of these steps. Serially, we'd have to hold a lock for each transaction and that's gonna make the system quite slow. There may be long network delays in each of these steps. And so it's an unacceptably slow outcome. So transaction scheduling is a means of reordering the steps in transactions such that they can be completed safely. So we wanna show that the result of some reordering is safe and it will be safe if it's equivalent to serial ordering. And there's two serial orderings for these two transactions. We can either do T1 first or we can do T2 first. Both of those are by definition safe. And if we have some other ordering, ideally we'd like to show that it's gonna produce the same result as one of these. All right, so that's the goal of this step. Here's a new schedule, we've interleaved the steps. How do we check whether the new schedule is equivalent to one of these two? So a serial schedule is one that does not interleave the operation, so it's one of the two examples we just saw. Equivalent schedules are, any other schedule that we can show is equivalent to one of those serialized, serial schedules, right? And a serializable schedule is one of these two. One that's equivalent to some serial execution. So it's going to do the same operations as though they were performed one transaction at a time. Okay, so we can do a variety of things that are not safe, though. So let's look at some of those. The first kind of conflict we can have is called a read-write conflict. Here we did a read in transaction one, and a write from a different transaction was interjected before we did another read here. So these two reads, which are consecutive in the original transaction, they should produce the same output, they're gonna produce different output. So yeah, so T1 in general won't be a transaction. All right, so an example of this is if Mary and John both wanna buy a TV set on Amazon but there's only one left in stock, John starts his transaction, reads the count of that item. In the meantime, Mary logs in and actually purchases it, changes the state of A, John starts to check out and somehow the item's disappeared. A write read conflict involved