Chapter 4.pptxAdvanced Data base Transaction processing

Advanced Data base Transaction processing

1. CHAPTER 4 Concurrency Control Techniques

2. What is Concurrency Control? 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 .

3. Concurrent access is quite easy if all users are just reading data . There is no way they can interfere with one another. Though for any practical Database, it would have a mix of READ and WRITE operations and hence the concurrency is a challenge .

4. DBMS Concurrency Control is used to address such conflicts, which mostly occur with a multi-user system . Therefore, Concurrency Control is the most important element for proper functioning of a Database Management System where two or more database transactions are executed simultaneously, which require access to the same data .

5. Potential problems of Concurrency Here, are some issues which you will likely to face while using the DBMS Concurrency Control method: 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.

6. 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

7. Example Assume that two people who go to electronic kiosks at the same time to buy a movie ticket for the same movie and the same show time. However, there is only one seat left in for the movie show in that particular theatre. Without concurrency control in DBMS, it is possible that both moviegoers will end up purchasing a ticket. However, concurrency control method does not allow this to happen. Both moviegoers can still access information written in the movie seating database. But concurrency control only provides a ticket to the buyer who has completed the transaction process first.

8. 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. Following are the Concurrency Control techniques in DBMS: Lock-Based Protocols Two Phase Locking Protocol Timestamp-Based Protocols Validation-Based Protocols

9. Lock-based Protocols What is Lock-Based Protocol in DBMS? Locking protocol in DBMS is like a set of rules for managing access to data in databases . With lock-based protocols, a transaction cannot read or write data until the appropriate lock is obtained. This helps to solve the concurrency problem by locking a given transaction to a specific user. The lock is a variable that indicates which operations can be carried out on the specific data item.

10. The formal definition of a lock is as follows:  A Lock is a variable assigned to any data item in order to keep track of the status of that data item so that isolation and non-interference is ensured during concurrent transactions.

11. In Case 2, which has been portrayed above, if the user/session on the right attempts an update , it will be met with a  LOCK WAIT  state or otherwise be  STALLED   until access to the data item is unlocked. In some situations – if the stall exceeds a time limit – the session is terminated and an error statement is returned .

12. Types of Locks in DBMS There are two types of Locking protocol in DBMS :  Shared Lock (S)  Exclusive Lock (X)

13. Shared Lock  A shared lock is also known as a Read-only lock. The data item can be shared between transactions via the shared lock . We will not be able to update data on the lock. Example of shared lock:  Consider the situation where two transactions read a person's account balance. By establishing a shared lock on the database, they will read it . However, a shared lock prevents another transaction from updating that account's balance until the reading procedure is completed.

14. Exclusive Lock The data item can be read and written via Exclusive Lock. This exclusive mode cannot be used on the same data item simultaneously. The lock-x instruction is used to request X-lock . Transactions may unlock the data item after completing the 'write' process . Example of exclusive lock : When a transaction needs to update a person's account balance, for example, you can allow it to proceed by putting an X lock on this transaction. As a result, the exclusive lock prevents the second transaction from reading or writing.

15. The read_lock operation – read_lock (X): label: if lock(X) = "unlocked" { then lock(X) = "read-locked"; no_of_reads = 1; //since only the first transaction in queue is now able to read item X } else if lock(X) = "read-locked" { then no_of_reads +=1; //simply increment as a new transaction is now reading the item X } else //lock(X) write-locked { wait (until lock(X) is "unlocked");//transactions observe a LOCK WAIT during this time go to label; }

16. When the lock(X) is set to “write-locked” (in the final else clause), the item is exclusively being accessed by a transaction. In order for other transactions to access it, the  LOCK WAIT  must end (updating process must finish) and lock(X) = “unlocked”. This is what we wait for in the next line

17. The write_lock operation – write_lock (X): label: if lock(X) = "unlocked" { then lock(X) = "write-locked" } else //if a read-lock is issued to item X { wait (until lock(X) is "unlocked"); //so that the lock manager may wake up the next transaction go to label; }

18. If an item is unlocked, we simply write-lock it to grant exclusive access to the current transaction. Now the lock manager system must put all other transactions in a queue. If the item is in a read-lock state, the write-lock may NOT be directly issued. The item must first be unlocked before it can be write-locked. In doing so, the lock manager system also wakes up the queued transactions.

19. Unlike binary locks, Read/Write locks may be set to 3 values, i.e.,  SHARED ,  EXCLUSIVE  or  UNLOCKED . Hence, our lock, i.e., lock(X), may reflect either of the following values: READ-LOCKED – If a transaction only requires to read the contents of item X and the lock only permits reading. This is also known as a  shared lock . WRITE-LOCKED – If a transaction needs to update or write to item X, the lock must restrict all other transactions and provide exclusive access to the current transaction. Thus, these locks are also known as  exclusive locks . UNLOCKED – Once a transaction has completed its read or update operations, no lock is held and the data item is unlocked. In this state, the item may be accessed by any queued transactions.

20. shared Lock  Exclusive Lock  A transaction T 1    having been shared can only read the data.  More than one transaction can acquire a shared lock on the same variable. Represented by S   A transaction T 1    having an exclusive lock can both read as well as write  At any given time, only one Transaction can have an exclusive lock on a variable. Represented by X

21. Binary Locks : Remember that a lock is fundamentally a variable which holds a value. A binary lock is a variable capable of holding only 2 possible values, i.e., a  1 (depicting a locked state)  or a  0 (depicting an unlocked state) . This lock is usually associated with every data item in the database ( maybe at table level, row level or even the entire database level). 

22. Binary lock Should item X be unlocked, then a corresponding object lock(X) would return the value 0. So, the instant a user/session begins updating the contents of item X, lock(X) is set to a value of 1. Due to this, for as long as the update query lasts, no other user may access the item X – even read or write to it!  There are 2 operations used to implement binary locks. They are lock_data ( ) and unlock_data ( ).

23. The algorithms have been discussed below (only algorithms have been entertained due to the diversity in DBMS scripts): The locking operation : lock_data (X): label: if lock(X) == 0 { then lock(X) = 1; } else //when lock(X) == 1 or item X is locked { wait (until item is unlocked or lock(X)=0) //wait for the user to finish the update query go to label }

24. The unlocking operation : unlock_data (X): lock(X) = 0 //we unlock access to item X if (transactions are in queue) { then grant access or 'wake' the next transaction in line; }

25. Begin tran update test set ID = 'D107' where Name = 'Record3' select*from test select * from test

26. Create the following Table

27. Create PROCEDURE uspGetDepartmentById @ DepartmentId int AS BEGIN Set nocount on ; SELECT Id , DepartmentName FROM Department WHERE Id = @ DepartmentId End GO

28. exec uspGetDepartmentById 1

29. 3. Simplistic Lock Protocol This type of lock-based protocols allows transactions to obtain a lock on every object before beginning operation. Transactions may unlock the data item after finishing the ‘write’ operation. 4. Pre-claiming Locking Pre-claiming lock protocol helps to evaluate operations and create a list of required data items which are needed to initiate an execution process. In the situation when all locks are granted, the transaction executes . After that, all locks release when all of its operations are over.

30. Starvation Starvation is the situation when a transaction needs to wait for an indefinite period to acquire a lock. Following are the reasons for Starvation: When waiting scheme for locked items is not properly managed In the case of resource leak The same transaction is selected as a victim repeatedly

31. Deadlock 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 . In a database, a deadlock is an unwanted situation in which 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 it brings the whole system to a Halt

32. Starvation happens when a transaction is repeatedly denied access to a data item because of other transactions holding locks on it. Both deadlock and starvation can affect the performance, reliability, and correctness of a DBMS.

33. Timestamp-based Protocols Timestamp based Protocol  in DBMS is an algorithm which uses the System Time or Logical Counter as a timestamp to serialize the execution of concurrent transactions . The Timestamp-based protocol ensures that every conflicting read and write operations are executed in a timestamp order. The older transaction is always given priority in this method. It uses system time to determine the time stamp of the transaction. This is the most commonly used concurrency protocol. Lock-based protocols help you to manage the order between the conflicting transactions when they will execute. Timestamp-based protocols manage conflicts as soon as an operation is created .

34. Advantages : Schedules are serializable just like 2PL protocols No waiting for the transaction, which eliminates the possibility of deadlocks! Disadvantages: Starvation is possible if the same transaction is restarted and continually aborted

35. Validation Based Protocol Validation based Protocol  in DBMS also known as Optimistic Concurrency Control Technique is a method to avoid concurrency in transactions. In this protocol, the local copies of the transaction data are updated rather than the data itself, which results in less interference while execution of the transaction. The Validation based Protocol is performed in the following three phases: Read Phase Validation Phase Write Phase Read Phase In the Read Phase, the data values from the database can be read by a transaction but the write operation or updates are only applied to the local data copies , not the actual database . Validation Phase In Validation Phase, the data is checked to ensure that there is no violation of serializability while applying the transaction updates to the database. Write Phase In the Write Phase, the updates are applied to the database if the validation is successful, else; the updates are not applied, and the transaction is rolled back.

36. Characteristics of Good Concurrency Protocol An ideal concurrency control DBMS mechanism has the following objectives: Must be resilient to site and communication failures. It allows the parallel execution of transactions to achieve maximum concurrency. Its storage mechanisms and computational methods should be modest to minimize overhead. It must enforce some constraints on the structure of atomic actions of transactions.

37. End Of Chapter 4