 So, the next topic is concurrency control and as we discussed earlier, concurrency controls goals could be to ensure serializability or it could be to ensure some weaker form of serializability which a particular application may be happy with. So, how does one control concurrency locking is the traditional way, but then there are many variants which we will look at a couple of those. In particular, we will look at a little bit at the multi-version schemes on which snapshot isolation is based and at snapshot isolation also briefly and there are other topics in this chapter which we would not quite cover here including concurrency in index structures and so forth. So, as you are familiar with locking, there are exclusive locks and shared locks and standard lock compatibility matrix between exclusive and shared. So, we have the usual lock compatibility matrix and locking protocol is a set of rules which govern what you do. Just the fact that you do locking does not guarantee serializability. You have to follow certain rules for locking which will guarantee serializability and the standard locking protocol is two-phase locking, we will see that in a moment. Then there are the usual drawbacks of lock-based protocols which include deadlock situations. Again, I assume all of you are familiar if anyone has any questions, stop me otherwise I am just going to flip through these slides. Starvation is another possibility with locking, again I assume you are familiar with it. And two-phase locking, what is two-phase locking, the most barebones version of it is that you obtain locks in a growing phase, release locks in shrinking phase and once you release the first lock you cannot acquire any more locks. So, two-phase locking it is easy to show that it guarantees serializability based on the lock point. Lock point is when all the locks have been obtained. In fact, any point between the last lock obtained and the first unlock anything in there can be treated as a lock point and we can show that if you take any point in here the transactions are serialized by that point. So, that is easy and as you no doubt know, plain two-phase locking unfortunately does not guarantee recoverability or cascade freedom. So, what we normally do is strict two-phase locking which says that write locks, exclusive locks must be held until commit, only after commit can you release it which ensures that nobody can read uncommitted data items, at least nobody who gets a lock will read uncommitted data items. What about if a transaction wants read uncommitted, then it does not even get a lock, it just reads the data item, somebody else has exclusive lock but this guy can still read it, go and cause a problem. But writes, the database will make sure it can never happen without an exclusive lock, that is guaranteed. A rigorous two-phase locking, all locks are held till commit or abort and in fact, this is typically what is actually implemented because the database has no idea when you are going, you are ready to start releasing locks unless you explicitly say unlock, the database does not know. So, normally locks are obtained as and when you read and write items and they are released only at the end. So, rigorous two-phase locking is what is actually implemented. This terminology is slightly non-standard, if you read database system manuals they will say we use strict two-phase locking, by strict two-phase locking they mean what at least this textbook refers to as rigorous two-phase locking. This is the original terminology which you might consider standard but in common parlance these days strict often means rigorous. Now, when does transaction obtain locks? Again this is a usual thing, whenever you do a read, you get a read lock, when you do a write, you will get a write an exclusive lock which is an upgrade if you already had a read lock or a shared lock. So, that is lock conversion and this is automatic acquisition of locks that is again standard. If there are any questions ask me otherwise I am just keeping this. So, next is who implements the locking, how is this done physically? That depends on the database system, but pretty much all database systems which you know the part of it which runs on a single machine that is in a single shared memory you may have multiple processors, but as long as they are all running off a single shared memory the standard way to do locking is to maintain a lock table in shared memory. So, yeah I do not have details here, but again in the book there are details on how to implement the lock table in shared memory, but what about access to the shared memory itself? What if two people update something in the shared memory at the same time? So, you need a lock to control access to the shared memory, but that lock is different from a database lock. The normal database locks are held in two phase manner till you come in. That lock is a short term thing which just prevents anyone from updating the shared memory while you are updating it, then you release it even whether the transaction is committed or not is irrelevant, while you are updating the lock table you get a short term lock, update it, release it. Then there is a usual issue of deadlocks, how do you detect deadlocks and how do you resolve deadlocks and there are several approaches. One is to completely prevent deadlocks, now how do you prevent deadlocks? The practical mechanisms for preventing deadlocks which you as a programmer can use are basically to order access to data items. So, if your transaction is accessing items A and B updating A and B, if another transaction updates B followed by updating A, then you are setting up the stage for deadlocks. We have had, I have seen situations where programmers did this and then the system would deadlock frequently. The programmers write two transactions, one of which updates writes B writes A, the other one writes A writes B. So, what happens when they run concurrently, one of the transaction gets a lock on item B, the other gets a lock on item A and then they try to get locks on each other and they deadlocked. So, this is a bad idea, which could have easily been fixed by making sure both of them lock A and then lock B. So, this is something which as a programmer you can do, this is part of maybe tuning or it should be actually automatic, but if it happens that somebody made a mistake and then you detect frequent rollbacks, then you can go back and see what is the transaction doing, can I make some changes to these transactions to either completely prevent the chance of deadlock or at least reduce the chances by re-ordering. Any questions on this? What happened? So, if you are looking at real distributed databases today, so if you look back at the distributed database literature from a while ago, there are lot of different schemes. Today, what is used is nobody really gives control on locking to external thing, if you have a database here and another database there, that guy will never allow you to hold a lock and go away, it is very bad for him because you can mess him up. So, you cannot really truly distributed systems do not allow locking across different nodes and they are kind of independent systems and at best you can do an update here and propagate it there. I will leave it at that. In a parallel database, there are many things which are done, but I would not get into that at this point, but this protocol works regardless. If you order access, does not matter whether it is parallel distributed, centralized, it will still work, assuming all of them are doing locking. People have done a lot of research on what if database A uses locking, database B uses snapshot, database C uses optimistic, how do we ensure serializability in this situation and it is actually very hard. So, although there has been research, nothing is practical. How do you detect deadlocks? The usual way is you construct a weights for graph. So, if a transaction is waiting for another transaction to release a resource before it can proceed, it is waiting and that you form a graph and detect cycles. Any questions on this, please ask. Otherwise, I am going to continue zipping ahead. You are familiar with all this. How do you recover from deadlocks? You can roll back totally or you can roll back partially up to the point where you release a lock with somebody else needs and then they can proceed and you can also restart after sometime and continue from there. And of course, when you roll back, you do not want to kill the same guy repeatedly. So, there are tricks to make sure that every transaction will make progress and eventually commit instead of becoming the victim far too many times. Now, the most important extension to locking and one which is very, very widely used is multi granularity locking. Again, for lack of time and also because I think most of you are probably familiar with it, I am skipping this. But the key thing to note there is that there is a notion of explicit locks and then there is a notion of implicit locks. So, if you explicitly lock a page, you implicitly lock all the records within that page. If you explicitly lock a relation, you implicitly lock all the pages of the relation. And the protocol has to make sure that if you explicitly lock a page, nobody can explicitly lock a tuple inside that page. Or if you explicitly lock a relation, nobody can go in and lock a page of the relation or vice versa. If somebody existing already has a lock on a page, you cannot get a conflicting lock on the whole relation. And how to do that, the protocols are there in the book. Is there any questions on this? We can discuss that. So, these protocols are all based on locking. I will come back to multi-versions of these subsequently. The other alternative approach is to use timestamps. Yeah, you have a question? Overhead is a little hard. In today's lab, we have some exercises which demonstrate what is going on. They will demonstrate that something waits at some point or that something rolls back. But they are not performance tests. They are just to observe what is going on. Now, if you want to actually find the performance overheads of locking, you can do that by creating suitable transactions. But even there, it is going to be difficult to understand the database is a black box. So, what caused a certain performance issue is not necessarily obvious. So, you will have to set up transactions which conflict on data items. And then as a result, you can show that if they do not conflict, there is a certain speed. If they do, then the speed reduces. So, it is possible to set up such exercises. But the net result will be you will either say running fast or slow. So, may be a different way of doing an exercise like this. Maybe to create an application which is running slowly and then tell students, figure out what is wrong with this and go fix it. It is a tuning exercise. So, you can do tuning on SQL. You can do tuning on indices. You can do which we did yesterday in effect. And you can do tuning on concurrency similarly. But the biggest problem in all this is to set up a situation where there are actually conflicts. Multiple transactions accessing the same thing. So, in the real world, this will happen if there are many different people updating a data item. In a lab where you do not want to create a hundred different processes, you may have to create an artificial situation where two processes are conflicting frequently. Although in reality, that is not very likely. Yeah, you can observe the time taken typically. Some databases will also let you observe what are the locks in the database and which transactions are waiting. So, I have not used the latest version of the Oracle things. Sorry, PostgreSQL. But PostgreSQL does have some monitoring. It can tell you what transactions are there, if they are idle or they are waiting and so on. So, you can visualize a little bit of what is going on in the system. Through PG admin, there are some options there. You can play around with that. Other database of Oracle, for example, there are tools which will let you, their own SQL developer or other tools like Toad. You can monitor what is happening in the database in terms of locks and weights and so on. So, there are ways to look inside the database to some extent. Other than that, the best you can do is look for weights. You see that transaction went to commit but it did not come out. It has not completed. It is waiting. You can see that. Or you can see the total time to commit for a set of transactions. It is not easy to create inspiring labs, let us say. Any other questions? So, time stamp-based protocols keep track of at what time data items were written and what they basically make sure is that no, and they also assign time stamps to transactions. And what they do is make sure that no transaction sees the effect of a transaction with a lower time stamp. So, basically the time stamp of a transaction is the serial order in which transactions run. If anything happens out of time stamp order, you roll back transactions. So that what we need is for this is every transaction must have a time stamp. In the basic time stamp protocol, the time stamp is issued when the transaction starts and newer transactions will have higher time stamps. And every data item needs two time stamp values. W times time is the largest time stamp of any transaction that successfully executed right. If somebody tried to execute right and because of a time stamp conflict was rolled back, that does not matter. Similarly, read time stamp of any data item is the largest time stamp of any transaction that successfully read that data item. Now, it is possible that a transaction read the data item. Another one came after some time and read it. The second one may have a lower time stamp. So, the second one does not actually update read time stamp. For write time stamp, can this happen? Can you have a guy coming later with a smaller time stamp? Well, we will come to that. There is optimization called Thomas' right rule which you may be familiar with. With that, it is possible. Without that, it is not. So, the protocol is fairly simple. Whenever you issue a read, if the time stamp of the transaction is less than the time stamp of Q, then basically TI is trying to read. It actually needs an older version of Q which is not there anymore. So, TI rolls back. If it is greater than the time stamp of Q, then it is reading something written by somebody who is serialized before it. So, it is okay. It can go ahead. And the time stamp is said to the maximum of the original time stamp of Q and the time stamp of this transaction. So, this is what I said. It is possible that transaction at time stamp 100 read the item. Then another with 90 comes which will not even update the time, read time stamp. What about a write? If the time stamp of T is less than the read time stamp of Q, what does that mean? You are trying to write something which this other guy should have read in serial order. But this other guy has already read it and you have come too late. So, you are told, sorry, you have come too late, you have to roll back. And so, that is the first test. The second test is if your time stamp is less than the write time stamp, then what do you do? Again in the basic protocol, this means somebody has already written it to something which you should, according to the serial order, you should have written it first, then that person should have written it. But they have already done it. You are too late. And otherwise, you write it and just update the write time stamp. We know it. The write time stamps can only increase with this. So, there is an example here. I think for lack of time, I am going to skip it. And it is very easy to show that in the time stamp protocol, a transaction with a smaller time stamp has to precede on one with a later time stamp in terms of any conflicts. If they both write the item, one should have finished first and then the other, the later one does it. Similarly, for read write, the ordering will be preserved. So, there is clearly no cycles and everything is fine. There are some issues. The schedule may not be cascade free, may not even be recoverable. So, then you have to modify the protocol to keep some form of locking to ensure this kind of, to ensure cascade less schedules. I am going to skip this. There are few solutions. One of which is to do all the writes at the end. That is kind of the default assumption that is done atomically at the end. But then that may lock the database for a long time if there are a lot of writes. So, then there are some further optimizations to deal with that. Okay, I am going to skip this other details. And you had some questions about time stamp. Yeah. So, processing overheads of time stamps. Yeah, there are a lot, the storage overheads are significant for time stamp because every data item has to have a time stamp in the database. And the time stamp has to be around until some old transaction has gone away. So, it is possible to delete the time stamps completely if no transaction which is active now has ever has written this object. But that requires more work. So, there are overheads in some databases implement versions of it mainly for mighty version. So, that you can, we will see mighty version shortly. So, there are overheads but some implementations decide it is worth paying the overhead. The next protocol is validation which is actually you can think of it two ways. So, validation is kind of like time stamp, but the exact procedure is different. Validation can also be related to snapshot isolation which we will see later. Again, there are some commonalities and some differences. But let us see what is validation protocol. So, there are three phases. The first phase TI writes only to temporary local variables and can read anything. What is done is whatever it reads and whatever it writes is recorded. You have a read set and a write set. Now, when a transaction wants to commit a validation test is done to see is it ok to let this guy commit. Again, in the basic version of the protocol this validation test is done serially meaning you have many transactions. Only one transaction at a time can be doing validation. There are some extensions which allow concurrent validation. We would not get into that. So, validation happens serially and the validation decides does this transaction have any conflict with whatever committed earlier. If so, we will roll it back. If not, we will let it commit and it will also write the values into the database before allowing any other transaction to validate it. So, the write phase is the last phase when you are writing the updates which it did back to the database. Until the write point none of its updates goes to the database. That is an overhead to this. It means it should keep you know if it writes a lot of items you have to keep a lot of space to keep the values which will eventually be written back. So, validation actually has a significant overhead. So, most implementations do not implement it as is. So, this protocol is also called optimistic. Why is it optimistic? If you notice, transactions are never rolled back in the middle. They just go ahead. They read whatever values they can get. They do not do any writes immediately. And until the commit point, they just go ahead. And only at the time when they want to commit, does the validation happen and then you decide whether to commit or not. So, they are very optimistic. They believe that everything will go fine. Whatever value they read will be fine and they proceed. And if something goes wrong, then fine you roll back. So, it's actually been shown that under low conflict situations, it's actually a good idea to be optimistic. But there are overheads. So, that if you just look at concurrency by itself, it's a good idea to do optimistic under low overhead and low contention. If there is a lot of contention, many people are updating the same data items. This can result in a lot of rollback. So, it's not a good solution under that situation. So, how do you do validation? We will skip the details again because of lack of time. And if anyone has questions about it, we can discuss it afterwards. Then the next key idea is of multi-version schemes. The key idea so far, we have assumed that a data item has a value. If you read it, you can read that value. Or you have to wait till somebody finishes writing the value and then you can read it. And that's it. Those are the only two options. You can read the current value, you can wait or you can roll back. It's a third option. With multi-version schemes, you can have multiple versions of the same thing so that you can read a version which you need. And whenever you write the data item correspondingly, you cannot directly go and update the original copy. You can create a new version of it. So, that's the basic idea of multi-versioning. And multi-versioning can be applied with several different protocols. You can have multi-version timestamp ordering. You can have multi-version two-phase locking. What is multi-version two-phase locking? It basically allows, it creates multiple copies. But readers, read-only transactions can use, they are guaranteed that whatever copy they need will always be available. So, they will never block. They will always have a copy which they need and they will commit. Update transactions on the other hand use locking to prevent conflicts. It's a very brief summary. There are again details in the book. So, that is multi-version two-phase locking. And multi-version two-phase locking is actually a very nice protocol because for updaters, it guarantees consistency, serializability through locking. On the other hand, if you have a read-only transaction, it guarantees it will commit without any locked conflicts. It will get a version it needs and commit. Its serialization point may be a bit earlier in the history. That is, it started at time 11 o'clock. It will see all data as it was at 11 o'clock. In effect, it sees a snapshot of the data as of 11 o'clock. It may run until 12 o'clock. Updates may have happened to the items it was reading, but it will never see those updates. It can completely see only what was in the snapshot. And of course, that snapshot is consistent at that point and it will commit. So, that's a nice property. And for update transactions, they use two-phase locking. So, they are safe. So, this appears to be a very nice protocol. The only problem is who judges whether a transaction is read-only or an update transaction? How do you know? Somebody has to declare it. That's more work. So, what and if you have an existing base of applications, those cannot exploit it. They have to be rewritten so that each transaction has to declare whether it is a reader or a writer. Only read-only transaction. If you declare a transaction as read-only, it can benefit. Otherwise, it can't. So, the snapshot isolation mechanism does something similar. It also gives every transaction a snapshot view. Whatever the transaction reads will be exactly as of the point when it started. Except for updates that it itself makes. I have slides on snapshot. So, I'll come to that. So, anyway, coming back. So, all the multi-version schemes have this common property that reads never have to wait. Any questions on this? I'm going to come to snapshot isolation next. So, what is the drawback of multi-version? There are overheads to it. You need extra tuples, extra space for storing version information. Although very old versions can be garbage collected. If nobody is ever going to read it henceforth, it can be garbage collected. And again, there have been implementations. For example, SQL server had a prototype. I don't know if it's in the product yet. But there was a prototype which would store all versions of a data item historically. Why did they do that? Basically, their idea was you can actually go back and see the history of a data item if required. Why would you want this? It may be required for audit purposes. If you see the old pre-computerization era, I mean all our institutions probably still have many parts which are not computerized. People have these ledgers and they write in it. One of the properties is that you cannot overwrite, at least without being detected. Which is a nice property for preventing shady actions, corruption and so on. On the other hand, once you computerize it, it's very easy to go to the back end and do any update you want and cover all traces. So, the idea here was you keep all versions around. And so, you can later on audit it and see what happened to a data item. That is an overhead to keeping all this around. But if that's what the application needs, that's what it gets. So, that's actually implemented and reasonably efficient. How is the decision regarding whether the older version should be and useful to present? Whether it is useful? The application... No, any transaction, the application doesn't know anything about this. A transaction starts, it gets a timestamp, it reads all data items, versions which are the latest before that timestamp. The latest data item before that timestamp. That's all that the transaction is concerned with. Now, if you have three versions of a data item, one of which had a timestamp 20, one of which had a timestamp 50. And let's suppose the newest transaction in the system is of timestamp 60. So, let me write it here, so that it's clear. There's a data item 20, 50, in fact even two are enough. And now, the oldest transaction has a timestamp 60. There is no older transaction and future transaction timestamps can only increase. So, at this point, it's clear that even the oldest one will only read 50. It's not ever going to need 20. So, this can be garbage collected. So, that's most multi-version implementations include a garbage collection. Otherwise, this will keep collecting. On the other hand, this other database I told you, it's called ImmortalDB, that's the project name. Keeps all versions around for audit books. Any questions? Excellent. What are the different techniques used by most current implementations? So, all, most databases, in fact all databases internally do have locking. But they need locking for certain things like read committed. Now, what concurrency protocol they actually provide, they give multiple options by setting the isolation level. But if you take DB2 and SQL server, both are based on locking. Locking is the core. And they also provide some forms of multi-versioning. And SQL server also supports snapshot isolation, which we will describe. Oracle and PostgreSQL both support read committed by default, like everybody else, which uses some minimal form of locking. Otherwise, if you set the level to serializable, they use snapshot isolation, which I'm going to talk about next. MySQL I think uses locking, two-phase locking, as far as I know. I don't know if anyone knows if it has multi-version at this point. Optimistic is used in certain applications. I don't know if any database supports optimistic directly. But it turns out that snapshot isolation actually can be thought of as a, it does a validation, snapshot isolation, which we will see. It has a validation phase at commit. And that validation phase, it turns out is similar to optimistic concurrency control. So, part of optimistic is implemented as snapshot, although I don't know of any database which is based purely on optimistic. People have built specialized applications with specialized concurrency control techniques. And I'm sure optimistic is used in some places, because it's quite useful. No, it's a standalone application, which needs to support concurrency. It does not using a database, but it manages some data, then it can. So, what is snapshot isolation? Why was it motivated? A well-known problem earlier was that if you have a transaction which does a large reads and you just run that transaction, everybody else in the database gets affected. And the database appears to hang. If, you know, supposing you read every tuple in a relation, until you commit, if you are using two-phase locking, until you commit, nobody else can write to that relation. And all other users who need to write to it will see their applications hanging. This is a huge problem. I mean, imagine in that, in the earlier era, we didn't use web was not there. But if you bought an airline ticket, you go to the airline counter and then they say the booking system is hanging. Why is it hanging? Maybe it's hanging because the connection went down, but maybe also it is hanging because someone ran a query which read all the tuples in a relation and now you cannot write to that relation. So, this was a major problem. The solution which should be used, which was recommended in was that any such large transaction should simply use the read committed mode. That means it doesn't hold locks. It can hold a lock briefly, release it and move on. But that requires the cooperation of that transaction also, that it doesn't hold a lock. That is why the default in that system is read committed. So, even if the transaction does nothing special, it will release locks. So, the reason for default being read committed is to ensure this kind of hanging will not happen. So, that is a trade-off. On the one side, you want consistency. On the other side, if the cost of consistency is people cannot book airline tickets. People say the hell with consistency. I don't want to lose revenue. That's real money. What is consistency? Okay. So, two people got the same seed. Fine. We will deal with it. We know how to solve that problem. The expert of consistency is we didn't get money from one passenger once in a while. That's okay. We'll deal with it rather than lose all revenue from all passengers periodically. So, that's basically why people are willing. Eventually, they care about money, not about consistency, mostly, except for auditors. So, a snapshot isolation, it's selling point. Oracle sold this. They're saying that once you have this, you don't have to run a read committed isolation level, which can cause a lot of problems. You can run at serializable level, but still, you will never have to wait in this kind of situation. And that was their selling point. They got a lot of customers based on that. Of course, it's not really serializable. But the benefit is clear. The level is not as bad as read uncommitted. On the other hand, you never have to wait. What does it mean to never have to wait? Basically, it's multi-version, two-phase locking. In this, you give a snapshot to read-only transactions. Update transactions don't get a snapshot. They run normally. This is what we just discussed. And this works very nicely. But the catch is, how does the system know a transaction is read-only? So, a transaction runs. It does a read. It does another read. Should you give it a snapshot or should it be locking? So, you give it a snapshot. And now, it does an update. What do you do? Should you roll it back and rerun it? You can't. It's a problem. That means that to benefit from multi-version, two-phase locking, people have to declare that transaction as read-only or not. And as I was saying, people don't do this necessarily. So, snapshot isolation gives a snapshot to every transaction. But if only updates use two-phase locking to guard against concurrent updates, then what can happen? What do I mean by this? The transaction reads some old snapshot of data. But when it does an update, it does locking, regular locking. What can happen? Well, several problems can occur, including lost update. What's a lost update? You wrote something. Another guy overwrote it without even knowing you wrote it. How can that overwriting happen? It can happen because of a blind write, meaning they don't care. They just go and write a value. But that is rare. Typically, what happens is, before writing something, you'll read it and then write it. Or you'll insert a new tuple. That is different. If you're writing an existing tuple, you'll read and then write. So, what you want to make sure is that if you read-write, read-write, this situation should not happen. This is what is called... All of these are on the same data item A. Now, what is going on here? The first transaction reads A. The second transaction also reads A from its snapshot. Actually, both are the same at the beginning. Then the second fellow writes A. It gets a lock. The lock doesn't conflict with the other transaction because that is using its snapshot. It's not got a lock. The read doesn't get a lock. This is T1, T2. Now, this fellow writes A and commits and is gone. Now, what does this fellow do? It also wants to write A. It gets a lock. Can it get a lock? Yes, it can. This fellow got a lock and committed and went away. Now, this fellow can get the lock. The lock is free. So, it gets a lock, writes, commits and goes away. Now, this is a classic example of a lost update. Even though T2 read A and wrote A, it never saw the update of T1 and it overwrote the update of T1. This is a bad situation. If you try to fix multi-version two-phase locking by just getting locks, it's not good enough. You give a snapshot plus only updates get a lock, it's not good enough. Multi-version two-phase locking works because update transactions get read locks also. They don't just use a snapshot, they get read locks. This variant doesn't work. So, instead what they did is they... In snapshot isolation, the first part doesn't change. Every transaction gets a snapshot of the database. Any read that it does is from the snapshot. Of course, if it writes the item and reads it again, it will see its own rights. It won't see other people's rights, but it will see its own rights. But to prevent the lost update problem, snapshot isolation does some form of validation. And we'll see that in just a moment. So, like I said, this was proposed in 1995 in SIGMOD as a critique of the SQL isolation levels, but it's actually valid, though non-serializable protocol and implemented Oracle PostgreSQL. SQL server added it in 2005. DB2 has added some very poor form of it and eventually they are going to be forced to add it. So, it's very successful that way. So, what happens? How is it different from locking? So, any transaction executing a snapshot isolation gets a snapshot of committed data at its start. It always reads or modifies data in its own snapshot. It doesn't write to the common area at that point. And as a result, updates of concurrent transactions are not visible to it. So, the lost update anomaly which I said, it will almost happen. It will be caught at the variant. We'll see how. The rule for a right is as follows. There are two variants. The first committer wins variant says that you can commit if no other concurrent transaction has already written data that this guy intends to write. Now, how do you know who wrote what? What is concurrent? What do you mean by a concurrent transaction? First of all, a concurrent transaction is one which ran at some point concurrently. So, the transaction started here and ended here. This fellow started here and ended here. If their time intervals overlap, it's concurrent. That is the definition of concurrent. It's based on the times when they start. Now, how do you know if somebody who was concurrent? So, let me draw it here. Here's a transaction which ran from there to there. Here's a transaction which ran from here to here. Now, the rule for validation means the validation will happen here and here. Let's say both of them write A. This fellow also does a write A. If he did locking, the lock would have been released immediately here and this fellow would get the lock. But in snapshot isolation, this guy has written it. So, we remember that this transaction T1 wrote A and this is T2. When T2 comes to the end and it validates, it'll say, okay, I want to write A. Let me look at all transactions that were concurrent. Whether they're running or committed doesn't actually it's enough to see those which committed earlier. So, if anybody has committed earlier but was concurrent, if somebody was not concurrent, meaning they finished, if T0 finished even before T2 started, we don't care about it. What we care about is anybody who overlapped with this. And so, T1 overlapped. So, we'll see if did T1 write A, if so, it'll roll back. If T1 did not write A, then T2 doesn't have a problem with that. So, every item that it writes, it is going to check if a concurrent transaction wrote it or not. So, this is validation. If you are familiar with the validation protocol, the write validation basically does the same kind of thing. There are actually two kinds of validations in optimistic. One is read validation. The other is write validation. And what snapshot does is essentially write validation, but it does not do read validation. As a result, although the validation protocol guarantees serializability, snapshot isolation doesn't guarantee serializability. So, here is an example. So, this T1 wrote Y and went away. It's not even concurrent with this. It started and read X and Y and Z and then Y again. Now, in between, T3 came and wrote X and Z and it committed because there was no concurrent transaction which had actually committed by that point. So, T3 can write these and commit. Note that if this T2 reads Z, what does it get? It will get the original value. It does not get 3 because it is reading from its snapshot. If it reads Y, it again gets original value. Now, it's writing X and it requests a commit. Now, what happens? There is a conflict. Is there a read write conflict? The problem is the write write conflict. Read write conflict ignored, even though they are there, they are ignored. Write write, there is a conflict because this wrote X, this also wrote X. Now, this has already committed and therefore, this fellow rolls back. It's called a serialization error in Oracle and PostgreSQL also has some similar terminology. So, a transaction may be rolled back at the point where it wants to commit because of a validation failure like this. Any questions? Serialization, it means the transaction will fail. No, what I mean by does not guarantee serializability is even if the rollback happens here, that is fine. That is the case where there was a problem which was detected. The problem with snapshot isolation, there is a class of problems which are not detected. I will talk about it in just a moment. There are situations where it can lead to inconsistency and like I told you, the IIT example, I will show you a simpler example. That example is harder to explain. I will give a simpler example in a moment. So, why is SI popular? Reads never block. Performance is very close to read committed because blocks are very rare except that there are more rollbacks. Which problem? A read committed doesn't guarantee serializability anyway and if you look at the descriptions of snapshot isolation, they will say that they are read committed because it only reads committed values. They will even say they are repeatable read because if you read the same value again, you will get that value. But it does not prevent phantoms that somebody had mentioned phantoms. It doesn't prevent phantoms. Although there are a couple of places on the web where people claim that snapshot isolation prevents phantoms, in fact it doesn't. If you are aware of what phantoms is, it doesn't prevent phantoms. If you are not aware of what a phantoms is, don't forget it for the moment. And it actually avoids the standard list of anomalies. People have listed common problems because of non-serializability. Last update which we discussed was one of them. Dirty read is a simpler one where you read a value which is not yet committed. That is obviously prevented. Non-repeatable read is prevented because if you read it again, you will get the same value from the snapshot. Now, it claims that predicate-based selects are repeatable. This claim is to be taken with a pinch of salt. Yes, what is a predicate-based select? If I say, give me all tuples with name equal to John. There may be multiple tuples. Now, if you run this again, you may get with plain locking, you may get a different answer. Even with two-phase locking, you may get a different answer. So, some extra effort has to be taken to prevent different answers for the same predicate read. So, the definition of phantom in the SQL definition basically said predicate-read should be repeatable. And in fact, with snapshot isolation, a predicate-based read, if you say, give me all things with name equal to John, it will give the same answer how many ever times you execute it. On that basis, they say no phantoms. But that is only with respect to the SQL definition of phantom. But there are some other phantom problems, which actually it has. If you have time, we will discuss it later. So, those are the good points. But the drawbacks are it does not give a serializable execution always. And we will see this. And some integrity constraints can even be violated. So, let us look at an example. Two transactions, very simple transaction. One of them does x equal to y. The other does y equal to x. Let us say that x is 3 and y is 17 initially. If you run them serially, you can guarantee something will happen. Regardless of order, what is the guarantee? Both will be the same. Regardless of the serial order, if you run t1 first, x will become 17. And t2 will set y back to 17. So, they will both be the same. If you do it the other order, both will become 3. But regardless of the order, we can guarantee that the two values are the same. However, with snapshot isolation, consider what happens. Each of them runs in their own snapshot. So, t1 will set x to 3. If they run exactly concurrently. If they run one after another, of course, that is serial. But if they run exactly concurrently, both get the same snapshot. Then what will happen? x will be set to 17. y will be set to 3. Now, at the validation point, snapshot isolation will check if both have written the same data item. Have they? Have they written a common data item? No. So, snapshot is fine. Everything is okay. Go ahead and commit. What is the final result? x and y are swapped. And as we just saw, regardless of the serial order, x and y will be the same. But with snapshot isolation, the values have got interchanged. If you want, I can repeat this briefly. They both write the same snapshot. So, both of them saw x equal to 3, y equal to 17. But what they did is, one of them set x to 17, the other one set y to 3, and then both committed. So, x is 17 and y is 3 at the end of this particular execution, which cannot be equal to any serial execution. So, this is a proof that snapshot isolation doesn't guarantee serializability. There are other kinds of things. For example, find the maximum order number amongst all orders with the new order with order number equal to previous max plus 1. This is a very common thing which many people use to create new order numbers without gaps. Find max, add 1, and now what will happen here? Two of them can both read the same relation, find the max. In the snapshot, both will see the same max value. Both will add 1 and both will write the same value. Both create a new order with the same number. This cannot happen with any serial execution. Is that clear? Now, it turns out that for most such uses of finding max, the order number is also declared as a primary key. And that saves the day in this case. So, what happens? The primary key is not checked in the snapshot. The primary key validation is done on the actual database. So, the first one will create an order number let's say 11. The second one will create 11. Snapshot isolation won't have a problem, but primary key violation will occur and that transaction will get rolled back. So, it turns out that although snapshot isolation is dangerous in many situations, primary key constraints save the day. It so happened in the IITB example that that thing could not be a primary key because of duplicates. There were multiple records with the same voucher number. So, it was not a primary key. Therefore, it didn't get detected. So, I think I need to kind of put an end to concurrency control here in the interest of time. But I will just mention before you ask the question. I will mention what can you do if as a programmer you realize these systems are using snapshot isolation serializability is not guaranteed. What do you do? And the solution to that is this shown here in this example. If you do any select you can append to it a clause which says for update. So, that for update clause basically forces it to get a lock on that data item. And it will guarantee if you for every read transaction if you add a for update to every read only statement in your transaction then it will guarantee serializability. That is the cost that it gets locks which may not even have it gets a right lock. It doesn't just get a read lock. In effect it gets a right lock. So, it's like you know every transaction gets right locks on every item that it needs. So, it can reduce concurrency significantly but at least it will guarantee serializability. So, if you are having a problem you can add for updates statements and solve your problem. To use for update. No, like I said for update should be used with care. It will mess up performance if you use it all over the place. So, if you keep using for updates all over the place you will have a concurrency problem. You will have very low concurrency. So, you have to use it carefully. And there is some research on how to do this. There is some research papers on how to use a minimum number of for updates to guarantee serializability in spite of the fact that snapshot isolation is used. So, that's a good example. If you know it's a primary key, don't bother to add the for update. Yes. So, that's one of the techniques for reducing the number of for update statements added. It's definitely one of the most important techniques. There are a few others. So, if you're interested I can point you to research papers on this including one from IIT Bombay. For lack of time I'll just mention that there are these phantom problems and although snapshot isolation claims to avoid all phantom problems it doesn't avoid all of there are several kinds of phantom problems. So, the standard example is a transaction that finds the sum of balance of all accounts in some place and another one which inserts a couple. Now, both are in the same branch, Peririch. If you rerun this you may see a new account in that branch. So, with locking there are things to ensure that this kind of conflict will be detected that if somebody did this this insert will have to wait until the first one commits. So, this has to be done explicitly and DB2 and SQL Server and others do it. However, a snapshot isolation actually doesn't do this. It simply says fine, if you read this again, I'll give you the same value again. It's a repeatable read and it won't actually detect the fact that these two conflict because it's reading from a snapshot. But at the end there can be a problem because of this situation where this fellow commits and this fellow doesn't see it the update but basically there's a cycle of dependencies. I wish I had time to describe this in detail but I don't. So, I'm going to skip those details and to wrap up index structures are treated specially in databases and two-phase locking is not used on indices. Some other kinds of locking are used to allow high concurrency on index accesses. Not only do you need to lock data items you also have to lock indices to prevent problems. But indices are kind of special structures special concurrency control techniques are used which in general will not work they won't guarantee serializability but because of the special way in which indices are used it's okay. If I'm being vague I don't have time to explain it. It's there in the book you can read it. So, if you have if you release locks on index structures early what can happen is the access to some of these things is maybe non-serializable but the key thing to note is this point at the bottom. The exact values read in an internal node of a B plus tree are irrelevant as long as we land up at the correct leaf node when you are searching similarly for insertion. So, this is a key insight that low-level operations may not be serializable but at a higher level of abstraction yes I have saw some things which were written by another transaction which is concurrent with me serializably says I should never have seen it isolation says I should never have seen their updates what this says is it's okay if you saw their updates as long as your final result what is the final result? the search on the B plus tree or the insertion as long as the final result works properly it's okay to see some things written by concurrent transactions the lack of isolation is not a problem per se that's a key insight here but there are some issues with this the fact that we are releasing logs early complicates recovery also and in the recovery mechanism in the book in the fifth edition of the book for those who have been using that book it's called advanced recovery mechanism in the sixth edition it is slightly changed and it is called recovery with logical undo or early lock release and logical undo so that is motivated by such situations index concurrency control is one there are a few other examples we are not going to cover that in today's recovery part but I just wanted to mention that this has an impact on the recovery algorithm also any questions? this chapter