DE
UNIT - 5Transaction Processing and Concurrency ControlQ1) What is transaction in DBMS with examples?A1)A transaction can be defined as a group of tasks. A single task is the minimum processing unit which cannot be divided further.Let’s take an example of a simple transaction. Suppose a bank employee transfers Rs 500 from A's account to B's account. This very simple and small transaction involves several low-level tasks.
Q2) What are ACID properties in DBMS? A2) ACID PropertiesA transaction is a very small unit of a program and it may contain several lowlevel tasks. A transaction in a database system must maintain Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties − in order to ensure accuracy, completeness, and data integrity.Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none. There must be no state in a database where a transaction is left partially completed. States should be defined either before the execution of the transaction or after the execution/abortion/failure of the transaction. Consistency − The database must remain in a consistent state after any transaction. No transaction should have any adverse effect on the data residing in the database. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well. Durability − The database should be durable enough to hold all its latest updates even if the system fails or restarts. If a transaction updates a chunk of data in a database and commits, then the database will hold the modified data. If a transaction commits but the system fails before the data could be written on to the disk, then that data will be updated once the system springs back into action. Isolation − In a database system where more than one transaction are being executed simultaneously and in parallel, the property of isolation states that all the transactions will be carried out and executed as if it is the only transaction in the system. No transaction will affect the existence of any other transaction. Q3) What is Serializabiltiy? A3) SerializabilityWhen multiple transactions are being executed by the operating system in a multiprogramming environment, there are possibilities that instructions of one transactions are interleaved with some other transaction.Schedule − A chronological execution sequence of a transaction is called a schedule. A schedule can have many transactions in it, each comprising of a number of instructions/tasks. Serial Schedule − It is a schedule in which transactions are aligned in such a way that one transaction is executed first. When the first transaction completes its cycle, then the next transaction is executed. Transactions are ordered one after the other. This type of schedule is called a serial schedule, as transactions are executed in a serial manner. In a multi-transaction environment, serial schedules are considered as a benchmark. The execution sequence of an instruction in a transaction cannot be changed, but two transactions can have their instructions executed in a random fashion. This execution does no harm if two transactions are mutually independent and working on different segments of data; but in case these two transactions are working on the same data, then the results may vary. This ever-varying result may bring the database to an inconsistent state.To resolve this problem, we allow parallel execution of a transaction schedule, if its transactions are either serializable or have some equivalence relation among them.Q4) What is Equivalence Schedules and what are its types? A4) Equivalence SchedulesAn equivalence schedule can be of the following types −Result EquivalenceIf two schedules produce the same result after execution, they are said to be result equivalent. They may yield the same result for some value and different results for another set of values. That's why this equivalence is not generally considered significant.View EquivalenceTwo schedules would be view equivalence if the transactions in both the schedules perform similar actions in a similar manner.For example −If T reads the initial data in S1, then it also reads the initial data in S2. If T reads the value written by J in S1, then it also reads the value written by J in S2. If T performs the final write on the data value in S1, then it also performs the final write on the data value in S2. Conflict EquivalenceTwo schedules would be conflicting if they have the following properties −Both belong to separate transactions. Both accesses the same data item. At least one of them is "write" operation. Two schedules having multiple transactions with conflicting operations are said to be conflict equivalent if and only if −Both the schedules contain the same set of Transactions. The order of conflicting pairs of operation is maintained in both the schedules. Note − View equivalent schedules are view serializable and conflict equivalent schedules are conflict serializable. All conflict serializable schedules are view serializable too.Q5) What is a state of transactions in DBMS? A5) States of TransactionsA transaction in a database can be in one of the following states −
Fig 1 - States of TransactionsActive − in this state, the transaction is being executed. This is the initial state of every transaction. Partially Committed − When a transaction executes its final operation, it is said to be in a partially committed state. Failed − A transaction is said to be in a failed state if any of the checks made by the database recovery system fails. A failed transaction can no longer proceed further. Aborted − If any of the checks fails and the transaction has reached a failed state, then the recovery manager rolls back all its write operations on the database to bring the database back to its original state where it was prior to the execution of the transaction. Transactions in this state are called aborted. The database recovery module can select one of the two operations after a transaction aborts − Committed − If a transaction executes all its operations successfully, it is said to be committed. All its effects are now permanently established on the database system. Q6) What is an ACID property in DBMS explain in details with examples?A6) DBMS is the management of data that should remain integrated when any changes are done in it. It is because if the integrity of the data is affected, whole data will get disturbed and corrupted. Therefore, to maintain the integrity of the data, there are four properties described in the database management system, which are known as the ACID properties. The ACID properties are meant for the transaction that goes through a different group of tasks, and there we come to see the role of the ACID properties.In this section, we will learn and understand about the ACID properties. We will learn what these properties stand for and what does each property is used for. We will also understand the ACID properties with the help of some examples.ACID PropertiesThe expansion of the term ACID defines for:
Fig 2 - ACID Properties1) Atomicity: The term atomicity defines that the data remains atomic. It means if any operation is performed on the data, either it should be performed or executed completely or should not be executed at all. It further means that the operation should not break in between or execute partially. In the case of executing operations on the transaction, the operation should be completely executed and not partially.Example: If Remo has account A having $30 in his account from which he wishes to send $10 to Sheero's account, which is B. In account B, a sum of $ 100 is already present. When $10 will be transferred to account B, the sum will become $110. Now, there will be two operations that will take place. One is the amount of $10 that Remo wants to transfer will be debited from his account A, and the same amount will get credited to account B, i.e., into Sheero's account. Now, what happens - the first operation of debit executes successfully, but the credit operation, however, fails. Thus, in Remo's account A, the value becomes $20, and to that of Sheero's account, it remains $100 as it was previously present.
In the above diagram, it can be seen that after crediting $10, the amount is still $100 in account B. So, it is not an atomic transaction.The below image shows that both debit and credit operations are done successfully. Thus the transaction is atomic.
Thus, when the amount loses atomicity, then in the bank systems, this becomes a huge issue, and so the atomicity is the main focus in the bank systems.2) Consistency: The word consistency means that the value should remain preserved always. In DBMS, the integrity of the data should be maintained, which means if a change in the database is made, it should remain preserved always. In the case of transactions, the integrity of the data is very essential so that the database remains consistent before and after the transaction. The data should always be correct.Example:
In the above figure, there are three accounts, A, B, and C, where A is making a transaction T one by one to both B & C. There are two operations that take place, i.e., Debit and Credit. Account A firstly debits $50 to account B, and the amount in account A is read $300 by B before the transaction. After the successful transaction T, the available amount in B becomes $150. Now, A debits $20 to account C, and that time, the value read by C is $250 (that is correct as a debit of $50 has been successfully done to B). The debit and credit operation from account A to C has been done successfully. We can see that the transaction is done successfully, and the value is also read correctly. Thus, the data is consistent. In case the value read by B and C is $300, which means that data is inconsistent because when the debit operation executes, it will not be consistent.4) Isolation: The term 'isolation' means separation. In DBMS, Isolation is the property of a database where no data should affect the other one and may occur concurrently. In short, the operation on one database should begin when the operation on the first database gets complete. It means if two operations are being performed on two different databases, they may not affect the value of one another. In the case of transactions, when two or more transactions occur simultaneously, the consistency should remain maintained. Any changes that occur in any particular transaction will not be seen by other transactions until the change is not committed in the memory.Example: If two operations are concurrently running on two different accounts, then the value of both accounts should not get affected. The value should remain persistent. As you can see in the below diagram, account A is making T1 and T2 transactions to account B and C, but both are executing independently without affecting each other. It is known as Isolation.
4) Durability: Durability ensures the permanency of something. In DBMS, the term durability ensures that the data after the successful execution of the operation becomes permanent in the database. The durability of the data should be so perfect that even if the system fails or leads to a crash, the database still survives. However, if gets lost, it becomes the responsibility of the recovery manager for ensuring the durability of the database. For committing the values, the COMMIT command must be used every time we make changes.Therefore, the ACID property of DBMS plays a vital role in maintaining the consistency and availability of data in the database.Q7) What is database transaction in DBMS explain in detail?A7) In a database, the transaction can be in one of the following states -
Active stateThe active state is the first state of every transaction. In this state, the transaction is being executed. For example: Insertion or deletion or updating a record is done here. But all the records are still not saved to the database. Partially committedIn the partially committed state, a transaction executes its final operation, but the data is still not saved to the database. In the total mark calculation example, a final display of the total marks step is executed in this state. CommittedA transaction is said to be in a committed state if it executes all its operations successfully. In this state, all the effects are now permanently saved on the database system. Failed stateIf any of the checks made by the database recovery system fails, then the transaction is said to be in the failed state. In the example of total mark calculation, if the database is not able to fire a query to fetch the marks, then the transaction will fail to execute. AbortedIf any of the checks fail and the transaction has reached a failed state then the database recovery system will make sure that the database is in its previous consistent state. If not then it will abort or roll back the transaction to bring the database into a consistent state. If the transaction fails in the middle of the transaction then before executing the transaction, all the executed transactions are rolled back to its consistent state. After aborting the transaction, the database recovery module will select one of the two operations: Q8) Explain Implementation of atomicity, Isolation and DurabilityA8) The transaction has the four properties. These are used to maintain consistency in a database, before and after the transaction.Property of TransactionAtomicity Consistency Isolation Durability
AtomicityIt states that all operations of the transaction take place at once if not, the transaction is aborted. There is no midway, i.e., the transaction cannot occur partially. Each transaction is treated as one unit and either run to completion or is not executed at all. Atomicity involves the following two operations:Abort: If a transaction aborts then all the changes made are not visible.Commit: If a transaction commits then all the changes made are visible.Example: Let's assume that following transaction T consisting of T1 and T2. A consists of Rs 600 and B consists of Rs 300. Transfer Rs 100 from account A to account B.
After completion of the transaction, A consists of Rs 500 and B consists of Rs 400. If the transaction T fails after the completion of transaction T1 but before completion of transaction T2, then the amount will be deducted from a but not added to B. This shows the inconsistent database state. In order to ensure correctness of database state, the transaction must be executed in entirety.ConsistencyThe integrity constraints are maintained so that the database is consistent before and after the transaction. The execution of a transaction will leave a database in either its prior stable state or a new stable state. The consistent property of database states that every transaction sees a consistent database instance. The transaction is used to transform the database from one consistent state to another consistent state. For example: The total amount must be maintained before or after the transaction.Total before T occurs = 600+300=900 Total after T occurs= 500+400=900 Therefore, the database is consistent. In the case when T1 is completed but T2 fails, then inconsistency will occur. IsolationIt shows that the data which is used at the time of execution of a transaction cannot be used by the second transaction until the first one is completed. In isolation, if the transaction T1 is being executed and using the data item X, then that data item can't be accessed by any other transaction T2 until the transaction T1 ends. The concurrency control subsystem of the DBMS enforced the isolation property. DurabilityThe durability property is used to indicate the performance of the database's consistent state. It states that the transaction made the permanent changes. They cannot be lost by the erroneous operation of a faulty transaction or by the system failure. When a transaction is completed, then the database reaches a state known as the consistent state. That consistent state cannot be lost, even in the event of a system's failure. The recovery subsystem of the DBMS has the responsibility of Durability property. Q9) Explain testing of serializability in detail in DBMS A9) Testing of SerializabilitySerialization Graph is used to test the Serializability of a schedule.Assume a schedule S. For S, we construct a graph known as precedence graph. This graph has a pair G = (V, E), where V consists a set of vertices, and E consists a set of edges. The set of vertices is used to contain all the transactions participating in the schedule. The set of edges is used to contain all edges Ti ->Tj for which one of the three conditions holds:Create a node Ti → Tj if Ti executes write (Q) before Tj executes read (Q). Create a node Ti → Tj if Ti executes read (Q) before Tj executes write (Q). Create a node Ti → Tj if Ti executes write (Q) before Tj executes write (Q).
Fig 5 – Precedence GraphIf a precedence graph contains a single edge Ti → Tj, then all the instructions of Ti are executed before the first instruction of Tj is executed. If a precedence graph for schedule S contains a cycle, then S is non-serializable. If the precedence graph has no cycle, then S is known as serializable. For example:
Explanation:Read(A): In T1, no subsequent writes to A, so no new edges
Read(B): In T2, no subsequent writes to B, so no new edges
Read(C): In T3, no subsequent writes to C, so no new edges
Write(B): B is subsequently read by T3, so add edge T2 → T3
Write(C): C is subsequently read by T1, so add edge T3 → T1
Write(A): A is subsequently read by T2, so add edge T1 → T2
Write(A): In T2, no subsequent reads to A, so no new edges
Write(C): In T1, no subsequent reads to C, so no new edges
Write(B): In T3, no subsequent reads to B, so no new edges Precedence graph for schedule S1:
Fig 6 – Precedence graphThe precedence graph for schedule S1 contains a cycle that's why Schedule S1 is non-serializable.
Explanation:Read(A): In T4,no subsequent writes to A, so no new edges
Read(C): In T4, no subsequent writes to C, so no new edges
Write(A): A is subsequently read by T5, so add edge T4 → T5
Read(B): In T5,no subsequent writes to B, so no new edges
Write(C): C is subsequently read by T6, so add edge T4 → T6
Write(B): A is subsequently read by T6, so add edge T5 → T6
Write(C): In T6, no subsequent reads to C, so no new edges
Write(A): In T5, no subsequent reads to A, so no new edges
Write(B): In T6, no subsequent reads to B, so no new edges Precedence graph for schedule S2:
Fig 7 - Precedence graphThe precedence graph for schedule S2 contains no cycle that's why ScheduleS2 is serializable. Q10) Explain DBMS Concurrency Control in detail with examplesA10) Concurrency Control is the management procedure that is required for controlling concurrent execution of the operations that take place on a database.But before knowing about concurrency control, we should know about concurrent execution.Concurrent Execution in DBMSIn a multi-user system, multiple users can access and use the same database at one time, which is known as the concurrent execution of the database. It means that the same database is executed simultaneously on a multi-user system by different users. While working on the database transactions, there occurs the requirement of using the database by multiple users for performing different operations, and in that case, concurrent execution of the database is performed. The thing is that the simultaneous execution that is performed should be done in an interleaved manner, and no operation should affect the other executing operations, thus maintaining the consistency of the database. Thus, on making the concurrent execution of the transaction operations, there occur several challenging problems that need to be solved. Problems with Concurrent ExecutionIn a database transaction, the two main operations are READ and WRITE operations. So, there is a need to manage these two operations in the concurrent execution of the transactions as if these operations are not performed in an interleaved manner, and the data may become inconsistent. So, the following problems occur with the Concurrent Execution of the operations:Problem 1: Lost Update Problems (W - W Conflict)The problem occurs when two different database transactions perform the read/write operations on the same database items in an interleaved manner (i.e., concurrent execution) that makes the values of the items incorrect hence making the database inconsistent.For example:Consider the below diagram where two transactions TX and TY, are performed on the same account A where the balance of account A is $300.
At time t1, transaction TX reads the value of account A, i.e., $300 (only read). At time t2, transaction TX deducts $50 from account A that becomes $250 (only deducted and not updated/write). Alternately, at time t3, transaction TY reads the value of account a that will be $300 only because TX didn't update the value yet. At time t4, transaction TY adds $100 to account A that becomes $400 (only added but not updated/write). At time t6, transaction TX writes the value of account A that will be updated as $250 only, as TY didn't update the value yet. Similarly, at time t7, transaction TY writes the values of account A, so it will write as done at time t4 that will be $400. It means the value written by TX is lost, i.e., $250 is lost. Hence data becomes incorrect, and database sets to inconsistent.Dirty Read Problems (W-R Conflict)The dirty read problem occurs when one transaction updates an item of the database, and somehow the transaction fails, and before the data gets rollback, the updated database item is accessed by another transaction. There comes the Read-Write Conflict between both transactions.For example:Consider two transactions TX and TY in the below diagram performing read/write operations on account A where the available balance in account A is $300:
At time t1, transaction TX reads the value of account A, i.e., $300. At time t2, transaction TX adds $50 to account A that becomes $350. At time t3, transaction TX writes the updated value in account A, i.e., $350. Then at time t4, transaction TY reads account A that will be read as $350. Then at time t5, transaction TX rollbacks due to server problem, and the value changes back to $300 (as initially). But the value for account A remains $350 for transaction TY as committed, which is the dirty read and therefore known as the Dirty Read Problem. Unrepeatable Read Problem (W-R Conflict)Also known as Inconsistent Retrievals Problem that occurs when in a transaction, two different values are read for the same database item.For example:Consider two transactions, TX and TY, performing the read/write operations on account A, having an available balance = $300. The diagram is shown below:
At time t1, transaction TX reads the value from account A, i.e., $300. At time t2, transaction TY reads the value from account A, i.e., $300. At time t3, transaction TY updates the value of account A by adding $100 to the available balance, and then it becomes $400. At time t4, transaction TY writes the updated value, i.e., $400. After that, at time t5, transaction TX reads the available value of account A, and that will be read as $400. It means that within the same transaction TX, it reads two different values of account A, i.e., $ 300 initially, and after updation made by transaction TY, it reads $400. It is an unrepeatable read and is therefore known as the Unrepeatable read problem. Thus, in order to maintain consistency in the database and avoid such problems that take place in concurrent execution, management is needed, and that is where the concept of Concurrency Control comes into role.Concurrency ControlConcurrency Control is the working concept that is required for controlling and managing the concurrent execution of database operations and thus avoiding the inconsistencies in the database. Thus, for maintaining the concurrency of the database, we have the concurrency control protocols.Concurrency Control ProtocolsThe concurrency control protocols ensure the atomicity, consistency, isolation, durability and serializability of the concurrent execution of the database transactions. Therefore, these protocols are categorized as:Lock Based Concurrency Control Protocol Time Stamp Concurrency Control Protocol Validation Based Concurrency Control Protocol
A’s Account Open_Account(A) Old_Balance = A.balance New_Balance = Old_Balance - 500 A.balance = New_Balance Close_Account(A) B’s Account Open_Account(B) Old_Balance = B.balance New_Balance = Old_Balance + 500 B.balance = New_Balance Close_Account(B)
|
|
- Re-start the transaction
- Kill the transaction
|
|
|
|
|
Fig 3 - Transaction states |
- Re-start the transaction
- Kill the transaction
Fig 4 - Property of Transaction |
T1 | T2 |
Read(A) | Read(B) |
|
|
Read(B): In T2, no subsequent writes to B, so no new edges
Read(C): In T3, no subsequent writes to C, so no new edges
Write(B): B is subsequently read by T3, so add edge T2 → T3
Write(C): C is subsequently read by T1, so add edge T3 → T1
Write(A): A is subsequently read by T2, so add edge T1 → T2
Write(A): In T2, no subsequent reads to A, so no new edges
Write(C): In T1, no subsequent reads to C, so no new edges
Write(B): In T3, no subsequent reads to B, so no new edges Precedence graph for schedule S1:
|
|
Read(C): In T4, no subsequent writes to C, so no new edges
Write(A): A is subsequently read by T5, so add edge T4 → T5
Read(B): In T5,no subsequent writes to B, so no new edges
Write(C): C is subsequently read by T6, so add edge T4 → T6
Write(B): A is subsequently read by T6, so add edge T5 → T6
Write(C): In T6, no subsequent reads to C, so no new edges
Write(A): In T5, no subsequent reads to A, so no new edges
Write(B): In T6, no subsequent reads to B, so no new edges Precedence graph for schedule S2:
|
|
|
|
0 matching results found