 So, let us get started again. So, before the break, we left off at the asset properties which transactions must satisfy and it is the job of the database to ensure that these properties are satisfied except for consistency which is the job of the programmer. Now, a transaction can be in different states while it is executing. So, it is active when it is still doing something, the code for the transaction is still running. Now, when the code is finished running, it is done all the updates, reads updates everything that it needed to do. It is said to have partially committed. It succeeded in doing what it wanted to do, but some of the updates which it did may not yet be in a safe place. They may be in memory. So, if the power goes now, the transaction updates will not actually be seen outside. They are lost, but it has finished doing whatever it wanted to do may be in temporary in main memory. On the other hand, it might have failed. If there is a problem during the transaction execution, it cannot continue, then it is said to have failed. So, now from partially committed, what do you have to do? The database system has to make sure that the updates of the transaction are saved on disk and may be backed up elsewhere. And after that has happened, then you say that the transaction is committed. So, from partially committed, the transaction can become committed once the database has taken some extra steps to save all the updates somewhere. On in contrast, once the transaction has failed, the job of the database system is to undo whatever the transaction did. And so, that is called rolling back the transaction. So, the database system will roll back the transaction and restore the database state to its state prior to the start of that transaction. So, whatever updates it did are under. So, the database state is back to where it was with respect to this transaction. Other transactions may have come concurrently. We do not worry about them here. Whatever it saw and updated, you have to undo the updates. So, after this, the transaction is said to have aborted. It is clean. And after it has aborted, well, you have some options depending on, you know, in some cases, you can restart the transaction. It will run now or hopefully will run now. Maybe the failure was simply some transient issue sometime out on something, which hopefully will resolve and if you run it again. In contrast, if there was a logical error in the transaction, you are trying to withdraw money and there is no money, then it has to just quit completely. So, the transaction logic can ask for a rollback and quit completely if it finds there is a problem. But if the transaction logic did not request the rollback, but rather the database system found there was some other issue and rolled it back. In some cases, it might even restart. But typically, that does not happen. So, in any code that you write, you have to be aware that something can go wrong and the transaction can fail in the middle. It is not too common, but it can and does happen. And then, you may have to actually have a piece of code which tries to execute the transaction. If it fails, you may have to retry. If you use Oracle, there is a certain kind of failure, which is due to concurrency control. If that happens, you are supposed to retry the transaction. You are supposed to run it again and hope it works. So, the states of the transaction can be shown in this diagram. Initially, the transaction is active. If something goes wrong, it goes to a failed state. If everything has gone fine and it has finished all the updates, but the updates have not yet been written to disk and so forth. It is partially committed. After that, the database system saves the updates. It goes to committed. At this point, it is still possible for a power failure or something to happen or a disk failure at which point the transaction may go from partially committed to failed and then it may have to be aborted. Now, this aborted state might actually be reached not immediately. If there is a failure, if power goes, what is the state of that transaction? Some updates may have been written to disk. Others have not. So, it is a failed state. It may be partially committed also. But now, if power goes, it is too late for that transaction. If it goes before a certain point, when it is updates are written, then it moves to failed. Now, when power comes back up, the database has to roll back all those incomplete transactions and then those transactions reach an aborted state. In contrast, if all the updates are successfully written out, it reaches a committed state. Once it is committed, you cannot undo it. You cannot roll it back. You can run another transaction, which negates the updates. So, if you transferred money from B to A, you can run a fresh transaction which transfers money from A to B. So, that is a logical sort of thing. But the database cannot simply roll back whatever actions it did after it has committed. Why? Because after it has committed, some other transaction may come and update the same thing. So, now, you cannot just go and easily undo what this did. So, now, all this conceptually is fine, but how do you use it? As a programmer, how on earth do you control what a transaction does? So, the SQL language supports some constructs for handling transactions. So, in SQL, a transaction begins implicitly. You do not say start transaction. As soon as you run a query, a transaction runs. And you can either say commit work to commit the current transaction and begin a new one or roll back work to cost the current transaction to a bot. However, there is a little catch here. Pretty much every database system will do the following. If you run an SQL query or an update, it starts a transaction, runs that SQL query and commits the transaction, assuming it committed successfully. And that is it. That is a transaction as far as SQL is concerned by default. So, if you have five different updates statements in your transaction, what you thought was a logical transaction, the database may turn it into five different transactions as far as the database is concerned. Because by default, every SQL statement is a transaction by itself. That is not what you want. Now, what do you do? So, there are a couple of options. Database systems typically have a control which says, should a SQL statement automatically commit or should you turn autocommit off? So, you can either do it by some configuration in the database or if you use JDBC or other than ODBC, you can say something like this connection dot set autocommit false. So, autocommit means SQL statement commits automatically. Turn it off. It means after it completes, the transaction is not committed. It is still active. So, you can do multiple SQL statements and then issue an SQL statement commit to a core rollback work depending on what you want. So, now it is in your control. Or there are actually some of the constructs which in SQL, which say begin atomic and then do a whole bunch of stuff and then end. And before the end, you should have commit or rollback. So, that is another language construct that is SQL province begin atomic. Again, not all database is supported, but it is available in the standard. So, as far as the program is concerned, you write a transaction. You run queries. Now, any query which you run might fail. That query itself might give an error. So, then you should be prepared to rollback and quit. In fact, what happens is the transaction, the database, if you turned off autocommit and there is a bunch of things which are part of the same transaction, if something fails, it will implicitly rollback the whole transaction. And then you just exit the database has taken care of rollback. But there may be other errors which you catch. You say there is a problem now. Then I have to rollback or I commit. So, as a programmer, you have to look for error conditions and then either rollback or try some alternative and try to commit. So, you have to be careful about this. What exactly the database does, you have to figure out in case of an SQL error. So, this was the programmer's job. Now, what exactly does the system do? The first part is recovering from failure. This deals with atomicity and durability. Subsequently, we will look at isolation. So, C we are ignoring of acid. So, A and D we are going to look at first. I we are going to look at later. So, as I said, A and D are issues mainly because there are failures. If there were no failures, the system were perfect and you ran one transaction after another, every transaction should complete all its actions unless of course, there is an error condition and it will be durable. But that is not the case. What kind of failures can occur? Well, the first kind of failure is a logical error in a transaction. No money in the account. So, you have to rollback. Another could be a system error. Some kind of deadlock happening inside the system. The only way to resolve it is to rollback one of the transactions. So, that is a simple transaction failure. There may also be a system failure, a power failure or rather hardware or soft operating system fault and so on which causes the entire system to crash or the database component of the system to crash. Maybe not the operating system, but at least the database system. Now, in general, a failure can be arbitrarily bad. Failure could potentially go and erase the whole database. In practice, that does not happen. Most failures do not do such bad things. Why? Because programmers have programmed things carefully by and large. If there is an error, the program stops. It does not continue on and create a mess. Badly designed programs can do that, but database systems are not badly designed. People have put a lot of effort into designing them. Operating systems have been carefully designed and tested. So, generally speaking, an error will not cause any damage to the data. Once in a way, you do here of database bugs which cause data loss. They are very, very rare. So, we will not worry about them. We will assume that the database implementers have taken care of that. So, it does not happen. So, we make the fail stop assumption that non-volatile storage contents are assumed to not be corrupted by a system crash. Now, the database system internally does a variety of things. The only kind of corruption which you cannot prevent at the software level is if the hard disk itself has a problem and the bytes of a file are corrupted. Then, what do you do? You have to at least be able to detect it. So, the hard disk system has some checksums, what are called checksums, to see if the data and the file which you have read is correct. If there is an error in what you have read, the disk system will say, sorry, you cannot read this. The file is corrupted. If the file is corrupted, how do you recover from it? So, if there is a disk failure, you will have to take some other actions to recover. And we would not get into details of that, but it is possible using the same mechanism which we use for durability. So, the recovery subsystem of a database has to deal with failures and ensure atomicity and durability in spite of the failures. And the recovery subsystem of a database, recovery algorithms actually have two parts. One is actions taken during normal transaction processing to ensure that enough information exists to recover from failure. So, while the database is executing normally, some things are written out in the log and some other actions are taken to save some information. Now, if the failure happens, then this saved up information is used to recover from the failure, either to roll back the transaction if it is to be aborted or if the transaction is committed to in effect redo it. If the updates were not quite made to the database, but the information about the updates is available, it can redo the actions so that the database comes back to a state which reflects the completion of the transaction. So, the transactions updates are durable. They are in the database eventually after recovery. Just before recovery, they may not be in the actual place in the database. They may be updates may have been written to some log, but it is not there actually in the proper place in the database. So, during recovery, you may have to look at that log and you meaning the database system has to look at the log and update the actual locations in the database. So, that is what the recovery algorithms do. Now, recovery algorithms have to deal with different kinds of storage. There is volatile storage like main memory which does not survive any system crash. There is non-volatile storage which does survive normal system crash like disk, a flash memory and so forth. However, these can also die in certain circumstances. Disfailures do happen. So, there is a third level of storage called stable storage which is actually a mythical form of storage which survives all failures. As I said, it is not, it is mythical because it cannot actually be implemented. All failures is impossible. However, you can approximate it by maintaining multiple copies on distinct non-volatile media. So, if you have two disks, one here and another in Madras, state bank I think does that. They have a center in Belapur and another in Chennai and any transaction which runs, copy is saved here and over there. So, even if this whole place gets flooded, does not matter. That will be there. The data will be there. So, that is stable storage as far as we are concerned. And if we are not so paranoid, maybe you will just have a RAID system, two disks. So, if you write to both, one of them fails, still the data is there on the other one. That may be good enough. The chance that both will fail is fairly remote. It can happen if the place burns down, but you are willing to take that risk. Then, you can think of that as stable storage. So, now, how do you implement atomicity and durability? Here is a scheme which is actually silly as far as the database is concerned, but is used widely for text editors and others. So, the idea is you have an old copy of the database. Whenever you make any updates, do not make it to the old copy. Write to a completely new copy and after the transaction completes, you will update a point, a dv pointer which is used to point there. After the transaction successfully completes, it is partially committed, you update this pointer to point to the new copy. This new copy should have been flushed to stable storage first. Then you update the pointer. So, that is used by editors. If you use a word or any other emacs, vi, whatever you use, typically they will create a backup file. They will not directly update the file which you want to write to. They will only make all the changes in the backup file. When you say save, some of them will simply copy the thing into the original file. So, there is a danger of corruption if a failure happens during the save, but others will do this. They will keep the old file, rename the old file and then make the pointer point to the new file. I mean the file name. The file name is the same, but the actual file underneath, the file name will be referring instead of the old file, it will refer to the new copy of the file. So, this can be done for file updates of small files. Of course, it is not feasible for large databases. You are not going to copy 100 gigabytes every time. It is silly. It also does not handle concurrent transactions, even if the database is small. If two people want to concurrently make updates, what do you do? This guy has one copy, that guy has one copy. Now, you cannot merge them. So, the point is you have to make updates directly in the shared database. So, what do you do? The answer is using something called log-based recovery. This is the standard which is used all over. The idea is that the updates performed by a transaction are written to a log. All the updates are written to a log. What is written to the log? You will say that you update this part of the database from this value to this value. So, the old value and the new value are both there. Why do you need the old value? To roll it back. Why do you need the new value? To re-do it if required. So, the log contains for every change made to the database, the log will have the old and the new value. So, if the database crashes before the update is performed. So, when you recover, the failure system will look at this log and see if the transaction was committed or about it. So, it looks at each log record. If the transaction was committed eventually, what do you mean by committed? In the context of logs-based recovery, as the transaction performs actions, it writes the old and the new values to the log. It also writes the new value to the database in memory, but that may not yet have been written to disk. It may only be in memory. But the moment it finishes and it has written all the log records for its updates to the log and where is the log sitting? The log is temporarily in memory as the writes are happening, but then it is written to a file on stable storage. So, once all the updates of the transaction, the old and new value for all the updates which have been, they are all written to stable storage, you can say the transaction is committed. Now, the stable storage has enough information. So, even if the database crashes now, when you recover, the log has enough information to redo the transaction. Therefore, you can say it is committed. But if a failure happens before this point where all the records are written, then the transaction cannot be committed. The database has failed. So, you have to roll back the transaction. So, for those which have failed or transactions have failed, you have to undo. So, the log records have old and new. If you undo, you use old value. If you redo, you use the new value. This is the basic idea. And even if there are concurrent transactions, as long as they do not update the same data, if two guys concurrently update the same data, you are in trouble. And that is the job of the concurrency control part of the database system to ensure this does not actually happen. Assuming that does its job, there are a lot of details. I am hiding a lot of details here. Tremendous amount of details are being hidden here. But I just want you to understand that there is a log. You will see the log. If you look at the files in the database, you will see logs in there, a log file depending on the database system. They have different names. There are directory structures for the log. And the log contains these things, the old and new value. And it is used both for rollback and for redo in case of a system failure. Sir. Yeah. Whenever we are committing the transactions, it is writing into the log file. Yes. It is not directly updating the database. It actually updates the database also. But that is in memory. At the same in memory. One can happen immediately after the other. But the update is in memory. There is no guarantee that the update has gone to the disk at this point. But the log is immediately flushed to this. I mean writing into the disk only. When it writes into the disk finally. That is actually up to the database system. So the database system might write to disk immediately. It might actually wait and gather multiple updates to the same page and then write that page to disk. That is up to the database system. The log-based recovery gives it the flexibility. Otherwise, as soon as the update happens and the transaction wants to commit, all of the updates have to be written. Then the overhead is more. I overhead. The log-based recovery lets it get away with not writing it out immediately. Which can improve the speed of the database. Of course, it has to write it out eventually. It cannot postpone writing it indefinitely. So it does get written out. But maybe after a little while. What I understand in spite of this log-based recovery, we cannot ensure 100 percent recoverability. Because these logs are also for some time it is in the memory only. No. For a very short period. Like they define the size up to which like 2 MB or you give switching time so that the log switch happens. No, the log switch is a different. Log switch is a different thing. Log in memory is a different. These are two orthogonal things. So as long as the log records of a transaction have not been written to stable storage, the transaction is still in the partially committed state. The moment that last log record. This is actually a commit log record at the end for that transaction. The moment the commit log record has been written to stable storage, the transaction is partially committed. So this is physically the definition of when a transaction switches from partially committed to committed in a typical database is when that commit log record has reached the log on stable storage, on disk. Will this ensure a 100 percent recoverability? Yes. Yes. So once that log record has gone to stable storage, the transaction's updates will not be lost. They are durable. And after that you inform the application program that commit is successful. Then the application program informs the user that the commit was successful. So this is a normal mode of operation. In certain situations you can speed up the process at a small risk by telling the application it's committed even before this log records written to disk. It is possible to set up the database to do that. That will speed up processing but at a small risk. Some applications do that. They are willing to take that risk. So recovering from failures is one issue. We have seen a very high level view of how recovery happens. There is another issue. If you have a centralized system for all of LIC and it goes down, fine. It will recover. It may take a few minutes to recover. Or if it is not well designed, if the system is not powerful enough, it may take an hour to recover. Or if the power has failed totally in that area, your generator has failed, it may take several hours to recover. Now you cannot shut down all the branches for that long a time. People are waiting to do work to transact with you. So there is a notion of availability which means the database system or whatever system it is should be able to continue processing whatever work it is processing even in the presence of failures. How do you do that? So you have several levels here. The most common kind of failures, power failures, what do you do? Put a UPS. What is the next most common kind of failure? Well, there are system crashes if you use an operating system which is flaky. If you use the newest version of cutting edge version of even Linux, you may get into trouble. So what you do is use something which has been tested for a while. We know it is stable. So you will use an operating system which is stable. You will use hardware which is good. So you minimize the chances of that, but still it can happen. So the next most common sort of thing which we see after taking care of all of this by using good hardware and so forth are disk failures. Especially we have a lot of disk, disk do fail. So what do you do? Well, you use RAID. So a quick summary of RAID. The idea is that, actually here, keep multiple copies of data on separate disks. It looks like a single file to you, but underneath the operating system will keep two copies of the file on two disks. And if one of the disks fails, it is okay. The operating system will detect it is failed. And if you read the file, it will continue to give you data from the other disk. But whenever you write and both the disks are available, it has to write to both. If one has failed and the other is available, when you do a write, it writes to that. Now you have to deal with a failed disk somehow. So usually you have a hardware system which will indicate that this disk has failed. Somebody has to go unplug the disk, put in a new disk. And at that point, the system will copy all the data from one disk to the other. So a disk failure will be pretty much transparent. Yeah, the system will run a bit slower when the disk fails. And when it's copying data again, this system will run a little bit slower. But you don't have to even stop working just because it is failed. In contrast, if your PC disk fails, that's it. Your PC is dead. It's okay. You can, as a developer, you can afford that. But on your central database server, you cannot afford it. You have to use RAID. There are different types of RAID. I've just told you about the simplest kind of RAID, which is called mirroring. There are other kinds, but this is the most common kind. If you want to know more, chapter 11 of the database book has it. Okay, so disk failures can be masked by RAID systems. Network failures, again, there are ways of dealing with transient network failures. But longer term network failures, what do you do? So then the way you interact with the database itself is slightly different. There are what are called persistent messaging systems, where a transaction is sent as a message, like an email message. It's received, it's processed, and then a message is sent back to you. And if the network goes down in between, it's okay. You don't know whether the transaction is completed or not. But eventually when the network comes back up, you can find out. You can ask the database system, did it complete? And if not, well, you do something about it. Okay, so there are techniques to handle network issues like that. So the one thing which you can't really do much about here, with a single system is failure like a flood, fire, long power outage for whatever reason. And that is not uncommon. A couple of years ago, three years ago, I guess, 2005, half of Bombay was under water. Nobody can survive that kind of a thing. Even if you're building a safe, you don't have power, network is down. It cannot be used. I was talking about network failure. With availability, network failure is actually a very important problem. It's a non-trivial problem. So you have a network connection to your data center. If that fiber gets cut, it happens all the time. Somebody does construction, cut, gone. What do you do? The center is cut off. Nobody is able to process transactions. What you do again is have redundancy. You have multiple different fibers coming from different places. So that if one gets cut, the other is still available and can continue working. Maybe it's a little bit slower, but you can continue. So again, as far as possible, you must failures to maximize availability. But if you have a single center, there are something which you cannot mask. Flood, fire, can't help it. So there the solution is to keep a backup at a remote site. And it looks like this conceptually. This is called the primary site where you're doing the transactions. Now, all the updates that you do, including the logs which you write particularly, are shipped over the network to a backup system. If the primary site is gone, then the backup system must now tell the world, hey, the primary is dead. Come talk to me. So what do I mean by the world? Well, there are all these clients sitting here. They should know that they should stop talking to this fellow and start talking to this guy. So there the application logic has to see if it cannot talk to this guy, then it should try the other guy. And the other guy will say, no, I have not yet, I have not taken over yet. This guy is still alive. Maybe you're not able to reach him, but I have not yet taken over. Or he can say the king is dead, long live the king and take over. So now once this guy takes over, it doesn't matter what happened to the primary guy. He's dead, gone, fine. For now, the secondary is active. When the primary comes back up, something has to be done. It has to get all the updates which were done at the secondary, at the backup, copy it back, come back in sync. There is a protocol there, which is non-trivial. And eventually they are back in sync. And at that point, the primary may take over again. And the backup goes back to backup state. So all of this is part of a remote backup setup. And most databases today support remote backup, although there are issues in practice. How do you detect failure? How do you know the primary is dead? Maybe it's just that you are not able to talk to the primary. Your line is cut. So you will need to have multiple communication links. Then you have what is called a heartbeat, some way to keep probing the primary and say, are you alive? Are you alive? Or keep receiving a message saying, I am alive. I am alive. It's called a heartbeat. So all this is part of high availability solutions. They are built in. You don't have to implement any of this. You should just be aware that these things are possible. Once you have a database system in place, high availability options will do all this for you. Sir, when primary is dead, then what about the log records when backup? The log records are sent continuously. As they are written, they are sent to the backup. So the backup has the log records. So it can redo or roll back transactions as required. That's the idea. So then again, there are issues on transfer of control, which I just described. And in particular, to take up control the backup site essentially performs recovery using its copy of the database and the log records. So in effect, it runs the recovery algorithm and comes back in sync. At that point, it can start processing fresh transactions. So there are a couple of different possibilities. One configuration is the backup does nothing normally. It just collects log records. And then when required, it suddenly starts recovery and then takes over. But that can be slow. So there's a hot spare configuration where backup system is actually continually processing log records as they arrive, applying the changes locally. And if the failure is detected, the backup can quickly roll back any incomplete transaction. And within a very short time, literally a few seconds is ready to process new transactions. So that is, the hot spare is commonly used for, I'm sure SBI uses a hot spare, can take over instantly. Now there are other alternatives to remote backup, which are based on replicated data. These generally don't, you know, give very good performance. They're slower. There are transactional issues there. But it's still useful in certain situations where you don't want the cost of remote backup. But it still gives you some amount of fault tolerance. And we won't get into that here. So now we'll move on to the last part, which is concurrent transactions. Before we do that, are there any questions on recovery? So in the, yeah, say like the remote backup, we might have a lag of some, say like, say like for single transaction, say like some milliseconds, that, yeah, the last time has not been, say, copied into the log file or has not reached the backup. So in that case, how it will be handled. Okay, that's a good question. So let's say that a transaction was declared committed, the moment its log record reached the local disk, but that log record has not yet reached the remote backup. And now the primary dies. And that log record never reaches the backup. Okay, then you have a problem. You have a transaction which the primary thought is committed, but the backup thought is about it. This can happen. If you want to avoid that, you will declare a transaction committed only after its log record has reached the backup. And the backup acknowledges saying, yeah, I have got it. Then you tell the application program that the transaction is committed. All this configuration has a costing, as you mentioned, in terms of the performance. Yeah, definitely. When a transaction has to commit at two places and synchronization is to be sure. Yes, there's performance, there is management, the human cost involved in managing it, keeping it up, and making sure that your high availability feature doesn't decrease availability. Yeah, if your logs are not going through and the disk gets full, then even though one system is running, it crashes. Okay, so you have to deal with all these kind of Okay, so let's move on now to concurrent execution. So why do we want to allow transactions to run concurrently? Why not let them run one at a time? Most of the transactions, something else, you're not that big, right? You update five records. How long does it take to update five records? You may read five records from this. Each read may involve some time to read it, maybe 10, 20 milliseconds. Then it, so maybe 20 milliseconds times 500 milliseconds. And then it has to write those out. Another 100, 200 milliseconds. So maybe in about 200, 300 milliseconds, your transaction finishes. So why not just run things seriously? Then what is the maximum rate you can get? Maybe you'll get three, four transactions per second, perfectly acceptable at a branch, single branch, or even a divisional thing. Maybe it's okay. It's good enough. But if you have a central thing for all of India, you don't have that flexibility. So what do you do? You have to run things concurrently. And what is the benefit of running them concurrently? The idea is you can have increased processor and disk utilization. So the system has many resources. It has not just one actually, but today multiple CPUs. It has multiple disks. So if a transaction is waiting for a read to happen from disk one, the other disk will be idle. If only one transaction is running, the other CPUs, all the CPUs will be idle. And all the disks except that one disk are going to be idle. So when that finishes, maybe it does some CPU. So now all the disks are idle, and only one CPU is active. So most of the time, most of the resources of the computer system are just lying idle if you run things seriously. That's bad. So if you can run them concurrently, maybe while this transaction is waiting for a read to complete, another transaction can be using the CPU. A third transaction may be executing a read or write to another disk. So by allowing multiple concurrent transactions, you can actually make full use of the resources and yet have everything run very fast without major problems. But you have to be careful. What if all these transactions go and read or write the same data item? What happens then? There can be a mess. So if you are not careful, we saw an example of a transaction executing and another came in the middle and read inconsistent data. It can get worse. You may be writing some data, another guy can come and overwrite the same data and cause a complete mess. So you have to have a mechanism. If you want this benefit and it is worth it, people have spent a huge amount of effort on getting concurrency control right. And that effort is worth it because you get very good response because of that. Another problem is if there's one slow transaction, which takes five minutes, everybody is stuck behind this slow guy. If you run it seriously, that's a major problem. In the olden days, this was more common. You would have reservation systems where it would just hang for no apparent reason. You'd wait in the office for 20 minutes and say, okay, I'll give up or eventually it comes through. That may have been because of things like this. These days, that doesn't happen because they're well designed. So there are a lot of details in chapter 16 if you want to worry about it. But what we are going to do now is give a high level view of what are the issues. We're not going to look at in detail at how to do concurrency control. To understand what are the issues, you should understand what is a schedule. Schedule is a sequence of instructions that specify what the transaction is doing. And it also shows the interaction between transactions. So here is a schedule with two transactions. In this particular schedule, what has happened? This transaction is red A multiplied it by, well, subtracted 10% from A and added it to B and written B back. And then this fellow started and red A removed 50 from A and added 50 to B. So there are two transactions which are both operating on the same A and B. But the schedule shows that this guy ran first, then this guy ran. So what is this vertical line? It's time. This happened first, then this, then this, then this, then this. So that's what a schedule shows, what happened in time. You as a programmer don't have to worry about schedule, but I'm showing this to explain certain concepts which are coming up soon called serializability, which you will see in practice, you will see that word many times. So that is a schedule. So a schedule for a set of transactions consists of all instructions of those transactions. I showed you a schedule for two transactions. And it preserves the order in which instructions appear in each individual transaction. If the two transactions run concurrently, then the schedule can look different. I'll come back to this slide, but just show you an example. Here's an example where T1 did some work, then T2 did some work, then T1 did some more work, then T2 did some more work. This is a schedule where the transactions are mixed. They're running concurrently. I'll come back to this slide in a bit. So a transaction that successfully completes will have a commit instruction as the last instruction. And in the schedules which you'll see, we sometimes omit the commit instruction. It's implicit. After the last one, immediately after the last one is a commit. Commit is a request to commit, actually. And we'll assume that that happens immediately. And a transaction that fails to complete will have an abort instruction as the last instruction. So now we saw one schedule. In this schedule, one transaction ran, then the other. This is called a serial schedule. Serial ordering is there, one followed by another, followed by another. So in this serial schedule, this is also serial. Now T1 has run first followed by T2. What is the difference between this and this? Here T1, T2. There, T2 followed by T1. They're both serial schedules, just because I called them 1 and 2 doesn't mean anything. Doesn't mean that one has to run first and then 2. It's just a name. So these are serial schedules. Now from the consistency requirement which we saw, we can say for sure that if a transaction is consistent, take any serial schedule. Initially, the database is consistent. Then after the first transaction completes, the database is consistent. Then the next one starts, then the database is consistent. Everything is fine. So this is good. Serial schedules are good. But of course, they don't perform so well. What about schedule which are not serial? Take this one. It's not serial. These are called concurrent schedules. Now in this particular thing, is it going to lead to disaster, this particular schedule? Some concurrent schedules can lead to disaster. But is this particular one leading to disaster? Actually no. So what happens here is this fellow reads A, finishes writing A. After that, this fellow doesn't touch A. Now this fellow is reading A after this fellow ran. Then this fellow is reading B. This guy didn't touch B yet. This has read B and updated B. Then this has read B. Now if you look from the viewpoint of consistency, you can verify that the sum of A and B is preserved after this schedule, this particular schedule. There are other things where it won't be preserved. But here it is A and plus B will stay the same. The two transactions just move values from A to B. But the sum should be the same and it is in fact in this case. So this schedule intuitively is, in some sense it's okay. It's okay to run things like this. It's safe. But in what sense is it safe? You don't know yet. I have not explained in what sense this is safe and we will see that in a bit. But intuitively, there is something about this schedule which makes it safe. It's okay and we won't allow it to run. There are other schedules which are not safe. Then we won't allow them to run. Just to step ahead of the next few slides. In this case, look at these two parts. If I could move that up and this down. In other words, in this particular schedule, this executed, then this executed. But suppose I flip them. I moved all of this and did it before all of this. Would it make any difference to what each of them writes? This is reading and writing B. This is reading and writing A. So if I did this first, this will not change. So I could conceptually move this up and this of course correspondingly will go down. And that is the serial schedule. Where T1 ran, then T2 ran. So whatever this sees and whatever updates it does are exactly equivalent to the serial schedule where T1 runs, then T2 runs. Therefore, we can say that this schedule is equivalent to that serial schedule. Schedule 1, where T1 ran followed by T2 runs, equivalent in that each statement here reads the same values. Each write writes the same values as that schedule and the final values are all the same as in that schedule. It is equivalent in that sense. So that is just the intuition. It is defined more formally. However, here is a bad schedule. Here we have read A, subtracted A. We have not yet written it. In between this fellow jumps in. It reads the old A. Not the A after the subtracting 50, but the original A. It subtracts 10 percent from A, writes A and then this fellow writes A. So what has happened now? This update is completely lost. The value after this is 50 less than the original A. And then this continues, this continues. You are in trouble. The final value of A is wrong. Very similar. What is the difference between these two schedules? Almost nothing. There are only two small differences. One was write A has come here, read B has come here. Read B causes another problem. There are two problems in this schedule. So we want to allow this schedule, but not this. How do you do this? How do you recognize that this schedule is safe? But this one is not. How do you recognize it? And how do you ensure that only safe schedule is executed? That is the job of the concurrency control component. So here are a few concepts. As I said before, the C assumption says that each transaction preserves consistency. Therefore, serial execution of transactions preserves consistency. And we will say that a possibly concurrent schedule is serializable if it is equivalent to a serial schedule. What do you mean by equivalent? We will define that formally. There are actually multiple notions of equivalence. We are going to just use one notion called conflict equivalence. So here we say that if a schedule S can be transformed into a schedule S prime by a series of swaps of non-conflicting instructions, we say that S and S prime are conflict equivalent. So if you go back here, we could swap these two, meaning move the read B up ahead of write A. In the given order write A occurs, then read B occurs. But we could flip it. Do read B and then do write A. And the two do not conflict because they are touching completely different data items. There is no conflict. Now if you consider continue with read B versus this statement, there is no conflict. Similarly this and this, there is no conflict. So read B can be moved all the way up to here. Similarly B equal to B plus 50 can move up. Write B can move up. After moving all these things up, we have a serial schedule. So what we found is that schedule S can be transformed into that other schedule by a series of swaps of non-conflicting instructions. And therefore, we will say that this schedule is serializable. It is not serial, it is concurrent, but it is serializable. This word is very important, serializable. You will see this very often. And in particular here, we use the term conflict serializable. People usually omit the word conflict serializable and just say serializable. There are other notions of serializability, and this is the most commonly used notion. So serializability for our purpose will be this, that you can whatever order they executed, it is possible to swap whatever they did with non-conflicting things and come up with a serial order. And therefore, even though they ran concurrently, the final result is consistent. That is important. And it is exactly equivalent to result of some particular serial schedule. What was the serial order? We can figure that out if you wish. But in some sense, it does not matter. It may depend on which was submitted first, which was submitted second. If somebody entered, pressed enter a few seconds later, it could have been the other way. It does not matter. But it is equivalent to some serial order.