 So today's first topic is transaction so all of you know about transactions I am sure. What is it? It is a unit of a program execution that accesses data and may update data also. And there is the stereotypical transaction which everybody is familiar with which reads something and updates something. And if you see here it is very abstract it says read a, a equal to a minus 50, write a, read b, b equal to b plus 50, write b, transferring 50 units of currency from a to b. Now this is abstract. Now this is not how you communicate with the database. How do you communicate with the database? You write SQL queries. You will say select something from the database where account number equal to so and so. And then in your program you will get the value and then subtract 50 from it and then you will have a query, an update query which says update account set balance equal to so and so where account number equal to so and so. So the read and the write are done through SQL queries. Now this is a level of detail which we will ignore when we talk of transaction processing. In abstract you are reading something, you are writing something. What are you reading? You are reading some particular account number. We are just abstracting it away here by saying you are reading a, writing a, reading b, writing b. A and b are some things. In the abstract level we do not care about what exactly they are, how the query is issued and so forth. So it always helps when you are looking at something complex to abstract away details, simplify it and then it will let you focus on the hard aspects of what you are looking at. So now why is the notion of transaction important? The main issues are two-fold. One is that there may be failures. What if a system dies in the middle? Now in the old days IIT had a Foxpro database system which was not very good about these things. So any time there was a power failure after that somebody had to go manually rebuild indices to some other stuff. It was not too bad, it did not lose data but if you lose indices and then you have to go manually drop indices, rebuild them before the system was even usable. So failure in that system could lead to an inconsistent state. Now what about modern databases? They will run recovery, they will come back up but if you had a transaction that it started doing something and had not finished, obviously if you had not used the notion of a transaction, for example from JDBC you did not turn off autocommit, you might have executed the first query and not the second, you might have left it in inconsistency and so as a programmer you have to be aware of it. But here the focus is on what is the database have to do if you did say that turn off autocommit, issue some statements and then you say commit or you say abort or the system crashes in the middle. What should the database do? And that is a recovery subsystem and that is actually something we will look at later. But before that we will deal with concurrent executions of transactions. And here this is actually a lot more complex than recovery in some sense because first of all concurrency can happen in fairly complex ways. And dealing with it is not necessarily easy. So there are some brute force solutions to deal with it which is one example is they prevent any concurrent executions. Make all transactions run serially one at a time. How do you do it? You can have some kind of locks, transaction comes in, it gets a lock on the database, it does whatever it wants to do, it releases a lock and only then the next transaction can come in. So you can run transactions serially. That is possible. But it is a bad idea in general. There are some special cases where this is actually a good idea. In particular main memory databases with very short transactions turns out that running it serially can sometimes beat doing concurrency control. But in almost all situations running transactions serially is a bad idea. So what could the reasons be? Performance. If you are waiting in a queue and somebody ahead of you in the queue is taking a has a very, very big transaction, you are in a shop that person has bought 1000 items and is taking a long time. If you have to wait behind that person then you are going to get very bored. You would rather have multiple people in the shop who can one of who can be processing that person other can be processing you. So you want concurrency for performance to avoid long waits. Now once you allow concurrency the issue is that two transactions can read and write the same data items. So now what does it mean for a particular execution to be correct or not correct? So this is actually a very difficult question. The abstract. So all of you who are familiar with the transaction concept would have seen the definitions but if you think about it it is not obvious. So if you just think ask somebody who is not aware of this grab a student who does not know these concepts and say hey these things can run concurrently what does it mean for a particular execution to even be correct? How do you know it is correct or it had a problem? This is a deep question. And this is a question which other communities outside of databases usually pointed out. They said we will operating systems which we will give you some of course you do what you want it is your headache. The database system people said no no that is not good enough we cannot dump this on the programmer. We have to understand what is the right thing and how to control concurrency such that bad things do not happen. So that is the whole issue of concurrency control. So sticking with this example of reading A and updating subtracting from A and then adding that to B. Atomicity requirement if this sequence fails after 3 but before 4 you subtract it from A but not updated B you have lost 50 rupees from the account that is not acceptable. D is once you have finished the whole transaction you have updated A and B the database is not allowed to forget about it. If it forgets then user who has transferred the money is going to be very upset that is durability. And the third is consistency so what is consistency that is actually a little hard to pin down in general it is dependent on the application but for a financial application one of the notions of consistency is that you cannot create or destroy money. You can transfer money but you cannot create it. You can receive money from somebody else and then you record that you got money into the system. You can hand out cash from somebody you record that you handed cash out of the system but you cannot create or destroy money. That is the reserve of the Reserve Bank of India and of finance ministers who can goad them to do. Actually in the olden days governments could just go print money go tell the printing press print so much money today it is harder there have been some checks put in place to make this harder. So what does the government do instead it goes borrows money. So there is a very interesting book on the history of money by Neal Fabius and any of you have seen the book. It is a nice book it traces back the history not of money per se money is a very old concept but of the modern banking systems which trace back to Italy where the lot of small city states which needed money to fight wars and they borrowed money from you know merchants and then the merchants ended up creating banks. So from there came these modern concepts so you cannot just go create money. So anyway coming back to our application the notion of consistency is dependent on the particular application and what each transaction has to ensure is that it will not violate consistency. Now there are some things you can do as a database designer to include integrity constraints. So if a primary key is violated the database will roll it back but there is a limit to this to the kinds of things you can do with foreign key primary key for example having a constraint that the total amount of money cannot change cannot be imposed through a SQL integrity constraint. So that is something the application has to take care of and the database system cannot do anything about it. However what the database system can ensure is that the transaction see a consistent state what do I mean by this supposing you had concurrent executions. Now look at point at the point between step 3 and step 4 there is the consistency constraint that the total amount of money is conserved is it satisfied at that point it is not you have subtracted 50 you have not yet added the 50 in the other place. So that integrity constraint has actually been temporarily violated. Now if a transaction receives a state where it has already been violated it is going to lead to a state where it is still violated. So what we are going to insist is not that the end state always be correct but the requirement is that if the starting state is consistent a transaction must always leave the database in a final state which is consistent it cannot make it inconsistent. So if you run one transaction after the other serially at every point that after each transaction the database will again be consistent. So that is what the system should ensure but the problem that can occur is because of concurrent execution for example here the same transaction at this point 3 another transaction came in and it reads A and B and prints A plus B. Now supposing these are the only two accounts in the bank you know what the sum of it the two was before but if this transaction sees the intermediate state it is going to see a inconsistent state. So the isolation requirement is that no transaction should be able to see an intermediate state like this. So putting these together you have the asset properties atomicity consistency isolation and durability. Now isolation it turns out is surprisingly hard to pin down precisely what is isolation? It is not running them serially that is too strong running them serially ensures isolation of one transaction another because there is no concurrent activity but that is too strong. Now some people might say that two phase locking is requirement for isolation that is also too strong there are other protocols that you know ensure that nothing goes wrong but they do not follow two phase locking. What is isolation? How do you define this property independent of specific concurrency control mechanism? So it is actually a little tricky and hard to define but one fairly weak but probably a necessary condition is for every pair of transactions ti and tj it appears to ti that either tj finished execution before ti started or tj started execution after ti finished. So if you take any pair of transactions either it should appear to have gone in this way or in that way. If you take a pair of transaction and you can figure out that they ran concurrently not like this or like that then there is a problem so that is the that is a very basic notion of isolation. What this does not say is what if there are multiple transactions there are three transactions this does not actually prevent you know from A, B, C A thinks B is after it B thinks C is after it C thinks A is after it. So it is a little too weak actually but if you make it stronger you get into trouble. So this definition is from a book by Gray and Reuter which is considered the Bible of transaction processing. Then Gray got the ACM Turing Award which is the equivalent of the Nobel Prize for his work on transaction processing he is known as the father of transaction processing. So the transaction state what is a transaction is active it is running at some point the transaction says I am done please commit the updates which I have done. So when you in JDBC you say connection.commit at that point the transaction is partially committed but it is not done yet the database has to do some more work before it can ensure the updates are durable. So if it does not come to this state at some point it may say abort the connection may say connection dot abort at which point it is in a failed state. So but it can also be in a failed state because power went off the computer crashed and so on. Now a failed state is still inconsistent things are not yet cleaned up. So from a failed state you should clean up and after that the transaction has been rolled back and it is abort it you clean up after it or that you said commit the transaction it was in a partially committed state the database took some actions to make sure the updates are safe on disk or wherever else and after that it is in committed state. So this is the abstract notion of what a transaction goes through a life cycle of a transaction. So if you see this diagram it can go from active to partially committed or failed. Now what is less obvious is from partially committed you might go to the failed state because the database may crash after the transaction says please commit but before it has done whatever is required to commit the transaction. So coming back why concurrent executions there are two primary reasons both performance related. The first one is increased processor and disk utilization and this is particularly important these days because a typical computer system has many processors. Each processor has multiple cores we already have four cores on mobile phones now and processor may have 8 16 even 64 cores on the way and then you have multiple CPUs and then you have disks multiple disks on a machine and then you may have multiple memory banks which can be independently accessed. So there is a lot of scope for parallelism within a single computer and if you run things serially you are wasting all this but if you can run transactions concurrently one of them can be using one CPU the other can be using a second CPU a third can be using disk one or fourth disk two and so on. So up to some point having more concurrent transactions will give you better performance. But beyond the point the whole place gets too crowded the transactions are pushing and shoving at each other waiting for each other and then there is chaos. So you do not want to go to that point but you want a good degree of concurrency. And the second issue is average response time even if you had just a single processor nothing else is nothing is really running concurrently. If you have to wait behind somebody else in the queue you are going to be upset but can you quantify what is happening in the system okay upset is a big human oriented term can you quantify what is happening and you quantify it by average response time what is the average response time transaction is submitted to the point where it is completed and the user is fine it is true well the database part we do not worry about the application here the transaction arrives at the database and it completes that is the response time for that one fraction. So if you have exactly one processor and sequential execution of things if there is even one large job many small jobs may be waiting behind it and they all wait as much as the time as the long job takes to finish. Even though these were small jobs their average waiting time became very big. In contrast if you have a one long job and many small jobs if you can somehow interleave these in between while the big thing is running the small things can come finish up and go away you temporarily stop the big guy okay preemptive scheduling so you can preempt the big task allow small guys to go in. So immediately the waiting time for the small guys comes down drastically and the average waiting time will go down drastically so that is the second very important reason. So we will look at how to control the concurrency to prevent problems but before that we have to understand what are the kinds of problems that can arise and what does it mean to say that there is no problem first of all. For this there is an abstraction which called schedule which again you must be familiar with what is the schedule it is a sequence of things which are transaction does. Now what we mean by instructions you can look at it in terms of SQL and the expressions that are evaluated inside the application program and so forth. Here is a slightly abstracted transaction which reads A subtracts 50 from A and reads B this is the one we saw and one more transaction which reads A computes 10% of A and subtracts that from A and then adds that value to B. So here we are seeing what is going on inside the transaction. Now the problem is if you have any notion of concurrency control which depends on what is going on inside the transaction then you need to know what is happening inside the application in general. There are ways around it for example if these operations addition of amount or any other such thing is done in the database and you know what is going on you can do a little bit more. But in general this computation may be happening outside the database so the application reads a value does something writes a value. So all the concurrency control schemes at least the basic ones there are some more advanced ones which take into account what exactly is happening that it is adding money it is not doing something random it is adding 50 dollars or rupees to the account. So then you can actually modify how you do concurrency control but initially at least we will assume you do not know what is going on all we know is there was a read of A followed by a write of A followed by a read of B followed by a write of B we are going to ignore what happened in between. So this is the part which the database can see so that is the abstraction of a schedule which the basic concurrency control techniques work with. So now a schedule is simply a sequence of instructions and so this is a schedule. So the schedule shows one more thing not only is the sequence for a transaction it shows how the instructions for two transactions were interleaved. Now there is a basic assumption here when we show schedules like this that one single instruction here is atomic that is read and write do not happen at the same time. So that is a simplifying assumption they can actually occur at the same time but the concurrency control techniques will make sure that if they occur at the same time they do not conflict with each other if they conflict with each other in some way they will not actually occur concurrently. So for the purpose of the schedule we are simplifying the whole thing we are going to assume they happen one after the other. In reality in the database on the computer some of these may be happening concurrently but we will arbitrarily pretend one happened after the other way if they did not interact in any way. If one of them read a the other wrote B it does not matter they happen concurrently we can treat it as this way or as that way we do not care the result is the same. So we are basically making that assumption when we say a schedule is a sequence of steps. Now here is another schedule for the same pair of transactions which is flipped what is the difference between this and this this came down and that went up. Now what is the final state of the database after this in the first case you transferred 50 and then transferred 10 percent in the second case you transferred 10 percent and then you transferred 50. The final result is not the same here 10 percent is before subtracting 50 in the other case it is after subtracting 50. So 5 rupees difference will be there between what you transfer in these two cases. So the final result of the two schedules is different but does it mean one of the schedules is wrong and the other is right no both are correct depends on the order in which they were submitted it may happen that two people submitted these more or less at the same time and the database chose one or the other and if in the external world the person who came in first was T1 and T2 came behind him in the queue they were waiting in the queue and they were T1 knows he got in first and then T2 came in but the final result shows T2 happened first and then T1 if they were buying tickets they may be very upset. So I got in first you gave him the ticket but you do not know if they are in two separate counters you do not know so it is okay they are happy it is not incorrect the real world notion of time does not matter here when they arrived when they were told they are done does not really matter. Now here is a third schedule where the instructions are broken up differently and in this case the transactions are not serial in the previous case there was serial one after the other here they are concurrent so some part of T2 is running in the midst of some part of T1. Now is this schedule safe or not clearly some concurrent stuff is going on that does not mean it is wrong but how do we know it is right or wrong is it safe or not how do we know it I think all of you how many of you are already familiar with the notion of serializability not all of you so let me spend a minute on this the notion of serializability basically says that we will say that this kind of interleaved schedule is okay provided it is equivalent in some sense to a serial schedule. Serial schedules we know are okay the previous two are both serial we know both are safe they are they are actually not concurrent and so they are fine. If we can show that this is somehow equivalent to one of them then this is also fine so what do we mean by equivalent well there are several different notions of equivalence but at the core what all should happen the if you read a value in both the schedules you should read the same value we might have printed it out to the user if you did some updates at the end of everything the final result should be the same so that's the basic notion of equivalence which is called view equivalence but from the viewpoint of concurrency control usually something even simpler is used which is conflict equivalence and I'll come to that in a moment but if you look here look at these two parts this part and this part this is working on a this is working on b will they affect each other no they in other words these two yeah this set of instructions does not conflict with this set of instruction but in fact what is done is usually you take a pair of instructions at a time and say that this instruction does not conflict with that instruction because they operate on different data items not on the same data okay so what can you do you can flip so you can pretend that happened later and this happened earlier so now you can do this again what has happened read has moved up what are the two adjacent instructions a is a minus temp and b actually I told you earlier we are going to ignore the exact computations here so let's actually do that shown here but let's ignore it totally so the next step is b and read a read b and read a they don't conflict will read a and read a conflict no both are reading a if they happen in the other order it doesn't matter the result would be the same so you can actually swap these things and move this whole you know so let's just take the read and write here you can move this read be above right a and read a and bring it up similarly the right be can move above right a and read a and come to the top so what have we lined it up with we have lined it up with a serial schedule where t1 runs first and then t2 runs so the two schedules are basically equivalent why because we have done a series of swaps each of the swaps we guarantee preserves equivalence in what sense they don't affect each other so whatever they read in each case will be exactly the same so we are swapping things which do not conflict and this notion is known as conflict equivalence I will come back to it in a few slides but before we see that we have to understand when things are not actually equivalent so here is one more schedule which reads a it has not yet written a and meanwhile other guy comes in and reads a updates it and then this writes a what is going to happen here something very bad the right which this transaction t2 did here is going to be clobbered by the right done by this guy it will write some old value back some other value the old value minus 50 is what it writes back the subtracting 10 percent of a has got wiped out of the database clearly this is the kind of problems due to concurrency which we do not want to allow so let's verify that this schedule is not conflict equivalent to any serial schedule if it is then we have a problem it cannot be right if you ran one after the other in either case the total amount of money is conserved but if you do this a is updated in a bad way and the total amount of money is not conserved here that is easy to verify so is it conflict equivalent to any serial schedule so let's see if we can pull t2 down so now here is a right a and here is the right a this cannot go below this so you cannot swap it so it is not actually um conflict equivalent in you cannot swap it so you cannot move t2 down can you move t2 up that is the other option then it will be equivalent to the schedule where t2 is first and then t1 can you move t2 up unfortunately there is a right b and right b here those cannot be swapped so t2 cannot be moved up it cannot be moved down either way there is if you swap some of those instructions the result will be different so what we can infer is that this particular schedule is not actually equivalent to either t1 t2 or t2 t1 so it is not equivalent to any serial schedule in this case there are only two possible serial schedules t1 t2 or t2 t1 it is not this schedule is not serializable so this brings up the notion of serializability um concurrent schedule is serializable if it is equivalent to a serial schedule and there are these two notions which I told you about conflict and view again I am not going to cover view here there is some discussion in the book uh conflict serializability is what we are going to look at and as I told you we are going to ignore all operations other than read and write now I told you informally that things don't conflict under certain circumstances so here is um what it is supposing you have instructions ii and ij of transactions ti and tj so if the first one is read queue the other one is also read queue they don't conflict if one of them is read and the other is write on the same thing then they do conflict if the write and was followed by the read it will write the value which is written if you swap it it will read an old value so they conflict similarly write and read conflict write and write conflict but if they are on two separate data items it doesn't matter write and read of data item a does not affect data item b so if they are on different data items they don't conflict and a conflict forces a logical order between the two if it happened this way in the schedule in any equivalent schedule it must happen in the same order so uh if uh t1 did something before t2 which conflicts in any serial schedule t1 must be before t2 that's a basic idea so if a schedule can be transformed into another schedule is by a series of swaps of non-conflicting instructions we say they are conflict equivalent so this is what I explained earlier and a schedule is conflict serializable if it is conflict equivalent to a serial schedule so all this is basic stuff so we have another concurrent case this one is read a write a well it's actually the same one we saw before where these pair of instructions can be swapped meaning pulled up above these and you get this one so three is conflict serializable we don't know what is happening in between but what is important to notice I don't care what is happening in between this will hold regardless that application might have computed something very complex in between these two doesn't matter it will still be serializable okay any questions here and uh there is also one more notion of recoverability uh which basically is illustrated here supposing you had this schedule t8 read and wrote a then t9 read a supposing it could read the value written by t8 and it commits but after this t8 does some more operations it might be forced to abort maybe the system crashes t8 did not complete it's going to be rolled back but now unfortunately t9 has seen something which t8 wrote and even if it is serializable it is not recoverable at this point if the database crashes you have a situation where t9 has seen a state which is then rolled back so it is not recoverable you don't want such schedules so not only should it be serializable it should be recoverable and then thus for lack of time I am going to skip this stuff about cascading and so on you if you don't know about it you can read it up later but the important thing to notice that you you should ensure recoverability and in fact this other property of cascade less schedules if you know about it it's fine it's preferable but it's not essential and the goal is concurrency control protocols which ensure this now serializability is the gold standard but it turns out that for performance reasons databases will actually never enforce serializability unless you specifically ask for it in fact it's quite bad the default level even if you say begin transaction commit it turns out that most database systems today will not ensure serializability what are they doing they are concurrency control protocols you know serializability has a cost so what many database implementers decided is that if you really need serializability you will ask for it if you don't ask for it you don't really need it and they will do stuff which results in non-serializable executions which as an academic who has learned the whole theory of serializability you will say but why are you doing that isn't it illegal shouldn't they be put in jail or something so the answer is you know you get so this this is a market right the stock market you you get what you ask for and if you are not careful if you don't read the fine print your toast so in this case the fine print and all databases is that by default they do not ensure serializability and you need to know this fine print if you don't know and I will assume it ensures serializability you are in trouble and in the lab session you will be trying this out so how do you tell the database be serializable there is a command in most databases in PostgreSQL there is a set serialization level to serializable set consistency level to serializable and you have to execute that or you have to configure the database so there is usually a config file where you can set this option one of the two has to be done now why would you want to allow weak level of serializability and wouldn't it always result in problems the answer is no many times it doesn't we are result in problems there are many cases where when people read information from a database in particular things which read a lot of data they don't really care for accurate statistics if you say how much money does the bank have if you run this query while updates are going on you know very well that there may be some updates which you missed if you really want a consistent view you should do it differently how much money does the bank have should be checked by seeing all transactions that happened before some time are included and transactions happen after that time are not included that's a logical way to deal with it if you you can do that always you can run a query which asks for only updates that happened before this time and then add those up and get what you want but if you just read the balances in the account and just add it up you know that you're getting a potentially inconsistent state that's fine so you can live with it then there are other things like statistics used for query optimization they're slightly off it doesn't matter not like the query execution plan chosen will be totally different it's likely to be the same thing so the default is weak level of serializable serializability so there are the SQL standard actually defines these levels which a database can should support so the lowest level is read uncommitted you can read anything including uncommitted records okay so that's the worst level many applications don't want such a bad thing where you can read uncommitted updates the next level up is read committed what does this ensure if you read a value you will only see a value which is committed if another transaction is has updated it but has not committed it you won't see the value you might have to wait so the locking protocols will make you wait some other protocols will give you an old value but regardless you can only see committed values so most databases actually run at this level read committed is the default level so what does read committed not guarantee that's the next step repeatable read so what is repeatable read if you read a particular item once and then after sometime in the same transaction read it again if the system guarantees you will see the same value that is a repeatable but in read committed the system does not guarantee it so you can say read the account balance for account 321 and after sometime in the same transaction you say read the balance again it might have changed what has happened the first time you read it some transaction had committed you see the committed value before the next time you executed another transaction came updated it when committed and went away and you're seeing the new committed value okay so this can happen with read committed the repeatable read level says this problem cannot occur but is this good enough is this the same as serializability serializability is the gold standard it's not you can have schedules which ensure repeatable read but are not serializable so serializable is the and actually this slide has a mistake it says it's a default um and it's the default in the standard so if the slide is technically correct but it's misleading the standard says serializable should be the default but in the slide says below some databases do not ensure serializability will show you by default it shouldn't be some should be all okay all the common databases run at uh read committed nobody runs at serializable by default and in particular oracle and postgresql do something different from these levels read committed is their default but they also support something else called snapshot isolation which it turns out is not quite one of these levels and postgresql versions up to 9.2 use the old level 9 point up to 9.0 use this thing called snapshot isolation which is does not guarantee serializability oracle also as far as i know still does that and what is versus if you tell oracle or the old versions of postgresql saying set the consistency level to serializable they'll say sure but then underneath they do something else called snapshot isolation which is not serializable so in spite of doing everything right the database will cheat you so that that in sometimes is really cheating because you also serialize they said fine but it's not okay so they are lying they were lying postgres eventually decided they shouldn't be doing this and they implemented as of 9.1 which was released in 2012 i think or 2011 they released version which is a new protocol called serializable snapshot isolation which actually ensures serializability so they have corrected oracle as far as i know it's not yet corrected that problem this is an interesting so just to jump in what is the research right so this was a nice piece of research there's a person called Alan Fakete who visited us we've done some other research with him on snapshot isolation he and his phd student worked out a way to easily change the postgresql concurrency control mechanism to ensure serializability and they published a paper and then it got implemented in postgresql line it's now released so that's very nice that's it for this chapter