1 / 37

Introduction to Data Management CSE 344

Introduction to Data Management CSE 344. Lecture 23: Transactions. Announcements. HW6 is due tonight Webquiz due next Monday HW7 is posted: Some Java programming required Plus connection to SQL Azure Please attend the quiz section for more info!. Outline.

shaw
Download Presentation

Introduction to Data Management CSE 344

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. Introduction to Data ManagementCSE 344 Lecture 23: Transactions CSE 344 - Winter 2013

  2. Announcements • HW6 is due tonight • Webquiz due next Monday • HW7 is posted: • Some Java programming required • Plus connection to SQL Azure • Please attend the quiz section for more info! CSE 344 - Winter 2013

  3. Outline • Serial and Serializable Schedules (18.1) • Conflict Serializability (18.2) • Locks (18.3) [Start today and finish next time] CSE 344 - Winter 2013

  4. Review: Transactions • Problem: An application must perform several writes and reads to the database, as a unit • Solution: multiple actions of the application are bundled into one unit called Transaction • Turing awards to database researchers • Charles Bachman 1973 for CODASYL • Edgar Codd 1981 for relational databases • Jim Gray 1998 for transactions CSE 344 - Winter 2013

  5. Transactions in Applications BEGIN TRANSACTION [SQL statements] COMMIT or ROLLBACK (=ABORT) CSE 344 - Winter 2013

  6. Transaction PropertiesACID • Atomic • State shows either all the effects of txn, or none of them • Consistent • Txn moves from a state where integrity holds, to another where integrity holds • Isolated • Effect of txns is the same as txns running one after another (ie looks like batch mode) • Durable • Once a txn has committed, its effects remain in the database CSE 344 - Winter 2013

  7. Implementing ACID Properties • Isolation: • Achieved by the concurrency control manager (or scheduler) • We will discuss this today and in the next lecture • Atomicity • Achieved using a log and a recovery manager • Will not discuss in class (come to 444 instead) • Durability • Implicitly achieved by writing back to disk • Consistency • Implicitly guaranteed by A and I CSE 344 - Winter 2013

  8. Isolation: The Problem • Multiple transactions are running concurrentlyT1, T2, … • They read/write some common elementsA1, A2, … • How can we prevent unwanted interference ? • The SCHEDULER is responsible for that CSE 344 - Winter 2013

  9. Schedules A schedule is a sequenceof interleaved actions from all transactions CSE544 - Spring, 2012

  10. A and B are elements in the database t and s are variables in tx source code Example CSE 344 - Winter 2013

  11. A Serial Schedule CSE 344 - Winter 2013

  12. SerializableSchedule A schedule is serializable if it is equivalent to a serial schedule CSE544 - Spring, 2012

  13. A Serializable Schedule Notice: This is NOT a serial schedule CSE 344 - Winter 2013

  14. A Non-Serializable Schedule CSE 344 - Winter 2013

  15. How do We Know if a Schedule is Serializable? Notation T1: r1(A); w1(A); r1(B); w1(B) T2: r2(A); w2(A); r2(B); w2(B) Key Idea: Focus on conflicting operations CSE 344 - Winter 2013

  16. Conflicts • Write-Read – WR • Read-Write – RW • Write-Write – WW CSE544 - Spring, 2012

  17. Conflict Serializability Conflicts: Two actions by same transaction Ti: ri(X); wi(Y) Two writes by Ti, Tj to same element wi(X); wj(X) wi(X); rj(X) Read/write by Ti, Tj to same element ri(X); wj(X) CSE 344 - Winter 2013

  18. Conflict Serializability • A schedule is conflict serializable if it can be transformed into a serial schedule by a series of swappings of adjacent non-conflicting actions CSE 344 - Winter 2013

  19. Conflict Serializability Example: r1(A); w1(A); r2(A); w2(A); r1(B); w1(B); r2(B); w2(B) r1(A); w1(A); r2(A); r1(B); w2(A); w1(B); r2(B); w2(B) r1(A); w1(A); r1(B); r2(A); w2(A); w1(B); r2(B); w2(B) …. r1(A); w1(A); r1(B); w1(B); r2(A); w2(A); r2(B); w2(B) CSE 344 - Winter 2013

  20. Testing for Conflict-Serializability Precedence graph: • A node for each transaction Ti, • An edge from Ti to Tjwhenever an action in Ti conflicts with, and comes before an action in Tj • The schedule is serializableiff the precedence graph is acyclic CSE544 - Spring, 2012

  21. Example 1 r2(A); r1(B); w2(A); r3(A); w1(B); w3(A); r2(B); w2(B) 1 2 3 CSE544 - Spring, 2012

  22. Example 1 r2(A); r1(B); w2(A); r3(A); w1(B); w3(A); r2(B); w2(B) B A 1 2 3 This schedule is conflict-serializable CSE544 - Spring, 2012

  23. Example 2 r2(A); r1(B); w2(A); r2(B); r3(A); w1(B); w3(A); w2(B) 1 2 3 CSE544 - Spring, 2012

  24. Example 2 r2(A); r1(B); w2(A); r2(B); r3(A); w1(B); w3(A); w2(B) B A 1 2 3 B This schedule is NOT conflict-serializable CSE544 - Spring, 2012

  25. Scheduler • The scheduler is the module that schedules the transaction’s actions, ensuring serializability • How ? We discuss one technique in 344: • Locking! • Aka “pessimistic concurrency control” CSE 344 - Winter 2013

  26. Locking Scheduler Simple idea: • Each element has a unique lock • Each transaction must first acquire the lock before reading/writing that element • If the lock is taken by another transaction, then wait • The transaction must release the lock(s) CSE 344 - Winter 2013

  27. Implementation inVarious DBMSs • SQLite: one lock for entire database • SQL Server, DB2 = one lock per record • … and index entry, etc • Bottom line: Implementation of transactions varies between DBMSs • Read the documentation! • Some DBMSs don’t even use locking! CSE 344 - Winter 2013

  28. Let’s Study SQLite First • SQLite is very simple • More info: http://www.sqlite.org/atomiccommit.html CSE 344 - Winter 2013

  29. SQLite • Step 1: every transaction acquires a READ LOCK (aka "SHARED" lock) • All these transactions may read happily • Step 2: when one transaction wants to write • Acquire a RESERVED LOCK • This lock may coexists with many READ LOCKs • Writer txnmay write; others don't see updates • Reader txns continue to read • New readers accepted • No other txn is allowed a RESERVED LOCK

  30. SQLite • Step 3: writer transaction wants to commit • It needs exclusive lock, can’t coexists with read locks • Acquire a PENDING LOCK • Coexists with READ LOCKs • But no new READ LOCKS are accepted now • Wait for all read locks to be released • Acquire the EXCLUSIVE LOCK • All updates are written permanently to the database CSE 344 - Winter 2013

  31. SQLite first update commit requested no more read locks begin transaction RESERVEDLOCK PENDINGLOCK EXCLUSIVELOCK None READLOCK commit executed CSE 344 - Winter 2013

  32. SQLite Demo create table r(a int, b int); insert into r values (1,10); insert into r values (2,20); insert into r values (3,30); CSE 344 - Winter 2013

  33. Demonstrating Locking in SQLite T1: begin transaction; select * from r; -- T1 has a READ LOCK T2: begin transaction; select * from r; -- T2 has a READ LOCK CSE 344 - Winter 2013

  34. Demonstrating Locking in SQLite T1: update r set b=11 where a=1; -- T1 has a RESERVED LOCK T2: update r set b=21 where a=2; -- T2 asked for a RESERVED LOCK: DENIED CSE 344 - Winter 2013

  35. Demonstrating Locking in SQLite T3: begin transaction; select * from r; commit; -- everything works fine, could obtain READ LOCK CSE 344 - Winter 2013

  36. Demonstrating Locking in SQLite T1: commit; -- SQL error: database is locked -- T1 asked for PENDING LOCK -- GRANTED -- T1 asked for EXCLUSIVE LOCK -- DENIED CSE 344 - Winter 2013

  37. Demonstrating Locking in SQLite T3': begin transaction; select * from r; -- T3 asked for READ LOCK-- DENIED (due to T1) T2: commit; -- releases the last READ LOCK CSE 344 - Winter 2013

More Related