 Now, so far so good, the next idea which people had is based on what is called multi versioning. The idea is when you write a data item, instead of clobbering the current data item, you make a fresh copy. You record that you know the data item was written, but if somebody wants to read the old value of the data item, they can go ahead. It is actually very natural with the time stamp protocol. In the time stamp protocol, there is a multi version variant of the time stamp protocol. What is this multi version variant? If somebody does a write, it actually creates a new version of that data item. It never overwrites an existing version. Every write creates a new version based on its time stamp. When a read happens, it turns out that reads will never roll back in this protocol. Reads can find out whichever version they need. It may be an old version. The data item was written later by somebody else, but they created a new version. The old version is still available and the read can pick whichever is the latest version, which is older than it. So, reads will always succeed. On the other hand, there are situations where writes cannot succeed because the write is trying to write something, which somebody else ought to have read, but the write had not happened in time. So, they read an older value. Now, if you try to do the write, you are causing a problem for that guy who read an old value. So, a write may have to roll back. Reads will never roll back. So, in fact, this is a nice property of multi version protocols that you can show that readers will always commit. They will never have to roll back. Only writers may have to roll back. So, that is a nice property and that is one of the major reasons that multi versioning is used. It turns out, multi versioning time stamp, the original one proposed, has a fair amount of overhead for implementation. It can be done. There are systems which have done it, but current generation systems do not actually implement the multi version time stamp protocol. However, they do implement something called snapshot isolation. There is one more thing over here called multi version two phase locking. Again, for lack of time, we would not get into it and focus on snapshot. So, there is a cost to multi versioning. You have extra space to create new versions, but new versions do not have to, old versions do not have to live around forever. If somebody updated, created a new version, the old version may be needed for some time. You cannot delete it immediately, but you can actually analyze what is going on in the system and go and delete the old one after some time. So, that is called garbage connection. So, any system which implements multi version concurrency control has to garbage collect old versions. An alternative which many databases implement is to create old versions on demand, meaning you allow the transaction to update the thing. Now, somebody says, I want an old version of the data item. How do you create it? It turns out with in the case of a snapshot isolation, which we are going to see. It is possible for the database system to keep log records, which are needed anyway for recovery. It has log records of what all updates happen. So, there is a neat implementation trick, which is used in many database systems, where it will actually take the current data item, find which log records recorded updates to it and it will undo those updates on a fresh copy. It makes a copy. This is the data item at time stamp 20. It takes that data item at 20, goes back through the log and sees maybe it needs the data item as of time 10. So, it sees what all things updated it after time 10 and undoes whatever they did to recover the value as it was at time 10. So, that is a cute idea, which Oracle uses. I think PostgreSQL actually keeps multiple versions around and garbage collects them, but it could also implement it if it wanted to. So, that is a trick to implement multi versioning. So, now let us focus on the snapshot isolation, which is a multi version protocol. So, the motivation for snapshot isolation came from the following observation. If you have a single system, where you have lot of small updates, it is your typical OLTP workload. So, your railway reservation, airline reservation have a lot of small updates. Many queries come in. They see the ticket available on this train or this flight. If yes, they update that issue the ticket and go away. Those are all small transactions. On the other hand, if you have a large transaction, which maybe says what is the total number of tickets issued today and for that it goes through all the records, all the trains or flights and reads everything that is in there. The problem is that this large reader will end up locking everything and now a small update, which comes in cannot proceed. It is stuck and this can cause havoc. If people are not able to buy tickets for a particular airline, for five minutes they say wait the system is down. Let me go purchase it from some other airline. The airline has lost business. So, it is actually a very, very bad situation for that airline. I have actually seen this happen. I forget which airline it was. I was trying to book tickets. Its site was down. I went to some other airline and bought the ticket there. So, you do not want this. Now, in this case the site was down for other reasons, but if the site was unable to process transactions simply because reader had locked a lot of data, you are in trouble. So, the idea is supposing I instead of locking data, I say that this reader will get whatever data was committed at the time it started. In other words, the reader has a time stamp. It started at this point. If it tries to read a data item, which was updated after it started, we are actually going to not give that version. We are going to get an older version as it was when the reader started. Now, the reader does not need to lock any data item. Why? Because it is going to get exactly this you think of it as here is a database. I have a camera. I click and get a photo of the database at that point in time. This is a snapshot of the database. Now, other things can happen. The database can get updated. I do not care. The reader will run on this snapshot and give a result, which is actually consistent. It is an exactly consistent result as of the time when the snapshot was taken. So, if it says that the total sales was 5 million rupees and 23 paisa as of this time when the snapshot was taken, that is exactly the right answer as of that point. Now, of course, how do you take a snapshot? You cannot take a camera and take a photograph of a disk. That is silly. So, if you try to copy the whole database, that is also very silly. It will take forever. So, the way a snapshot is implemented is you either use multi versioning, which means that every time the database item is updated, you create a new version, but you keep the old version around. And this transaction which was reading gets a snapshot as of when it started. It will get the old version. It does not do any locking whatsoever. It simply reads an appropriate version and keeps going ahead. Meanwhile, updaters can actually use two-phase locking if they want. That protocol is not snapshot isolation. That is called multi version two-phase locking. So, in multi version two-phase locking, readers get a snapshot. Updaters use regular two-phase locking. So, this was proposed in the early 90s. But there is a problem. Most database systems do not know what the transaction is going to do next. So, in order to use this protocol, a transaction would have to declare saying I am a reader, only transaction. I will not do any writes. Give me a snapshot. Now, this would have been actually a very sensible solution. But many databases decided that that requires rewriting a lot of programs to get benefit. Therefore, let us try to do the following. Supposing every transaction gets a snapshot. Whether it is going to do updates or not, it gets a snapshot. So, what happens here? The transaction, whether it is going to read or write, it gets a logical snapshot. It reads everything from its snapshot. Now, can there be a concurrency control problem when these guys write? Yes, of course, there can be. So, if you do the following. Transactions will read from the snapshot. When they want to write a data item, then they will get an exclusive lock on it and do the write and then commit. So, let us understand the protocol. You do not get necessarily read locks. Reads are from a snapshot. You get a write lock when you want to write the data item. Get the lock, update it and go away. Is this protocol going to work? The answer is no. You can get many problems such as a lost update problem which I will show you next. So, what people did is they said, well, let us do a little bit of extra validation when the transaction wants to commit. And if certain problems occur, we will roll it back. Otherwise, we will let it commit. This particular protocol was interestingly proposed in a paper in 1995. And that paper was actually a critique of the SQL standards for transactions. They said, look, SQL has given these four levels of serializabilities, serializable, repeatable read, read committed, read uncommitted. This paper said, look, this is not the only four possible levels. Other things are possible. And here is something called snapshot isolation, which lies in between these levels. It allows repeatable read, but it is not serializable. So, they pointed it out as a critique. But it turned out Oracle and later PostgreSQL both actually implemented this protocol as one more level. In fact, Oracle and PostgreSQL implement this as their default, not their default. They implemented, if you tell them to run in serializable mode, they actually implement snapshot isolation. They do not actually ensure serializability. So, let us see what can go wrong if people read from their snapshot. So, here is an execution. T1 writes y commits. T2 starts, it reads x. Let us say all items have initial value 0. So, its read returns x 0. T2 reads y. What does it get? It gets a value in its snapshot, which is 1. Now, meanwhile T3 starts up and it writes x and it sets x to 2 and it sets z to 3 and it commits. There is no problem. Nobody else is writing x or z. T2 has read x and read y, but it does not, it got a snapshot. So, the writer does not conflict with the snapshot. Now, let us see what happens when T2 reads z. T2 got a snapshot when it started. So, even though z has been set to 3, T2 will get back the original value, which was 0. So, what is happening is it is not seeing concurrent updates. When it reads, it is reading from its own snapshot. Next, it reads y. It gets the value 1. Actually, if it had written y here to 2, it would have got 2 here. So, it can see its own updates. Next, it writes x and at this point, if you just did plain locking, what would happen? T3, supposing you did locking only for writes. So, when T3 came in here, it got an exclusive lock on x, an exclusive lock on z. All was fine. It committed and went away. Now, when T2 wants to write x, supposing it also requested a lock, it says fine. Nobody else has a lock. It gets it, writes it and goes away. Now, something funny has happened. T3 wrote x equal to 2. T2 came and clobbered it. It never saw what T3 did. It just clobbered it. This particular problem is called a lost update problem. This is one of the known problems if you do not have serial visibility. So, what the designers of snapshot isolation did is they said, look, locking is not enough because it will result in lost updates. Let us do something slightly different. Let us see at this point, if any other concurrent transaction wrote x. If a concurrent transaction wrote x and has committed, then I cannot commit. I will have to roll back. So, what are the concurrent transactions? Is T1 concurrent? No. T1 had completed before T2 started. So, T1 is not concurrent. Is T3 concurrent? Yes, because T3 ran while T2 was running. So, T3 is concurrent. Did a concurrent transaction update x and yes, T3 updated x? Now, there is a conflict and this first commit a rule, Wins rule says that the first guy to do the commit will succeed. So, this fellow came to commit first and it succeeds. Now, when this fellow tries to commit, it detects that this earlier fellow had already committed, but there is a conflict because the concurrent transaction had written the same data item and therefore, it says cannot commit. It is not the first committer of x and it is forced to abort. So, this is the series of steps. Note that when a transaction does writes to satisfy the snapshot property, what it does is it actually collects the writes and performs it at the end of the transaction. It does not do it ahead of time. So, that is the basic idea of snapshot isolation. Logically, when a transaction starts, it gets a snapshot. It does all its computation in the snapshot. If it wants to do a write, it does a write on the snapshot and it reads from the snapshot. When it wants to commit, that is when all its writes will have to be done back to the database. At that point, it will check if there was a concurrent transaction which had already written that item. If so, it rolls back. If the test passes for all the data items which it wants to write, it is allowed to commit. That is the first committer wins version of snapshot isolation. That is another variant which is basically equivalent, but its test is slightly different. It is called the first updater wins. In fact, many databases implement that because it is more efficient, but for simplicity, I would not get into it. So, what are the benefits and what are the problems with snapshot isolation? The first benefit is readers never block. They do not affect other transactions either. They do not get halted. They do not halt anybody else. The performance is similar to read committed. In read committed isolation mode, what happens is transaction will read whatever is the current committed version. If it reads again, it will read again whatever is the current committed version. In fact, read committed may have to block if somebody is currently updating the data item. Snapshot isolation will never block. In fact, it can be better than read committed in performance. But the good news is it avoids many of the common problems which people have pointed out with lack of serializability. The first problem is dirty read. What is a dirty read? A dirty read is a value which has not been committed. If you read a value which is not committed and that guy rolls back, you have a schedule which is not recoverable unless you roll back, in which case there is cascading about. Now, it should be clear that with snapshot isolation, there is no dirty read. The snapshot contains only committed updates. Any uncommitted update is not part of the snapshot. So, no dirty reads. No last update. What is the last update problem? A transaction did an update and that update was clobbered by somebody else who never read that value. So, it can, because concurrent transactions cannot write to the same item, the last update problem does not occur. No non-repeatable read. What is repeatable read? If you read the same value again, you should get the same value. So, if it reads the same value again, it gets it from its snapshot and nobody can update its snapshot except itself. So, reads are actually repeatable. Then, it says that predicate-based selects are repeatable and what many systems claim is that this means there is something, no phantom. Now, I am not going to have time to get into phantoms. I will briefly mention it later on, but there is a problem called phantoms and snapshot isolation wateries will say that phantom, the particular problem called the phantom problem does not occur because if you ask for what all accounts, say what all instructors are there in the CS department, that answer will not change even if other people insert records into that. But, turns out that is kind of a lie. A particular form of the phantom problem still persists. I will discuss that if I have time, which I probably do not. But, that is the good news so far. The bad news is snapshot isolation does not guarantee serializability. Basically, if it is serializable, you know it if you have two transactions, which update a common item or read a common update or read a common item. If you run them serially, it should be the case that if the first one updates in the second one should see the update. With snapshot isolation, you can have two transactions running concurrently such that neither sees the updates of the other and you can get into trouble. It is not serializable. So, let us take a very, very simple example. Let us say there are two transactions, one of which reads y and writes that value to x. The other one reads x and writes it to y. These are two transactions. Think what happens if they run serially. Let us say that x is 3 and y is 17. If they run serially, what will happen? The first one will read y and write it to x. When it finishes, x and y are both 17. When the second one runs, it does nothing. It writes 17 to x, which is y, which is already 17. So, both have the final value 17. On the other hand, supposing t2 ran first and then t1, t2 reads x and writes it to y. So, it is going to read 3 and write it to y. At this point, x and y are both 3. Then, if t1 runs, it will set them to the same value. So, the n both are 3. So, with either serial ordering, the final result is either both are 3 or both are 17. But, what happens with snapshot isolation? Both start at the same time. Both get a snapshot. What is the snapshot? x is 3, y is 17. The first one will read y and assign it to x. So, what does it set x to 17? The other one will read x, which is 3 and write it to y. So, what does it set y to 3? Now, if you look at the local states, x and y are actually interchanged. At this point, x is 17 and y is 3. Now, they are both going to check. Was there any concurrent update? If so, they will roll back. But, it turns out in this case, t1 updated x, t2 updated y. So, there is in fact no data item, which both of them updated. So, the check for did this write something, which was also written by a concurrent transaction says no, no problem. Nobody else wrote x. For the other one, nobody else wrote y. So, both of them will be allowed to commit by snapshot isolation. So, what is the final result? x and y are flipped. In snapshot isolation, the final result is x is 17 and y is 3. This let me clarify. This can happen in snapshot isolation. If you run the actual transactions, it may be that one runs first then the other. So, even though PostgreSQL supports snapshot isolation, when you run it, you will see that everything is fine, both are 3 or both are 17. The point to notice this schedule, where the values are flipped can occur with snapshot isolation, which can never ever occur with a serial schedule. In other words, what we have just proved is that snapshot isolation does not guarantee serial visibility. By the way, this kind of a write is called as Q write, because both of them are reading something and then they are writing other data items. So, if a transaction, both the transactions write the same data item, there is no problem. The problem here is they are writing different data items. There are other examples like this. So, here is another example. Now, every order which a company receives has to be entered in a database with a new identifier and most systems will require that there be no gaps. If there is a gap in this sequence, it is like what happened to that order? Did you delete it? Auditors, financial auditors do not like gaps in sequences. So, any financial system does the following. It will find the current maximum number for an order and add one to it and create a new order with that number. So, let us say that this transaction has two copies. Two people are trying to order simultaneously. So, both the copies read all the orders and find the maximum order number. So, both of them are going to get the same order number, because they are reading from the same, both get the snapshot. In this case, they are identical. They are running concurrently. So, each of them finds the current maximum order number is say 10. So, both of them will set the number for the new order to 11. There are two separate orders, both of which have the number set to 11 and then they insert those tuples. Will snapshot isolation detect a problem? Did they write the same tuple? No, they inserted a new tuple. So, they wrote actually two different tuples. There is no right conflict between the two. But what is the final state? In the final state, you have two orders with the same order number 11, which could never have happened with the serial. This is actually a real problem. In fact, we saw this problem occur in IIT Bombay and guess what? It was caught by a financial auditors. They pointed out that our financial system, which was built by a third party, not by our programmers. They pointed out that it had created a situation where there were two, in this case it was not orders. It was voucher for payment. They said there are two vouchers with the same number. How did this happen? As far as they are concerned, it may be an evidence of some corruption going on. It was not, but that is their job to check for such things. So, then we said, how did this happen? And it was third party code. So, we had a lot of trouble figuring out what the code was doing. But luckily, we had, we could talk to them. We told them this has happened. They tried to debug it. They could not figure out what was going on, because they did not understand snapshot isolation. They were using Oracle. It turned out, the person who had written the code initially, he had left by then. He had realized there could be a problem and he had tried a variety of tricks to avoid the problem. But he did not know the right way to avoid it. And as a result, he could not avoid the problem. He said, well, I tested it. It does not seem to occur and he just left it at that. And it did not occur very often. How often do two orders get created at exactly the same time? It is rare. In our system, we found in one year, we would have maybe five such instances. We could go back and check from the database how many orders had repeated numbers. In one year, it was five or ten. But we had to avoid it. How do we avoid it? So, it turns out that in this particular case, if we had set the order number as a primary key, this would have been avoided. In fact, most systems would set the order number as primary key, obviously. You do not want duplicate order numbers. And then, what would happen is, even with snapshot isolation, the second insert of that record will detect the conflict and will roll back. So, life is fine in that case. In fact, this is why most people get away with using snapshot isolation in Oracle, even though this problem can arise. The problem arises, but the primary key constraint deals with it. And they never knew about this. It just works. In our case, it turned out life was a little more complicated. The way the system was designed, there was something called a bill, which had a primary key. With bills, this problem could occur. And in fact, the second insertion would roll back. But when we made a payment against the bill, there was something called a voucher number, which was created, which could have duplicates. It was legal to have duplicates, if it was for the same bill. So, we could not say that voucher number is a primary key. And as a result, this situation actually happened, where the voucher number was duplicated for two different bills, and we ran into problems. So, the solution for this is actually interesting. We will come to it in a couple of slides. So, a few points about snapshot isolation. First of all, if you run Postgres or Oracle or any other database, by default, the level of serializability is read committed. None of them even supports serializable or snapshot by default. So, what does read committed mean? It basically means the moment an SQL statement runs. If it updates the data item, it just goes and in effect allows, it does it. And it keeps a shared lock. Read locks, it can release at any point. It gets a read lock, reads, releases. It is not at all serializable. This is the default. You do not realize it. But for performance reasons, most database systems leave this as the default. So, they will say that, look, our system performs great. And then if something goes wrong, they will say it is your fault. So, it is our cleverness that it performs well. It is your fault if things break. That is cheating, but they all cheat. Even though the SQL standard says serializable should be the default. Now, on all databases using the SQL thing, you can change this level. So, you can say, set the isolation level to serializable. On some of the databases like DB2 and SQL server, once you set the isolation level to serializable, it will ensure serializability. Again, there are some trickery involved here. You can set it at the database level or you can set it at a connection level, in which case every time you make a connection, you again have to set it. But let us say you have done it. It will enforce it. With Oracle and PostgreSQL, they cheat. Even if you say, set the transaction level to serializable, they will quietly set it to snapshot isolation. They will not actually make it serializable. In fact, there was a point when certain people argued that snapshot isolation in Oracle is actually serializable. They did not realize that already somebody had shown that it is not. I think even before Oracle implemented it, they had already shown that it is not serializable. But Oracle went ahead anyway and confused enough people by doing this, that they thought that what Oracle does is serializable, but it is not. So, this is what happened to us. And how do we correct it? As I said, I will tell you in a moment. But the reason why Oracle could do this and get away with it with nobody noticing for the most part is that most of the time when such problems arise, you have a concurrency, integrity constraints such as primary key, which prevents a problem. But it can happen. Our application is an example where it did happen. So, programmers should know about this fact that it can happen. That is one of the reasons we put more coverage of snapshot isolation in this edition. There is also another kind of serializable anomaly for read only transactions. I am not going to get into that. I should mention that Oracle implements this first updater wins variant of first committer. I will skip the details. I have already said that both implement snapshot isolation by default. In SQL server, recently they introduced snapshot isolation as an option. So, you can say set isolation level to snapshot and then it will run snapshot isolation. So, the problem is I told you there is a lack of serializability. What do you do about it? How do you enforce it? And the answer is provided by both of these systems. Everyone who implements snapshot gives this one extra clause in SQL. Here was a query which was read only, which was select max order number from orders. Now, what you do is you add a clause at the end of the query, which is shown in red. It is a select max order number from orders for update. What does that mean? It is not actually being updated, but you are telling the system, please treat this read as if it were a write. In other words, what will happen is when you check whether the transaction can commit at the end. If two people did the same read in concurrently, they ran concurrently and both of them read orders, but both of them had labeled it as for update. What is going to happen? The system will treat this query as if it updated orders. Whatever tuples from orders were read will be treated as if they were updated. Now, in this case, supposing they had scanned orders relation, they would have read all the orders tuple and it will appear that both of them wrote it. So, the second one to commit or do the update will be rolled back. So, the for update in this case will prevent this particular conflict and ensure serial visibility. In reality, most system would use an index on order number to find the maximum order number, in which case they would read that one tuple, which was the current maximum order number, but then again both will be treated as writing it and even in that case, only one would be allowed to commit, the other would roll back. So, the for update clause causes a conflict between any writer and a reader which uses the for update clause. It also causes conflict between two readers which use the for update clause and in the above example, if you use it, it guarantees serializability even without the primary key constraint and in fact, that is what we did. In our system, we went back and added a for update clause to that SQL statement and after that, that problem went away. Of course, there is a cost, even if they do not actually conflict, one of them will be rolled back, but if you need to prevent it, that is worth doing. We are behind time, have just a couple more slides. One is on the phantom problem and the other is on weak levels. Maybe what we will do is, let me just take another 2, 3 minutes and wrap this up quickly. I will not get into details. I will let you read it, but here is an example of what I call the phantom problem. Let us say a transaction that finds the sum of the balances of all accounts in the peri-rich branch and another transaction that inserts a tuple into the same relation, an account at peri-rich. The first transaction found what are all the tuples, accounts in peri-rich and locked all of them. It is running now. The next transaction comes along and inserts a new account. Is this tuple locked by the first transaction? No, it is a brand new tuple. It could not have been locked by the old transaction. So, it inserts a tuple, it commits and goes away. So, it is committed. The first transaction now does some more locking. What is its lock point now? Its lock point is after the second transaction. But when it commits, what does it see as the sum? It sees the sum before the second transaction. It did not see the account that was inserted by the second transaction. What we have just shown is that if you lock tuples like this, you have a situation where you are not actually guaranteeing serializability. Turns out this is a fundamental problem. If you lock just the tuples which satisfy the predicate branch's peri-rich, then you have a problem when a new tuple is inserted. You actually need a different kind of lock which not only locks all the tuples which are currently there in the peri-rich branch, but conceptually it locks also any tuple which may be inserted subsequently with branch name is peri-rich. Now, how do you implement this kind of thing? There are interesting and efficient solutions. I do not have time. But I should mention that again Oracle and PostgreSQL do not prevent the phantom problem. They very much allow this to go ahead even with snapshot. So, although in an earlier slide, we said repeatable reads are guaranteed. Therefore, no phantom. There are web pages on the web which claim this strongly. It turns out that this phantom problem very much occurs with a snapshot isolation as implemented in Oracle and PostgreSQL. So, it does not guarantee serializability even in this case. Certain other database DB2 pays a lot of attention. So, does SQL server. Both of them will actually ensure that the phantom problem will not occur. If you say isolation level serializable, they will ensure that the phantom problem does not occur. So, they are a little more trustworthy in the serializability sense. So, the book has some more coverage of efficient protocols which use indices to prevent the phantom problem. I will leave it to you to read it. I will also mention, I did mention this before that SQL allows different levels of serializability starting with serializable which is supposedly the default, but nobody creates it as default. Repeatable read which says that if you read the same tuple again, you will see the same value. Read committed which says that you may read the same thing again. You may see a different value, but you will only see committed values and read uncommitted which is you can even see uncommitted values. And set isolation level is the SQL syntax. In PostgreSQL, I think it is set transaction isolation level and you can set it to whichever one of these four that you want. And finally, I will mention that two-phase locking is very good. It is effective, but if you do two-phase locking on index structures which, you know, that is a tree. Every read or write on that tree goes through the root. So, these index structures especially the upper levels of the tree have a lot of reads and writes. If you do two-phase locking on this, then it affects concurrency severely in the system potentially. So, people have put a lot of effort into seeing how can we do special concurrency control protocols for trees which allows locks to be released early to increase concurrency without compromising the semantic correctness of what is going on. So, again I do not have time to get into the details, but if you are interested, go read this up in the book. We describe what it means for a tree to allow non-two-phase locking, but still guarantee some form of correctness even though nodes of the tree are not locked in two-phase manner. So, that is it for concurrency control. In the post-break session, we are going to switch into recovery, but before I start recovery, I will take a few questions on concurrency control. During the break, we had Professor Fatak who as you all know is the person who has made this whole thing happen. He is the one who had the vision for running such programs and has already conducted two of them on computer programming and this is the third in that series. Today, he happened to be in Indore and he actually was at the Indore Center and he got back to us. Now, if he is back at the Indore Center, maybe he would like to say a few words to all of you. So, let me see if he is at Indore right now. So, right now I have chosen Maulana Zal, Bhopal. If anybody there has a question, please go ahead. I will be happy to take which operation is a solution, their join will be done first or whether union will be done first. So, if you have a query of the following form, select from where, union select from where. So, first of all from the structure of the SQL query itself, it is clear that union is has a lower precedence from than select from where. So, the join is in the from and where clause. So, that will be done first before the union. That is how the SQL structure is. I do not know if that answered your question, but let us move on and see if there is any other question. There are quite a few. Shirpur R C Patil over to you just one minute before I turn you on. Yeah, Shirpur. Okay, this question is regarding yesterday's assignment, when solving different query plans, when we are executing it first, it is showing some time stamp as a or the execution time as a 10 millisecond. But the same theory is or the same plan is executed several times, it shows the different times. Why it is so? Because it is executed on the same relation over to you sir. Thank you, that was a good question. It is probably something I should have told you about before the lab, but I omitted to do so. So, let me explain it now. If you looked at the lab solutions which we made available to the coordinators yesterday, that had an explanation briefly, but it was hidden from you and today morning I have exposed that. So, you can go back and read the answer in detail for yesterday's lab assignment. It is now visible to all participants, solutions or explanations. But to answer the specific question, the question is if you run the same query again, you get a different time. Why is this happening? There are two basic reasons. The first time you run the query, most probably the data which you needed to run the query was not in memory. It might have been, if you ran some other query before, which acts as the same data. But if you start the database from scratch, you start up the database after you boot up the machine and so forth, you can be reasonably certain that the required data is not in memory and it will have to read it from disk. So, time goes in reading the data from disk. The next time you run that query, remember that the data is there in the database buffer as well as some of the data is there in the file system buffer. So, the next time you access the same thing, if it is in the database buffer, it is going to get it very fast. Even if the database buffer was small and it was evicted, it may be there in the file system buffer in which case it does not actually go to the disk. So, the second and subsequent runs are usually much faster than the first one. For some queries, the difference can be enormous. If you are doing thousands of random accesses first time around and second time everything is in memory, the time can run reduced drastically. Now, supposing you run the query again third, fourth, fifth time, you will often see that the time varies a little bit, not as much as from first to second, but there is some amount of random variation. Why does this happen? There are several reasons. One is that the timer used to measure the time is often little approximate. So, it may be a few milliseconds off. So, it is an inherent timing variation which does not mean anything about actual run time. It is just a measurement error of the operating system. The second reason could be that some pages were evicted in between. After the first run, a few pages were evicted. So, this second run found some pages, but did not find others. The third run found a different set of pages, but did not find some others. So, there could be some inherent variation because of IO even in subsequent runs. So, that is the reason you see some of the variability between ones. Now, this is a standard issue and whenever you report timing numbers, we did not ask you to do it here, but normally when you report timing numbers somewhere, you should actually run it multiple times. The first time on what is called a cold cache, meaning there is nothing in the database or file system cache. So, you report a cold cache number. You also report a warm cache number which is the subsequent accesses. And for the warm cache, normally you would take the average of multiple runs. You run the same thing two, three times and make sure they are not widely varying and report the average of those. So, when we ran these queries, for example, you would have found that there is a few if it was 40 milliseconds once, it might have been 42, 44, 36, you can report the average of those. Back to you if you have a follow up question. Hello, my second question is sir, we recorded the time before analyze. Like you have given the scripts, we run that after that it is created the table, inserted the value and before analyze, we found the timestamp for the queries and we find the timestamp for after analyze it. So, both timestamps are different. So, what analyze do? Yeah, so that is also a very good question. If you did not run the analyze command and ran the query, you got a certain timing. By the way, timestamp means the point in time, whereas time here means how long did it take to run. So, the correct word here is execution time not timestamp. But coming back, what the analyze command does is actually it updates the database statistics. Now, sometimes the analyze command after the statistics are updated, the query plan may actually change. So, if the query plan changes because the statistics initially were wrong and a particular plan was chosen, after you ran analyze the statistics were corrected and after this a better plan may be chosen. So, when you run the same query before and after, it would have different plans and because it has different plans, the execution times are different. There are also certain cases where the changes you did were not that big. So, even though you ran analyze the statistics did not change that much, the query plan did not change at all in which case the run times would be identical. Analyze only affects the statistics, it does not affect anything else. Hope that answered your question, back to you if you have any follow up. Now, let us switch back to Indore, where Professor Fatak is ready to address you. Over to Indore. Hello, thank you very much. In fact, that is what I was going to suggest that at any remote center when you raise a question, immediately after raising a question, as a precaution you just switch off your mic for the time being, then your echo will not go back to the center. But otherwise, I think I just chatted with the participants here, they are enjoying the course immensely and I am very happy to talk to people from remote center. Another reason why I wanted to do that is that tomorrow a good teacher from any one of the remote centers could be teaching a course like this to thousands of teachers. So, please note that this technology is scalable in multiple facets. So, please be prepared to volunteer in future if not a complete course to actually give expert lectures on some topics of your own specialization to larger number of teachers across the country. Thank you so much. Over to you. Thank you, Professor Fatak. One of the reasons I have not been using the push to talk consistently is that it requires multiple clicks for me to switch between the modes. So, I have been using it only when we actually listen, we get an echo. We had not realized this feature exists earlier on in the course. But when we had the team from Amrita come here, that is when we talked to them and figured out how to do this. So, whenever we get echoes, I will follow the same protocol. I will ask you to stop, switch to this mode. Thank you. First one is, can a view serializable schedule be conflict serializable? The answer is it can be. Every conflict serializable schedule is also view serializable. So, certainly most view serializable schedules in fact are conflict serializable, but there are a few which are not. That is all. The next question is, what is a phantom phenomenon? I covered it very briefly in my last slide there and the phenomenon is basically a conflict between a query which does a predicate read. What is a predicate read? It is of the form, give me all tuples that satisfy a predicate. So, there is a predicate read and there is an insert or in some cases an update also can have the same effect, which creates a new tuple which satisfies the predicate or updates a tuple which never satisfies the predicate and now makes it satisfy the predicate. Now, what happened is when you did the predicate read first, if you locked all the tuples that satisfy the predicate, you would have missed these new tuple or the other tuple which initially did not satisfy the predicate. Now, if somebody comes and inserts a tuple, the locking which was done of the existing tuples has missed this. So, the first thing which people will wonder is what is this? I thought you said two phase locking ensures conflict serializability and here you have a case which is actually not conflict serializable. The conflict is between a predicate read and a write not between a tuple read and a write. So, it, but it is a conflict and it turns out that if you view the same thing at a different level, how did you do the predicate read? How did you find out which tuples satisfy that predicate? To do so, you needed some data structure. Maybe you scanned the tuples in the file, maybe you used an index to get to the tuples, but you had some auxiliary information which helped you locate those tuples and maybe it also helped you avoid tuples, avoid finding tuples which did not satisfy the predicate. So, the point to note is that is also information and to satisfy two phase locking, you really should be locking that information also. So, to really do two phase locking, you should lock everything which you write not just tuples, but also relation metadata. So, what is the relation? Where is, where are the tuples of the relation? What all is in the file? All this is metadata. So, really metadata ought to be locked in two phase fashion, but practically systems do not do that for because it affects concurrency and because they do not lock metadata in two phase fashion, you land up with the phantom problem and then you have a solution to the phantom problem. I did not get into the details, but the book talks a little bit about how to do concurrency control to prevent the phantom problem. So, I hope that answers your question. Next question, can you state some SQL queries which could lead to starvation so that I can implement in the lab session? Unfortunately, it is not so easy to create starvation, but one way is to have a single long query and while it takes a long time to run, you introduce many updates. With snapshot isolation, this is actually easy. So, all you need to do is have a transaction which runs for a while. So, and it should also do an update. So, it has to stop. If it is just a read only transaction, it will never stop. It will finish. So, it has to do an update which conflicts with something else updating. So, let us say you run a query which takes a long time, then updates something. Meanwhile, you have other transactions which update exactly the same thing, but they do not read this other large relation. So, and there is steady stream of those coming in one after another. So, you have to keep submitting the one after another. Then you will see that this other query which does a lot of read and then the update will keep aborting repeatedly and when it aborts, it will give a message saying serialization error or some such and it will fail. So, feel free to try it out in today's lab and report it in your assignment. The next question is partial rollback causes could lead to violating the atomicity property. Then why do we use partial rollbacks? That is a very good question. When you have a partial rollback, the idea is that you do not just roll it back partially and then commit it. Rather, you roll it back partially and then restart the execution of the transaction from the point up to where the rollback occurs. So, think if you had a transaction executing one query after another, you roll back, but now your code should be in a position where it was at the first place. So, it will again start executing and go forward. So, the final thing which it does when it commits is what it, that is the transaction. So, partial rollback by itself, if you just partially roll back and commit, it will cause atomicity violation, but if you do this, it will be okay. But again, like I said, partial rollback is practically not very useful if you are running transactions from a program because you will have to know that it rolled back up to that point. Your program execution control has to go back to that point which can be done, but it is hard to do it. So, you probably will never use it. How can we design a database in a regional language? How can you have operational data in Marathi? Actually, that is very, very easy. We just use Unicode and as I told you very early on, use nWareCare or in Oracle WareCare 2 to store multi byte Unicode text. And once you do that, you can have a system which allows data entry in Dev Nagri and stores it in the database. It will work just fine. Okay. Why multi version timestamp ordering protocol is not commonly used? Why is it not used? First of all, the timestamp ordering protocols require a little bit more work to ensure that there is no problem with recoverability and cascadelessness. And on top of it, once you implement that, you also have to keep multiple versions of the data item which you have to garbage collect. That is more work. So, for efficiency reasons, they are not so widely used. However, some databases do support keeping multiple versions and they could implement multi version timestamp protocol. I am sure that some people have built it. It is just the commonly used databases don't have it. That is all. There is nothing wrong with the protocol overall otherwise. Next question. I presume that locking is implicit when we issue a select or update command. But how do we unlock a specific data item? The answer is there is no explicit command in most databases to unlock a specific data item. Some databases allow special SQL extensions which say lock a table or unlock a table. But it is not part of the standard. You can check if your database does it. I think Oracle has a lock and unlock command for tables, but it is not widely used. Next question is, does the database support column level lock? That is again a good question. So, we have talked of tuple level locks. We have talked of relation level locks. How about locking just one column of a relation while leaving other columns unlocked? I do not think any database I know of actually supports this. But it does make sense. There are situations where you may want one guy to update one column and another guy to update another. But again I do not know who supports it if any. The next question is, in PostgreSQL there is a concept of table space. Do we need to specify the table space when creating tables? That is another good question. So, what is the table space? It is basically an area of disk logical area where you create tables and many databases Oracle, PostgreSQL and others. Let you create table spaces and then you can associate a relation or a schema with a table space. PostgreSQL has a default table space and everything which you create goes to that. So, in general you do not need to mess around with this, but having separate table spaces might give you the ability to back up one table space while not backing up another and so on. So, it gives you some operational flexibility. You can move a table space from one disk to another while leaving other table spaces unchanged. So, if you outgrow your disk, you can move one table space to another disk for example. Next question is, we could not follow skew insert. Can you once again explain it? I will see if we have time at the end. Otherwise we have to cover recovery also. So, please read it up on your own if I do not have time to cover it. I will stop there with questions.