 We switch gears now and we switch gears to a topic which we left half way through. We discussed it very briefly, namely the notion of transactions. If you recall, when we discussed SQL, we discussed the data definition language part of it including constraint definitions etc. We also discussed the three statements which are used to modify data in database tables. Namely insert, delete and update, how they could be operated upon. We however did not discuss the notion of transaction in detail particularly considering multi-user environment where multiple users are trying to approach the same database for insertion, deletion, updation etc. And the recovery issues that is in case the database crashes, how do we recover it? Today we will look at the transaction concept in some more details. We mentioned the notion of acid properties once. We shall see what ACID stands for. These properties actually emerged not during the relational system research that was happening as I said in 70s through Professor Carl. But these were more better understood by the work of another giant called Professor Jim Gray. Jim Gray is no more unfortunately is another Turin award winner and the book that he has written on database transactions is still considered the Bible in transaction processing. He worked with various companies. He was involved in the original IBM DB2 design. So DB2 is considered to be his product although there were six or seven other people. The last he was working for was Microsoft for their database research group. And he first defined the notion of performing performance benchmark. How many transactions can a database application run on a particular hardware? And while defining the transaction he defined the first debit credit transaction benchmark. And then he defined various classes of transactions and the problem that they may create etc. We shall briefly see the SQL features supporting these transactions. The notion of long transaction distributed transactions and replications are currently only going to be the names for you. At some point appropriate in time and we discussed large distributed systems. I will describe the conceptual meaning of these things. Although we will not have time to discuss and deliberate upon each of these later. So here what a transaction is. It is a unit of program execution that excesses and possibly updates various data items. Note that it is not an instruction in your program. It is a unit of program execution. So this unit of program execution may have five instructions, five hundred instructions, three or four SQL statements or a single SQL statement. It does not matter what it is as long as we recognize this as a unit which is executed. A transaction which is so executed that is a set of statements which are so executed is normally expected to support asset properties to preserve data integrity. We look at what asset properties are but the fundamental objective is to preserve data integrity. We have already seen why data integrity may be compromised. We shall see some examples of actual transactions particularly multi-user transaction to see and understand why the integrity may be compromised and what ought to be done about it. So here is the concept. First a transaction which is a unit of program must see a consistent database and it must also leave behind a consistent database. Consistent database means that data integrity of that database is intact. So when a transaction starts it expects to have a consistent database. When the transaction ends it is expected to leave behind a consistent database. It is not expected to leave behind the same database obviously. We are going to insert values, update values, delete values. It will leave a database which has different values. What is important is it is consistent. We will see soon an example of such consistency. Now the important point is during transaction execution the database may be inconsistent. Consider a simple consistency example. I withdraw 50 rupees from my account. The account balance originally was 1000 rupees. I had 0 rupees in my hand. Let's consider transfer of money. I want to transfer 50 rupees from my account to a friend's account. Originally my balance was 1000 rupees. My friend's balance was 500 rupees. When I execute this transaction my balance should be 950 and my friend's balance should be 550. At the end of the execution of the transaction this is the consistent phase. At the beginning the consistent phase is 1000 and 500. During the execution of the transaction certainly there shall be a moment when my balance will show 950 but my friend's balance will not show 550. It will still show 500. So during the execution of a transaction it is possible that the database is inconsistent. However when the transaction is completed and there is a special word used to define completion of a transaction which is called a transaction commit. That means I have committed that transaction. That means I have done everything that was required for fulfilling the operations of that transaction. So when a transaction commits the database must again be consistent. That is a common sense requirement and this requirement must be fulfilled no matter whether the system crashes so that when you recover and claim recovery the database must be consistent. No matter if there are 10 users trying to access the same database and want to update it. No matter what. This is a requirement on the database engine. We are trying to understand what that requirement is by taking example of some specific transactions. So as I mentioned briefly earlier there are two main issues. One is failures of all kinds. Hardware may fail, disk may crash, system crash, operating system fails. How many times you have seen an operating system on the PC just getting locked up? What happens if you are let's say preparing a document, you are inserting some document. So what would happen? You must have had an example of an experience of such a thing. You are typing something and the power goes off. You will typically lose whatever you have written. If you have an intelligent software at the back end it will tell you that unknown to you I was saving your document from time to time. This is the last save copy. Would you like to recover it? And you may suddenly find that lots of what you had typed is actually there. It may not be to the last moment. We can't afford the luxury of having something useful back. We need a guarantee of having exactly a consistent database back after we recover. How do you achieve that is a technical problem but that requirement has to be understood by us. The second issue to deal with is the concurrent execution of multiple transactions. Where different transactions are trying to read and update same rows and columns. So I am transferring 50 rupees from my account to some friend's account and my wife is withdrawing money from my account. Both are updating the same account. I must have a consistent thing after both these transactions are executed. So we will see one of the examples of what could happen and why that could happen. Here is a transaction, a fund transfer. The transaction to transfer 50 rupees from account A to account B. I give you this example. Consider this example. How will this be executed? It does not matter whether I use SQL or C program or whatever it is. So that is why I have written in some kind of a pseudo code the program instructions that will have to be issued. One instruction will say read A means read the value of account A balance. Subtract 50 rupees from that balance. Write back that A. B is the other friend's account. So read the balance in B. Add 50 rupees to B and write B. You will agree that these six steps will actually ensure that my transaction is successfully done. You can also notice that somewhere around this point my database will be inconsistent because account A has 50 rupees less but account B continues to have the old value. If the disk crashes here I will have to guarantee that when I recover through whatever software I will restore this 50 rupees to A. Or I will subtract or add 50 rupees to B. One of the two things I must do otherwise my database will not be consistent. This is for a crash. What happens when multiple users are there? First look at the SQL statements that I will execute. I have just written SQL statements here. Update accounts set bal equal to bal minus 50 where A code equal to A. You can understand this SQL query now. It is an update statement which updates the account stable. And it sets the bal field in the table to bal minus 50 for an account whose account code is A. This single SQL statement will do all these three things. You agree? In order to do the remaining thing I will have a similar update account statement where account code is B as a plus sign. Notice that these are two independent SQL statements. Each SQL statement is a complete program as I mentioned. So when this SQL statement is executed this job is done. And only when this SQL statement is executed the second part of the job is done. Now imagine the disk crash that happens here. The SQL statement is correctly executed. The second one has not even started. And the point is if I am just doing a SQL statement execution without maintaining a trace of all these activities after I recover the system. I will not even know that this SQL statement is executed but the second one was not executed. So I will have to build a huge elaborate system of maintaining shadow balances, shadow entries of maintaining what was executed and what was not executed. In short I will need a mechanism to tell my SQL engine that please do not treat these two as two separate statements but treat both of them together as a single piece of transaction. Now I hope you understand the notion of it all. And this is an example. There could be 20 statements which comprise a transaction. I must have an ability to define a transaction begin, transaction end and my backend SQL engine must have an ability to ensure the consistent stage before the transaction or after the transaction. That is as far as the recovery is concerned from crashes. But here we are considering if multiple users pile on to the same account A then what could happen? The asset properties are briefly defined as follows. Atomicity is the fundamental property. Either all operations of the transactions are properly reflected in the database or none are. If A minus 50 has to be reflected B plus 50 must be reflected. Or A minus 50 should also not be reflected. That is the consistency difference. This is called atomicity. Atomicity means that the two operations that I showed you as example either both are executed or none is executed. So that means atomic unit is formed by these two statements together. Either we do this entire thing or nothing at all. Consistency is execution of a transaction in isolation preserves the consistency of database. That means not only these two operations are done but under whatever crashes or multiple user operation etc. If a transaction is executed in isolation there is no other user on the system. I am executing that transaction alone. The system is not doing anything else. Then it is guaranteed that the consistency of database is preserved. Which again means that atomicity is guaranteed when a transaction is executed in isolation no matter what crashes etc. will occur. If the transaction fails after step 3 and before step 6 the system should ensure that updates are not reflected in the database else an inconsistency will result. What is step 3 and step 6? Step 3 is completion of reduction on account A. Steps is completion of optimization of account. Consistency requirement is simply this. The sum of A and B is unchanged by the execution of the transaction. So the consistency is defined as a mathematical assertion arising out of the database state and the changes that are being made. You can see both are related. Isolation. This is the eye of the acid. Atomicity we saw that. Isolation is even if multiple transactions are executing simultaneously there are 200 users piling on to the bank branch for doing transactions. No matter how many users are simultaneously executing each transaction must be unaware of another concurrently executing transaction. So when I execute my transaction I should not care. My transaction should not care that there are other 200 friends who are trying to withdraw from my account. Each one of those friends also must not be aware that there are other people trying to do that. And yet I should guarantee consistency even when hundreds of people are executing. So this is the property of isolation. That means theoretically each transaction must execute as if it is executing in isolation of others. How can you guarantee that? Logically therefore the consistent stage is defined not by just individual transaction but if there are 10 such transactions which are affecting common things then logically I must be able to show that these 10 transactions are executing in isolation in some sequence. Isolation means what? There is nothing else executing. So if there are 10 transactions legitimately what must happen is I am executing one transaction. Logically some of the transactions should execute only after I complete my transaction. Third one should start only after the second one completes. Whether mine is first or somebody else's first does not matter. But in whichever order I show that the transactions are executing in isolation the database should be consistent at the end of all these 10 actions. So this is an isolation requirement is actually tough to guarantee but that is what asset property requires. Intermediate transaction results must be hidden from other concurrently executed transactions. This is the theoretical statement about isolation. For every pair of transactions t i and t j so there are 10 transactions which are executing parallel. It does not matter whether they affect your account or not by the way those are the transactions which are maximally problematic but there could be some transactions will be affecting something else. As long as they try to impinge on the same thing those transactions we have to worry about. The definition of isolation is generic. If there are 1 million transactions executing simultaneously on a large system then the following must be true for every pair of transactions. So you take a transaction t i, you take another transaction t j. It appears to t i that either t j finished execution before t i started or t j started execution after t i finished. This is called serializability requirement. In short, while transactions are happening simultaneously they must appear as if they are happening serially. Logically t i should either finish completely before t j begins or t i must end completely before t j begins or t j must end completely before t i begins and this must be true for every pair of transaction that is working simultaneously. One simple way of guaranteeing that is that if I am executing a transaction who will all other million jokers saying that Fadak is doing something and Fadak finishes the transaction you take the next one. That way you will never be able to give meaningful information system to people. So there are different ways I will not go into the details but if you look at the book by Koth, Silversat and Sudarshan you will find out actually mechanisms which will guarantee that. For example, suppose I am working on I will give you some glimpse of how this problem can be handled when we look at this example further. So here is an example. There are two transactions t 1 and t 2. T 1 is Deepak that is me, t 2 is Pratibha that is my wife. Assume that I have 14,500 in my account. I know it, my wife knows it. Somehow my wife is angry with me, she wants to clean up my account. You can see the intention. Really A equal to A minus 14,500, right? Here I am actually transferring 50 rupees to a friend's account. So I have much noble intention. Now these transactions are executing. Originally this entire transaction will be this set of instructions and Pratibha's entire transaction will be this set of instructions. What I am showing is in the hustle-bustle of million transactions that are happening on a single server, individual instructions of these transactions will come and get executed at a random point in time which I can predict. Assume that the random execution so arranges the sequence of activities which are shown by these serial numbers. That means first the instruction read A is executed from my transaction. So I have read this read. What I have got the value of A? 14,500 in my memory. Then Pratibha's transaction executes read A. She also gets 14,500. The next value of transaction is remove all 14,500. That is executed as number three. The value of A in the memory is how much? No, after this is zero. The value of A in memory for my transaction is how much? 14,000. Not zero. Because this A is different, this A is different. This transaction is executing in some other program. This transaction is executing in some other program. What is common is the database discontent. The discontent was 14,500 so far. It is still 14,500. Now A is equal to A minus 50 is executed here. A has how much? A is now 14,000. It was 14,500 before this. After this in memory A is 14,000. I have not written anything. Now Pratibha writes her transaction writes. How much she has written? 0 rupees. So the value is zero. But now my transaction writes. How much will my transaction write? My transaction will write 14,000. When it read it was 14,500. Oh sorry, 450. 14,450. Of course my transaction will read B, my friend's account. My transaction will give him 50 rupees. And it will write update their friend's account. Look at what has happened. My wife has cleaned up 14,500 rupees. And the bank does not know of it. The bank still has 14,450 rupees in my balance. So actually the balance only exhibits the effect of my transaction. 50 rupees given to my friend. It does not exhibit the impact of this transaction. Is the database consistent? Certainly not. Who is unhappy in this case? My bank. I am okay. I did exactly what I did. My wife is terribly happy. But this is not the way the transaction. What can be done? What can be done in actual practice? Now you understand the importance of the definition of isolation? Logically the database must execute these two transactions such that either this executor first and then this executed, or this executor first and then this executed. Notice that if this executor first, there is no way A minus 50 is tangible. There is zero rupees. So this transaction will give an error. If this executor first, the transaction will give an error here. In fact this is a clear case where both these transactions in isolation could not have executed. Only the first one will execute. The second one will not. Provided I had some way of knowing it. One way, for example, is that whenever I read A here, if my intention is to update this value, I put a lock on the field of A. A lock, a flap, which says that I am doing something with this A. When I release this transaction, don't permit anybody to change this. It is called an update lock. When this transaction reads A, since this transaction also intends to modify A, that transaction will return a value saying wait. It will not cancel the transaction. It will ask the developer to wait till my transaction is completed until the lock is released. The locking mechanism therefore is an obvious mechanism. What is that? While it will delay the transaction T2 of Pratibha, it will possibly not delay other million transactions which are happening which have nothing to do with it. Consequently, to ensure serializability, one of the requirements is not lock everything when I open a reader. But lock only those fields or those rows which I intend to update. Imagine I have a read-only transaction where I am reading all accounts and I calculate the sum total of balance of all account holders in my branch. Then I don't need to worry about the lock even if it is put by T2. I say go ahead and read it. Such reads are called dirty reads. This is actually a technical word. Dirty read means I am reading values which are likely to have been changed before my transaction completes but for semantic purposes I don't care because I want an average value. Here and there some changes are there, it does not matter. But there will be some transactions which will not permit even dirty read. A report which has to be submitted to Reserve Bank of India. It cannot say approximately. It has to have exactly. Then even dirty read I will not permit. Such are the mechanisms which actually database semantics permits now for you to. And without such mechanisms you cannot guarantee consistency. That's the factor. So you understood this? If during the transaction T1, another transaction T2 reads value of A and attempts to modify it, a wrong balance for every result at the end of these transactions and transactions may be illegal. In this particular case the transaction is illegal. But imagine a transaction of the type where this says A equal to A minus not 14,500. It is just say set A to 0. That means withdraw whatever you have. My wife she is clever. She will not say 14,500. She will go to the bank and say, how much money is it? It's all there. Whatever it is. It is 14,400 and 50. She is happy with 14,400. In this case this transaction becomes illegal transaction. So she is a joint account holder. So in this case these two transactions can go on. But I must ensure that they go on as if they are sequential. So if my first transaction executes, the second transaction is legal and it can still execute. But if T2 executes first and then T1 executes, it is not a serializable transaction because nothing will be left. So you see PI, TJ, one of the two things is combination is valid, the other combination is invalid and this semantic has to be understood and implemented by my database engine when millions of transactions are executed. Is that clear now? What is the isolation requirement? Another transaction is allowed to access the partially updated database. It will see an inconsistent database. It can be ensured trivially by running transaction serially as I said. That is one after the other. However, executing multiple transactions concurrently has significant benefit. There is no point in having a huge server which executes one transaction at a time only. So I want to execute transactions concurrently but I want to give an impact as if they were logically executed serially. That is the purpose of isolation. Durability is the last of the acid things. After the transaction completes successfully, the changes it has made to database persist even if there are system failures afterwards. So durability of the transaction is theoretically till eternity. Once the database is updated and the transaction is committed, whatever is the consistent database, unless the state is changed by some other transaction, the database changes are durable. So if you get a CPI of whatever 6.8 and if no changes occur there after that transaction, if five years later you come back, your retriever should still say 6.8, it should not make it 5.9 or 9.5. That is the durability required. And this inverse not just the database state, even the backup, the restoration of the backed up database, any time you make a query the consistent data is formed. I hope you now appreciate why the acid properties are important for transaction. Atomicity, consistency, isolation and durability. If these four properties are guaranteed, then whether there are millions of transactions that are happening, you have no problem. Now you can understand the complexity of a database engine which has to guarantee concurrent execution for most efficient operations and yet has to preserve the acid properties of the transaction. A SQL engine is one of the few things in the software pieces that you have which guarantee all of those things. So there was an example which Dr. C. Mohan once gave that if a database crashes, you know you have to recover. So the recovery will do what? I mentioned that the transactions are partially done. Actually every time a transaction is executed, an entry for each step is made into what is known as a log file. So transactions start, there is an entry. Then this part is done, this part is done, this part is done. When transaction finishes, there is another entry called transaction commits. These entries are made into a log file which are written onto a tape or some other disk is nothing to do with database. Suppose the operating system crashes, this crashes, you actually take this other entry and you have a copy of the original database before you started operation today morning. Then you start with that database and apply these logs. So wherever the transactions are fully committed, you treat them as what is known as redo logs. That means you redo those transactions. But wherever you don't find a commit record in this backup, you go through those transaction steps which have been done and you undo those because those are not to be executed. This is how you maintain the database consistent in the recovery process. I will throw another squiggle. If while the recovery is going on because this recovery could be a massive operation, you might not have today morning's database copy. You might have taken a last backup 15 days ago. Then you have to start with that 15 days ago database and apply all the logs from the 15 days today. It might take massive amount of time. Now imagine that this recovery process is going on and the system fails again. So you can see the complications. You now want to recover faster because you don't want to do everything that you did there. So during the recovery process also you are required to maintain separate logs. You can see how complex the system will be. Operationally this system has to be made completely transparent. The operator should be able to press some clicks and buttons and do that. So this is the example Dr. Mohan was quoting that on a mainframe DB2, the system crashed. When the system crashed, the operator went into a recovery mode and initiated recovery. After 9 hours, nothing was happening. He panicked and shut down the machine. This was a bank database and the banker was literally, you know, struggling to retain control. As we say in Hindi, he was desperate. He thought everything is gone. The experts were called in and the expert told the operator when the manual says wait till the operation finishes, why did you stop? He says, how can you take 9 hours? He looked at the database sizes and other things. He says, oh my estimate is it will take 14 hours. So what to do? Restart the process. The process was restarted and actual recovery after the second restart was 6 hours. That is not 9 plus 5, but 9 plus 5 plus 1 hour, that 1 hour was taken because they had to merge the two logs, etc. But not a single transaction was lost at the end of, not a single transaction was lost. Now that is the beauty of the recovery mechanism that modern databases built. This is of course the DB2 on mainframe. On units, the database products that you have including DB2, Oracle, Ingress, Sybase, all the products like PostgreSQL or MySQL, they all comply with exactly the same rigor, these assets proper. So here is the durability requirement interpreted. Once the user has been notified that the transaction has completed, that is transfer of rupees 50 has taken place, the updates to the database by the transaction must persist despite further failures. Let me just show you simple normal syntax and special prescription which is available in SQL. Ordinarily every SQL query by the way by default is a transaction. So if you run an SQL query whether it is update or whatever, that will be taken as a transaction. So each statement that execute successfully is automatically committed. That means each transaction would then consist of only a single statement. Automatic commit can usually be turned off allowing multi-statement transactions. And how to do that depends on the database system. The SQL standard prescribes some transaction. Automatic commit means what? Since each SQL statement is a transaction, the moment SQL statement executes, the transaction is committed. So that means that part has been done. If you do not want that commit to happen, as in the case of example where there are two update statements which together must form a single transaction. Otherwise what will happen? One will commit that is considered as transaction committed, next will commit that will be considered as transaction committed. And therefore the interpretation of the semantics will be completely different. You won't both add or subtract from it and have to be considered as a single atomic unit. You have to have a mechanism to define these two things together as a single atomic unit. What I am describing is the normal syntax and semantics of SQL. The special unit, let's forget this. These are, for example, a transaction is started implicitly but then terminated by commit work or whole back work. These are two statements which are available, we will not go into that. If any step of the transaction fails, all work done by the transaction can be undone by the whole back work, that is the statement that you have. Rollback of incomplete transaction is done automatically in case of system failures but what is important is this, this is the syntax that you should use. Then close all SQL statements within begin, atomic and end. The word begin, atomic is a very important syntax. When you say begin atomic, all SQL statements that you have written here inside this block will not be considered individual transactions. They will all form a single bunch or a unit of work, a single transaction and when you say end, then this whole unit is an atomic unit.