DBMS_Transaction processing – Schedule –Serializable Schedule – Concurrency Control.pptx

Transaction Processing -Schedule - Concurrency Control

1. DBMS TRANSACTION PROCESSING Transaction processing – Schedule –Serializable Schedule – Concurrency Control 9/15/2022 Dr.Thenmozhi K 1

2. INTRODUCTION 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 . Process: Single user Multi user Read/write Property of Transaction Atomicity Consistency Isolation Durability 9/15/2022 Dr.Thenmozhi K 2

3. States of Transaction 9/15/2022 Dr.Thenmozhi K 3

4. Schedule A series of operation from one transaction to another transaction is known as schedule . It is used to preserve the order of the operation in each of the individual transaction. 9/15/2022 Dr.Thenmozhi K 4

5. 1. Serial Schedule – NO INTERLEAVING The serial schedule is a type of schedule where one transaction is executed completely before starting another transaction . In the serial schedule, when the first transaction completes its cycle , then the next transaction is executed . two transactions T1 and T2 which have some operations. If it has no interleaving of operations. Ex: T1 executes all operation, then T2 executes all operation 9/15/2022 Dr.Thenmozhi K 5

6. 2. Non-serial Schedule - INTERLEAVING If interleaving of operations is allowed , then there will be non-serial schedule. It contains many possible orders in which the system can execute the individual operations of the transactions. Figure c & d – interleaving operation – Ex: T1 executes but not complete the operation T2 starts to execute before T1 completes its operation. 9/15/2022 Dr.Thenmozhi K 6

7. 3. Serializable schedule – non serial to serializable The serializability of schedules is used to find non-serial schedules that allow the transaction to execute concurrently without interfering with one another. It identifies which schedules are correct when executions of the transaction have interleaving of their operations. A non-serial schedule will be serializable if its result is equal to the result of its transactions executed serially. 9/15/2022 Dr.Thenmozhi K 7

8. 9/15/2022 Dr.Thenmozhi K 8

9. Schedule A and Schedule B are serial schedule. Schedule C and Schedule D are Non-serial schedule. 9/15/2022 Dr.Thenmozhi K 9

10. serializability Testing Conflict View 9/15/2022 Dr.Thenmozhi K 10

11. Testing of Serializability Serialization Graph is used to test the Serializability of a schedule . graph has a pair G = (V, E), where V consists a set of vertices, and E consists a set of edges all edges Ti -> Tj   , hold one of three conditions: 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). 9/15/2022 Dr.Thenmozhi K 11

12. Conflict Serializable Schedule A schedule is called conflict serializability if after swapping of non-conflicting operations, it can transform into a serial schedule. The schedule will be a conflict serializable if it is conflict equivalent to a serial schedule. Conflicting Operations The two operations become conflicting if all conditions satisfy: Both belong to separate transactions. They have the same data item. They contain at least one write operation. 9/15/2022 Dr.Thenmozhi K 12

13. Example: Swapping is possible only if S1 and S2 are logically equal. Here , S1 = S2. That means it is non-conflict . Here, S1 ≠ S2. That means it is conflict. 9/15/2022 Dr.Thenmozhi K 13

14. Conflict Equivalent one can be transformed to another by swapping non-conflicting operations S2 is conflict equivalent to S1 (S1 can be converted to S2 by swapping non-conflicting operations ). Two schedules are said to be conflict equivalent if and only if: They contain the same set of the transaction. If each pair of conflict operations are ordered in the same way. 9/15/2022 Dr.Thenmozhi K 14

15. Since, S1 is conflict serializable . T1 T2 Read(A) Write(A) Read(B) Write(B) Read(A) Write(A) Read(B) Write(B) 9/15/2022 Dr.Thenmozhi K 15

16. View Serializability A schedule will view serializable if it is view equivalent to a serial schedule. If a schedule is conflict serializable , then it will be view serializable . The view serializable which does not conflict serializable contains blind writes. 9/15/2022 Dr.Thenmozhi K 16

17. View Equivalent An initial read of both schedules must be the same .  In schedule S1, if a transaction T1 is reading the data item A, then in S2, transaction T1 should also read A.  9/15/2022 Dr.Thenmozhi K 17

18. Recoverability of Schedule Transaction may not execute completely due to a software issue, system crash or hardware failure. In that case, the failed transaction has to be rollback . 9/15/2022 Dr.Thenmozhi K 18

19. Irrecoverable schedule:  The schedule will be irrecoverable if Tj reads the updated value of Ti and Tj committed before Ti commit. 9/15/2022 Dr.Thenmozhi K 19

20. Recoverable with cascading rollback:   The schedule will be recoverable with cascading rollback if Tj reads the updated value of Ti. Commit of Tj is delayed till commit of Ti. 9/15/2022 Dr.Thenmozhi K 20

21. Transaction support in SQL ( refer 1 st unit) Properties ACID Transaction Control The following commands are used to control transactions. COMMIT  − to save the changes. ROLLBACK  − to roll back the changes. SAVEPOINT  − creates points within the groups of transactions in which to ROLLBACK. SET TRANSACTION  − Places a name on a transaction. Transactional Control Commands DML Commands  such as - INSERT, UPDATE and DELETE only.  9/15/2022 Dr.Thenmozhi K 21

22. Transaction Property 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. 9/15/2022 Dr.Thenmozhi K 22

23.  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. 9/15/2022 Dr.Thenmozhi K 23

24. 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.  9/15/2022 Dr.Thenmozhi K 24

25. 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. 9/15/2022 Dr.Thenmozhi K 25

26.  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.  9/15/2022 Dr.Thenmozhi K 26

27. Concurrency control techniques Concurrency Control is the management procedure that is required for controlling concurrent execution of the operations that take place on a database. Concurrency Control  in Database Management System is a procedure of managing simultaneous operations without conflicting with each other . It ensures that Database transactions are performed concurrently and accurately to produce correct results without violating data integrity of the respective Database. 9/15/2022 Dr.Thenmozhi K 27

28. Problems with Concurrent Execution In 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: 9/15/2022 Dr.Thenmozhi K 28

29. 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 . 9/15/2022 Dr.Thenmozhi K 29

30. t time t1, transaction T X  reads the value of account A, i.e., $300 (only read). At time t2, transaction T X  deducts $50 from account A that becomes $250 (only deducted and not updated/write). Alternately, at time t3, transaction T Y  reads the value of account A that will be $300 only because T X  didn't update the value yet. At time t4, transaction T Y  adds $100 to account A that becomes $400 (only added but not updated/write). At time t6, transaction T X  writes the value of account A that will be updated as $250 only, as T Y  didn't update the value yet. Similarly, at time t7, transaction T Y  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 T X  is lost, i.e., $250 is lost. 9/15/2022 Dr.Thenmozhi K 30

31. 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. 9/15/2022 Dr.Thenmozhi K 31

32. 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. 9/15/2022 Dr.Thenmozhi K 32

33. 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. 9/15/2022 Dr.Thenmozhi K 33

34. At time t1, transaction T X  reads the value from account A, i.e., $300. At time t2, transaction T Y  reads the value from account A, i.e., $300. At time t3, transaction T Y  updates the value of account A by adding $100 to the available balance, and then it becomes $400. At time t4, transaction T Y  writes the updated value, i.e., $400. After that, at time t5, transaction T X  reads the available value of account A, and that will be read as $400. It means that within the same transaction T X , it reads two different values of account A, i.e., $ 300 initially, and after updation made by transaction T Y , it reads $400. It is an unrepeatable read and is therefore known as the Unrepeatable read problem. 9/15/2022 Dr.Thenmozhi K 34

35. Potential problem Lost Updates  occur when multiple transactions select the same row and update the row based on the value selected Uncommitted dependency issues occur when the second transaction selects a row which is updated by another transaction ( dirty read ) Non-Repeatable Read   occurs when a second transaction is trying to access the same row several times and reads different data each time. Incorrect Summary issue   occurs when one transaction takes summary over the value of all the instances of a repeated data-item, and second transaction update few instances of that specific data-item. In that situation, the resulting summary does not reflect a correct result. 9/15/2022 Dr.Thenmozhi K 35

36. Why use Concurrency method? Reasons for using Concurrency control method is DBMS: To apply Isolation through mutual exclusion between conflicting transactions To resolve 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 . This control is achieved using concurrent-control schemes. Concurrency control helps to ensure serializability 9/15/2022 Dr.Thenmozhi K 36

37. Concurrency Control Protocols Lock-Based Protocols Two Phase Locking Protocol Timestamp-Based Protocols Validation-Based Protocols 9/15/2022 Dr.Thenmozhi K 37

38. Locking techniques for concurrency control There are several techniques to avoid the interference when the transactions are executing concurrently. Locking techniques for concurrency control: Concurrent execution of transactions can be controlled by locking the data items. A lock is a variable associated with a data item that describes the status of the item with respect to the operations applied to it. Two types of  locks :  1.Binary Lock.  2.Shared/Exclusive lock. 9/15/2022 Dr.Thenmozhi K 38

39. Binary Lock Binary lock values: It can have only 2 states or values: Locked (1) Unlocked (0) If  LOCK(x)=1,then x cannot be accessed by the database operation that requests the item. If LOCK(x)=0,then the item can be accessed when requested. Binary Lock operations: 2 operations with binary lock:  Lock_ item  Unlock_ item 9/15/2022 Dr.Thenmozhi K 39

40. Shared/Exclusive Locks Several transactions should be allowed to access the item X if it is for reading purpose only . If a transaction has to do write operation then it should be given exclusive access to x . There are 3 lock operations. 1.read_ lock(x) 2.write_lock(x) 3.Unlock(x). Therefore LOCK(x) can have the above 3 possible states. The locking operations can be handled by maintaining lock table and keeping track of the number of transactions holding a shared lock on the item in the lock table. Each record in the lock table will have 4 fields. 1. Data item name. 2. LOCK. 3. Number of reads. 4. Locking transactions. The state of the LOCK is write locked or read locked . No interleaving is allowed unless the transaction started terminates by giving the lock or the transaction is placed on a waiting queue for the item. 9/15/2022 Dr.Thenmozhi K 40

41. Two Phase Locking Growing phase:  In the growing phase, a new lock on the data item may be acquired by the transaction, but none can be released. Shrinking phase:  In the shrinking phase, existing lock held by the transaction may be released, but no new locks can be acquired. 9/15/2022 Dr.Thenmozhi K 41

42. A deadlock is a condition where two or more transactions are waiting indefinitely for one another to give up locks. Deadlock is said to be one of the most feared complications in DBMS as no task ever gets finished and is in waiting state forever. 9/15/2022 Dr.Thenmozhi K 42

43. THANK YOU .. [email protected] 9/15/2022 Dr.Thenmozhi K 43