 So, I think pretty much everybody here is familiar with the notion of transactions. I would not belabor the basic stuff, we have the usual acid property, atomicity, durability, consistency and isolation. Now, all of you know very clearly what is atomicity, what is durability, consistency is in the eyes of the beholder or another way to say it is depends on what database constraints are declared and then whatever constraints application enforces without the database even knowing about it. All of those are part of the consistency requirements that the database does not really have a major role beyond enforcing the integrity constraints which it supports. And the last part is isolation, does everyone understand what is isolation? How many do not understand isolation clearly and ambiguously, I am impressed, well I should raise my hand. Isolation is actually not very well defined, it is very hard to pin down precisely. There are in fact many definitions of isolation and the definition we use is from the Bible of Transaction Processing, but even they make it clear that isolation is actually hard to pin down. Now, what do I mean by this? Many of you will say that isolation means serializability, that is one traditional interpretation of what we mean by isolation, we will just briefly discuss serializability in a minute, but that is something which guarantees isolation. But it turns out many systems do not actually implement serializability, they do something weaker, can we say they do not provide isolation? No, they do provide some kind of isolation, but it is something weaker than full serializability. So, then what do we even mean by isolation? There are different degrees of isolation, so we cannot define you know we cannot define all possible degrees, there are maybe there are infinite number of degrees of isolation. So, if you look at the traditional acid properties the way Gray and Reuter define isolation is a simple variation which says that a transaction cannot see the intermediate state of any other transaction or in other words if you take any pair of transactions and if you see what this wrote and that read, if you take a pair of transactions it will look like to this transaction T1, it looks like T2 ran after T1 meaning it does not see any update of T2 or T2 ran before T1 which means it sees all the updates of T2. So, T1 should either see none of the updates of T2 or all the updates of T2, so this is kind of a minimal requirement, if you violate this then you are seeing intermediate results and then the transaction is not isolated, but it turns out that a pair wise serializability this is a in a sense a pair wise serializability requirement given a pair of transactions one should appear to run before the other or after the other, but it turns out pair wise serializability does not guarantee global serializability. So, this definition of isolation is not equivalent to full serializability it is only pair wise. So, this is a reasonable definition which does not force full serializability, but does insist on something which is quite reasonable which pretty much all the variations of isolation actually do support this that you would not see intermediate results of other transactions. So, it is a minimum requirement for isolation. Now, of course, you can ensure everything works fine by running transactions serially. So, why take all this trouble to run things concurrently? Why go through all this business of serializability, all this theory, all this concurrency control, why is it worthwhile? Does it even make sense? Why is it an efficiency issue? What do we mean by efficiency? Why? Why not run things serially? Yeah, exactly, while what? So, there are two motivating reasons. The first is that you may have some very large transactions and if you make other transactions wait for a very large transaction to complete. So, run them serially one after another, large transaction has to run at some point and any other transactions which come after it have to wait for it to complete and this may take a long time. So, even if they do not logically conflict in any way, you will have to force them to wait. So, this is the number one reason that you may have large transaction. The number two reason is that even if a transaction is not large, if you have a magnetic disk storage system, it may access something which is on disk and once it does a disk access, it has to wait. How long does it wait? Maybe it takes 10, 20, 30 milliseconds for the disk access. This may not seem large, but for a system which processes thousands of transactions a second, a wait of 20, 30 milliseconds is significant. So, even though the transaction is not a large transaction, it was actually a small transaction, if you run it serially and then you have to wait for 30 milliseconds, there is a problem. But it turns out that these days people are going back and looking at this issue and saying maybe serial execution is not such a bad idea after all in some domains. What is that domain? A, the database has to be in main memory. So, you never have to wait for this caro. B, all the transactions are very small transactions and so there has been some work which has shown that under this limited assumption, running things serially is actually much more efficient than playing the overhead of concurrency control, locking and so forth. In fact, some of the work in this area that Hancock has some publication in the area, which have implemented these systems and shown that serial execution is actually a good idea under some such requirements. But of course, that is not the whole world. There are other things. So, understanding, in fact this is very funny because Hancock's earlier avatar, when he did his PhD and for a while after that, all of his research was on providing very high concurrency. So, now in his middle age, he has gone back and said all that I did before is useless. Actually, I am kidding. He says that under certain scenarios, all that I did before is not really required. In other scenarios, it is still useful. So, moving on, we have seen this definition of isolation which I talked about. Every pair of transactions T1, T i and T j, it appears to T i that either T j finished execution before T i started or T j started execution after T i finished. This is the minimum. We also know about transaction states, transaction runs. When it finished its last statement, it is partially committed and then it tries to commit. When does it commit successfully? That depends on the recovery implementation. Typically, when a commit log record has been written and otherwise, it fails and it is aborted. We have already seen two of the reasons for concurrent execution, which are to do with transactions waiting in two different scenarios. There is a third reason which I did not talk about, which is if a transaction is waiting for a disk IO. It is not that another transaction is conflicting with it, but if it is a non-conflicting transaction, why cannot you let it use the CPU while this fellow is waiting on disk IO? In fact, for typical systems today with many disks, while one guy is doing IO on one disk, another guy can be allowed to do an IO on another disk. They have to be running concurrently. To run them serially, only one of CPU or disk or one of those disks will be activated. All the rest are idle. So, you are wasting resources. If you let them run concurrently, you can improve resource utilization sharply. In fact, this was also one of the major requirements for concurrency control in early days. Concurrency control schemes are mechanisms to ensure isolation. We are going to look at some of them briefly. There is also a notion of a schedule, which really helps us understand if a particular concurrency control scheme is actually working properly or not. What does it mean to allow concurrent execution and still nothing will go wrong? It is actually not easy. If you already know about schedules, it seems obvious, but if you do not or for the moment you pretend you do not know about schedules, how do you know that a particular execution where things are all interleaved? How do you even know that it is okay? How do you know it will not cause problems? This is actually a very non-trivial problem. The notion of schedules is actually a major theoretical advance, which helps us understand why certain things are fine and why they are not. If you were a systems guy who was there in the era before the notion of schedules was formalized, you would probably have just played by seat of fans. You say, well, let us lock this and things work, but there is no proof that things are safe. Concurrency is an area where unless you can prove something correct, it is really dangerous. For example, many people write applications which are multi-threaded today. They are concurrent applications. They do not use serializability. They use some form of locking, which does not really guarantee serializability. There is a big problem. A lot of software today is buggy. Have you ever had a browser crash on you? How many of you have had a browser crash? It is a few times. Other software crash. A lot of time it is because you have multiple threads in there and there is a synchronization problem. I have seen Firefox hang because the Adobe Flash plugin does something and gets into a deadlock. Trying to prevent concurrency, but gets into trouble and hangs. So, there are many kinds of problems which can arise because of concurrent execution. A lot of people did not pay too much attention to it. They say, okay, fine, the browser hangs. Kill it and restart it. You may think it is a virus, but in this case, it was Adobe Flash. It was not a virus. Adobe is notorious for performance and bug issues. Thief job had a field day bashing up Adobe for this. That is why it does not run flash on iPad and iPhone. Anyway, that is a digression. Coming back, handling concurrency is actually very hard and it is bad enough in scenarios where the worst case is you have to restart a browser. But if this is a bank database and lack of concurrency causes money to be lost by the bank, bank will be very unhappy. So, there is a strong motivation to understand it theoretically and be sure that nothing will go wrong. That is where this whole notion of schedule comes in. So, what is a schedule? It is a set of operation that the transaction runs. Again, one of the important parts of the schedule is abstraction. Of course, a transaction does many things. It is a COBOL program or a C program which is compiled down to machine code with hundreds of operations. How do you understand what this is doing? You cannot really deal with all this complexity and still get anything meaningful out unless you abstract away. So, here is the schedule which has several operations. One is read operation, which reads from the database. Another is some computation internally and then a write operation. One of the key things which is done by concurrency control is to say, let us throw all these operations out. I do not understand what the transaction does internally. In this case, I know it is A equal to A minus 50. In general, given a transaction, it can do very complex calculations in there. I do not know what it does. I will not even try to find out. I will just assume it can do anything in between. So, there is a read, there is a write. In between the read and the write, anything could have happened. So, that is how we abstract away details of what the transaction does and use schedules which have only reads and writes. So, these schedules are still involving operations. If I know what the transaction is doing, I may realize that two schedules are equivalent. They will generate the same final answer. If I do not know, I just wanted an example of a schedule, which involves only reads and writes. This has reads and write, read, write. And the read and write is on a particular data item A. So, now, how do I know that a given schedule like this is safe or not? And the answer is to use the notion of conflicting instructions. So, for reads and writes, again you are probably familiar with this notion of conflict. If both are reads, they do not conflict. If even one of them is a write, there is a conflict. So, what does a conflict mean? It means that if I have a particular order, the opposite order will give a different result. If I have two writes, I flip them, then the result will be different. If I have a read followed by a write and I flip them, the read will get a different result. So, this is an important notion for deciding when two schedules are equivalent. There is actually another notion of serializability, which is not based on conflicts, but on something slightly different called view serializability. Again, many of you cover it in your syllabus. I am going to skip it here for lack of time. So, I would suggest that if you have control on the syllabus, view serializability is a very special case. It is not used that much. So, if time is limited, you might as well skip that and do other stuff and focus on conflict serializability only. Ignore view serializability. Coming back here, if you have two schedules or rather if you have a schedule and you swap a pair of instructions, what do we mean by swap? By swap, I mean there is operation here and an operation here. And swap means move this down and move this up. They of course, stay within the same transaction. You do not take operation and move it to a different transaction. It is you move one up and one down. And we are swapping adjacent ones. Now, note that this also makes another assumption. The notion of a schedule here is making an assumption that the operations run one after another. Is this even a reasonable assumption today? It is not quite because you have multi-threaded processors with multiple cores. So, even a single operation may be, you know, you do not know whether this ran first or that ran first. So, in this schedule, we are assuming things run serially, but there could also be schedules where you have two operations where you do not know which one ran first. Now, this does not really matter as long as the operations do not conflict. I do not know which processor did road A and which road B in what order. But as long as they are on two different items A and B, I do not care. So, the notion of conflict serially can still be used even if we do not know the exact order of the operations. So, let us first see what happens if we know the exact order. So, in this case, can you swap write A and read A? You cannot write and read on the same item conflict. On the other hand, I have a read B and a write A. Can I swap them? Yes, because they are on different items that moves down. Now, I have a read B and a read A. Can I swap this? So, this read B can be moved up. Similarly, this write B will be swapped with write A and again with read A. So, when we move both of these up, I have landed up at this schedule. So, these two schedules which we got by swapping are conflict equivalent, because they do not swap anything which conflicts. So, if two schedules are conflict equivalent, we are sure that they will produce the same result, because the order does not matter. Similarly, if I have a schedule where I do not know the exact order of these two operations, as long as they do not conflict, I can pretend they happen in this order or I can pretend they happen in that order and it really does not matter. The end result will be the same. So, that is how schedules can actually deal with multi processor, multi threads and so on. Now, these two schedules are conflict equivalent. Moreover, this second schedule is serial, because one transaction is executed completely and then only the next transaction starts. So, this is a serial schedule and as you are aware, the notion of conflict serializability says that if I have a schedule and I can swap it to get another schedule. In other words, it is conflict equivalent to some serial schedule. I know that serial schedules are safe. There is no concurrency, so there cannot be any problem due to concurrency. Now, here is a schedule with concurrency, which I have just shown is exactly equivalent to some serial schedule. Therefore, I can say that this concurrent schedule is safe. It does not have any problem due to concurrency. This is the key insight which people had maybe 30, 40 years ago in the early days of databases when the notion of serializability came in. This was the key insight and then they can say, look I will allow concurrency, but I will prove that it is equivalent to a serial execution. Therefore, you are safe. So, this was a huge leap forward and here is an example of a schedule which is not conflict serializable, because if I want a serial schedule, either this should have come first or this should have come later. Unfortunately, all of these are operations in the same queue. So, this read write conflict, so I cannot move it up. This write write conflict, I cannot move it down. So, this is not a safe schedule. Now, of course, schedules are what we observe. What I want to do is prevent any such schedule, which is not conflict serializable. So, the question is how do I prevent it? I cannot generate a schedule and then check if it is conflict serializable, it is too late. So, the concurrency control scheme is the one which is used to prevent bad schedules from occurring. So, what I want to do is I will describe a concurrency control scheme. I will show that any schedule that occurs under that scheme is conflict serializable, then it is safe. Therefore, the concurrency control scheme guarantees safety with concurrency. There is also schemes which ensure view serializability, even though it is not conflict serializable, we would not bother about them at this point. Now, it is not enough for a schedule to be conflict serializable, because what may happen is that even in a conflict serializable schedule, a transaction may read an item which is not yet committed by another transaction. So, why can that happen? In this case, they take this first one here, T1 road B, T2 red B. Now, at this point, are we sure that T1 has committed? I do not know, maybe T1 has not yet committed and it only commits here. Then T1 has read something which is not yet committed. What if T1 has not committed and it decides not to commit at this point? What do you need to do? You need to roll it back, which means you have to undo the effects, which means you have to write the old value of B back. Now, see what happens. If you have to write the old value of B back, this read B is in between two write Bs. So, if you include the roll back, this is not actually serializable. So, to ensure that a transaction can roll back, we do not want to allow reads to happen to something which has been written until this has committed. And so, recoverable schedules basically insist that once an item has been written, it cannot be read until it has been committed. So, that is a minimum requirement. Otherwise, you can get into trouble. You cannot even roll back a transaction safely. Well, technically, the condition is a little weaker. What it requires is, recoverable schedule requires is that if a transaction TJ reads a data item previously written, then the commit of TI of the first transaction appears before the commit of TJ. That is the notion of recoverable. So, that is a weaker thing. So, we allow it to read an uncommitted thing, but we do not allow it to commit. If it commits you are in trouble because the value may actually vanish because of a rollback. In reality, a stronger notion where we do not even allow it to read until it is committed is safer. Why allow it to read? And then decide later to not allow it to commit. There are certain situations where it is still useful, but typically in practice, you do not even want to let it proceed reading an uncommitted value. So, correspondingly, the minimum kind of isolation level which most databases support is what is called read committed. So, read committed isolation level ensures that nobody will read a value which is not yet committed, which then gets rolled back. So, you are seeing something which never ever existed in the database. That is prevented. Data analysis that it says that this is the probability of not committing this transaction. What is the probability of commit and failure? Then it takes the decision that we are making. That is a very good, that is a very good question. So, the question is what is the probability that this fellow fails? What if almost always the transaction will commit? Then why not let it go ahead and read it even before this is committed? And the thing is that if you read an arbitrary value in the middle of an execution, this fellow may write it again. So, that is a bad idea. But what if it has finished its last step and it is actually just waiting to commit? Then the probability that it will roll back is very small. So, many systems will allow you to read a value of a transaction that is not fully committed, but it is partially committed. And then they make sure that this will not commit until this commits. So, it is not quite read committed, but it is read partially committed. So, this is a small optimization which many systems use. But that question can also be interpreted as if a transaction does a read, just let it read whatever value is there. And if this fellow updates it subsequently, you have to roll that fellow back. If this fellow rolls back, also you have to roll that back. In fact, this is what optimistic concurrency control, a version of optimistic concurrency control, there are several versions, but one of the variants of it will let you do such things. The normal version says you will do a write only at the end, just before you commit. But there are variants which let you do a write ahead of time. And so people have shown that under certain circumstances, it is better to optimistically go ahead and read it if the probability of rollback or update is very low. But I do not know of any real system which implements it because it depends a lot on the usage environment. So, you can implement it if you hard-code it for a specific application. For a general-purpose database, it is not a safe thing to do. So, that was a recoverable schedule. Recoverable schedule can still cause cascading rollback and cascade-free schedule, what is a cascading rollback? This fellow wrote it, this guy wrote it, this fellow aborts. At this point, this guy must be aborted. So, if you do not allow any uncommitted data to be read, this prevents it. So, a cascaded schedule is basically equivalent to read committed. It allows only items to be read after they have been committed. Move on to concurrency control. But before that, I want to say a little bit about weak levels of consistency. So far, what you have said is serializability is good and it guarantees safety. In practice though, for efficiency, many systems do not actually run at serializable level. They say that we live life dangerously. And they are being optimistic. So, this brings back the issue. It says, you know, what if a problem occurs? And they say, optimistically fine, we will allow schedules which are not serializable. Once in a way, you may get into trouble. You, the programmer, are responsible for dealing with this. Do not sue us if that happens. We have documented it in our manuals, even if you do not know about it. So, in fact, almost all databases today. In the default level, by default, they will commit individual SQL transactions immediately. And the, even if you consider each SQL operation as a transaction, the serialized, they do not guarantee serializability even under this assumption. What they guarantee is something called read committed. That is the default level. We've been talking of read committed. Most databases treat read committed as the default. If you get the database out of the box and use it, what you get is read committed. It gives good performance. If you want serializability, you have to do more work. Unfortunately, most of us, including me, forget that this is the default. You know, you may know that this is the default. But when you build an application, you kind of forget about it. So, it's actually a little dangerous. What most databases do is they allow you to change the isolation level. So, for example, instead of read committed, you can tell the database move to serializable isolation level. And if you tell DB2 or SQL server to do this, we'll see how to do it shortly. If you tell DB2 or SQL server, then they will say, okay, fine. We will make sure that all transactions submitted from this connection are serializable. You may also be able to do it at the database level where you set the globally everything is serializable. Alternatively, for each connection, you set this serializable. So, every time you connect to the database, you have to say, okay, now switch to serializable mode. And then all transactions which run from here run in serializable with respect to other transactions which run in serializable mode. You may have another transaction jumping in which is not serializable. That may cause trouble. But usually they make sure that those transactions cannot mess up transactions which want to be serializable. But these transactions mess up someone who didn't say they want to be serializable. That is usually the guarantee that databases give. Now, two of the most popular databases, Oracle and PostgreSQL, cheat even on this. If you tell them run in serializable level, they don't actually run in serializable level. They run in another level which is called snapshot. And this doesn't quite guarantee serializability. It comes close though. Many applications will actually run fine under snapshot isolation and will actually be serializable under snapshot isolation. Is that a reasonable statement to make? Well, people at Oracle assumed it. Now, of course, it's not that hard to create transactions which are not serializable under snapshot isolation. The question is, do they occur often in reality? And what people have shown, you can't prove this in general. But what people have shown is that, for example, the TPCC benchmark, it's a benchmark application, reasonably complex application. And people have analyzed it and shown that under snapshot isolation, it runs fine. It will not have any serializability problems. And people have also come up with ways of checking an application to see if it is safe under snapshot. And then there's been some work including here at IIT Bombay which says that what if it is not safe? What do you do? Again, databases provide some support for dealing with this. So the work here was basically automating the analysis of applications to see if they are safe. And then you can modify the transactions using some tricks which I will come to later. And then you can reanalyze and see if it is safe. Now, like I said, the people who analyzed TPCC showed that TPCC is safe under snapshot isolation. But is it safe in general? Well, it turns out that it's not. In IIT Bombay, we have a financial application which was built by TCS. Now, the TCS programmers who built this were reasonably smart people. But they didn't fully understand snapshot isolation. And they did their best. They realized there is some places where there could be problems. And they tried to do something about it. They set isolation level to serializable. And then they thought things are okay. It turned out that there were some weird situation which was specific to this application where things were not serializable. And who found this out? It was not programmers. It was our auditors. Why are auditors? Our financial auditors found this. They found a situation where you are supposed to have a voucher number for bills. And two different bills cannot have the same voucher number. And our auditors found a situation where this happened. Two different bills had the same voucher number. And that could reflect somebody who is cheating and pocketing the money. It could, in theory. So they caught that. But all of this is automatically generated even if a human in the accounts wanted to do this. They cannot do it because it is generated by the system. So then we looked into it and said how did this happen? And eventually it was traced down to the fact that this was running under snapshot isolation. And most of the time it does not cause a problem. But there was a very specific way they created voucher numbers for bills which unfortunately did not guarantee serializability. In this case it is rare. But the rare thing did happen in IIT Bombay. And it happened very rarely. It happened twice in a year. Auditors caught it twice. Then we looked from the back end and found that over 3 years it had occurred maybe 7 or 8 times overall. Across, in 3 years we probably had 150,000 transaction bills. 100,000 bills at least. It happened 8 times out of 100,000 bills. So it is rare. But it can happen. Anyway, that was a digression to motivate what all happens with weak levels. So let us summarize the weak levels of consistency as defined in SQL. The lowest level is read uncommitted which allows you to read anything at all. This is rarely used. But it is used occasionally if a transaction just wants to get statistics about a relation. We do not care if the statistics are slightly wrong. So we do not even care if we are reading an uncommitted value. We will just read whatever is there at a point in time. So that is read uncommitted. Read committed is what we just discussed. You are allowed to read a value only after it has been committed. But that does not guarantee serializability. The next level is what is called a repeatable read. The difference between repeatable read and read committed is the following. So let us say there is a P1 which is reading A. T2 which has, well let us say readsay later. This has written A commit and this readsay. Now here is T3 which is running somewhere here. Now T3 writes a commit. Maybe it readsay also here. This guy is not yet committed. Now it readsay again. Under read committed what value can T1 see? T1 is allowed to see the value over here. It can see the value written by T2. At this point it can see the value written by T3. So the same read can return different values the next time under read committed. What repeatable read says is this is not allowed. If you read a value once, if you read it again you must get the same value. Otherwise that is bad. That clearly shows that somebody else ran in between. It does not ensure isolation. So repeatable read is the next level. But even repeatable read does not guarantee serializability. If we have time we will see this later. We understand the question is when T1 reads a value here we have two options. One is to say look it will be updated in between roll back. Another option is to give it the same value as before which it read in the first place. That allowing it to see the old value is actually used in snapshot isolation. Snapshot isolation lets it read the same value as before and therefore if you read any description of snapshot isolation you will say we allow repeatable read. But for reasons I do not want to get into at this point that is cheating. Repeatable read has another aspect also which is repeatable read with predicate. If you said give me all the records which satisfy A equal to 5 and then I ask for the same thing again you may get a different result. Now snapshot isolation will guarantee I will get the same result but it is not an up to date result. So let me leave this discussion out for the moment. But the point is that although snapshot isolation says it does repeatable read it does it by giving an old value which can cause other problems. And then serializable according to SQL should be the default level for SQL. But in reality for all databases the real default level is read committed. It is not serializable that is something which you should remember. No database supports serializable as the default order consistency level. And like I said PostgreSQL and Oracle cheat even if you say set isolation level serializable. That command tells the database to switch to serializable mode for that connection. We will actually be doing this in today's lab. But when you tell PostgreSQL and Oracle to do this they will say ok we will do it. But what they do is they do not guarantee serializability still. What they implement is what is called snapshot isolation. We will see some of the impact of this in today's lab. Now another issue in SQL is what is the transaction? We saw in JDBC you can say turn off auto commit. So every statement from the first one is part of a transaction until you say commit or abort. A transaction begins implicitly and then commit or rollback has to be issued to commit it or roll it back. In PostgreSQL by default transactions each SQL statement is a transaction and commits immediately. If you want to say that number of SQL statements together form a transaction you can say begin. We will be using that in today's lab. All the subsequent statements are part of a transaction and after that you have to say commit or rollback to complete that transaction. So that completes the high level view of serializability and so on. The next step is understanding how to control concurrency to ensure one of these isolation levels. I should also mention that snapshot isolation is yet another level which kind of comes in between these two. It appears to be a repeatable read but it is not serializable. So although it is not one of the four levels defined by SQL it is actually some intermediate level. In fact there is an interesting history to snapshot isolation. So people who only knew about locking defined SQL standards and said here are the four meaningful levels of isolation from serializable down to read and committed. Which made perfect sense with pure locking. Now somebody published a paper, some famous people actually published a paper which said that these are not the only four levels possible. Here is one more level possible which is actually in between these four. Here is the fifth level and therefore this idea that there are only four levels is not theoretically is not clean notion. And almost at the same time I do not know which inspired which but I have a feeling it was snapshot isolation had been actually implemented but not made public by some of these authors or maybe it was other way. They wrote the paper and then Oracle and PostgreSQL saw this paper and said hey let us implement this. So like I said I do not know which came first but at about the same time Oracle released snapshot isolation as this paper. So that is kind of the history of snapshot isolation. And I think Oracle mistakenly assumed that snapshot isolation guarantees serializability. And later people in fact this paper showed this that it does not guarantee serializability. So I guess that is evidence for saying that Oracle had implemented it first and this paper clearly pointed out a snapshot isolation lacking serializability.