1 / 21

Database Systems

Database Systems. Lecture 23/24 Concurrency Control I Asma Ahmad FAST-NU April 14 th , 2011. Concurrency Control Techniques. Techniques ensure serializable schedules by using protocols that guarantee serializability Main types of techniques are locking and timestamps. Locks.

gretchen-medina
Download Presentation

Database Systems

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Systems Lecture 23/24 Concurrency Control I Asma Ahmad FAST-NU April 14th, 2011

  2. Concurrency Control Techniques • Techniques ensure serializable schedules by using protocols that guarantee serializability • Main types of techniques are locking and timestamps

  3. Locks • A lock is a variable associated with a data item which describes the status of the item with respect to what operations can be applied to it (eg. read or write operations) • used to synchronize access to data items made by concurrently executing transactions • types of locks: binary & multiple-mode

  4. Binary Locks • 2 states: locked (1) or unlocked (0) • Lock(X) is the value of the lock • Lock(X) = 1; value cannot be accessed • Lock(X) = 0; value can be accessed • if another transaction wishes to access X when Lock(X) = 1, it must wait until the lock has been released.

  5. Operations on Binary Locks • lock_item (X) :.A transaction requests access to an item X by first issuing a lock_item(X) operation. If LOCK(X) = 1, the transaction is forced to wait. If LOCK(X) = 0, it is set to 1 (the transaction locks the item) and the transaction is allowed to access item X. • unlock_item(X) : sets LOCK(X) to 0 (unlocks the item) so that X may be accessed by other transactions. • lock_item and unlock_item operations must be implemented as indivisible units (known as critical sections in operating systems); that is, no interleaving should be allowed once a lock or unlock operation is started until the operation terminates or the transaction must wait until the lock_item goes through.

  6. Operations on Binary Locks • wait command within the lock_item(X) operation is usually implemented by putting the transaction on a waiting queue for item X until X is unlocked and the transaction can be granted access to it. Other transactions that also want to access X are placed on the same queue. Hence, the wait command is considered to be outside the lock_item operation.

  7. Binary Locks • Simple but restrictive. • Transactions must lock every data item that is read or written • No data item can be accessed concurrently • we should at least allow concurrent read access

  8. Multiple-Mode Locking Schemes • 2 kinds of locks: read (shared) and write (exclusive) • 3 locking operations: read_lock(X), write_lock(X) & unlock(X) • 3 states: read-locked, write-locked or unlocked

  9. Lock Table • Lock table will have four fields: • <data item name, LOCK, no_of_reads, locking_transaction(s)>. • Value of LOCK : SOME ENCODED VALUE FOR READ /ENCODED VALUE FOR WRITE • LOCK(X)=write-locked, the value of locking_transaction(s) is a single transaction that holds the exclusive (write) lock on X. • LOCK(X)=read-locked, the value of locking transaction(s) is a list of one or more transactions that hold the shared (read) lock on X.

  10. Multiple-mode locking (con’t) • rules for multiple-mode locking schemes • T must issue read_lock(X) or write_lock(X) before read(X) • T must issue write_lock(X) before write(X) • T must issue unlock(X) after all read(X) and write(X) are finished • T will not issue unlock(X) unless it alreadyholds a lock on X

  11. Conversion of Locks • UPGRADING: • If T is the only transaction holding a read lock on X at the time it issues the write_lock(X) operation, the lock can be upgraded; otherwise, the transaction must wait. • DOWNGRADING: • It is also possible for a transaction T to issue a write_lock(X) and then later on to downgrade the lock by issuing a read_lock(X) operation.

  12. BUT ... • Multiple-mode locking isn’t enough to guarantee serializability. • If locks are let go too early, you can create problems. • This usually happens when a lock is released before another lock is acquired. • See Figure 20.3 in the book.

  13. T1 T2 read_lock(X) read_item(X) unlock(X) write_lock(Y) read_item(Y) Y:=X+Y write_item(Y) unlock(Y) read_lock(Y) read_item(Y) unlock(Y) write_lock(X) read_item(X) X:=X+Y write_item(X) unlock(X)

  14. Two-phase Locking (2PL) • A transaction is said to follow the two-phase locking protocol if all locking operations (read_lock, write_lock) precede the first unlock operation in the transaction. • transaction divided into 2 phases: • growing - new locks acquired but none released • shrinking - existing locks released but no new ones acquired • Rules: • if T wants to read an object it needs a read_lock • if T wants to write an object, it needs a write_lock • once a lock is released, no new ones can be acquired.

  15. Observations • 2PL protocol guarantees serializability • any schedule of transactions that follow 2PL will be serializable • we therefore do not need to test a schedule for serializability • But, it may limit the amount of concurrency since transactions may have to hold onto locks longer than needed.

  16. VARIATIONS OF 2-PHASE LOCKING • BASIC Two-phase Locking • As described above • CONSERVATIVE 2PL (OR STATIC 2PL) • Requires a transaction to lock all the items it accesses before the transaction begins execution, by pre-declaring its read-set and write-set. • If any of the pre-declared items needed cannot be locked, the transaction does not lock any item; instead, it waits until all the items are available for locking. • POLICY - Lock all that you need before reading or writing. Transaction is in shrinking phase after it starts. • PROPERTY: Conservative 2PL is a deadlock-free protocol • PRACTICAL : Difficult to use because of difficulty in pre-declaring the read-set and write-set.

  17. VARIATIONS OF 2-PHASE LOCKING • STRICT 2PL: • the most popular variation of 2PL is strict 2PL, which guarantees strict schedules. In this variation, a transaction T does not release any of its exclusive (write) locks until after it commits or aborts. Hence, no other transaction can read or write an item that is written by T unless T has committed, leading to a strict schedule for recoverability. • POLICY - Release write locks only after terminating. Transaction is in expanding phase until it ends (may release some read locks before commit). • PROPERTY: NOT a deadlock-free protocol • PRACTICAL : Possible to enforce and desirable due to recoverability. • RIGOROUS 2PL: • transaction T does not release any of its locks (exclusive or shared) until after it commits or aborts Behaves similar to Strict 2 PL except it is more restrictive, but easier to implement since all locks are held till commit.

  18. LIMITATIONS OF 2 PL • The two-phase locking protocol guarantees serializability (that is, every schedule that is permitted is serializable), but it does not permit all possible serializable schedules (that is, some serializable schedules will be prohibited by the protocol). • Use of locks can cause two additional problems: deadlock and starvation.

  19. Deadlock T1 T2 read_lock(Y) read(Y) T1 read_lock(X) read(X) T2 write_lock(X) write_lock(Y)

  20. Deadlock Prevention • Require that every transaction lock all the items it needs in advance. • If any items cannot be obtained, then no items are locked. • Further limits concurrency!

  21. References • Chapter No 20: Elmasri • Chapter No 14: C. J. Date

More Related