1 / 10

CS3223 Tutorial 10

CS3223 Tutorial 10. Fan Qi fan.qi@nus.edu.sg Database Lab 1, com1 #01-08. Transaction Management. ACID Atomicity : Commit or Abort Consistency: DB initial-end state is consistent Isolation: one Xact is unaware of other Xacts Durability: Once commit, forever commit

nerea-case
Download Presentation

CS3223 Tutorial 10

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. CS3223 Tutorial 10 Fan Qi fan.qi@nus.edu.sg Database Lab 1, com1 #01-08

  2. Transaction Management • ACID • Atomicity : Commit or Abort • Consistency: DB initial-end state is consistent • Isolation: one Xact is unaware of other Xacts • Durability: Once commit, forever commit • Recoverable Schedule • If reads from , then should commit after • Ensures cascade aborting can be performed

  3. Transaction Management • Cascadeless Schedule • Every read is from a committed value or from DB • Strict Schedule • If one object is written by a transaction, it is not concurrently read/write by other transactions • Recovery can be efficiently done by before-image

  4. Protocol Implementation • 2PL & Strict 2PL • Gain all the locks before release • Hold all locks before commit/abort • Multi-granular Locking • Four locks available, IS, IX, S, X • To obtain S or IS lock on a node, must already hold IS or IX lock on parentnode • To obtain X or IX lock on a node, must already hold IX lock on parent node

  5. Question 1 • R1(X), R2(X), W1(X), W2(X) • W1(X), R2(Y), R1(Y), R2(X) • R1(X), R2(Y), W3(X), R2(X), R1(Y) • R1(X), R1(Y), W1(X), R2(Y), W3(Y), W1(X), R2(Y) • R1(X), W2(X), W1(X), Abort2, Commit1 • R1(X), W2(X), W1(X), Commit2, Commit1 • W1(X), R2(X), W1(X), Abort2, Commit1 • W1(X), R2(X), W1(X), Commit2, Commit1 • W1(X), R2(X), W1(X), Commit2, Abort1 • R2(X), W3(X), Commit3, W1(Y), Commit1, R2(Y), W2(Z), Commit2 • R1(X), W2(X), Commit2, W1(X), Commit1, R3(X), Commit3 • R1(X), W2(X), W1(X), R3(X), Commit1, Commit2, Commit3

  6. Question 2 • R1(X), R2(X), W1(X), W2(X) • W1(X), R2(Y), R1(Y), R2(X) • R1(X), R2(Y), W3(X), R2(X), R1(Y) • R1(X), R1(Y), W1(X), R2(Y), W3(Y), W1(X), R2(Y) • R1(X), W2(X), W1(X), Abort2, Commit1 • R1(X), W2(X), W1(X), Commit2, Commit1 • W1(X), R2(X), W1(X), Abort2, Commit1 • W1(X), R2(X), W1(X), Commit2, Commit1 • W1(X), R2(X), W1(X), Commit2, Abort1 • R2(X), W3(X), Commit3, W1(Y), Commit1, R2(Y), W2(Z), Commit2 • R1(X), W2(X), Commit2, W1(X), Commit1, R3(X), Commit3 • R1(X), W2(X), W1(X), R3(X), Commit1, Commit2, Commit3

  7. Question 2 (Examples) • failed 2PL! • 2PL! • Not 2SPL, why? • failed 2PL! • ), failed 2PL!

  8. Question 3 Database | File | Page | Record • Read record P1200:5. • Read records P1200: 98 through P1205:2. • Read all (records on all) pages in file F1. • Read pages P500 through P520. • Read pages P10 through P980. • Read all pages in F1 and (based on the values read) modify 10 pages. • Delete record P1200:98. (This is a blind write.) • Delete the first record from each page. (Again, these are blind writes.) • Delete all records.

  9. Question 3 (Examples) Database • Read records P1200: 98 through P1205:2 • D IS • IS • P1200 IS, P1205 IS, P1201-P1204 S • P1200:98-100 S, P1205:1-2 S • Delete record P1200:98 • D IX • IX • P1200 X • Delete the first record from each page • D IX • IX, IX • P1 X, P2 X 1,1000 1001,2000 File 1 File 2

  10. Thank you! All the best for your exams!

More Related