 Now, what is concurrency control? It is a way to ensure that transactions follow the rules that we talked about and guarantee serializability or at least one of the weaker levels depending on what you want. So, the basic concurrency control mechanism the first ones to be implemented were lock based protocols. What are lock based protocols? You get a lock on a data item, nobody else can get a lock on the data item. What is a protocol? The protocol is a set of rules which everybody should follow. So, in this case the rule that is followed is you cannot read a data item until you have got a lock on it, you cannot write a data item until you have got a lock on it. In fact, databases have two kinds of locks, a read lock and a write lock or read lock is also called shared lock because multiple people can get a shared lock on the same data item and an exclusive lock which says only one person can get a exclusive lock and when you have an exclusive lock nobody can have a shared lock. So, the protocol is that you have to get a shared lock before you read an exclusive lock before you write that is a minimum. When you release the lock, well it depends on the protocol and depending on how you do this you might get serializability or you might get read committed and so on. You can get the other levels based on the particular protocol you use. So, this talks about the two modes exclusive mode and shared mode for locks. Now, there is some underlying system the lock manager which manages the locks. The lock manager will ensure that no two thing transaction can get conflicting locks at the same time but it does not ensure the rest of the protocol all it manages is the lock. You ask for a lock it will ensure that when it says you got the lock nobody else can have a conflicting lock. If you ask for a shared lock others may have the shared lock but not an exclusive lock. If you ask for exclusive lock when you get it nobody else can have any lock only you have it. That is what the lock manager or the concurrency well the underlying lock manager guarantees only this much. The concurrency control manager might do something more. So, here is the lock compatibility matrix shared and shared is true meaning they can two people can have two transactions can have shared locks on the same item at the same time the other three entries are false meaning if somebody has an exclusive lock nobody else can have a shared lock at the same time or an exclusive lock at the same time. Now, this matrix is symmetric the order is irrelevant meaning we do not care whether the x-lock came first or the x-lock came first all we are saying is they cannot coexist maybe the x-lock request came first was granted then the x-lock request has to wait or it may be the x-lock request came first it was granted then the x-lock has to wait. So, ordering is irrelevant all it says is they cannot both be granted at the same time on the same object. So, basically if somebody else has a lock you wait now what can happen when you have waiting you can have dead locks cyclic wait and the concurrency control lock manager one of its other jobs is to detect that there is a cycle of locks and then do something about it. So, what do you do if transactions have got locks and now there is a cyclic waiting A is waiting for B B is waiting for C C is waiting for A what to do now it is a dead lock how do you resolve a dead lock you have to release locks how do you release a lock you roll back a transaction. So, the good thing is transactions always be rolled back the database makes no guarantee it will commit it until you have said commit and the database says I am done until that point at any point the database can unilaterally say I have rolled you this transaction back and the application has to deal with it the application is told sorry you are rolled back what to do now that is up to the application it can retry or it can tell the user sorry your transaction failed. So, that is dead lock I am going to yeah there is some other stuff on starvation and so on I am going to skip that and now we will come to the two-phase locking protocol it is a protocol because it is a set of rules which are followed and the two-phase locking protocol the rules it follows the first part is that you can only read when you have a lock you can only write when you have a lock that is common now and unless you are doing the uncommitted read the lowest level you can read without a lock even so let us leave that out for the other levels you have to follow these now if you just do this to read you get an S lock to write you get an X lock when you finish reading you release a lock when you finish writing you release the lock what can go wrong you can have a transaction which read a value got a lock read a value release the lock another transaction comes gets an X lock updates the value releases the lock it commits also it commits even maybe that is ok now this guy again comes and gets the S lock and then reads the same value and now it is seeing a different value is this serializable it is not it is two different values it cannot happen in a serial schedule in a serial schedule nothing else is happening how can it see two different values unless it only did the update so this is not serializable and the problem is that you release locks too early you release the shared lock early so one possible protocol is that you hold all locks to end of transaction once you get a lock you never release it and practically speaking that's what systems do and practically speaking people say two-phase locking you know databases do two-phase locking what they mean is that you get the lock and hold it till the end but the actual two-phase locking protocol does not insist that the locks be held to the end so the basic two-phase locking protocol says that following that there is a growing phase where transactions may obtain locks but cannot release anything then there is a shrinking phase where transactions may release locks but may not obtain any new lock that's all the protocol says and this is a minimum requirement for serialize if you follow this rule you will get serializability turns out you will not get recoverability because it doesn't say anything about committing so if you release an X lock somewhere in phase 2 before you committed somebody can read the value which you wrote so that's a bad idea so practically speaking you must at least hold exclusive locks until you commit okay so that version of two phase locking is sometimes called strict two-phase locking strict because the exclusive locks are held to the end and this is minimum that any database has to support this is just a theoretical concept nobody actually implements the simplest version of this so you have these rules and then you have one more rule which is exclusive lock must be held to the end that is the minimum which anyone supports but in fact what most databases support is even shared locks are held till the end till commit and that version of two-phase locking is called rigorous two-phase locking now as I said if you go to the industry they say two-phase locking it's inevitably databases actually never release locks early like they don't unless you you know they do allow they do have a command for releasing locks but if you don't say anything the locks are released only by the commit instruction till then it will never release a lock by itself so that rigorous two-phase locking is what is actually implemented but this is the minimum requirement and a lot of theoretical work says that if you satisfy this minimum certain other properties are true in particular serializability is guaranteed so a lot of the results you want to make the minimum assumption if this minimum is satisfied if I can prove a property that's better than saying oh you must also hold locks till then only then this property is satisfied so theoreticians like to minimize the assumptions so they're under this minimal assumption something holds so theoretically this is important practically rigorous two-phase locking is this one rigorous two-phase locking is what is implemented I'm going to skip a lot of details about lock conversion you can always go from an S lock to an X lock you can downgrade from an X lock to an S lock but that is usually not done but it can be done as part of two-phase locking I'll skip the details for lack of time it's tough on lock manager in the main course I'll cover all this deadlock prevention deadlock detection and then a whole bunch of other stuff multi granularity locking time-stamp time-stamp based protocols so the main thing I'll talk about all this and validation based protocols multi version scheme snapshot isolation there's a lot of stuff here okay so in the main course I won't cover all of this in great detail but I'm going to at least expose people to what are these ideas in the book these are covered in a lot more detail I don't really expect the first database course to cover all these things when we do the course here I don't have time by the end of the course to do all of this in detail so I expose people to the concepts and that's about it locking is the only thing which I cover in some detail beyond that I just expose them to the ideas and in the spirit of exposing people to ideas I just want to mention snapshot isolation because practically speaking this is widely used and it's pretty useful actually so it is a concurrency control protocol which is different from plain locking it's a member of this class of protocols called multi version what is the multi version scheme so normally you say there is a value when it's updated it gets a new value the old value is forgotten in a multi version scheme you not only keep the new value you keep the old value also and there's something nice conceptually about this if you are running a company maybe you should keep the history of what happened but that's not the goal here so the goal here is to keep all values around to help improve concurrency of transaction so let's take the simplest case of snapshot isolation supposing I have a database in a particular state and when the transaction starts I click a snapshot of the database and now any read with the transaction does you give the value as of this snapshot meanwhile what can happen other transaction can come update the database so forth but in this snapshot I clicked nothing has changed it's frozen so that does not clicking a snapshot does not affect the database in any way but what is the benefit it gives for the transaction which is just reading the database it is going to see values as of a particular point in time it does not need to get locks it does not need to trouble other transactions it gets a quick snapshot and then it can take its time to read the snapshot so it's a very attractive idea how do you physically implement a snapshot you can't go physically click a snapshot of a database so what do you do the idea is you use multiple versions so whenever anybody updates a data item they will keep the old version but you keep some count of time when did the version change at what time when a transaction comes and takes a snapshot you take the time of the snapshot so now when you read a data item you will see what is the correct version as of that time so what is important is when a transaction commits you need a time associated with when the transaction committed so if you get a snapshot before that time you will see the some earlier value if you have a snapshot after that time you will see that value or a later value is this clear that is conceptually what snapshot isolation is but of course there are details so a snapshot isolation is fantastic if you use it for weed only transaction it doesn't affect serializability it doesn't affect anything and it gives you very good performance the problem is that people used it for even when there are updates it's actually possible to use two-phase locking for updates and weed only transactions get a snapshot and work off the snapshot that is actually a nice protocol and there are database systems which support this protocol however what systems like Oracle and PostgreSQL did is they hacked up the snapshot isolation protocol by adding some checks they don't do full locking they do some some vague kind of locking I won't get into the details and they call this the snapshot isolation protocol and update transactions followed this protocol and what happens with that is that you don't get serializability you can have a result which is not serializable and as an example of that so what does the protocol do essentially if you have two transactions t1 and t2 which are concurrent so this is running this is also running at the same time if there was serial one was running after it finished the next one started no problem if they are concurrent like this if both of if this writes a and this also writes a then one of them will be rolled back let's say the second one this will not be allowed to commit that's a check which snapshot isolation does transactions get a snapshot they read values from that snapshot and if they write a data item and another concurrent transaction writes the same data item one of them is rolled back that's the intuition of snapshot isolation protocol the problem with that protocol is it allows something like this read a and take that value and write it to be write that value into be and another guy reads be and copies that value into a now take these two transactions this is copying the value from a to b this is copying the value from b to a if they ran seriously what can happen if this runs first and then this what will happen first of all a is copied to be so both a and b are the same value after this which is the initial value of a and what does this do with a copying b to a which is actually having no effect because they are already the same with the initial value of a on other hand if t2 ran first and then t1 what will happen b is copied to a and then t1 has no effect because it's copying but the final state is b so in any sequential execution of these two both the things will have the same value but in snapshot isolation if they run concurrently what can happen this reads a this reads be from a snapshot then this writes b this writes a what is the net result swap the two have been swapped because you read this you read that that is copied here this is copied there so in snapshot isolation this swap can happen and it's not prevented why is it not prevented they wrote different data item they didn't write the same data item snapshot isolation allows it so this is a problem with snapshot isolation and this can lead to other issues so why did oracle go ahead and implement it even though it it's not serializable this was a small example showing that it's not serializable it turns out that most of the time it doesn't matter and people don't notice it and oracle did it because most people don't notice it and academicians like Alan Fakete were worried about what is going on how can oracle do this don't people run into problems they said how do we see this let's take some sample application and see if it can cause problems so they took one such sample application which is a widely used benchmark and they analyzed it and they showed that that particular benchmark even if you run it under snapshot isolation all the executions are serializable and why because when there are no transactions like this in that benchmark which do the swap there are other funny cases which can happen and then they said that maybe this is why people are getting away maybe most common situations this does not happen but that's not the same as saying it cannot happen in fact it happened in IIT Bombay we had a system which PCS developed using oracle and seem to be running fine I mean had other problems many other problems but anyway one day the financial auditors came and said look you have two different bills which have the same number it's a financial bill and two bills having the same number can be taken as evidence of fraud why is it evidence of fraud let's say you collected money from two people and gave them the same receipt number so they think they have it's been recorded in your system you keep one of them and throw away the other okay but if you somehow left trails of the two receipts with the same number maybe you pocketed money that's the job of financial auditors to find such things and they found this had happened what's going on you know clearly the bill numbers were generated by the database and the application not by humans we presume that the database application was not trying to commit fraud how did this happen eventually we traced it down to snapshot isolation so it turned out that there were some unusual properties of the application which were necessitated by how things are done IIT had a slightly different way of doing things than other places which the application implemented and this unfortunately had failed under snapshot isolation they said are the engineers from TCS who build this are they fools that's no they're actually very smart people pretty smart they had in fact I think they had you know there's this history which gets passed down within these organizations so somebody would have told them look when you do things an oracle you could run into this problem here is one way you can try to fix it so we found in that code when we were looking at it that they tried various tricks there it was there in the code but none of it worked because the tricks that they had been told were not the correct solutions somehow that message didn't got garbled in transmission through TCS and they didn't know exactly how to correct it they did something wrong and that's how this happened so the moral of the story is sometimes you need to understand what the hell is going on behind the scenes you cannot assume that everything is clean underneath so then what did we do there is a way work around there is something for update annotation which oracle supports oracle also realized that there may be situations where people are running to trouble so they provided this syntax to work around it so we could add that and we could eventually work around and we fix that but this led to research so we said can we look at an application and judge whether it can cause trouble with snapshot isolation or is it safe what the earlier work of Fakete and all was this particular application is safe you said can we build a tool which automates this and so we had an M.Tech student and Alan was also involved in this so we the M.Tech student built a tool this was M.Tech thesis build a tool which can analyze an application and say can it run into trouble under snapshot isolation so that got published in one of the leading conferences so this is the kind of thing which good research is about you find a real problem and you can solve it in one of two ways so at that point our solution was we will analyze the application and see if it can get into problems and then add the for update statements and clean up but there is another thing which we could have done we didn't do Alan went back and did it with his PhD student which is can you modify PostgreSQL snapshot isolation to avoid the problem in the first place so that eventually got implemented in PostgreSQL which is actually a nicer solution because it it's more general and it still gives the performance benefits of snapshot isolation so that's a nice piece of work so I'll stop there