 Dear students today we are going to discuss lecture number 42 of the database management system course and as you know the course code is CS403. In our previous lecture we finished our discussion on views and after that we started the topic of transaction management and we discussed that the two major components of the transaction management are the database recovery and the concurrency control. The basic notion on which these two areas or topics work is the transaction that is the transaction is the basis on which the database recovery and the concurrency control mechanism work. Similarly these two activities are handled by two subsystems of the DBMS because we know that DBMS is a collection of different programs there are thousands of programs but they are divided into different modules every module perform its own activity. So the database recovery or the crash recovery and the concurrency control they are handled basically by two different subsystems or modules of the DBMS. We defined in our previous lecture that transaction is a logical unit of work when we say logical it means we cannot associate certain boundaries this would be the transaction no it depends upon the environment the system. So we define the transaction rather more precisely I can say we take the transaction in the transaction management activity as a logical unit of work that is performed on the database but the thing is it is generally based on the activities of the real world. So we can say what is considered as an activity as a kind of activity in any environment and as I mentioned in the previous lecture that you have an understanding in every environment which has a precise definition it may have a little bit of a difference. If we look at our banking system the account opening is an activity that is performed in all the banks. Now the precisely operations involved in it may have a little bit of a difference maybe one bank asks for two guarantors one bank asks for one guarantor and maybe no one asks for one. One bank asks for a minimum of the amount that you want in the account it is called 500 or 1000 and there may be minor differences but the thing is within an environment we can identify easily that this is a thing where it is considered as a whole activity. When we are modeling that system in our database then normally the transaction of your database is reflecting on it but again that is not a hard and fast rule. It is the choice of the designer it is your choice whether you want to perform it as a single database transaction or if you want to perform it as a whole or if you want to perform it as a whole. There is a possibility but there could be another possibility that you can do multiple transactions through the operations. One thing to note is that transaction especially with the perspective of this transaction management transaction is that logical unit of work that reflects a single activity and that is performed on the database but it is that when we say transaction that activity that we perform on the database can be asked in our mind that how do we define its boundaries how do we say that this is a transaction so the answer to that is that it is okay that is normally taken that is generally taken from the environment from the system that you are developing. So, it is that it is a system but when you implement it on the database then the focus is that it is a transaction in the database that operation that all together all together reflect one activity that this is a work so you say this is a transaction when we look at the example and read it later then you will have a clear concept of transaction but remember that the basic concept of transaction management is the transaction itself. So, we are going to discuss one more concept based on this which we are going to discuss in a little while. One transaction in our exam system may be computing GPA of the students and the operations involved can be different. For example, if you are talking about computing GPA then GPA is the great point average and obviously it is computed in your every semester. So, what happens is that you secure your marks on their basis you get a great point allocated in every subject. So, when you have to calculate the grade point average then you will say that all the great points in a student's subjects you will sum them up and after that all their credits and courses will be divided that would be your grade point average so this is how you define the grade point average computation in your real world system. But when you define it when you perform it in the database then you will say that GPA computation involves that first you get the ID of the student whose GPA you want to calculate then you come to the enroll table and from there you access the great points of this student in different courses you sum them in one variable fine. Then you find the sum of the credits of all the courses in which this student was enrolled and the credit of the courses you will get it from the course table then you refer to the course table and then you read the credits of individual courses that the students was enrolled in. When you get the sum of the credits then GPA calculation involves k you divide the sum of great points with the sum of credits and then you write somewhere the GPA of the student. These are the operations that are involved in the calculation of the GPA. So this is an example of a transaction and the steps we have done on the database to perform or the database is that even when we have summed that thing is being performed in the memory is being done by the processor so those activities and those operations are also involved basically in the transaction so this is an example this is a transaction and these are the operations that are involved in this transaction yes we can have even some more complex transactions. Consistent state of database. Dear students this is a very important and basic topic regarding the transaction management rather I will say this is a very important topic of the databases. You must have a clear understanding what do we mean by a consistent database. See database definitions we have discussed. One of them was that database models a real world system in the computer in the form of data. See this is what I have already told you that the rules, the constraints, checks exist in your real world database if you are modeling that system then we will say the proper representation only if it is following all the rules and all the constraints then this is the second thing that we said that when we read the basic features of the database then we said that the advantage of the database is that it controls the redundancy so it reduces it. See this redundancy does not complete the duplication but there is duplication in the database but that is controlled. If you look at the primary for on key example there is a situation where the same data is being stored at two different places in the primary key and also in the for on key form. You are duplicating the same data and repeating it and similarly when we talked about denormalization we also said that we will duplicate some of the data and we are duplicating it with our own means that is not something that is uncontrollable that is something that we are doing deliberately or we could not avoid that. For example in the primary for on key case we could not avoid that. So this means that at certain places there is duplication in the database environment fine so if there is duplication in the database then that duplicated data must not contradict each other. The fact that if there is a stored in one place the fact that supports it should be written somewhere else. If it is not then your database is not consistent. So from today you will always have a special feature in your mind that what do we mean the consistent state of database. First of all what is state? State means the state of the data, the values, the data in the database which in a particular time in the data that basically reflects a state of the database. For example you have a table with 15 tables in the database. Now in a table at any time that is in the beginning there is no data in it. You define the database there is no data in the database because there is no data in the table. You start entering the data in the tables. First at any moment we had 15 school tables. First of all we have 500 records and the rest of the records are 300 or 200 or 400. So at any particular time whatever data is in the database that reflects a state of the database. The state will be reflected, the state will be represented by the data in the database. Now all the records in that record will have attributes. The values of attributes will be jointly called the state of the database. Now next requirement is or rather the basic requirement, the most important requirement is that database should be always in the consistent state. Consistent state means that what I have just explained. One thing is that it should be correct data. Correct means that whatever the business rules are and you should remember that constraints or rules can be called two major forms. One of the constraints or rules is that are implemented through the DBMS or you can say that are the part of the data model. If you talk about the relational data model then mind it. Entity integrity constraint or your referential integrity constraint. These two constraints they come from the data model. When they come from the data model then definitely they are the part of every DBMS that is based on the relational data model. Every DBMS will support it last but not the most. Otherwise that DBMS cannot be called a relational DBMS. This is the first thing. Apart from this, even when you determine the data type, when you check the range these are the checks, these are the constraints that are controlled, that are implemented through the DBMS. The basic functionality of the DBMS is implemented through the DBMS. I will especially mention the integrity constraint that I have mentioned. They are right from the data model. You should realise this. This will definitely support every DBMS that is relational and will be implemented through it. Apart from that, there are certain rules that come from the system, the environment for which you are creating the database. Obviously they vary. For example, if you talk about the backing system, they have their own rules. If you talk about the exam system, they have their own rules. If you talk about the ticketing system, they have their own rules. If you talk about billing system, they have their own rules. Similarly, if you go to a pattern bump, they have their own rules. This means that when we talk about the rules, the constraints, we can clearly categorise them. One of them is that they are inherent that are built into your DBMS. Why? Because they are coming from the data model. That category is basically the DBMS-oriented that are data model-oriented. You can call it basic constraints. Another category is that is business-oriented. These are two broad categories. Your consistent database will be following all these constraints. That is when it will be in the correct state. It will be in the correct state when it is following all the business rules. If you pay attention to one more thing, I have already told you that even your integrity constraints, these are two basic constraints, that are basically business-oriented. But they are so basic, they are almost everywhere. That is why we make them a part of the definition of your own DBMS. But the other rules vary from system to system. When we say that the correct database, basically the requirement of consistent database is that the data that is in your database, it must be following all the rules of the system. Whether it is an integrity constraint or other business rules. So consistent database means that the database should be correct. The other thing is that if there is duplication of data which is our deliberate duplication, then the duplicating data has duplicating facts. They must not contradict each other. They must support each other. If one of these facts has been corrected or not corrected or they have duplicating facts, then you will say that your database is inconsistent. Mind it, this is a crime. If your database is inconsistent, then there can be such errors and blunders that you might not even imagine. If you have a business of any system, you can down it upside down. Because these things are not so easy to detect. If there is an error inside, then it cannot be so easy to detect. It is possible that it has been damaged by the time it is detected. Because these errors are not so common that your database will break down. They keep working, but the result you are getting, that is wrong, that is misleading. If you are deciding based on data information, then if the business you are deciding is wrong, then your decision will not be right. The prime concern of the transaction management is that the consistency of your database should be maintained. Remember that there is a huge responsibility for a developer programmer. DBMS supports it to the maximum limit. There are many features that are available for developers that maintain consistency. But mind it, the consistent state of the database means that when the data in the database is correct and the duplicating data does not contradict it. Or jointly we can say that when the database is properly reflecting the real-world system, which is a state possible in your real-world situation, your data reflects it. For example, I have given you the first example. You say that there is a bank rule that your customer cannot draw more money than their balance. If this is the rule, then your database should not have any transaction in which the withdrawing of the balance is more than its balance. If you say that we have to make a teacher a professor when he is a Ph.D., when he has 10 publications, when he has 15 years of experience, and when this requirement is being fulfilled, then your data is saying to a teacher as a professor. It is not that in the real-world system, a teacher can be considered a professor. But your system, the fresh MSE is called a professor. In that case, your system is not reflecting the real-world, rather it is joking with the real-world. Consistent state of the database means that duplicate data does not conflict or database properly reflects the real-world system that is follows the business rules. A transaction should transform the database from one consistent state to another consistent state. Abhiyama discussion has been done. State means the data in the database. So, your transaction starts with the database. Obviously, we will read that there will be a point when we say that this is the start of the transaction and there will be a point when you say this is the end of the transaction. So, the first state that is consistent and the end state that should be a consistent state should be different or the same. The starting, the state in which the database was at the start of the transaction and the state at which the database was at the end of the transaction. They could be the same or the different. But the point is that it should be consistent. The concern is not that what is the state. The concern is not that what is the store and what is the study. If we talk about the bank system, the concern is not that the balance is zero or the balance of millions. If it had to be, it would have been different. If the balance of a person was 100, it would have been 10 lakhs. Well, it does not matter. But the thing is that whatever the state, it should be a consistent state. And from that, we can say what is the consistent state of the database and clear this in our understanding because the transaction management is based on this. Yes. During the execution of the transaction, this is the start and this is the end. The time in between when the transaction is being executed, then your database can temporarily be in a consistent state. This is possible. So, it is obvious that the consistency of the transaction that you will check first or after the end of the transaction. In between, it may. It is not necessary that your transaction will definitely temporarily be inconsistent. But it may. So, if it is, it does not matter until the end does not make it consistent. As we have just discussed in our discussion, there are transaction boundaries. The boundaries are that where the transaction is starting and where the transaction is ending. This means that there is definitely a point where you will say that this is the start of the transaction. Normally, we will represent the beginning of the transaction. This is the beginning of the transaction. This is the start of the transaction. And similarly, there would be a point where the transaction ends. Now, the transaction that ends and the beginning is the same. But the end of the transaction which is its termination that could be in two forms. Either we will say that the transaction is committed or we will say that it is aborted. What does this mean? Let's see. It may commit or abort. If executed successfully. Meaning the transaction that we have executed or the operation that we have done all of them have been successful and we want them to execute. So, we will say that after that when all of them have been successful then we will say that the transaction has been committed successfully executed and we will say that in this the database has been brought into a new consistent state. So, this means that it is possible that the transaction may start but we don't want it to be successfully complete. This is because sometimes the transaction that is being done is based on a different condition. So, if a special condition is done then you will say that it is okay to commit the transaction but if it is not done then you will say that abort it. It is not complete but it is considered as a cancer. So, if all of those steps are successfully completed and we want to confirm that it is okay then we will say that this transaction is committed. Otherwise, if we want or the logic or the situation that the transaction should not have been done then we will say that abort this transaction. For example, we said that we want to give 10% raise if we have a company then we want to give 10% raise to our customers. But they say that the raise that we want to give is either more than 20,000 or between 20,000 and 30,000. If it is less than 20,000 then we will not give more than 30,000. So, what you have done is that you have calculated the amount of the raise that we want to give to the customers. So, when you sum it up and you have started raising it but if you have calculated the sum then we did not want to do it because it was either less or more. So, you will say that cancel the transaction and we will do it again. So, in that case this transaction would be considered as abort it. You do not want to commit that transaction. So, we will say that this transaction is abort it. And then when you abort the transaction then we have already said that when the transaction starts then before that the database was in the consistent state. So, the committed transaction that brings the database into a new consistent state but your abort transaction means that you will take this consistent state which was before this transaction. Such transaction is rolled back or undone. A committed transaction cannot be undone, compensating transactions. Once you have committed a transaction you have said that this transaction will be committed. After that this cannot be rolled back or abort. It is necessary that you have committed a transaction and you have confirmed the transaction should be committed and that has been committed means that the changes that result from the operation of that transaction those changes has been transformed, those changes have been reflected in the database nor they cannot be taken back but suppose you want to cancel that action although it has been committed then you execute another transaction and the second transaction is defined in such a way that after executing, after committing the second transaction cancels. It negates the effects of the first transaction. So, the second transaction this is a compensating transaction because it is compensating the effects of the first transaction. For example, you have given a raise, 10% has been committed. Oh, I made a mistake. Now, it is done. It cannot be returned. So, what should we do? Why should we minus 10% from that? Basically, you have executed two transactions but the effect is that it will remain in the same state in which the first transaction was before. So, mind it, when you committed a transaction then it cannot be rolled back but it can be cancelled through compensating transaction. Boundaries, as we have said that it will begin and end to define the start and the end of a transaction generally it is the responsibility of the programmer, the person who is writing the transaction will know explicitly that this transaction does not work. Otherwise, it varies from dbms to dbms. Some consider the entire program as a transaction. Some consider every statement or every operation as a transaction. This varies. Otherwise, if you have not told yourself, it depends on the dbms. Possibilities are to consider the entire program as a transaction or to consider every operation individually as a transaction. Acid properties of transaction are very famous. Whenever you read the transaction management then the properties of the asset are mentioned in it. They are famous. So, the four letters are the four properties mentioned in it. The first one is the atomicity. Atomicity means all or none. Transactions if executed then it will be complete or it will not be complete. There is no concept of partly executing a transaction. No. Why? I said during the execution of the transaction the database may be temporarily in an inconsistent state. If you allow the transaction to be executed partially, it means you are allowing yourself that a transaction transforms your database into an inconsistent state. It means it is a very basic property that all or none or all of them have said that as many operations are there because we have said that one transaction can be a multiple operation and generally transactions consist of multiple operations. So, as many operations as there are 2, 3, 4, 10, 15, 20, 100 are there. Either they will be executed or they will not be complete. This means that you have started the transaction and reached in between if something happens then you when you will turn it on again or you can do the rest of the operations or you can cancel them because it is certain that at the start of the transaction the database was in a consistent state and the transaction will be transformed into a new consistent state. There is no responsibility this is very important that the transaction is all or none thing as it is not possible that it is partly executed. The second property is consistency that stands for C rather C stands for consistency that is responsible for the consistency of the database at the end of the transaction it maintains the consistency and the third is isolation. Isolation means if multiple transactions are executed at the same time and in any database environment the DBMS supports multiple users at the same time. The multiple users it is accessing the database and performing some activities so if we have multiple transactions executing at the same time then isolation means that one transaction it is not affected by the operations of the other transactions so it should have an environment to execute as if it is executing in isolation as it is executing alone the other transactions it is not like that how do we achieve this but isolation means as this transaction is executing is executing in isolation alone and D means durability durability means the transactions changes the transactions operations once the transaction is committed those actions the changes they must be reflected permanently in the database so the transaction that should be durable that should be permanent this is what is meant by the durability so 4 properties atomicity, consistency isolation, durability acid properties atomicity all are known consistency maintains consistency in the database isolation, transaction should be protected from the effect of other transactions or durability changes by transaction are permanent whenever you update the database updates means that updates normally there are two situations one is that when you change the value of any field or any attribute so this means your database is changing but specifically this means that when you change the value of any attribute in any record so that change is first in the RAM buffer which is as you can see the input or output from the disk that is through a buffer a space in the memory of RAM the data is coming in the disk after that you are going to the disk so this means the first thing that you change the value of any attribute that change will be in RAM and from there it will be in the disk at any time there may be some delay between the two when you have changed the data in the buffer and when you move it from the buffer to the disk due to different reasons due to the delay there might be some delay between these two activities so if a crash occurs during this delay then the database is in the inconsistent state that means the transaction has been declared and first the user has submitted the transaction and declared that it has been committed but the underlying is that you have already updated it in the buffer now it is not in the disk there are certain rules to send on the disk of operating system your disk management has its rules it is moving at an appropriate time because it is running multiple processes so that is why the I.O. on the disk sometimes delay it is being updated but it was not in the disk that the system crashed now your database is in the inconsistent state because whatever state it should be as an example you say that you have taken money from an account holder and moved it first you have taken money from an account in the buffer you have updated it you have added it in the buffer and moved it from the buffer that the system crashed now your transaction its atomicity has been disturbed because now this transaction has been executed partially so your inconsistent state is in the database on restart when you turn on the system dbms has to identify it it will have to know it will have to identify it that previously the database was being executed it was not complete it was terminated and recover data into a consistent state so in consistent state either you complete the transaction the remaining activities which could not be executed you complete them or you cancel them in both the cases your basic requirement to maintain the consistency of the database that would be fulfilled so you do any of them that is fine reasons of failures can be many for example there is a natural disaster there is no control over it Sabotaj a person who makes a mistake makes a mistake because he has his own nature sometimes he does not even know why he is making a mistake but this is also a reason for example sometimes it is very interesting that especially in computer centers I have seen that they will take out a mouse ball what is to be done? nothing they will throw it out but they have taken out a mouse ball there will be a person who will take out a mouse ball even if he keeps it somewhere Sabotaj is saying that these things happen carelessness unfortunately but I would recommend you as a professional this is something which is not in a good professional what you should do with full effort with full honesty whether someone is watching you or not this is your own honor that whatever work you are given in real life or even now but especially in your profession your worth will be there you have just started your career now this is my advice whatever work you are given with full honesty and with full effort ultimately it will pay ultimately whether someone is watching you or not it does not matter your impression or in a professional field it is made from your habits from your work if you are not getting a honest work or a reward then you will get some time and the fact is that your impression is established that this is an honest worker so even if this happens but people come to you and ultimately you would be known as a chakrabahas so obviously that is not a good thing disc crash again this is something that may happen and especially when disc is being used then it may happen yes you can make an error in software whether it is a system software or you can make an error in your software and all these situations the impact is that when your database was a routine a proper procedure when it was not shut down it was not closed because when you down the dbms or close the databases there are certain activities that are performed and in that all these steps are included that take us toward maintaining the consistency of the database and in all your cases this is what is happening that your work is an abrupt stop so that reflects a failure, a crash recovery techniques the situations in particular that are the target of the database recovery mechanism that is the situation when the system crashes the buffer in your ram those are lost but the disc copy your database is safe because if your disc crashes then that is another issue in that case you have to activate your backup so backup is another issue the situation here is that your system is abruptly down and closed the buffer that was lost the system is off but when the disk was on the database because you did not shut down properly the buffer did not move properly so your database is in the inconsistent state so the recovery mechanism is in particular to handle this situation need to determine the transaction that need to be redone or undone after that you have to identify those transactions or you will do it again or you will undo them log file is a tool obviously a file which is used in the recovery so the basic idea is that whatever operations you are doing on the database you need to maintain a proper file the log when you for database operations continuously involve the log file when it will help you ultimately identify previously there has been a crash and the log file will help you to recover the database in this another thing since we are going to read one more thing as we have a database which we have a database it is a disk store and the buffer is the buffer through which you are outputting input similarly the log file has a proper file which is on the disk and the log file has a buffer so the way we have a normal file first your buffer has entries and you have to move from there on the disk likewise your log file will have your entries on the buffer on the ram and then it will move towards the log file on the disk that is how you mention t start one more thing what we are discussing here it is very simplified and this is to give you a basic idea similarly we are talking about the log file so practically in dbmsc log files have entries they may be very different from this in that different types of pointers and transactions and operations but here you are showing a basic idea so that you can understand what is the basic idea if you want to look at the log file then you can look at the tool in which you are using so the t here means the identity of that transaction here is t so you can give the idea or name whatever you would identify that this is the transaction and this is the start of this transaction after that another entry in the log file that would be the entry regarding the end of the transaction and obviously either it will commit or it will abort it depends on these two entries there would be an entry from that we have called data entries and basically those entries that would be in the result of the operations that you are performing on the database within your transaction so this is done apart from this there will be more entries we have just studied that log file contains one the end of the transaction now we are discussing the other form of entries that are in the log file these are the transactions transaction involves all sort of operations for example variables calculations can be involved in that your keyboard is reading or you are reading or writing a lot of operations can be done but the thing is the functions of the concern of the DBMS are those that are related to the database if you are multiplying a variable adding something subtracting something that is happening in RAM so there is no connection to the DBMS unless it affects the database the data in the database this means that whatever you have in the transaction that you can perform through a statement in any language but the operation of the concern of the DBMS or the transaction management are those that are related to the data in the database and mainly those two operations are read or write whether you are reading or writing write whether you are inserting a new record whether you are deleting that is the form of write whether you are updating somebody so the statement of the DBMS are those that concern the database and in the same way the transaction management are further narrow down so the thing that could disturb the consistency of the database that is the right statement because in case of a read statement you are not affecting the state of the database the value you have read you will do such steps on the basis that ultimately you will write that is another thing but the read statement by itself it is not changing any change on the state of the database so this means if we further narrow down the concern of the transaction management operation that is only the write write operation this means that the log file we have told the records that it is starting and ending other than that those entries would be regarding the write operations in the transaction now we have to say that the value of x is 50 and the value of y is 10 now what we have log file entries is that again I have told you that there is no entry for that read operation in the log file then what we have to say x is equal to x plus 5 in the RAM by the processor no operation no entry in the log file but when you encounter the write statement this is something that can affect the state of the database so definitely it relates to the consistency of the database so you would make an entry in the log file against this write statement so what will happen in the log file representing the transaction x is a particular variable whose value is being updated and the new value which as a result of this operation will have a new value that is going to happen similarly when you said y is equal to y star 15 first you have read then you said write y so if y was 10 now it is going to be 30 so its new value again you are seeing there and in the end it is T commit meaning when T is here we will end today's lecture in today's lecture we have started the topic of transaction management and in this two important points two important things which when you said that we have read the database then you should know what is meant by the transaction one and secondly what is meant by the consistent state of the database if you do not know this means that you have not read the course or I have not read it because I have read it correctly then the second thing is that transaction management is the recovery of the database and the concurrent access so we have just started the database recovery in the next lecture Allah Hafiz