 Assalamu alaikum. Dear students, today we are in the lecture number 44 of the database management system course. The course code is CS403 and as you know from last few lectures we have been discussing the transaction management. The transaction management concerns two main thing one is database recovery after the crash and secondly the concurrency control. In the previous lecture we started discussion on the concurrency control. Concurrency control means that the problems that may arise due to the concurrent access of the database that should be taken care. And the concurrency control sub mechanism of the DBMS it is responsible for taking care of the problems due to the concurrent access. And as I told you that the concurrency control mechanism focuses only on those problems due to the concurrent access. And as far as the individual transaction is concerned the concurrency control is not responsible for that. The consistency within an individual transaction concurrency as a result of the execution of a single transaction that is the responsibility of the designer the developer the programmer. But the concurrent access is managed by the concurrency control mechanism of the DBMS. Then we studied that there may especially three problems that may arise due to the concurrent access. And those problems if encountered they may convert the database into an inconsistent state. Or us may say these problems may say we discussed the first one and that was the lost update problem. In the lost update problem the update made by one of the transactions that are secreting concurrently is lost. Any more update although you have committed that the transaction was correct. Despite that the effect of that was lost just like it did not happen. And this is a situation that incorrect your database or inconsistent. That was one problem. Today we will start our lecture by studying the rest of the two problems and then our discussion will go on. So let us start today's lecture. Next problem is uncommitted update. Again you see here that here we have the same style of writing the transactions and their effects that left most column we are showing the time. Or after that the operations of the transaction A and then the operations of transaction B. And remember that when we are talking about the concurrent access there could be tens of hundreds of thousands of transactions at the same time. Here if we are just looking at two transactions it does not mean that at the same time there are two transactions executed. This is just for simplification. You see two transactions. Otherwise as I said in real life situations in big systems in big organizations thousands of users they are accessing data at the same time. So you see the operations of transaction A in the second column. You read the balance and first of all the balance was 1,000 the initial value. Initial value means that before the start of these transactions. Then transaction A added 100 in the balance. Obviously it is saying that it is 1100 but look at the balance column you can see the value. That is still 1,000 because this statement balance is equal to balance plus 100. That is being executed in RAM. The processor is executing but the data is still in the RAM. It has not been transferred to the database yet. That is why the database contains the 1,000 as value for the balance. At time T3 TA writes the value of balance and so the value of balance is updated in the database and it becomes 1100 fine. Then at time T4, TB reads the balance because it has been updated to 1100. So TB receives 1100 as value of the balance. Now the value of balance is multiplied by 1.5 times T5 in the transaction B. And that value signifies the balance. In the balance you have 1.5 times increase. So as per the transaction B, the when at time T6 write balance statement is executed for the transaction B. So the balance key value that becomes 2650 fine but as you are seeing at time T7, transaction A is rolled back. But the transaction B, it has performed certain operation on the basis of the value generated by transaction A. And this inconsistency has been generated. Due to the concurrent access of the transactions. Because the database is being accessed concurrently. So these are two problems because of the concurrent access. Third problem that we face, that we may face in case of concurrent access if we do not control it properly and that is inconsistent analysis. The basic logic of inconsistent analysis is that you have two transactions. One transaction is involved in such operations that it is operating on a table every day or may be it is operating on a large part of the table. So the transaction A is going on due to the provision of the concurrent access. A core transaction, it may change as see that the part on which your transaction A is operating and the part on which transaction A is operating and the transaction B changes the incorrect, wrong effect on the calculation on the analysis of transaction A. So this means that because of the concurrent access, because of the concurrent execution, your transaction A was doing your analysis and that was being performed on all the rows or on large number of rows of the table. So that analysis was ultimately that would be incorrect, wrong. One transaction operates many records meanwhile another modifies some of them effecting result of the first. Suppose transaction A computes interest on all accounts, account balances and other moves balance from one account to another. For example, the part on which the transaction A is operating and the part on which you have to do it, it goes wrong. You see it is on the screen and in the right side you can see that the transaction A is written as Read Balance A. It means the balance of one record one client, one customer, one account, so time t1 page of transaction A, it reads the balance A through read operation and suppose that the balance A that was 5000, fine, t2 time pay your transaction A that it calculates interest on this balance, how it multiplies it from 0.05 and the value of interest is summed in a total variable and we initially did the total of 0 what happens at t5 when control shifts to transaction B so transaction B, the balance A that was your account A, it read the balance from there, first time it got a balance of 5000, it did minus 1000 out of the balance A and the 1000 minus that it stored on balance A, it stored 4000 and at the same time this 1000 that is added into another account, let's say balance E we said there was an account of balance E, let's say its value was 5000, it added 1000 and when it added 1000, it becomes 6000 and transaction B wrote the balance E as 6000 now when transaction A is returned again, transaction A reads all the records when it reaches the balance E, there it finds 6000, it calculates interest on 6000 and it adds it to its total interest now here if you feel that 1000 has been considered twice once within the balance A, the account A got counted and second time it came into balance E so this error, this inconsistent or incorrect analysis, why it happened? it happened because of the concurrent access one transaction was operating on some records and one of them made such manipulation of another transaction that the net effect of the transaction of the transaction A that has been spoiled, that has been made inconsistent, you are wrong Dear students, these three problems are because of the surface that database is being accessed concurrently so to understand the mechanism of concurrency control you have to realize that these three problems are because of the concurrent access and the mechanism of concurrency control is the objective that these three situations are not created it handles them, how it handles them? this is what we are going to discuss in the next lecture the next concept that we are going to discuss is of serial execution and execution, obviously of the transaction where transactions are executed in a series as serial transactions what is the meaning of this? the meaning is that we have studied that the transaction can have one or more operations it can have one operation in any transaction but such transactions are very less in most of the transactions, multiple operations are involved now, the meaning of serial execution is that the number of transactions that you have that is 2, 3, 4, 10, 15, 20, 100, whatever you want all those transactions are executed the first transaction is that all the operations of one transaction followed by all the operations of the next transaction and like that first, we have three transactions T1, T2 and T3 or TA, TB and TC is your choice now, from serial execution first, all the operations are T2 then, all the transactions are T3 then, all the transactions are T1 and this shows that when we say that what is the meaning of serial execution that one transaction has all the operations and the other transaction is like that so, this should come to your mind immediately that with a given set of transactions we have 2, 3, 4 there are different possible serial executions for example, if we have only two transactions then, two serial executions are possible first, T1, then T2 or first, T2 then, T1 so, these are two possible serial executions in case of two transactions if there are three transactions then, it is possible that T1, T2, T3 or T1, T3, T2 or T3, T1, T3 and like that any combination is possible but, the basic property of serial execution the basic feature of it is that the complete transaction anyone is executed first and at that time, no other transaction is being executed so, any operation all the operations of one transaction then, all the operations are like that this is called serial execution the very big feature of serial execution is that this is guaranteed that in the serial execution you will not have inconsistency generated I am talking about concurrent access because you have 10 users transaction you have fixed their sequence fit and after that, you have executed another transaction in series in serial fashion so, this is guaranteed that as a result of this execution your database will never be inconsistent but mind it within a transaction consistency that we are not talking about here okay we are talking about that inconsistency which can be caused by concurrent access these are the three problems which we have just discussed so, if you have serial execution then in that case, these three problems cannot be done because these three problems were that there was interleaving there is no interleaving if there is serial execution then in that case, there is no question of these three problems schedule or history when you have the operations of multiple transactions if you want to execute them in the order in which you want to execute then if you write that first this operation then this and then this and then this that is called a schedule or history for two transactions two serial executions and schedules are possible as I have just talked to you we have two transactions the same transaction we have just seen the T or TB we have seen in the first two problems when your first column is stable that shows that first you have TA then TB now the second column it shows you the effect on the value of the balance attribute suppose the initial value of balance key that is 50 then written read balance A means read balance statement of transaction A the value of balance is 50 read balance is equal to balance minus 10 and what I have written A here this statement is the part of transaction A you have minus 10 out of 50 then you said right balance A in the system of A A transaction part right statement executed and the value of balance was 40 then you said read balance B in a transaction B read balance balance is equal to balance star 2 to say multiply you said write balance and B wrote balance so this means the final value of B that was 80 this was one serial execution fine now if you look at the other serial execution that is TB first TA later it got 50 balance is equal to balance star 2 100 in the RAM write balance B means B transaction wrote the value of balance and 100 was stored after that the A transaction read balance it got 100 transaction A minus 10 it became 90 in RAM but database is still 100 and then when transaction A write balance B it became 90 so this means if we have two transactions and we adopt two different serial schedules these different serial schedule may end differently that is at the end of different serial schedule after execution the database state it may be different it may be same but it may be different as well you just understand that if we have those transactions if we have any serial order then we have the end state after the execution of those serial schedule it will be same it may be same it may be different but all of them they will be consistent state final value database state but it is guaranteed that it will be a consistent state it will not be wrong you should think that with certain transactions we can have different serial schedule these different serial schedule may end with a different database state but this is confirmed that serial schedule always leaves the database in a consistent state this is what you should understand yes different serial schedule may result in a different final state database state but serial execution is guaranteed to leave database in a consistent state because none of the three concurrent access problems lost update uncommitted update or inconsistent analysis we have learned serial schedule is guaranteed correct should we adopt it no that will be inefficient under utilization of the resources angry users why? because users have to wait if you are doing it in series there is a transaction during that transaction user's input is required the user is talking the computer has left it even if you terminate this kind of transaction but that you would be able to identify after some time that yes maybe the user has slept then you can terminate it but the thing is meanwhile your resources are lying idle the processor is not doing anything secondly it is doing nothing this is a very bad solution with powerful machines especially in the large organization when there are so many users you are seriously executing them that is very very inefficient so as I said earlier concurrent access is required by everybody by the organization by the users or even the system would like if my resources are maximum utilized then concurrent access is recommended required we would prefer concurrent execution multiple user accessing database at the same time an interleaved schedule consists of operations from different transactions so first we talked about interleaved transactions now we have done the same notion a little bit below now we are talking about interleaved operations such a schedule in which different transactions operations are mixed and see when we talk let's say that we have two transactions then we have two serial schedules but if you don't have two transactions and as we saw that on average 3 or 4 operations then you can determine this that interleaved schedule is possible because now we have got larger number of operations in which there are many so if we mix in different orders then any order in which one transaction may be 1, 2, 1, then 3, then 1, then 2, then 4 like that so as soon as we mix there is no need for sequence there is no need for order if everyone should have 2, 2 everyone should have 1, 1 it can be so that will be called an interleaved schedule first we have a transaction T A read X, write X or commit second is read Y, read X, write Y or commit and see I have already emphasized that the order of these operations within the transaction that has to be fixed by you by the designer, by the programmer the order which is interleaving interleaving with their different mixing but the order within the transaction is your concurrency control mechanism it will not touch it it cannot be that when you interleave write Y and read Y this you cannot do no your concurrency interleaving their internal order you will not touch it otherwise you can do their different mixing see I am showing you a schedule this is a schedule and if you note we have got R A X transaction A read X transaction A write X commit of transaction A this is a serial schedule because all the operations of transaction A first followed by all the operations of transaction B so this is a serial schedule now we have written the shape of the operations but this is yet another serial schedule all the operations of transaction B first followed by all the operations of the transaction A this is also a serial schedule this is an interleaved schedule see read B write Y read X read X read X read X write Y write Y then B commit transaction A write X commit A this is an interleaved schedule in which you are mixing the operations of different transaction see this interleaved schedule it is based only two transactions if we have 3, 4, 5, 6, 10, 15, 100 1000 transactions you can see how much it will become a mixture type in which a lot of transactions are executed after each other now you know what is meant by a serial schedule and what is meant by an interleaved schedule and now both of you have to know what is K the special thing of the serial schedule is that it guarantees the efficiency but that is a badly under utilization of the resources as a result you are getting angry users so this will be a special thing of the serial schedule and when we come to the interleaved schedule the benefit of the interleaved schedule is that it provides you the current access the efficient use of the resources and the happy users but at the same time if not controlled properly this can give you that three problems that may turn the database into an inconsistent state or mind it if you compare the two situations in the worst situation you can also tolerate the anger of the users but you cannot afford to tolerate the inconsistency okay that is not acceptable in any case so that means you are going to adopt the interleaved schedule but at the same time you are taking care of the problems that may generate if the concurrent access is not controlled properly so let's see how we can control this interleaved schedule provides a concurrent execution but three problems of the concurrent access may be encountered yes that we have discussed we have studied this that we may encounter those three problems in case of concurrent access but we need to study a bit in more detail what is the real problem there see in case of the concurrent access there may be three different situations the first situation is multiple transactions suppose if they are writing if they are performing the right operations on two different variables any T A T B T A is writing X T B is writing Y no problem at all if there is a problem there will be no problem what is the situation two transactions writing different variables attributes and different data items the different objects the second is two transactions and their operations they are reading the same value the same variable the same object no problem there is no problem different transactions are accessing the same object attribute variable and one of them is performing the right operation again two transactions are executing there are two operations they are accessing the same data item and one of them is performing the right operation if both are doing then there is a problem if one is doing then there is a problem this kind of operations we call them the conflicting operations what are the conflicting operations there are two different operations and they are accessing the same data item and one of them is writing they are doing the right operation and one more thing I repeat the concept of the conflicting operation it is not it is not related only to the concurrent access the concept of the conflicting operation it lies even within the single transaction in one transaction if you are reading the object or writing even within the same transaction the operations they will be called the conflicting operations if you need to give a special attention to those operations again the order of the operations within the single transaction within an individual transaction that is not the concern of the concurrency control mechanism now when we are talking conflicting operations now we are discussing the conflicting operations in the situation of the concurrent access the situation that is of concern for the concurrency control mechanism so this is the concept of the conflicting operations and on the basis of that we define the concept of conflicting transactions any AC transactions which have such operations which are conflicting mind it, clear this concept what do we mean by the conflicting operations two operations and in case of the concurrent access operations from different transactions accessing the same data item same object performing the right operation these AC operations conflicting operations or AC transactions conflicting operations conflicting transactions see now slowly we have chocked out the actual problem which the concurrency control has to handle that is basically of the conflicting operations because the other operations do not cause any problem in the concurrency control if those different transactions or those different variables are writing no problem different transactions operations reading the same data item no problem but the conflicting operations they reflect the third situation or that problem that can create inconsistency for you now the concept in the concurrency control serializability what is that serializability serializability its concern is that it produces a serializable schedule or serializable schedule what is that an interleaved schedule is serializable if the final state of the schedule is equivalent to a serial schedule it has different serial schedules now you can make your interleaved schedule so the final state of the schedule is equivalent to any serial schedule we will say this is a serializable schedule because your serial schedule always leaves the database in a consistent state so when your output of a serial schedule is equivalent to any serial schedule that means that serializable schedule will transform the database into a consistent state so what is desirable in the concurrency control your concurrency control mechanism it should generate a serializable schedule or you can say that it should guarantee it should check that the interleaved schedule that is being executed is serializable serializability can be defined in another way that is a schedule where conflicting operations are in a serial order see now we have discussed that when we have a problem in concurrency control that is only your conflicting operations that means that your operations are not conflicting and which operations are not conflicting those operations which are writing different data items another thing is that the same data item in both the situations the mixing of the operations which are interleaving do it in the desired order it does not have any concurrency control in the concurrency access there will be no difference in the consistency of your database so the concurrency control is not concerned of transactions but your concurrency control mechanism it has to be concerned about the conflicting operations and the second definition is that your conflicting operations you execute them in a serial order see your serial execution your serial schedule all transactions are done specifically three transactions are 2, 1, 3 all operations are T2 all operations are T1 all operations are T3 this serializability theory says you do not do all operations you do the mixing you get an interleaved schedule you get the concurrency access but to make sure the consistency you do the conflicting operations you do whenever when they do their own individual transactions when they do their own conflicting operations the exact place but the order is equal to a serial schedule that is three transactions you do the conflicting operations in 1, 2, 3 orders in 2, 3, 1 or 3, 2, 1 but remember we have three transactions and the conflicting operations are multiple for T1 or T3 they conflict on a variable Y for T1, T2, T3 they conflict on X for T2 or T3 they conflict on Z now you have conflicting operations on different transactions conflicting operations should be in serial order conflicting operations should be in serial order that means all the conflicting operations on your variables there should be one order that means if you fix 2, 1, 3 this will be the order so if there is a conflict on X then in the case of X the order should be 2, 1, 3 if there is a conflict on Y in the case of X the order should be 2, 1, 3 it should not be in the case of X the order should be 2, 1, 3 and you say I have adopted 1, 2, 3 in Y no, not like that and also there may be a question that the conflict should not be in T3 but whatever is the order that will remain the same for all conflicting operations whatever will be the order you will not change now both the definitions of serializability or serial schedule are a little confusing in both the mind for example the first definition which we have discussed after seeing that definition comes to mind how would we ensure the serializability when we say an interleaved schedule is the final state that should be equivalent to any of the serial schedules then would we ensure serializability that first we will make all possible serial executions to note their final state after that whatever we make an interleaved schedule we will see its final state and then we will see whether the final state of our interleaved schedule then we will say that this is a serializable schedule no, it will not be and the second thing that the second definition in that you say how we will fix this serial schedule that we will first tag it and then we will stop the operations that you cannot come till then it will not be like that you will see that the serializability theory its idea the definition is this but we ensure the serializability by adopting some other techniques in which both these things come to your mind the definition that can come to your mind they are not both techniques are something else but they ensure the serializability or mind it from now your conclusive control the objective of the mechanism is to check the serializability of your interleaved schedule why? the serializability it ensures the consistency of the database let us go ahead now these are the two major approaches to implement or to ensure the serializability a locking mechanism let us start with the locking mechanism locking an object is locked before it performs any operation i.e. it means that it has been reserved the concept of lock is that you apply the lock and you say that it is ok now this thing is restricted well, sometimes you do not do it on lock this is it is taken care of when you do an operation first you apply the lock when you get the lock when you get the lock you apply it you have been granted the lock after that you do the operation this is the basic idea of the locking mechanism two types of operations two types of locks as you know we are discussing that we perform normally or the right operation so the type of operation you have to do you will apply the lock so what are the types of locks shared and exclusive so when you want to do read operation on any variable or any object so first you will apply the shared lock you can call it as shared lock because shared lock is applied in case of read share lock it means the same thing when you want to perform the right operation you will apply for the exclusive lock exclusive lock right lock lock compatibility this means how lock works together this means that if you have a transaction it has applied lock on any item for example on an item X and on that there was no lock when there was no lock and transaction applied the transaction will be allocated will be granted that lock by home obviously and the concurrency control mechanism they are managing the locks lock is found now that item is reserved for the right operation of let's say transaction A let's say meanwhile when this transaction is not far from operation or transaction is not yet over because when the transaction is over or when the operation is done then that lock will be released so it means that there was a lock transaction A that another transaction applies for lock this is the situation that we call the compatibility of locks on one item a transaction has a lock and at the same time another transaction applies a lock on it so if that lock was applied if it is compatible then another will get a lock now for this situation we need to consider what type of lock was already applied and what type of lock was requested from here the compatibility of each lock is established you can see the matrix that is showing you the compatibility of locks let's see now look at the left side which is written as row wise we said that transaction A holds a lock on an item first if it had a shared lock and transaction B also applies a shared lock means transaction A is going to read the item and another transaction B is going to read it so the particular cell which is also written there yes means that shared lock is compatible with shared lock that is if you have a transaction that it has applied a read lock that it wants to read it that it has applied a lock that it has got a lock it has to read it although there was a lock before but since these two locks are compatible it will get a lock and the transaction B will also read it and likewise if there are 2, 3, 4, 5, 6, 10, 11 transactions they want to read the same data item they want to read the street they will apply the shared lock and you will remember the transactions are reading the same data item then there is no problem if multiple transactions want to read the same data item all of them will be allowed why allowed because they will be able to perform that operation only when they are granted a lock if they do not get a lock then they cannot operate if transaction A has got a shared lock then exclusive applies no and the rest you can see that there is no written why? because in that from either side exclusive lock is involved and you apply for the exclusive lock for the right operation and we have read this before that if someone is doing a right operation then they are conflicting operations so from here if you have got the conflicting operations in that case you will allow one of them to write and whether he wants to read or write you will not allow the second one so from here you can see that lock is a compatibility and how the three of our problems stop them from occurring that until someone has exclusive they will not allow to read and until someone has read or write now the question is do you apply a transaction and the lock was already there and the locks are not compatible then what will happen? the transaction which has applied and the lock is not compatible that transaction will have to wait that means that transaction enters into a wait state until until your first transaction it does not release the lock when the lock is released when it is done when it is done when it is completed when the lock is released when the lock is released the transaction waiting for the lock to be released then the transaction will be granted it has been granted now that it has been reserved for that transaction now that transaction when that item has been reserved not that transaction will perform the operation on that and then the same thing is that yet another transaction applies lock on that that item again the compatibility of the locks will be checked if it is compatible then that transaction will also be granted if it is not until this transaction it does not release the lock as you saw your conflicting operations how your locking mechanism has a serializability that has been created when serializability has been created then it is guaranteed that it will not have any inconsistency dear students we will wind up today's lecture today our discussion is on conclusive control and we have seen that the other two problems of conclusive control that is uncommediate update and inconsistent analysis we studied them and after that we discussed the serializability theory and this is important and your mind should be clear what is meant by a serializable schedule after that the two major mechanisms of conclusive control are locking and time stamping we have started the discussion on locking we will continue this discussion in our next lecture I would like to request you Allah Hafiz