1 / 17

Lecture 8 : Transactions & Concurrency

MSc IT UFIE8K-15-M Data Management Prakash Chatterjee Room 3P16 prakash.chatterjee@uwe.ac.uk http://www.cems.uwe.ac.uk/~pchatter/2010/dm. Lecture 8 : Transactions & Concurrency. Transactions.

aristotle-bryan
Download Presentation

Lecture 8 : Transactions & Concurrency

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. MSc IT UFIE8K-15-MData ManagementPrakash ChatterjeeRoom 3P16prakash.chatterjee@uwe.ac.ukhttp://www.cems.uwe.ac.uk/~pchatter/2010/dm Lecture 8 : Transactions & Concurrency

  2. Transactions • ‘Transaction’ in a DBMS means an elemental unit of work which is either completed in full or fails totally • DB Transactions are not the same as a user transaction such as ‘place an order’ • Each transaction has • begin transaction • some work - reads and writes to the db • either COMMIT (make changes permanent) • or ROLLBACK (destroy all evidence of work) UFIE8K-15-M Data Management 2010

  3. ACID • A Atomicity • Transaction is indivisible unit of work - can’t partly succeed • C Consistency • Transaction (failed or completed) must leave DB in a consistant state • I Isolation • Each transaction must appear to run in isolation to any other transactions • D Durability • Work done must be permanent UFIE8K-15-M Data Management 2010

  4. Implementing ACID • (A) Atomicity • system must be able to undo work if transaction fails - e.g. due to failed integrity constraint • system could record the before state of an record and restore these when rollback required • (C) Consistency • fail if an integrity or transaction constraint violated • (I) Isolation • concurrency problem >> • (D) Durability • DB backup and recovery, transaction logging UFIE8K-15-M Data Management 2010

  5. Concurrency Control • If transactions could be serialised - executed one at a time - each would execute in Isolation • but this would slow the system down - most of the time is spent waiting for disk access • If not prevented, interaction between transactions can cause anomalies UFIE8K-15-M Data Management 2010

  6. T1 bot read(x) x=x+1 write(x) commit if x=2 at start, it should be 4 at end T2 bot read(x) x=x+1 write(x) commit but its only 3! Lost Update UFIE8K-15-M Data Management 2010

  7. T1 bot read(x) x=x+1 write(x) abort x should be 3 T2 bot read(x) x=x+1 write(x) commit but it is 4! Dirty Read UFIE8K-15-M Data Management 2010

  8. T1 bot read(x) read(x) commit T1 sees different values of x during its execution T2 bot read(x) x=x+1 write(x) commit Inconsistent Read UFIE8K-15-M Data Management 2010

  9. x+y must = 100 T1 bot read(y) read(x) ? x+y=90 commit T2 bot read(y) read(x) x=x -10 write(x) y=y+10 write(y) commit now x+y = 100 again Ghost update UFIE8K-15-M Data Management 2010

  10. Scheduling • A schedule is a sequence of operations (reads or writes) from multiple transactions • Reads and writes are assumed to be atomic themselves • The Scheduler tries to create a schedule which preserves Isolation • Serial schedule puts one transaction after another, but this will be inefficient • Need to find equivalent, shorter schedules UFIE8K-15-M Data Management 2010

  11. Pessimistic/Optimistic • Pessimistic • assume transactions will interact and prevent it • Locking • Timestamping • Optimistic • assume transactions will NOT interact but take action if they do UFIE8K-15-M Data Management 2010

  12. Locking • Read lock (shared lock) • any number can read but no one can write • readers need counting • Write lock (exclusive lock) • no one else can read or write • Unlock - drop the lock • Transaction waits if resource already locked • Locks held in DB - lock table • Lock granularity • best if only a record is locked but lock table large UFIE8K-15-M Data Management 2010

  13. Two-phase locking • Discipline on transactions to ensure schedule is serialiable • Growing phase: • Transaction must acquire all its locks in one phase • Lock level can be escalated ( read > write) • Shrinking phase • Transaction must release all its locks in the second phase • Lock level can reduce (write > read) • Can’t acquire a lock after releasing a lock UFIE8K-15-M Data Management 2010

  14. T1 bot wlock(x) read(x) x=x+1 write(x) unlock(x) commit T2 bot wlock(x) wait wait wait read(x) x=x+1 write(x) commit Lost Update with Locking UFIE8K-15-M Data Management 2010

  15. T1 bot wlock(x) read(x) x=x+1 write(x) unlock(x) abort x should be 3 but it is still 4 ! T2 bot wlock(x) wait wait wait read(x) x=x+1 write(x) unlock(x) commit Dirty Read with locking UFIE8K-15-M Data Management 2010

  16. Strict Two-phase locking • Locks can only be released after commit • Deadlock • T1 wlocks(x) and waits to wlock(y) • T2 wlocks(y) and waits to wlock(x) UFIE8K-15-M Data Management 2010

  17. Deadlock • Deadlock • T1 wlocks(x) and waits to wlock(y) • T2 wlocks(y) and waits to wlock(x) • Approaches • timeout - lock expires so transaction aborts • deadlock detection - identifying deadlocks and killing one transaction UFIE8K-15-M Data Management 2010

More Related