UNIT IX - Transaction & Concurrency Control [4 Hrs Transaction Concept ‘The transaction is a set of logically related operation. it contains a group of tasks. A transaction is an action or series of actions. It is performed by a single user to perform operations for accessing the contents of the database. 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 simpie and small transaction involves several low-level tasks. A's Account Open Account(a 1d Balance = A.belance Close Account( States of Transactions / Transaction Model transaction in a database can be in one of the following states — Bogin End 1) Active = in this state, the transaction is being executed. This is the intial state of every transaction. 2) Partially Committed - When a transaction executes its final operation, itis said to be in a partially committed state. 3) 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. 4) 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 ~ -Re-start the transaction + kill the transaction 5) 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, ACID Properties A transaction is a very small unit of a program and it may contain several low-level 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. 1) Atomicity + This property states thata transaction must be treated as an atomic unit, that is, either all ofits 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/‘eilure of the transaction, + Consider the following transaction T consisting of 4 and T2: Transfer of 100, from account X to account ¥. Beforen500_—_] Yao "Tancaction Read Read) we=X=100 ysv+ 100 Write 00 ‘write 09 Tales: 405) ¥:300) If the transaction fails after completion of T1 but before completion of 2. (say, after write(X) but before write(¥)), then amount has been deducted from X but not added to ¥. ‘This results in an inconsistent database state. ‘Therefore, the transaction must be executed in entirety in order to ensure correctness of database state. 2) 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.+ Referring to the example above, the total amount before and after the transaction must be maintained. Total before T occurs = 500 + 200 = 700. Total after T occurs = 400 + 300 = 700. Therefore, database Is consistent. inconsistency occurs in case T1 completes but 2 falls. AS a result, T is incomplete. 3) Isolation ‘+ In a database system where more than one transaction is 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. Let X= 500, ¥ = 500. Consider two transactions T and 7”. Read (%) ead (*) “100 Read (Y) Witte e) axHY ead (¥) write @) 50 Write Suppose T has been executed till Read (Y) and then T” starts. As a result, interleaving of operations takes place due to which T” reads correct value of X but Incorrect value of ¥ and sum computed by ‘T’: (K4Y = 50, 000+500=50, 500) is thus not consistent with the sum at end of transaction T: (%+Y = 50, 000 + 450= 50, 450). This results in database inconsistency, due to a loss of SO units. Hence, transactions must take place in isolation and changes should be visible only after a they have been made to the main memory. 4) Durabil +The database should be durable enough to hold all its latest updates even if the system fails or restarts. * Ifa transaction updates a chunk of data in a database and commits, then the database will hold the modified data. + Ifa 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,Serializability ‘When multiple transactions are running concurrently then there is a possibility that the database may be left in an inconsistent state. Serializability is a concept that helps us to check which schedules are serializable. A serializable schedule is the one that always leaves the database in consistent state. Y ‘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 Schi 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 ina serial manner. Types of Serializability There are two types of Serializability — 41. Conflict Serializability 2. View Serializability 1) Conflict Serializability Conflict Serializability is one of the type of Serializability, which can be used to check whether a non-serial schedule is conflict serializable or not. A schedule is called conflict serializable if we can convert it into a serial schedule after swapping its non-contflicting operations Conflicting operations ‘Two operations are said to be in conflict, if they satisfy all the following three conditions: 1. Both the operations should belong to different transactions. 2. Both the operations are working on same data item. 3. Atleast one of the operation is a write operation. Let's consider this schedule: n 2a 2) aa) ni) ni) way To convert this schedule into a serial schedule we must have to swap the R(A) operation of transaction T2 with the W(A) operation of transaction 71. However, we cannot swap these two operations because they are conflicting operations, ‘thus we can say that this given schedule is not Conflict Serializable.Example of Conflict Serializability, Lets take another example: —anerswapeng WA) nt a) ot 72 we ot Lats sap roncoeicting operation: pe yang ao and Wi OFT? We 2) View Serializability View Serializability s a process to find out thata given schedule is view serializable or not. Two schedules $1 and 52 are said to be view equal if below conditions are satisfied: a) Initial Read Ifa transaction T1 reading data item A from initial database in 51 then in $2 also T1 should read A from initial database. rH) wey na) me) Transaction 2isreadlng A ter inal databace, b) Updated Read if Tlis reading A which is updated by Tj in S1 then in S2 also Ti should read A which is updated by Tj. nop Pp nonnp we) wea) way Rea) Read wa ‘Above two schedule are not view equal as in St 73s reading A updated by T2 In S2 TS 1s reading A updated by T1 tr ssaping R(T ax RB) oT gec] Final write operation Ifa transaction T1 updated At last in $4, then in $2 also T1 should perform final write operations. no oR on on na) 1) way Wa) ay ua) ‘Above two sctecule arent vew as Final mite operation St isdone by 1 wre m'S2 done ny 72 Concurrency Control v Concurrency controls the procedure in DBMS for managing simultaneous operations without confictng with eath another. ae . v Concurrent access is quite easy if all users are just reading data. There is no way they can interfere with one anather. ‘nougn for any practical database, would have a mix of reading and write operations ana hence the concurrency isa challenge. Concurrency controls used to address such cnflits which mostycesur with 2 mult-user system. It helps you to make sure that database transactions are performed concurrently without violating the data integrity of respective databases. ‘Therefore, concurrency control is 2 most important element for the proper functioning of a system where two or multiple database transactions that require access to the same data, are executed simultaneously. Why use Concurrency method? Reasons for using Concurrency control method is DBMS: + To apply Isolation through mutual exclusion between conflicting transactions + Toresoive read-write and write-write conflict issues * To preserve database consistency through constantly preserving execution obstructions + The system needs to control the interaction among the concurrent transactions. control is achieved using concurrent-control schemes. + Concurrency control helps to ensure serializability Concurrency Control Protocols Different concurrency control protocols offer different benefits between the amount of concurrency they allow and the amount of overhead that they impose. + Lock-Based Protocols + Two Phase + Timestamp:Based Protocols + Validation-Based ProtocolsLock-Based Protocols ‘A lock is a data variable which is associated with a data item. This lock signifies that operations that can be performed on the data item. Locks help synchronize access to the database items by concurrent transactions. All lock requests are made to the concurrency-control manager. Transactions proceed only once the lock request is granted, ¥ Binary Locks: A Binary lock on a data item can either locked or unlocked states. v ‘Shared/exclysive: This type of locking mechanism separates the locks based on thelr Uses Fa Tock is acquired on a data item to perform a write operation, iis callec an Shared Lock (5): v {A shared lock is also called 2 Read-only lock. With the shared lock, the data item can be shared between transactions. ¥ ‘This is because you will never have permission to update data on the data tem. v For example, consider s eace where twe trancactions are reading the account balance of 2 person. ‘The database wil let them read by placing 2 shared lock However, if anothor transaction wants to update that account's balance, shared lock prevent ‘it until the reading process is over 2. Exclusive Lock (X) ‘With the Exclusive Lock, a data item can be read as well as written. v ‘THis is exclusive and can't be held concurrently on the same data item. X-lockis requested ‘7 wsinglockxnstution ‘Trancactions may unlock the data item after finishing the ‘write! eperation v For example, when 2 transaction needs to update the account balance of a person. You can allow this transection by placing X lock on it. v ‘Iherefore, when the second transaction wants to read or write, exclusive lock prevents this, operation Deadlock Handling v Deadlock refers to a specific situation where two or more processes are waiting for each other to release a resource or more than two processes are waiting for the resource in a circular chain. v ‘A deadlock isa condition where two or more transactions are waiting indefinitely for one ‘another to give up locks, ‘ Deadiock is said to be one of the most feared complications in DEMS as no task ever gets, finished and is in waiting state foraver. Deadlock Example in DBMS v For example: Inthe student table, transaction T1 holds a lack on some rows and needs to Update some rows in the grade table, ¥ ‘Simultaneously, transaction 72 holds locks on some rows in the grade table and needs to Update the rows in the Student table held by Transaction T1.