Controlling Concurrent Access lecture 3

1. Controlling Concurrent Access lecture 3 By: Tufail khan

2. Deadlock • Locking solves the problem of erroneous updates • but may lead to another problem called deadlock: • An impasse (a position or situation from which there is no escape) • that results when two or more transactions have locked a common resource, • and each must wait for the other to unlock that resource.

3. Contd… • The figure on the next slide shows a common example of a deadlock. • John and Marsha have a joint checkingaccount, • and both want to withdraw some cash at the same time, • Each using an ATM terminal in a different location.

4. Deadlock Figure 3 The problem of deadlock John and Marsha will wait forever for each other to release their locked resources!

5. Contd… • When john initiates his transaction, • the program places a read lock on his account record, • Since he is reading the record to check the account balance. • When john requests a withdrawal, • the program attempts to place an exclusive lock on the record, • Since this is an update operation.

6. Contd… • However as we can see in the figure on previous slide, • Marsha hasalready initiated a transaction that has placeda read lock on the same record. • As a result his request is denied, • and now john’s transaction is waiting for Marsha’s transaction, • To remove the read lock from the account record and vice versa.

7. Contd… • Neither person can withdraw money from the account, • Event though the balance is more than their requirements.

8. Figure User B Time User A 1. Lock Record Y 1. Lock Record X 2. Request Record Y 2. Request record X 3. Wait for X 3. Wait for Y Deadlock

9. Explanation • The slide on previous figure shows a slightly more complex situation of deadlock. • In this example user A has locked record X and user B has locked record Y. • User A then requests record Yintending toupdate, • and user B requests record X also intending to update. • Both requests are denied since the requested records are already locked.

10. Managing Deadlocks • There are two basic ways to resolve deadlock. • Deadlock prevention and deadlock detection and recovery. • Deadlock prevention: when deadlock prevention is employed, • user programs must lock all records they will require at the beginning of a transaction, • rather than one at a time.

11. Contd… • If either record is already locked the program must wait until it is released.

12. Two Phase locking protocol • A transaction follows the two phase locking protocol if all locking operations precede, • the first unlock operation in the transaction. • According to rules of this protocol, every transaction can be divided into two phases: • First a growing phase, in which it acquires all the locks needed, • But cannot release any locks.

13. Contd… • And then a shrinking phase, in which it releases its locks, • But cannot acquire any new locks. • There is no requirement that all locks be obtained simultaneously. • Normally the transaction acquires some locks, • Does some processing and goes on to acquire additional locks as needed.

14. Contd… • However, it never releases any lock until it has reached a stage where no new locks are needed. • The summary of these rules is: • 1. a transaction must acquire a lock on an item or data resource before operating on that item. • The lock may be read or write, • depending on the type of access needed.

15. Contd… • 2. Once the transaction releases a lock, • it can never acquire any new locks. • Locking all the required records at the beginning of the transaction, • called conservative two phase locking prevents deadlock.

16. Contd… • Unfortunately, it is often difficult to predict in advance, • what records will be required to process a transaction. • As a result deadlock prevention is not always possible.

17. Deadlock detection and Recovery • The second and more common approach is to allow deadlocks to occur, • But to build mechanisms into the DMBS for detecting and breaking the deadlocks. • Deadlock detection and recovery mechanisms work as follows. • The DBMS maintains a matrix of resource usage,

18. Contd… • Which indicates what subjects (users) are using what objects (resources). • By scanning this matrix, the computer can detect deadlocks as they occur. • Once deadlock has been detected, • the DBMS needs to abort one or more of the deadlocked transactions. • There are several issues that need to be considered.

19. Selecting a victim • In some situations, the selection of transactions to abort may be obvious. • However in other situations, the selection may not be so easy. • In such cases we would want to abort the transactions that incur the minimum costs. • This may take into considerations:

20. Contd…. • (a)how long the transaction has been running. • It may be better to abort a transaction that has just started rather than one that has been running for some time. • (b) How many data items has been updated by the transaction. • It would be better to abort a transaction that has made little change to the database, • rather one that has made significant changes to the database.

21. Contd… • How many data items the transaction is still to update, • It would be better to abort a transaction, • that has many changes still to make to the database rather than • One that has few changes to make. • Unfortunately it is not necessary that the DMBS will have all these details and will decide accordingly.

22. How far to roll a transaction back • Having decided to abort a particular transaction, • we have to decide how far to roll the transaction back. • Clearly undoing all the changes made by a transaction is the simplest solution, • although not necessarily the most efficient. • It may be possible to resolve the deadlock by rolling back only a part of the transaction.

23. Starvation • Starvation occurs when the same transaction is always chosen as a victim, • and the transaction can never complete. • The DBMS can avoid starvation by storing a count of the number of times, • A transaction has been selected as the victim and, • using a different selection criterion once this count reaches some upper limit.

24. Versioning • A newer approach to concurrency control, • called versioning takes the optimistic approach, • That most of the time other users do not want the same record, • Or if they do, they only want to read, • but not to update the record.

25. Contd… • With versioning no form of locking is required. • Each transaction is restricted to a view of the database as of the time that transaction started, • and when a transaction modifies a record, • the DBMS creates a new record version, • Instead of overwriting the old record.

26. Explanation with example • Versioning can be best explained with the help of an example. • Suppose there is a central record room (database) • The record room has a service window. • Users (transactions) arrive at the window and requests documents (database records).

27. Contd… • However the original documents never leave the record room. • Instead the clerk (DBMS) makes copies of the requested documents and timestamps them. • Users then take their private copies of the documents to their own workplace • and read them and/or make changes.

28. Contd… • When finished, they return their marked-up copies to the clerk. • The clerk merges the changes from marked-up copies into the central database. • When there is no conflict, • for example if only one user has made changes to a set of database records, • That user’s changes are merged directly into the central database.

29. Contd… • Suppose there is a conflict; • for example two users have made conflicting changes to their private copy of the database. • In this case the changes made by one of the users are committed to the database. • The transactions are timestamped, • so that the earlier transaction can be given priority.

30. Contd… • The other users must be told that there was a conflict and his work cannot be committed. • He must check out another copy of the database records and repeat the previous work.

31. Figure 12-15 The use of versioning Better performance than locking

32. Explanation of previous figure • The figure on previous slide shows a simple example of the use of versioning for the checking account example. • John reads the record containing the account balance, • successfully withdraws $200, • and the new balance $800 is posted to the account with a COMMIT statement.

33. Contd… • Meanwhile Marsha has also read the account record and requested a withdrawal • which is posted to her local version of the account record. • However when the transaction attempts to COMMIT, • it discovers the update conflict, • and her transaction is aborted.

34. Contd… • She can then restart the transaction, working from the correct starting balance of $800.

35. Advantages • The main advantage of versioning over locking is performance improvement. • Read only transaction can run concurrently, • with updating transactions without loss of database consistency.