 Good morning everyone. So, today we are going to continue with concurrency control and finish up with recovery and your lab session today also uses you know covers these two areas. Covering concurrency control in the lab is a interesting exercise because it means you have to start up multiple windows in parallel and run queries and you can observe what is going on. So, your lab today has several exercises which require you to do such things. Observing recovery in the lab is a lot harder because it is hard to crash the database. The database generally does not crash. You will have to physically reboot the machine if you really want to test it and even then it is hard to see what exactly was happening what happens during recovery. It is all hidden. So, we do not have a detailed lab on recovery although we have a few exercises which show you what happens when a transaction rolls back. So, there is not much lab work on the recovery front, but certainly the concepts are very important to understand how databases do what they are doing. Now, some of these concepts in recovery are used not only in databases, but they are used in many other settings including operating systems, distributed data storage systems and many other places. So, they have a fairly wide applicability and that is an extra reason why we study recovery in the context of database systems in a course. So, let us start off by perhaps taking any questions that people have from yesterday. So, if you have a question please raise the A-view flag or send the question by chat and I will be happy to spend a few minutes taking questions from yesterday's topics. I see a flag up from Anna University Chennai. Anna University, do you have a question? If you do, please go ahead. Regarding the quiz 2 of our yesterday, how organizing the transactions, how to group the read and write order in T1 and T2? The question was in the quiz yesterday, how do you decide on the order of T1 and T2? I assume you talked of this particular schedule and the question was how do you decide the order of T1 and T2? So, what happens is in this case we are trying to understand what happened. This is not what actually happens when the database is executing in the following sense. What you see here is a schedule which could occur when the database is executing. So, we are trying to see if the schedule is serializable or not. So, this checking whether it is serializable or not does not actually happen in a real database system because we already follow protocols which guarantee serializability or perhaps some weak forms of serializability. So, what we are doing here is we are trying to understand how to make sure that a schedule will be serializable and what we did is we looked at example schedules and then saw you know can we swap instructions and make them serializable. Now, there is no specific algorithm here for it although you could ensure come up with the algorithm it is not of any importance. What is important is to understand what you can swap in these examples. So, that you can understand when a schedule is serializable and when it is not. Now, among the things which we did not cover in detail we will talk about it if time permits is given a schedule that is produced by a particular concurrency control mechanism. How do we show that that schedule will be serializable? To understand that we have to know what are the operations we have to know what it takes for a schedule to become serializable by swapping instructions and so forth. So, that is basically what our goal is when we deal with these examples. So, in this particular case we could see it and figure out that we can swap the first instruction of t 2 and the second instruction of t 1 and come up with a schedule where t 1 is first and then t 2. If you want to do this in general if you are given an arbitrary schedule and you want to know is it serializable and if it is what is a corresponding serial order for this schedule there is a way to do it and that way is to introduce is to create a graph where the transactions are nodes and there is an edge between any two transactions it is a directed edge from t i to t j if t i did some operation first and t j did a conflicting operation afterwards. So, what does that mean? If t i did write and then t j did a read there is an edge from t i to t j. If t i did a read and then t j did a write then also there is an edge from t i to t j in the order in which it happened in the schedule and finally, if both of them did a write again there is an edge from t i to t j if t i did the write first and then t j did the write. So, what we have here is a graph with transactions as nodes and the edges indicate a precedence order forced by this schedule. So, what we do is construct this graph and then we want to see if there is an ordering of the nodes which are transactions here which does not conflict with any of the edges. So, this turns out to be basically a topological sorting if you are familiar with it if you are not do not worry it is a fairly straightforward way of taking a graph and ordering the nodes such that any edges in the graph go from a lower numbered node to a higher numbered node. In other words the lower numbered node would come first in the serial order the higher numbered node would come later in the serial order. So, any conflict edges remember the conflict edges are created from the schedule and indicate that one of the transactions ran first then the other did that operation and if you can order the nodes of the graph such that all these edges are going forward from a lower numbered to higher numbered then everything is fine. Now, of course, there are graphs where we cannot actually do this because for example, there is a cycle in the graph there is a cycle which means that T 1 did something before T 2 in the order of you know T 1 write something which then T 2 wrote or any of the other conflicts eventually you can have a cycle. If you do not do concurrency control a schedule can have a cycle such a schedule is not serializable in fact, it has been shown that it is exactly equivalent. If you can order the nodes of the graph such that you know edges are always going forward that is the same as saying that the graph has no cycles which is the same as saying the schedule is serializable. However, in the practical situation nobody is actually going to create the graph and then sort the nodes because we already have a concurrency control mechanism in place. So, this is more for formally proving things about this. In theory you can have a concurrency control manager which actually constructs this graph and then decides whether to allow an operation or not, but the overheads would be tremendous. So, nobody actually does anything like this. I hope that answered your question back to you if you have a follow up question. So, good morning. This is about the transactions that is in real time we face a problem in the ATM that is even before getting the amount in hand we it is as though our tables are updated that we have retrieved it. So, what exactly happens internally? Thanks that was a very good question and something which anyone who learns about transactions ought to wonder about and the question is if you go to an ATM and withdraw money then how does the you know there is an order there are two things which happen. One is that it gives you the money the other is it debits the amount from your account. The question is how can you guarantee atomicity? What if you if the ATM first deducts the amount from your balance and then does not give you cash you are going to be very unhappy or what if it gives you the cash and then is not able to deduct the money from your account the bank will be unhappy. And in fact if you think about it these are two separate actions one is updating a bank account somewhere and the other is physically handing out cash here. So, it is actually impossible to actually do this atomically even inside a database nothing is really atomic you get the illusion of atomicity by rolling back actions which happen in the database. Now, it is easy to roll back an action in the database because you know we will see how to do this using logs, but how do you roll back an action outside the database and how do you even detect that something went wrong. How does the database know that the money was not actually paid out to the customer and there are actually several mechanisms for this. The ATM itself is able to will keep a log of when the cash is actually handed out. So, the transaction is split into two steps. The first step goes and contacts the bank actually if the bank is not contactable most the ATMs will give you the cash anyway assuming that you are not cheating and what it will do is it will first contact the bank and it will tell the bank deduct this much amount which was requested. The bank will confirm yes the account has enough balance I am deducting it go ahead. When the ATM receives to go ahead it will actually dispense the cash and it will record the fact that it dispense the cash somewhere. Now you may have observed current ATMs will give you all your cash together as one single ward of money. There were ATMs before which would go release one note at a time one note after another comes out. Now think in that older ATM you may have a situation where power goes off actually they have UPSS and so on, but let us say the machine crashes or whatever after giving you five notes, but not giving you the other five notes which it was supposed to give you that makes life more complicated. So, what it has done is it has actually turned giving you the cash the actual physical act of delivering you the cash into one atomic operation which is a whole bundle comes out. Now there are still potential problems what if something went wrong after it counted the notes, but before it gave it to you. The ATM will generally have its own log of when operations happen. So, when it spews out the money it will log saying the money was dispensed. Now supposing there was a failure at that point the ATM is brought back up and it says wait it appears that the something was in progress it records what was in progress. So, it says there was a transaction in progress, but I did not hand out the cash. So, at this point it can send a note in effect back to the bank saying this particular transaction did not work out I could not hand out the cash and then the bank will put the money back. But if in between you went to the bank and said what is my balance the bank will say you know you withdrew you know 10,000 rupees from this ATM and you will say no I did not get that money and you can have a fight with the bank manager it will get resolved eventually. So, there is a temporary inconsistency which eventually will get resolved assuming that the machine did not completely go mad it did record the fact that it was going to give you the cash, but did not give you. Now there are certain situations where some mechanical error happens and the ATM thinks it gave you the cash, but you did not get it. What do you do in such situations? There are actually litigations which go on and banks have an interesting mechanism to deal with this. Most ATMs I think pretty much may be all ATMs these days have a small camera and they are actually recording what is going on and as a result if you actually got the cash and then you say you did not get it the bank has a video record to show that you got the cash. On the other hand if you did not get the cash the bank can look at that video record and say yes it is clear that this customer did not receive the cash. So, we will refund the account. So, the video recording is in some sense a log of what happened and it can be used to recover. So, this is actually quite interesting. So, there are different notions of the log. A log says what happened and helps you undo or redo events. So, what we have now is a video log at a higher level that log is not something the machine can use automatically and do recovery, but humans can do recovery at a higher level. So, this in fact is actually characteristic of many systems. Certain failures in fact most failures will be handled at a certain level if it cannot that failure is reflected up to the next level that next level we try to deal with it if it cannot it will be pushed up to the next level and so forth. As an example maybe the human says look I received money, but you cheated you gave me only 9000 rupees not 10000. Now, what is the bank do the log does not have enough information to see exactly how many notes you got and then it goes up to maybe the next level manager who tries to see is this person likely to be trying to con the bank or is this person honest. They will check the machine and eventually they will come to a decision and either say we think you are telling the truth here is 1000 rupees or they will say we think you are lying go away you can see us in court if you want and then the court is the next level of recovery in case the person is mad with the bank. So, as you can see there are multiple levels of recovery any real system actually has to have such levels. Hope that answered your question back to you. Sir, I would like to know the difference between the serial schedule and serializable schedule over to you sir. The question is what is the difference between a serial schedule and a serializable schedule. A serial schedule is one where the transactions run one after another. So, in a serial schedule there is no concurrency. First one transaction runs it completes it commits or rolls back then another transaction starts it completes either commits or rolls back and then the next one and so on. So, they are one after the other this is a serial schedule. In contrast what we are trying to say is look for performance reasons we want schedules where instructions of different transactions are interleaved, but the schedule is somehow equivalent to a serial schedule. So, it is not actually serial, but it is equivalent to some serial schedule. So, the notion of serializable is that this schedule is not necessarily serial, but we can move the instructions around in a manner that conflicts are not violated and come to a serial schedule. In other words we can show that there is some serial schedule which is equivalent to this particular schedule. So, then this particular schedule is not serial, but it is said to be serializable. So, that is the difference between serial which is actually serial and serializable which is not actually serial, but can be shown to be equivalent to a serial schedule. Back to you if you have any follow up questions. Good morning sir. So, I am going to do the brain reverberation system. So, there we have an option of selecting the disable seat by taking an extra amount of 10 rupees or so. If that is done that is the same seat number which we get in our bureau but if we don't select our standard, if we leave it to the automatic selection, I get a seat number on my e-reverberation, but in the actual thing during the journey, I get a different seat number, not like the use of every six to seven seat numbers. Okay. The audio was not very clear. I understood that the question was something about going to an airline system and they allow you to choose a seat number by paying a little extra money. The last part was not very clear. I think what you said is that sometimes you choose a seat number, but when you actually see the seat allocated, it is not the same. Is that your question? Back to you to confirm or reply otherwise. Sir, the question was correctly understood, but it was a brain reverberation system. Okay. So, it is not an airline reservation system. It is a railway reservation system. Okay. So, then this is a good question. So, in a railway reservation system, if you are, let us say there are two modes. One is an online mode where you are first entering all your data, that part is still not started a transaction. It is collected all the information required to run a transaction. Then the system says, yes, I have seats available. Go ahead and make a payment. Now, you do an online credit card payment. It takes a few seconds. Then you come back and by then something could have changed. Now, there are two possible options for running a system like this. One is the moment the reservation system hands you over to the, you know, tells you that there is a seat available from there till the point where you finish the credit card payment. It can lock those seats, meaning it sets aside that many seats for you. So, once your payment is successful, it will give you those seats. But there is a problem with this, especially towards the end. If many people are trying to grab the last few seats and somebody sees that there are five seats and then goes away and the terminal is still hanging there. They have not completed the booking. These five seats cannot be given to anybody else until something happens here. That is a very, very bad idea. You do not want to do that. So, what this means is that a transaction, if it is boundary extends beyond what happens in the database, but actually goes and covers what a human is doing outside of the database. Such transactions are called long running transactions. So, they involve a database lookup or update, human making a decision, coming back and then another database update typically. Such transactions if they have to be atomic, the cost of concurrency is very, very high. It will seriously affect the functioning of the system. So, pretty much no system in the world lets you do such things, except in a few cases where when you do a booking, a hotel booking, they may say you have 20 minutes to complete this booking. So, airlines used to do this. You could make a booking without making the payment and then they would say you have five days to complete this booking. Till then we will hold the seat for you. These days they do not do that. So, there are situations where you have such long running transactions, but in most cases they are not used. So, what this means is after you make the payment and you go to the next screen, by then somebody else may have also seen there were five seats left and booked five seats. Their credit card payment or whatever went through fast and they got those five seats. You also saw there were five seats, but by the time your payment is processed, those five seats are gone and then the system says sorry. Now, how do you recover in such situations? In such cases you will go back, I mean you meaning the reservation system will go back and will tell you look this did not work out shall I refund the amount and at that point they will refund the amount to your credit card. So, these are transactions which has actually separate transactions. They are committed. If you meanwhile ask the credit card company they will say yes the charge for 1000 rupees was made. So, you are able to observe the internal state which means it is not really one, the whole procedure is not one transaction. It is split into small transactions and if something goes wrong you have to go back and compensate for an earlier transaction. Note that compensate is different from undo. It is kind of similar that semantically to humans it seems the same, but in recovery terminology undo or rollback means that it is not yet committed and we can undo the changes which it made partially. In contrast, compensate means it was committed already. Now, we have to run a fresh transaction to basically negate the whatever the first one did. So, if a credit card charge was made a credit card refund is done to compensate. So, such compensation is fairly common. In other words when there are transactions which span human interaction any long transaction you pretty much have to commit and then compensate. That is the only way most systems in the world run. So, again this is a nice question because it illustrates the fact that the notion of transaction and atomicity and so on are actually pervasive. They are all around us and what we see in the database and study in concurrency control and recovery is one small part of it which is inside the database. But when you build an application you also have to think about this bigger picture which is spanning user interaction. So, thanks there is a good set of questions. In the interest of time I am not going to take any more questions. To recall where we were yesterday we had looked at locking and in particular at the two phase locking protocol and if you recall it has a growing phase where transactions obtain locks and then a shrinking phase where they release locks and once the first lock is released no more locks can be obtained. And in reality what is implemented is strict two phase locking at a minimum which in which exclusive locks are held till the end of transaction and shared locks can be released after the lock point. But most real implementations actually implement rigorous two phase locking where all locks are held till the end and then released at the end. Now, why do they implement this? Simply because any SQL engine you know there is a sequence of operations which it receives which are part of a transaction. It really has no idea what you will do next. So, far it has received three queries and next it may receive an update it may receive a query it does not know. It has to obtain locks for those. As a result it cannot release locks early. As a result most real implementations will release the lock only when you say commit or roll back. So, now let us look a little bit about how locking is done practically. So, practically the first time the engine tries to read a tuple it would get a shared lock on it. If it knows it is going to update the tuple it will get an x lock. But you may have a situation where you have two SQL queries. The first one reads a tuple the second one goes in updates the same tuple. So, now what happens is that you may ask for an x lock on something on which you already have an s lock. Now, it looks like s and x locks conflict, but hey it is the same transaction. So, there is no real conflict within a same transaction. In other words what you want to do is you have an s lock now you want to upgrade it to an x lock. So, this is called a lock conversion. So, it is called an upgrade and pretty much all systems support this because all they see is a sequence of queries each query does a read or a write correspondingly they get s locks or x locks. And if they already had an s lock it is an upgrade. Now, if you want to do two phase locking in other words if you have more control and you say that now I will not acquire any more locks I can release locks. There is a form of release which is not a complete release. You can release the exclusive lock and go down to shared lock which is called locked down grade. This is consistent with two phase locking, but again as you can imagine if you release an x lock before you commit you are asking for trouble because somebody will read uncommitted data. And then it will be there will either be cascading rollbacks or in fact it would not be recoverable. So, practically speaking upgrades are what matter not down grades. So, there is a few pieces of code which indicate how this upgrade happens. In fact lock acquisition happens the database receives read and write operations. And there is a layer which automatically obtains whatever locks are required as it gets read and write operations read and write on tuples. So, I am going to skip these details, but I will just note that whenever you ask for a lock you may have to wait. In locking that is the basic property that if somebody else has a conflicting lock you may have to wait. So, you will wait and eventually it will be your turn and then you go ahead or in some cases you will be rolled back. I am going to skip the details of acquisition of a lock and point out that any database system has a module called a lock manager which does all this. Now, if you think about the lock manager as a separate process to which you send request and it sends replies back to you that is a nice model conceptually, but practically there are overheads to having a separate process to which you send messages and get replies back. It could be a separate thread, but a separate process has overheads. So, what typically is done in most database systems is anyway there is shared memory in the database shared among all the processes that are accessing the database. So, what they do is they stick a data structure called a lock table in shared memory. The lock table has information about all locks which have been granted currently and when a transaction wants to get a lock it can actually go through the lock table and see if it can grab the lock and update the lock table or it waits till somebody releases the lock and then it can update the lock table to indicate it got the lock. So, it is kind of a cooperative thing there is no separate manager each transaction has code in a library which accesses the lock table and updates it. Of course, if two transactions update the lock table at the same time there is trouble. So, all databases have actually two levels of locking. One is this database tuple level locking which we are studying. Another is a lower level of temporary locks which are obtained. So, that two transactions do not simultaneously update a shared data structure. The lock table is one such example it is a shared data structure. So, there is actually a semaphore or mutex as it is called which you can also think of as a lock which is not two phase. So, that is actually grabbed by any transaction which wants to update the lock table. It does its update releases that mutex or semaphore or short term lock and then proceeds. So, that is a different form of locking which does not itself have to be two phase. So, when we saw earlier that whenever there is locking there can be deadlock. We had an example of a situation where two transactions are running they require locks which conflict with each other and lined up in a deadlock. So, how do you deal with deadlocks? One way is to try to come up with a protocol which ensures that there is no deadlock. In fact, for specific domains if you are building application where you know what are all the transactions and it is a simple application you may be able to do something to prevent deadlocks and what is that something? A simple way is if you can order all the items which you are ever going to access sequentially and what you will do is you will only request locks in the correct order you will never go out of order. So, if you can order items like this and access them or access the get the locks in the correct order then we can show that deadlocks will never happen. Even though you use two phase locking you impose a little bit of extra rules on the way you access locks to ensure there is no deadlock. I would not get into the details, but it is not hard to show that if the items are ordered and locks can only be accessed in order meaning if I get a lock on some item I can never ask for a lock on an earlier item after that I can ask only for locks on later items subsequently. Furthermore, I cannot even upgrade the lock for the same item from S to X as an example if two people lock the same item in S mode and then both of them try to upgrade it to X they will deadlock on a single item both have the shared lock neither can get the X lock immediate deadlock. So, under certain fairly strict rules we can show that deadlocks cannot happen and they are actually quite useful there are applications which can minimize or totally avoid deadlocks by doing this, but the programmer has to be careful. So, how do you detect deadlocks? Deadlock detection is based on creating a weights for graph it is a graph with nodes as transactions this is similar to what I told you in the beginning today where the graph had transactions as nodes there an edge from one transaction to another indicated a conflict meaning this transaction did something on some item and that other one did a conflicting thing afterwards. Here it is different here an edge from one transaction to another means that the first transaction has a lock sorry this one let us take this case I will make it clear there is a edge from T 18 to T 20 what that means is that T 20 has a lock which T 18 wants and the lock which it wants is conflicting with the lock that T 20 holds in other words T 18 is now waiting for T 20 to release the lock. So, this graph is called a weights for graph. So, now you can build this weights for graph every time a transaction wants to get a lock it cannot proceed you have to add a node in the weights for graph to indicate what all transactions this is waiting for. If two transactions have a shared lock and a third transaction now wants an X lock it is now waiting for both of them. So, you can build this graph and you can actually check if this graph has a cycle. If it has a cycle that means A is waiting for B, B is waiting for C, C is waiting for A there is no way they can progress. So, if a cycle is there you have a deadlock situation and you have to abort one of those transactions. In contrast if there is no cycle in the graph yes some transactions are waiting, but eventually it is possible that the one of the transactions which is not waiting for anybody will release its lock allowing the next one to go ahead and so forth. So, if there is no cycle the system is still safe you do not have to do anything, but the moment there is a cycle detected you have to roll back a transaction. So, every database system which implements locking has a deadlock detection mechanism built in and that mechanism is actually based on building a graph like this and looking for cycles. There are some cheaper heuristics, but this is what most systems do. Luckily these graphs are not very big because how many transactions typically run concurrently. If you have 10, 20 transactions running concurrently that is already a fairly highly concurrent system. So, a graph with 10, 20 nodes and detecting cycles in it is not too expensive. So, what do you do when there is a deadlock you can completely roll back the transaction, but another option which is possible in some cases is a partial rollback. So, that you release what is the partial rollback well as you went forward you acquired locks and did updates. A partial rollback understhe updates and releases the locks going backwards in order of what the transaction did till a point where some lock has been released which now resolves the deadlock cycle. Now the transaction again go forward, but it is actually hard to code such transactions which can roll back partially and then again execute going forward. Think about it if you are writing a JDBC program, how on earth can the database tell you well you did these three steps. Now I have undone the last two steps go back to the first step and start again. JDBC does not provide you any such feature in its API. So, partial rollback conceptually is fine, but practically it is not very widely used. So, I mentioned something about deadlocks and transactions waiting. So, here is a quick quiz. Here is a schedule where T 1 has got an S lock on A, T 2 has got an S lock on B and oh wait a minute there is a typo here bigger pardon. T 1 has got has requested an X lock on B. Now can it actually get the X lock at this point? It cannot because T 2 already has an S lock. So, it is waiting and this one should have been lock X of A. So, let me write out the thing over here. So, if you see the instructions here a lock instruction is basically a request to get a lock that does not mean it will be granted. It will be granted immediately if nobody else has a conflicting lock if not the transaction has to wait. So, I will come back to the quiz question later, but just note this correction this right bottom instruction should have been lock X of A. So, what I suggest is you note the answer on paper and I will give you a chance to enter the answer later. So, the options are the schedule is not 2 phase, the schedule is dead lock, the schedule is not dead lock and none of the above. Those are the 4 options do not enter the options here it is going to take a few minutes, but please note on paper what is option and I will come back to it in a few minutes. So, far so good. The next topic which I am just going to point you to, but do not have time to cover is what is called multiple granularity locking. So, far we assume that we are going to read a tuple and we are going to lock the tuple. This is fine up to some point, but supposing a transaction needs to read every tuple in a relation and it is a very very large relation millions of tuples. If it gets a lock on each tuple one at a time it has to put an entry in the lock table. As I said the lock table is in shared memory. Now, if you have millions and millions of tuples this shared memory is going to get full with lock information. Now, what do you do? You can say a right part of the lock table to disk, but it is actually fairly silly. You are putting a lot of effort into locking each individual tuple when actually you know very well that this transaction is reading all the tuples or most of the tuples in the relation. So, why cannot this transaction lock the whole relation. So, now what we have is relation level locking. On the other hand if every transaction by default only locked relations even if it updates or reads one tuple if it locks the whole relation that is bad. You have a situation where two transactions are reading and writing completely different tuples, but because they locked at the relation level the lock manager says wait you are conflicting one of you has to eat or your dead lock you have to die which is completely unnecessary because they never even looked at the same data. So, what we have is a tradeoff. There are cases where you want to lock at the level of a relation. There are cases where you want to lock at the relation level of individual tuples. Can you do this adaptively depending on the need and that is exactly the idea of multiple grand variety locking. The idea is that transaction can choose to lock at the level of tuples it can choose to lock at the level of relations. In fact, there may be other levels in between, but let us keep life simple and it can in fact do this adaptively it can even change its mind and it is locked tuples now it wants to lock a relation and that is also possible. The question is how do you implement this effectively. So, as an example supposing transactions has locked a few tuples in exclusive mode. A new transaction comes and says I want to lock the relation in shared mode. If you look at that what locks they are requesting one is on the relation one is on tuples. If you are not careful you will say wait these are two separate things let this guy go ahead, but in fact there is a problem when you give a lock on the relation what you have done is you have given a lock on every single tuple in that relation. What the lock manager just did is it gave an s lock on a tuple on which some other transaction had an x lock that is a mistake it should not be doing that. So, the question is how does the lock manager actually know that somebody has a lock on individual tuples and then it makes sure that it does not grant a lock on the relation and there is a very efficient way of doing this by recording what are called intention locks that is even if you are locking tuples you will kind of leave a flag behind other relation level saying that I am getting an intention lock on this relation. So, new type of lock which basically says I am actually going to lock some data below individual tuples. So, now if somebody else comes and ask for a conflicting intention lock on the relation you will say no wait somebody is already locking individual tuples you will conflict you have to wait. So, that is the idea of multiple granularity locking and details are there in the book if you are interested. The next topic in concurrency control it is actually a very important topic unfortunately you know I had a choice given the time constraints of how much detail to go into timestamp base protocols and how much to go into another protocol which is also based partially on timestamps called snapshot isolation. So, given the constraints I decided that I will give a very quick overview of the basic timestamp based protocols and then focus more on snapshot isolation. Partially because snapshot isolation is relatively new in terms of textbook coverage is actually been implemented for a while in database systems, but most textbooks did not pay too much attention to it, but we have introduced we mentioned it a bit last time around in the previous edition. In this edition we have expanded coverage of snapshot isolation because more and more databases are now supporting it. So, I am going to spend more time on that, but before that let me quickly cover what is timestamp based protocols. In the basic timestamp protocols each transaction is assigned a timestamp when it enters the system. So, you can read the clock and if two transactions come so fast the clock did not change in between you can actually add some other bits to make them unique. So, each transaction has a unique timestamp which you got. In fact, you do not have to read the clock you can have a counter and each time a transaction comes in you update the counter that is actually much easier and that is what systems really do. Even though it is called a timestamp it is a logical timestamp it may not be a physical timestamp it is simply a counter 1 2 3 4. So, any transaction which comes in later will have a higher timestamp. So, now given that each transaction has a timestamp the job of the concurrency control manager here is to ensure that the serial order of transactions matches the timestamp that is the decision we have made. So, the timestamp protocol has decided upfront that any serial ordering will be consistent with the timestamp. In other words if any two transactions execute a pair of conflicting operations in an order which violates their timestamp ordering then one of them has to be rolled back that is the key idea. So, how do you implement this idea? The idea is clear whenever you detect a conflict which is going to happen because a read or write has been requested at that point you see if the conflict is such that the one which executed first had the lower timestamp and the one which made the request now has a higher timestamp then it is ok. In other words the lower timestamp 1 did the operation say a write and the higher timestamp 1 now wants to read it that is ok. But if you find that the higher timestamp transaction had done the write and the lower number transaction now wants to read the same item there is a problem. If you allow it to proceed the lower numbered one has read something which was written by a higher numbered one which means the serial order would be flipped the lower numbered one would come later in the serial order than the higher numbered one. So, that is bad the serial order has to respect the timestamp order. So, in this case if as an example t 10 wrote a data item and now t 5 wants to read it t 5 is told sorry it is been updated by a later guy you have to roll back that is a basic idea it is symmetric if t 10 wants to write something which t 5 wrote that is fine it is allowed to go ahead. If t 10 wants to read something which t 5 wrote that is also ok, but if t 5 now wants to write something which t 10 has already read what does that mean. So, t 5 is writing t 10 already read it if the serial ordering is respected t 10 should have read the value which t 5 is now trying to write, but it is too late t 10 has already read the value therefore, you have a choice you can roll back t 10 or you can roll back t 5 and the choice normally is the one which is requesting the action t 5 is requesting the action of writing t we have to detect the t 10 already read it and roll back t 5 this is what the protocol has to enforce. The question is how does it enforces and the answer is for each data item we are going to call data items q the system maintains two timestamps the write timestamp q is the largest timestamp of any transaction that successfully wrote q. The read timestamp of q is the largest timestamp of any transaction that successfully read q. So, when the read or write is successful the timestamp is updated. So, that is always the largest one. So, that is what is required now here is a details of the protocol. So, supposing a transaction t i issues read q if the timestamp of t i is less than the write timestamp of q what does that mean it means that t i is trying to read something which a higher number transaction already wrote. So, the old value is gone now it is no longer available. So, t i will be forced to read a value written by somebody who is later in the serial ordering. So, that cannot happen. Therefore, the read operation is rejected and t i is rolled back. So, this is a simple test if the timestamp of for a read if timestamp is less than write timestamp of the data item being write this q then you roll back. Now, if on the other hand the timestamp of t i is greater than or equal to the write timestamp of q that is the else case then you allow the read to proceed there is no problem, but you have to do one more step you have to update the read timestamp if required. So, the read timestamp of data item q is said to the maximum of its current read timestamp and the timestamp of t i. Why is this max required let us say that data item had been read by t 7. So, its timestamp read timestamp is now 7. Now, t 5 comes in and reads the same data item there is no problem t 5 can go ahead, but the timestamp of that data item has to remain as 7 not as 5. So, we will take the maximum of its current read timestamp 5 t s of t i which is sorry maximum is the original one was 7 and the new one is 5 the maximum was 7. So, we do not actually update it we leave it as 7. So, that is the protocol for read the protocol for write is fairly similar to this. So, first of all you check if the write the timestamp of the operation which wants to do the write that is over here t s of t i is less than read timestamp of q what does that mean you have let us say t 5 which wants to write it and some other transaction t 8 already read q which means it is too late t 8 should have got the value which was written by 2 t 5, but it has already read some other older value. So, cannot do it. So, in that case the write operation is rejected and t i is rolled back. So, that is a simple case now here is another possibility let us say this test succeeded. So, the read timestamp of q is we know that it is less than the write then the current guy is trying to write it what if it is equal read timestamp is equal to the timestamp of the new transaction that is because it is the same transaction. So, that is also. So, if read timestamp is less or equal there is no problem. Now, the next test is to compare with the write timestamp of the transaction which is trying to do the write is less than the write timestamp of q what does that mean that means that let us say t 7 already wrote the data item and now t 5 wants to write it. If you allow it to proceed the final value will be that of t 5 not of t 7. So, you cannot allow it to proceed. So, the protocol in this version says sorry you reject the write operation and roll it back. I am going to come back to this step in just a minute, but assuming all of this is ok then you complete the write operation and set the write timestamp of q to what to the timestamp of t i. We know that this has to be the highest numbered one to write it. So, we can directly update write timestamp of q to t i. So, if I had time I would actually go through a detailed example of this protocol there is an example I will go through it very quickly, but you will have to go back and read this if you are not familiar with this you will not be able to understand it fully, but I hope you have got the basic ideas. I will just mention here that there is a special case here that instead of rolling back t i we can actually ignore the write just say forget it there is a write which was subsequently overwritten. So, let us just forget we will ignore this write and let the system proceed. In fact that is correct, but it turns out if you do that you are not ensuring conflict serializability you are actually enforcing some other thing called view serializability which we did not get into. So, there is a version of the timestamp protocol which although it does not ensure conflict serializability it enforces a weaker form which is still good enough called view serialize it is still serializable. Here is a small example I will just look at a small part of it. So, these are the operations which happen t 1, t 2, t 3, t 4, t 5 are the 5 transactions which are running and those are the timestamps 1, 2, 3, 4, 5. Now, it is possible that they all started at the same time and before these 4 could do anything they got their timestamps, but before they could do anything else t 5 went ahead and read x. So, going down there is a read y, read y, write y. Does this write y have a problem? If you notice here what is the read timestamp of y at this point t 2 read y. So, the read timestamp is said to 2, t 1 read y the read timestamp is said to not to 1 to the maximum which is still 2. Now, t 3 is writing it. So, luckily the timestamp of this transaction 3 is greater than the highest number of a transaction which read it which is 2. So, the write can proceed at this point the write timestamp of y is 3 the read timestamp is 2. Next t 3 writes z what is the write time of z? z is not been read forget is read time of z to 0 let us say. Now, the write time of z is said to 3. Now, proceeding further t 5 read z what is the value it gets? It gets a value written by t 3 and that is ok it can proceed. Going further t 2 reads z and now what happens? The value of z was written by t 3, t 2 is a lower number transaction. So, it is not allowed to see anything done by t 3. So, it cannot proceed it has to abort here. So, that is what the timestamp protocol does, but I should point out there is a small issue here. t 5 has a higher timestamp than t 3. So, we allowed the read to proceed, but think what happens if t 3 now aborts. If t 5 commits and t 3 aborts here what happens? You have a schedule which is not recoverable. So, you actually have to do something about it. You this fellow read a value which was never committed. So, you actually have to abort t 5 it is not shown here, but if t 3 aborts t 5 also will need to abort. So, there is a dependency there and the correctness in terms of serializability is actually very easy to show. The correctness in terms of recoverability well if you just follow the protocol as I have shown it is not recoverable there is a problem. So, there is actually extra stuff which has to be added on to either force cascading aborts or to make transactions wait in case a new transaction is trying to read an uncommitted value then it has to wait. So, extra steps are required here as it says here the schedule may not be cascade free and may not even be recoverable the previous schedule is not recoverable. So, there are a couple of solutions to this for lack of time I am not going to get into it, but essentially it either uses some limited form of locking or keeps track of commit dependencies and rolls back or the solution number 1 is you do not do writes during the transaction you accumulate all the writes locally and when the transaction is ready to commit at that point you check all the writes see if they can all complete and then do all the writes atomically at the end. So, while the transaction is doing the writes nobody can read those data item they have to block temporarily if they try to read it the transaction commits and then only the writes are allowed to proceed. So, in fact, solution 1 and 2 are kind of related in this sense. So, again I am skipping details if you did not understand it do not worry go read it up later. So, that was the time stamp based protocol. The next protocol is what is called a validation based protocol here the idea is you allow transactions to run. Now, when they do a write you do not immediately do the write you keep the write pending you do not write it to the database and when the transaction says it is ready to commit at that point you will check if these reads and writes which it did are conflicting with any other concurrent transaction and if it is then it rolls back if there is no conflict it is allowed to commit. The question is how do you detect if it conflicts with a concurrent transaction? First of all what is the concurrent transaction? A concurrent transaction you can define as one which was running while this one was running. So, you have to track what was running while this was running even if it is gone if it is committed and gone still it was running while this one was running. So, you have to check what all the concurrent transactions did which have already committed and then check whether this fellow conflicts with any of them if so roll it back otherwise committed. Again I am not going to get into all the details, but let me just tell you how this is done at a big level. In the first phase read an execution phase T i reads from the database it never writes back to the database it writes to temporary local variables what if it reads something which it wrote it has to read it from its local variable otherwise it will get an old value. So, if it has to first check if it wrote the item read the value which it wrote locally if it did not write it go fetch it from the database that is the first phase. The second phase is a validation phase where it checks what other concurrent transactions did and if it is ok to commit or not and then if the validation succeeds you do the right if it does not succeed you roll back the transaction and do not do any rights that is easy. Now, in the basic version of the protocol validation and writes are all done together for a transaction atomically. So, while its rights are in progress nobody can read anything which it writes. So, how to implement this again some form of locking is required I would not get into the details, but the basic idea is that in the simplest form you validate and write atomically while you are doing this nobody else is reading anything or nobody else is validating nobody else is writing only one guy can do validation and while it is doing validation and writing nobody else can read or write anything. So, that is not very good in terms of concurrency, but it is simple. Now, of course, in real life people have figured out how to do validation and writes concurrently again there are lot of details we do not have time for that. So, I am going to skip that this protocol by the way is called optimistic concurrency control. Since a transaction executes fully in the hope that everything will go fine at the very end when you know everything which it wants to do that is when you check if everything is ok and go ahead and commit or otherwise roll back. Optimistic it has been shown to work well when conflicts are rare if there are lot of conflicts it does wasted effort. There is a conflict which may be it could have detected long back it simply postponed detecting the conflict did a lot of work and finally, detected the conflict and then rolls back. In fact, there are situations where you have a long transaction which if you are careless here while it runs for the long time somebody else will come conflict with it and commit and force it to roll back repeatedly. So, in high conflict situation this protocol is actually a very bad idea, but in very low conflict situation it works well. So, how to implement it again it is based on in using two time stamps and then having time stamps for data items and so on or maintaining read and write sets I am going to skip the details you can read it from the book.