Concurrency Control in Distributed Database.

This is an PPT of DBMS . I include the various topic such as"Concurrency Control in Distributed Database ",

1. BIRLA INSTITUTE OF TECHNOLOGY,MESRA RANCHI JAIPUR CAMPUS Presentation on: “CONCURRENCY CONTROL IN DISTRIBUTED DATABASE ” Presented By: MAYANK SETH ARUN BRIJWASI

2. CONTENTS Meaning of distributed database Introduction to concurrency control Locking based protocols Timestamping

3. Distributed databases A distributed database is a single logical database that is spread physically across the computer in multiple location that are connected by data communication links. Distributed database is a kind of virtual database whose components parts are physically stored in a number of distinct real database at a number of distinct location.

4. CONCURRENCY A situation in which two or more persons access the same records simultaneously is called concurrency Concurrency control involve the synchronization of access to the distributed database, such that the integrity of the database is maintained.

5. Concurrency control Concurrency control can be achieved through: Locking protocols Timestamping

6. Lock-Based Protocols A lock is a mechanism to control concurrent access to a data item. Data items can be locked in two modes : E xclusive (X) mode. Data item can be both read as well as written . X-lock is requested using lock-X instruction . S hared (S) mode. Data item can only be read. S-lock is requested using lock-S instruction. Lock requests are made to concurrency-control manager. Transaction can proceed only after request is granted.

7. Lock-Based Protocols (Cont.) A transaction may be granted a lock on an item if the requested lock is compatible with locks already held on the item by other transactions Any number of transactions can hold shared locks on an item, but if any transaction holds an exclusive on the item no other transaction may hold any lock on the item . If a lock cannot be granted, the requesting transaction is made to wait till all incompatible locks held by other transactions have been released. The lock is then granted.

8. Lock based protocols Lock based concurrency control can either be : One phase locking protocol Two phase locking protocol

9. One phase locking protocol In this method each transaction locks an item before use and releases the lock as soon as it has finished using it.

10. Two phase locking protocol This protocol requires that each transaction issue lock and unlock request in 2 phases: First phase : a transaction only acquires all the locks it needs and do not release any lock. This phase is called growing phase. Second phase : a transaction releases the locks and cannot request any new locks. This phase is called shrinking phase .

11. Various locking protocols The various locking protocols that can be used in distributed databases in following manner: Single lock manager approach Distributed lock manager approach Primary copy Majority protocol

12. Single lock manager protocol The system maintains a single lock manager that resides in a single chosen site—say Si . All lock and unlock requests are made at site Si . When a transaction needs to lock a data item, it sends a lock request to Si .

13. The lock manager determines whether the lock can be granted immediately. If the lock can be granted, the lock manager sends a message to that effect to the site at which the lock request was initiated. Otherwise, the request is delayed until it can be granted, at which time a message is sent to the site at which the lock request was initiated.

14. Distributed lock manager The lock-manager function is distributed over several sites. Each site maintains a local lock manager whose function is to administer the lock and unlock requests for those data items that are stored in that site. When a transaction wishes to lock a data item Q that is not replicated and resides at site Si , a message is sent to the lock manager at site Si requesting a lock (in a particular lock mode).

15. If data item Q is locked in an incompatible mode, then the request is delayed until it can be granted. Once it has determined that the lock request can be granted, the lock manager sends a message back to the initiator indicating thatit has granted the lock request.

16. Primary copy When a system uses data replication, we can choose one of the replicas as the primary copy. For each data item Q, the primary copy of Q must reside in precisely one site, which we call the primary site of Q. When a transaction needs to lock a data item Q, it requests a lock at the primary site of Q. The response to the request is delayed until it can be granted. The primary copy enables concurrency control for replicated data to be handled like that for unreplicated data.

17. Majority protocol If data item Q is replicated in n different sites, then a lock-request message must be sent to more than one-half of the n sites in which Q is stored. Each lock manager determines whether the lock can be granted immediately. The response is delayed until the request can be granted. The transaction does not operate on Q until it has successfully obtained a lock on a majority of the replicas of Q.

18. Timestamp-Based Protocols Each transaction is given a unique timestamp that the system uses in deciding the serialization order. The protocol manages concurrent execution such that the timestamps determine the serializability order. In order to assure such behavior, the protocol maintains for each data Q two timestamp values: W-timestamp(Q ) is the largest time-stamp of any transaction that executed write(Q) successfully. R-timestamp(Q ) is the largest time-stamp of any transaction that executed read(Q ) successfully.

19. In generalizing the centralized scheme to a distributed scheme is to develop a scheme for generating unique timestamps. There are two primary methods for generating unique timestamps, one centralized and one distributed. In the centralized scheme, a single site distributes the timestamps. In the distributed scheme, each site generates a unique local timestamp by using either a logical counter or the local clock. We obtain the unique global timestamp by concatenating the unique local timestamp with the site identifier, which also must be unique.