 Dear students, today we are going to discuss lecture number 43 of the database management system course. In the previous lecture, we started our discussion on the transaction management. I told you transaction management has got two major phases, two major parts. One is crash recovery or database recovery of after a crash and the second part is concurrency control. We started our discussion on the database recovery and we studied that the basic concept, the basic notion behind the transaction management is of transaction and we discussed that transaction is a logical unit of work that is performed on the database and it may consist of one or more operations performed on the database or otherwise in the memory. And I also told you that transactions that are performed on the database, they are generally based on the activities that are performed in the real world system and then we discussed different types of transaction. Then we studied different reasons of the crash and the basic phenomena behind all that is that the improper shutdown of the database in such a way that your hard disk remains safe but the RAM, the memory, the contents of the memory are lost. So, this is the situation that is dealt by the crash recovery mechanism. The crash recovery subsystem of the DBMS log file contains the entries about the transactions, the operations of the transactions. One type of entry that is contained in the log file is the start and the end of transaction that is it marks the point from which a transaction starts and the point at which the transaction ends and apart from that the entries for the right operations on the database. Why? Because I told you right operation is the one that changes the state of the database. So, it means that your log file contains the boundaries of the transaction and all the right operations that are included in the in a particular transaction fine. Now, the type of entries about this right operations there are two types and it depends on how does the DBMS update your database. There are two approaches one is the daffod updates where the updates are daffod means the database on the disk it is not updated right away after the execution of the right statement rather it is daffod it is delayed until the commit statement is executed. And the second thing is second approach is the immediate updates and as the name suggests that the database is updated as soon as the right statement is executed. So, in the immediate update the DBMS does not wait for the commit statement to execute rather as soon as the right statement is executed the contents of the database are updated. So, though entry hothi hai database ke hendar against the right operation in the transaction that depends upon the policy of updating the contents of the database. So, paale hum jupadate that was daffod updates or iss case me jo apki log file hai that is called the incremental log with daffod updates or last lecture ke aakhir me we discussed form of entry that is made in case of the daffod updates and that was the identity or the ID of the transaction then the ID of the variable that is going to be updated and third component is the value that is to be placed for the that is to be placed in the variable. So, these three are the these are the values that are stored in an entry in the log file. So, jihantak we discussed in the previous lecture ap dekhye iss discussion ko hum aage bratein. What is the right sequence? What happens when a right statement is executed? So, for a right operation and tree is made in the log file in RAM in exista raa main ap se kata ki jah se database ke liye there is a buffer in the RAM likewise we have got a buffer for the log file in the RAM. So, sapsa paale when you when a right statement is executed then that statement is entered rather the entry for that statement is made in the buffer of the log file or entry koun si hai they say abhi hum discuss kia that contains the ID of the transaction the ID of the particular variable sapsa attribute kaya de and the new value the value that we want to place in that attribute. So, ye ttein chise sorongi and that entry is made into the buffer of the log file on commit ab ye jitli bhi aapki right operations the aapke in the deferred update situation me jitli bhi aapke right statement thi for all those statements there are entries in the log file ye to ho gya but when you reach the commit statement of the transaction usak kya ho gha ke aap jo aapki log file me buffer me entries pari hui hain on the basis of those entries first of all the buffer of the database is updated ye ne wo particular places RAM me ke jokhe un jagon ko represent kari hain jokhe jitko update kari hain to un key value misal ke dorpe if you want to change the name of let us say some student if you want to change the current semester of a student see here you are trying to update something you are trying to change some value to jis zahira ke I have say ka tha ke database ka bhi buffer hain RAM me to pehle ye ho gha ke log file me to change ho chi kui ye hain the commit ho gha to pehle kya ho gha ke database ke buffer update ho ghe first state first transaction uske ho kya ho gha ke jokhe log file hai that was in the buffer that log file will be moved from RAM to disk ye ne aap abhi tak aapki jo jo ki log file thi wo aapki temporary storage me thi aap you have moved it to the permanent storage disk pe jale gari ye now then write is performed from database buffer and it is moved to disk any way the buffer aap me database ka update ka liye wa tha vahaan si data move kar jaya gha practically disk ke andar to ye to wo sequence hai that is performed for a transaction aur jab write up version ho tne toh kya hote hain aur deferred update ke case me jab aapki commit statement execute hote hai usrat kya hote hai if system crashes pehke ek baat aapko naza ho gaya ke commit me commit ke case me kya ho gha ke commit ho gya aapki jo log file countries thi they move from the RAM to disk fine aap uske baad ye karna ke aapne database ke buffer hai the tables ke buffer attributes ke buffer hai from where you have to move data from the RAM to the disk aap first kane ke aapne you have moved the log file from buffer to disk fine but in the process when you are moving the data of the database tables from buffer to disk during that time the crash happens aap jiswak you will restart the computer next time to the dbms hai aapka crash recovery manager hai aapka subsystem hai dbms ka that handles the crash recovery that detects that there was a failure the previous time ke uske baad ye hai ke the recovery manager aur the recovery subsystem it examines the log file and any transaction for which the recovery manager finds a begin entry in the log file and an and entry for the same transaction in the log file the write operations for that transaction are performed again aur the ke because the recovery manager has detected a crash aap uske baad kane ke jiswak aap ka for the transaction for which it finds a begin and an end unke tamam ke write operations ko it performs again to hosakta hai ke aap ke zain mein svala hai ke hosakta ko peri se hochike bhi ho, peri se likchike bhi ho aur ye iss se us crash se iss ko effect na baad baad ye hai to be on the safe side agar ye dubhara bhi ho jain iss se farkh nahi padayega kyon isk liye ke all those write operations they will be performed in the sequence jini jo pehle write hoi thi wo pehle hogi joske baad thi wo uske and like that to agar first kane pehle wo transaction write ho bhi chuki thi ya wo first kane partly write hoi thi, lakin because now you are performing all the write operations again in the same sequence to agar wo dubhara bhi ho jatiye sare ka sare write operations jaan mein se kuch repeat hoi hai to iss se koye farh nahi padayega, the overall effect will be same to iss se yei ke jini wo iss sara se usko wo recover karta hai ke jitthi transaction jinn ka begin aur end milta hai unke write operations hai aur end se merada jinn ka commit milta hai, end means commit jinn ka begin aur commit mil raha log file ke andar unka unko wo redo kareega, inka unka tamar write operation wo sequence mein dubhara kareega to chahi wo dubhara pehle hoi thi ya nahi hoi thi ya partly hoi thi wo effect usse wo fag nahi padayega, no action for begin and aborted, ab asi transaction for which the recovery manager finds a begin transaction entry and an abort entry, un transaction against the recovery manager want to any action, aap ye kahain ke unko simply ignore kareega ek pad aur ishi tara if there are certain transaction for which the recovery manager finds the begin entry but there is no end entry, na to hape commit hai na unke against abort hai, to unke against koi bhi action nahi hoega, aap iss ka agar aap user point of view se dikhain, iss ka matta bhi hoega ka first kareega, aap aapas hai multiple users wo system ko usthmal kareega, to jinn users ke liye, jinn users ke liye the transaction of those users jinn ke liye aapki jo iss state tak aap ponch ke mein ke log file it has moved from the RAM to disk, you ka commit statement execute hoge thi, to commit ke statement mein they move the log file from buffer to disk, to jinn ke ye statement execute hoge thi those users will be informed ke your transaction has been committed, to agar first kare ke wo practically jasi aapne kata underlying wo aap bhi right kar sakta due to different policies, to agar first kare jon agar crash bhi ho gaya, to all the practically wo abhi changes they have not moved to the disk, but still when the dbms should be started again the recovery manager it would detect and it would perform those operations and it means if the user has been informed that the transaction has been committed, it means it has been committed even in the case of crash or due to the first karega, kuch user aap se hai ke jinn ke transaction they have been started, but they were not reached yet to abort or the commit statement or crash ho gaya. So, the recovery manager it is not doing anything for those transactions, but due to that if you see from the user perspective, so they are not informed about the commit of their transaction, so they will automatically understand ke bhi un ke jab crash ho gaya usat un ke transaction nahi hoi thi, so they would submit their transactions again. So, is thi kase aap ka recovery manager wo kaam kata hai. Abhi tal ka aap kuch is samaj aag hai ke kis tera se jab ka recovery manager aap ye log file ko dekhta hai aur vur tamam transaction jinn mein aap ka begin hai aur commit hai, onko wo redo kare taha hai. Yehansik swaal paida hai hote hai ke log file is being continuously maintained fine. Ab baat yeh ke if there are multiple users, then there are numerous entries in the log file. Now, let us say there is a crash and when you start a system again, the recovery manager detects a crash, aap usne log file se statements ko kuch ko redo kar nahi, kin ko in ke liye there is a begin and there is a commit. But the question is how far back it should go. Dhe ke nahi aap a toh sakta ke, aap aap entries hai. So, kithni aap aap pichhe jaana chahiye, kya shuru se? Maybe it is very far away, toh wo unko tamam ko kar de, agar wo tamam wo kar bhi deta hai, toh prakti karee, uspo gharthi toh koi nahi hai. Yuka mein aap sa kaya kya agar wo tamam operations dubara bhi hujatein because they are being performed in a sequence. Usse koi farkto hi parega, but it is a wastage of effort. Yuka aap, for nothing you are doing maybe tens or hundreds of write operations or maybe in mesi ke yungi surut nah bhi ho, kya wo confirm the. Toh iss cheez ko handle karne ke liye, ke jis fak aap ka system crash ho aur usko recover karne ke liye, kaha se aap ke jo recovery manager hai, it will start redoing the write operations. Toh uske liye jo solution hai that is a checkpoint. Checkpoint kya hai basically checkpoint is also a sort of entry in the log file. Toh jo aap ka checkpoint hai wo it represents certain things. Aap jis fak aap ka jis fak dbms crash hoa toh jis fak aap ko jab aap dbms ko dubara start karenge then checkpoint gives you a sort of starting point ke jaha se aap ka recovery manager hai, it would start recovering the database. Toh ke usse ye jo checkpoint hao it is maintained in such a way that dbms is sure that any statements, any activity that is that has been performed prior to this checkpoint, they are confirmed, they are confirmed transferred on the database. Unko se redo usse pehle ki jo entries hai jo write operations hai, the recovery manager does not need to redo them rather only the entries after the checkpoint, they have to be redone. Aayu dekhye ke checkpoint pe kya operations hote hain aur checkpoint kya wo crash recovery me kya se ussimal hote hain. Repetition no harm. Toh ke aap repeat kar lein. How far in the log file should be redone. Kitna bichche jahin. Checkpoint record is inserted in log file at certain intervals. Aap wo it se ko bhi logic hosakti hai, certain number of operations ke baaj, a certain time interval ke baaj wo usme checkpoints ki entries aajate hain. At a checkpoint, when we say ki this is a checkpoint toh usme hote hain, pehle baat jo aap ke buffers modify hote hain database ke andar, they are moved to the database pehle baat. All log records from buffer to disk. Jo aap ke log file hain uske records hain wo buffer se disk wo move hote hain. Uske baat kya hote hain writing a checkpoint record to log. Now log record mentions all active transactions at that time. Je ne ho ba yeh ke log file aap ko log jo record hoga, checkpoint record hoga kya kya kya kya wo yeh bata aaya kya ish point peh, jiswap ke yeh record enter kya jaara, checkpoint record yeh checkpoint entry ki jaara hi hai, uswap korn korn si transactions on hai, korn korn si iswap chal rahi hai, ne ke jinka begin to ho chuka ho hain. Lekin abit aap commit yeh abort nahi ho hoga. So, wo transactions ka record the checkpoint will store. Now, what is the recovery routine in case of checkpoint? To kya hoga, jiswap crash ke baat, the wire system start hoga aap ko recovery measure it would consult the log file. It checks for the last checkpoint. So, any transaction committed before checkpoint, no action. I am sure that in the log file, checkpoints se pehle aur be checkpoints hoga aur bahos hai ishi tarah begin and end hoga. So, jo checkpoint ki entry hai usse pehle jitani bhi aap ke paas write statements hai ya begin and end hai, un peh koi action nahi hoga. Pehli baat, ish ke bata aap ke usse aap ko aap khaas point dedya ki isse pehle pehle hoga hore nahi karna. Wo transactions, jin ki entries aapki log file me hai, aur jo transaction, jiswap ke wo aapki yehni checkpoint se pehle shuru hi thi. Jab aapne checkpoint entry ki, to zahara usse aap entry aagai ki yeh transaction on hai. Un ke baat ki jo log file entries hai, agar usse ko baat entries hai. Agar un entries me se kisi ka aap ko commit me le jatah hai. Commit entry me le jatah hai. To un transaction ko, ya ishi tarah agar kuch asi bhi transactions hai ki jin ki start bhi aur commit bhi, after that checkpoint wo aap ko mil jatah hi hai. To in dono tarah ki transaction ko aap redo kar denge, dobhara. Jo checkpoints se pehle ki yeh, jin ka start and usse pehle juka hai, start and means ke whether it is commit or abort usse pehle ko usse aap ko ignore kar denge. Jo checkpoint peh on hai aur checkpoints pehle on hi checkpoint pehle on thi aur iske baat agar kuch aapne log file pehle save kiya tha. To un transaction me se jin jin ke against aap ko commit mil jatah hai aap unko redo kar denge ek baat. Ishi tarah the transaction that started after this transaction aur sathe commit bhi haa unka. To unko bhi aap redo kar denge. In dono tarah ki transaction ko unki ek transaction ko aap ignore kar denge. In dono tarah ki transaction ko aap redo kar denge aur on and abort aur on and no end, no action. Wo transaction that were on at the checkpoint, lekin uske baat jo inki entry thi that was abort. To ashi transaction peh koi action ni hoega ek. To shri baat hai wo transaction jo sath on thi lekin uske baat inka koi end nahi hoa, in peh koi action nahi hoega. Aur wo transaction jo uske baat on hi thi lekin uske wo abort ogi thi uspe bhi koi action hi hoega. Ishtara the transactions that you have declared as commit, but their entries were there. To unko aap you would confirm them by redoing aur jo abort ogi thi unko aap ignore kar denge. To hai hopke jo kabrika procedure hai aur specially using the check point aap ko samaj aaga hoega. Now we summarize the daffered update procedure. Usme hi hai ke so basic idea hi hai ke the write operations are not performed on the database until a commit statement is executed. To jab commit statement hogi ussat kya hoega ke aap ke jo the first thing is ke whenever you execute a whenever you find a write statement to uspe kya hota hai ke aap ke jo entry hai log file mein wo it is made. Aur aap ko pata hai ke in case of this daffered update there is a particular type of entry that is made for the write operation. Yeh kaila kaam ho gaya. To uspe kaam hi hoega ke when you find the commit statement to usat kya hoega ke wo jo aap ne entries ki hui hai log file kya under unki basis pe you will update the database buffers you know jo aap ke database ke guess ram mein aap na jo locations create ki hui hai wo haa pe aap un values ko nahi ko liktenge. Aur uske baaz ek keringe ke aap ki jo log file hai uske buffer ko. You know usse buffer se aap data ko move kar denge disk ke opar jab aap ki log file has been moved to the disk after that aap kya keringe ke jo aap ki jo aap ke database buffer hai wo haan se aap update kar denge database ko. Aur first keringe ke iss turan ke jiss turan ke aap log file mein entry kar chuke hai aur lekin abhi aap ki database ko update nahi ki was drawn agar crash ho jata hai to how the dbms or the crash recovery subsystem of the dbms we detect, we as detect kerega ke wo entries ke jinn ke gainst onko usko begin mil jata hai aur commit mil jata hai, onko redo kar denga aur wo entries jinn ke gainst ke begin hai aur abhota obviously ignore kar denga because begin hai aur end nahi hai onko bhi ignore kar denga. Aur uske baal hum nahi dekha ke check point ka asme kya roll hai, check point wo ek place determine karta hai, ek point determine karta hai for the recovery mechanism ke aap log file ko jahaan se forward aap iss ko trace kere. Taak ke aap throughout the log file jini bhi aap ki log file hai to maam ko aap redo nahi karein, baal ke aap certain point se sef aage ke jo statements aap onko redo karein. To ye ek summary thi ke how the deffered updates are executed aur kase wo crash recovery mechanism jo hai wo nko istimal karata hai. Deffered until commit for transaction is found, for recovery purposes log file is maintained, log file helps to redo the actions that may be lost due to system crash, log file also contains check point records. That was the summary of the deffered updates. Now jasimine aap se kahaan tha ke dusri approach jo ke updates ke regarding hai aur uske aap ke log file hai maintain hoti hai that is immediate updates. Aur jasimine aap se zahir hai ke jo hampas deffered update tha uske aap ki updates are delayed until a commit statement is found. Japke iss me asya jasimine aap se zahir hai ke immediate updates are made as soon as a right statement is executed. Esme ke samaini jeega ke immediately jo chis hoti hai that is the update of the buffers. Yuki jo disk pe move karna hai that depends upon the DBMS ke wo kis wakth moove karta hai kuki asya nahi hai ki jasimine aap ke buffer update wo ek bhi to saathi moove karta hai kuki wo DBMS has to plan the things efficiently. To wo apne uske koi strategy iss ke basis pe moove karta hai. Lekin from the user point of view, the data is updated as soon as the right statement is executed. To jas ka basic idea hai iss ko hum ab discuss karta hai. Incremental log with immediate updates je us log file ko istaasi aap naam dete hai. Database buffers are updated immediately and files updated when convenient. Jasimine aap se bhi kaha hai. What is the right sequence in case of immediate updates? Start transaction record in the log file. Aap usme wo hi ek jasimine hai T start. It means T is the idea of the transaction. Which is any idea through which you can identify a particular transaction. And start it shows the start of the transaction. On write operation, write a log record of the form T that is the idea of the transaction. X, the particular data item or the variable or the attribute that is being updated. And then in the daffod update, we only stored the new value. But in the immediate update, we are storing two values. One is the old value. Old value means the value that was before this update. Let us say we want to increase the salary of an employee. Let us say by 5000. If the previous salary was 20,000, now when you add 5000, it becomes 25,000. So in this case, the previous value will be 20,000. And the new value that we want to place for this attribute would be 25,000. So in the immediate update case, both these values are stored in the log file. Whereas in the daffod update, you only stored the new value. So there is a difference between these two. Then you move a log record to file at appropriate time file. As I said earlier, update the database when convenient. So what happened is that the database will be updated only after when the log file has moved from buffer to disk. Because Dbms makes it sure that the log record has moved. Because if a crash happens, it will be determined from there and it will be recovered. On commit, write T commit in log file. So when your commit statement is executed, it will be shown in the log file that you will write the commit statement. What will be the recovery sequence in this case? Transaction with commit to redo by copying new values. Before that, let's study that on the one hand, we are saying that write operations are executed immediately. But the thing is that first, our transaction is aborted. Then what will happen? So if at this time, your values have moved from even from database buffer to the disk, the database even. So even in that case, if it is aborted, then what does it do? It can cancel the effect of this transaction execution. How does it cancel? That your right operations are executed in the inverse order. Mind it, in this case in the inverse order. When you have to undo the effect of any statement, especially in the case of abort. So what will you do? You will undo the right operations in the reverse order and by copying the old value. Because you know that in this case, the log file entry that also stores the old value, new value. So now to undo it, to cancel the effect, what will you do? You will start copying the old value in that item and in the inverse order. So when you reach the beginning of it, the effect of this transaction has been canceled. Now what is the recovery sequence in this case? The difference is that the transaction with commit to redo by copying new values. For the transaction in the log file, if you get the begin and commit, you can redo them. How? The new values are added in the forward sequence. You can do it again from the beginning of the commit. And as I told you before, even if it is being repeated, it will not have a difference because the overall effect will be correct. After that, transaction with abort or no end undo by copying the old value. Okay? What you have to undo is that the abort that you get is not the end, commit and abort. You start copying the old value, but mind it in the inverse order. The statement that is present at the end will be executed first, that is, the old value. Then the old value, then the back value. And ultimately, you will cancel the effect of the transaction. Repetition does not matter. And again, in this case also, we can use the check point record. Dear students, that was all about the database recovery. As you might have seen so far, the concern of the database recovery is that when your abruptly or accidentally, suddenly your DBMS functionally ends, it closes without proper shutting it down. In that case, you need to recover the database because maybe your database has gone into an inconsistent state. Or as a database professional or DBMS as a tool, the responsibility is that you both need to ensure the database consistency. So, the recovery mechanism is basically that any situation has such a possibility. And this possibility is right. There are many possibilities. In the crash case, your database is maybe in the inconsistent state. Why? Because I told you that although a transaction transforms a database from one consistent state to another consistent state. But during the transaction, when the transaction is being executed, temporarily, your database may be in the inconsistent state. So, to end that possibility or to check it, the recovery mechanism works. In both the cases, basically the log file is used. Or log file entries, they are almost the same except the right operation against the entry that varies in case of deferred updates and the immediate updates. In deferred updates, you only store the new value of the variable in the log file. Whereas in the case of immediate update, you store both the previous value, the old value and the new value. So, in this way, you use the log file to recover the database after a crash. So, you see what we have written on our slides, this is what we have written about the database recovery. And improper shutdown may damage database consistency. Yes, we have studied that. Has to be detected and database to be brought in a consistent state. Log file contains records of all updates. Updates may be deferred or immediate and record entries in the log file, they vary. Transactions are redone or undone depending on situation. Checkpoints help in efficient database recovery. And that was it about the recovery mechanism or the database recovery. I hope you have clear. If not, then read the references from the lecture notes. And I hope you will understand from there. Another resource that I will tell you is that you are using DbMS. Look at the online documents of it. And still, the contact with us is always an open option. Dear students, after this, we are going to discuss the other part of transaction management. That is basically the concurrency control. We are going to discuss that. The concurrency control is basically related to when the multiple users are accessing database at the same time. And in today's era, this is very frequent. That means single user databases are very less than multiple users. And multiple users are saying that at the same time, your database is stored, your applications are made. And more than one user is saying that there could be thousands of users in the big system. Which are accessing the database at the same time. Now, obviously, every user is doing various kinds of operations. Every user has their own requirements related to the database. Every user is doing their own work. But the thing is that all of them are accessing the database at the same time at the same time. They are reading or writing. Basically, as we studied, during the recovery procedure, there are basically two operations on the database. They are reading or writing. So the concurrency control is objective is to control the concurrent access. The control mechanism is that there are certain problems that can be encountered, that can happen just due to the concurrent access of the database. The concurrency control is only related to those problems that can make the database inconsistent due to the concurrent access. Look, the database that can be inconsistent, obviously, the transactions that you do, the operations, because of that, obviously, the database can be inconsistent. Now, as far as a single transaction is concerned, let's talk about one transaction, any transaction that a user is doing. As far as that transaction is concerned, the database that is inconsistent and not there, that is the responsibility mainly of the programmer, of the designer. That responsibility is mainly one thing. And secondly, DBMS also supports in this case. For example, as a referential integrity constraint, the constraint of the primary key is not null. With the help of that, although DBMS is also helping the designer, that as a single transaction, as a individual transaction, the database that is inconsistent is not there. For example, as I told you, a bank account holder could not get more money than the balance of that account. Now, if you allow this activity, then your database will be inconsistent. But again, as I said earlier, the consistency within a single transaction, within an individual transaction, that is mainly the job of the designer, the programmer, the developer. But the mechanism of your concurrency control, the subsystem of your DBMS, which is related to concurrency control, the responsibility is to monitor that inconsistency that can happen due to the concurrent access of the database. Mind it, your concurrency control mechanism that won't concentrate on the individual transaction. Individual transaction, it won't touch. As soon as you submit an individual transaction, as soon as it means that the sequence of individual transactions, which is the sequence of the operations, the concurrency control mechanism won't touch that sequence. It won't touch it, it won't do anything. But now we are going to study those problems that can happen only due to the concurrent access. The interesting thing is that first, we have two transactions. Those transactions are individually on their own. They are both such that they don't make the database inconsistent. They are both the right transactions. There is no problem. But if they are both execute together, then if their execution is not properly controlled, then in that case, they can make the database inconsistent. So this is the situation that our concurrency control mechanism uses to manage it. So let's see what are the situations and how the concurrency is controlled. This we studied in the very first lecture of our course. And the sharing is one of the basic objectives of the database approach. The database is shared among the multiple users. Multiple users accessing simultaneously is preferred. That is, at the same time, we are accessing the data from multiple users. Especially from your preferences, there are a lot of users in such organizations that this is preferred in that case. For example, there are a lot of users in the bank. If we talk about the transactions inside the banks, if we ignore them, if we take an example of ATMs, where you get money through your debit card, if you look at that case as well, because your data is coming from that database. The ATMs are working on the database. Because the details of your accounts are in the database. So the money you are getting from the ATMs is being confirmed from there. First of all, if there are 30 ATMs from any bank in the city, can you think of such a situation that if you say that our ATMs are 30, but at one time our database will respond to one ATM. This means that you are taking your card and putting it in the machine. You are waiting for 30 ATMs. And from those, you will say that when my ATM is ready, then it will respond to me. You can put your card in the ATM, you can take it back, you can take it back. People outside are waiting for a Qatar. And the first three ATMs from you, that person put it in the card and you are talking to someone else. Then you can imagine what the situation will be and how much people will prefer it. So it means that in general you would prefer, especially in the system where the number of users is large, that you get simultaneously access. Operations of more than one transaction can be performed simultaneously. And we will say that this is the situation in which we will say that they are interleaved transactions. We have said that the transactions consist of operations. There are more than one or more operations in a normal transaction. So interleaved transactions means that some of your transactions are executed, and then you can transfer this control on any other basis. Any basis can be discussed later. So some of your transactions are executed in some operations, then the control transferred to other transactions, some of its operations are executed and like that. So you can do all the transactions. The situation is that if there are 30,000 transactions, then if all of them are operating at the same time, and all of the users are accessing, then everyone will feel that I am being responded directly because the control is being transferred on everyone. So we call this kind of transaction execution as interleaved transactions. There are two factors, two major factors that allow concurrent access. What are they? The first thing is that the processors nowadays computers are very fast. And especially they are very fast as compared to input-output devices. For example, the RAM of your device, even the speed of the processor is much faster than RAM. After that, if your secondary storage is there, if you take a flash or a disk in it, then it is much faster than that. And after that, the other devices that the human responds to, for example, if you call it a keyboard or mouse, then it is much faster than that. Another thing is that the processor you want to use efficiently, you want to maximize it. Now, if you are responding to one user at a time, and for example, you want to input an access point in a transaction where the user has to input some data. If the processor is responding to just a single user and that user starts talking to a friend, then the processor is waiting and the rest of the transactions are waiting. It is not like that. In order to use it efficiently, the time of the processor is shared because the time of the transactions is being transferred from one transaction to the other transaction. The other thing is that the devices that you have, input-output devices, the controller they can control, they can work on their own without needing some support for particular activities from the processor. For example, like you are talking about the keyboard or disk, their controllers are so smart that if they get a statement from the processor, then they can perform the I.O. on their own and then they can transfer the data to the processor or they can use it from there. These are two major reasons on the basis of which the concurrent access of the database is possible. But there are problems due to interleaving or the concurrent access can cause problems in the database. The major problem, the most important problem regarding the execution of the transaction is that the database may become inconsistent. I told you that this should be your prime priority that the database stays in the consistent state. As I told you in the last point, due to this concurrent access due to the interleaving of the transactions, some of the operations are of that transaction and some of the operations are of that transaction. Due to this interleaving, there could be some problems. First of all, we are going to see what are those problems. Then we will look at the solution. What is meant by concurrency control? There are three problems due to interleaved transactions. What are they? First is lost update. Second is uncommitted update. And third is inconsistent analysis. Meaning, if you the concurrent access of the database if you do not properly control it, then you may face these three problems. And these three problems they transform the database into an inconsistent state. And the mechanism of concurrency control is the same responsibility or the same job that these three problems are not allowed or that they should properly control the concurrent access. So, let's see how these three problems make the database inconsistent and incorrect. First is lost update problem. This is a table in front of you. In this, you can see that during this chapter this type of table will have to be seen again and again. So, first you need to understand how to read this table. How to understand it. Its first column is that shows the point in time. Meaning, if you say that your processor is executing then this particular step is showing. Because we have a single processor then at one time a statement is being executed. Now, let's talk about the concurrent access. I have already said that because the processor executes so fast that it executes all the users at one time but because it is shifting every user feels that the processor is working only so that's why the T1, T2 are the time with respect to the computer, the processor and at this time we will relate it to that at one time a statement is being executed or a transaction is being executed at one time. So, you can see that when we have written T1 or T2 and like that in that case every particular time there is a statement written there is an operation written on it. So, the T A, T B is written on it. So, the first row first is the time then the transaction A and the second is the transaction B and in these columns there are different operations of these transactions. So, if you look at T A then basically the three operations of it are read balance and balance is equal to balance minus 50 and in the same way the T B is the transaction B there are three operations of it and you can see that when you are being shown that a transaction is being executed there is no operation of the second transaction and no operation is being executed or at least no execution is being executed so, these are the transactions and since these two transactions you can say an attribute or a variable you can access it. So, the rightmost column is showing that the attribute which these two transactions are accessing what is the value of that attribute what is the value of that variable what is the difference with the execution of the transactions. Now, look on T1 the transaction A its read operation was executed and it was read any particular row or particular variable its value was read and first of all its value was 1000 so, on T1 transaction A read the balance it got 1000 on time 2 transaction B read the balance because the value of the balance is 1000 then it was said that the balance is equal to balance minus 50 on T3 and on T4 transaction A read the balance when it is read the value of the database is 950 the transaction B added 10 the value of the balance was 1000 when it added 10 it got 1010 and when on T6 transaction B read the balance then it got 1010 now, in this note one thing that the update that was performed by transaction A that update has been lost that update is over because the transaction B that it read that it has over write that it has over write because if you see if we have the initial value 1000 then if we minus 50 if we look at the reference if we minus 50 it becomes 950 and if we add 10 then it should become 960 so, rather than having the 960 we are having 1010 the update of one transaction the effect is lost so, why this problem although individually the two transactions they did not do any such work no such operation that should create the inconsistency but the interleaving the concurrent execution because of that your database is incorrect and that is a form of inconsistency so, this is one problem that you face because you do not properly control the concurrent access Dear students today's lecture we will summarize or finish and in today's lecture we saw that the the second part of the transaction management which is the concurrency control we read that and in this we saw that the concurrency control mechanism its main job is to not create any inconsistency due to the concurrent access this is its main job the concurrent access of the users should be properly controlled and concurrent access is something that is definitely preferred by everyone by the DBMS by the by the organization the concurrent access is required but the concurrency control mechanism it ensures that the database is not inefficient due to the concurrent access the other two problems of the concurrent access and the solution we will discuss in the next lecture Allah Hafiz