Edit

IS633 Discussion 10

Postach.io IS633 published

So I’m trying to get a masters. For the 2018 summer session I’m enrolled in a Relational Database class where we get to work with the Oracle Database! <3

This is one of the open discussions, toward the end of the course, on transactions (units of program execution) and concurrency (running multiple transactions in parrellel).

NOTE Just because I work for Oracle doesn’t mean I’m right about all our products. I’ll try to be but I am only human! Also if you can point out how I can make my answers better please let me know, if only for my edification :)

Question 1:

Please decide whether the following schedule gives correct result and explain why. Initially in the database A=B=1000

step T1 (Timmy) T2 (Tommy)
1 Read(A)
2 A := A – 500
3 Read(A)
4 A:=A-100
5 Write(A)
6 Write(A)
7 Read(B)
8 B : = B + 500
9 Write(B)

To make this more relateable let me try to explain it as a story.

  1. Timmy reads the value of A = 1k
  2. Timmy sets A = 500
  3. Tommy reads the value of A = 1k (timmy hasn’t committed!)
  4. Tommy sets A = 900
  5. Timmy writes A := 500
  6. Tommy writes A := 900 (oops! he overwrote Timmy!!!)
  7. Timmy sets B = 1.5k
  8. Timmy writes B := 1.5k

Now Timmy thinks that A is still 500, alas their database (obviously not an oracle database) has fallen prey to what is known as The Lost Update Problem. This is undesirable behavior for a DBMS and violates consistency, thus giving an incorrect result [1].

To solidify our understanding let’s review ACID [2];

  • Atomicity = when a transaction is executed it’s all or nothing
  • Consistency = individual transactions behave as if they are executed sequentually, even in the event of concurrency
  • Isolation = concurrent transactions do not interfere
  • Durability = changes persist even in the event of failure

In steps 5 and 6 of my story timmy and tommy’s transactions did not execute as if they were done sequentially, as if you made both of them sequential blocks the result for A would be 400 (=1000-500-100).

References

[1] Ramez Elmasri, Shamkant B. Navathe, Fundamentals of Database Systems, 7th Edition, Pearson, ISBN-10: 0133970779 • ISBN-13: 9780133970777, 2016.

[2] Dr. Zhiyuan Chen. transactions.pptx. Presentation given for class IS 633, Database Management Systems, at University of Maryland Baltimore County. Summer 2018.

%23%20IS633%20Discussion%2010%0A@%28Postach.io%29%5BIS633%2C%20published%5D%0A%0ASo%20I%27m%20trying%20to%20get%20a%20masters.%20For%20the%202018%20summer%20session%20I%27m%20enrolled%20in%20a%20Relational%20Database%20class%20where%20we%20get%20to%20work%20with%20the%20Oracle%20Database%21%20%3C3%0A%0AThis%20is%20one%20of%20the%20open%20discussions%2C%20toward%20the%20end%20of%20the%20course%2C%20on%20transactions%20%28units%20of%20program%20execution%29%20and%20concurrency%20%28running%20multiple%20transactions%20in%20parrellel%29.%20%0A%0A**NOTE**%20Just%20because%20I%20work%20for%20Oracle%20doesn%27t%20mean%20I%27m%20right%20about%20all%20our%20products.%20I%27ll%20try%20to%20be%20but%20I%20am%20only%20human%21%20Also%20if%20you%20can%20point%20out%20how%20I%20can%20make%20my%20answers%20better%20please%20let%20me%20know%2C%20if%20only%20for%20my%20edification%20%3A%29%20%0A%0A%23%23%20Question%201%3A%20%0A**Please%20decide%20whether%20the%20following%20schedule%20gives%20correct%20result%20and%20explain%20why.%20Initially%20in%20the%20database%20A%3DB%3D1000**%0A%0Astep%20%7C%20T1%20%28Timmy%29%20%7C%20T2%20%28Tommy%29%0A-%7C-%7C-%0A1%20%7C%20Read%28A%29%20%7C%0A2%20%7C%20A%20%3A%3D%20A%20%u2013%20500%20%7C%20%0A3%20%7C%7C%20Read%28A%29%0A4%20%7C%7C%20A%3A%3DA-100%0A5%20%7C%20Write%28A%29%20%7C%20%0A6%20%7C%7C%20Write%28A%29%0A7%20%7C%20Read%28B%29%20%7C%20%0A8%20%7C%20B%20%3A%20%3D%20B%20+%20500%20%7C%20%0A9%20%7C%20Write%28B%29%20%7C%0A%0A%20%0ATo%20make%20this%20more%20relateable%20let%20me%20try%20to%20explain%20it%20as%20a%20story.%20%0A1.%20Timmy%20reads%20the%20value%20of%20A%20%3D%201k%0A2.%20Timmy%20sets%20A%20%3D%20500%0A3.%20Tommy%20reads%20the%20value%20of%20A%20%3D%201k%20%28timmy%20hasn%27t%20committed%21%29%0A4.%20Tommy%20sets%20A%20%3D%20900%0A5.%20Timmy%20writes%20A%20%3A%3D%20500%0A6.%20Tommy%20writes%20A%20%3A%3D%20900%20%28oops%21%20he%20overwrote%20Timmy%21%21%21%29%0A7.%20Timmy%20sets%20B%20%3D%201.5k%0A8.%20Timmy%20writes%20B%20%3A%3D%201.5k%0A%0ANow%20Timmy%20thinks%20that%20A%20is%20still%20500%2C%20alas%20their%20database%20%28obviously%20not%20an%20oracle%20database%29%20has%20fallen%20prey%20to%20what%20is%20known%20as%20**The%20Lost%20Update%20Problem**.%20This%20is%20undesirable%20behavior%20for%20a%20DBMS%20and%20violates%20**consistency**%2C%20thus%20giving%20an%20**incorrect%20result**%20%5B1%5D.%0A%0ATo%20solidify%20our%20understanding%20let%27s%20review%20**ACID**%20%5B2%5D%3B%0A-%20**Atomicity**%20%3D%20when%20a%20transaction%20is%20executed%20it%27s%20all%20or%20nothing%0A-%20**Consistency**%20%3D%20individual%20transactions%20behave%20as%20if%20they%20are%20executed%20sequentually%2C%20even%20in%20the%20event%20of%20concurrency%0A-%20**Isolation**%20%3D%20concurrent%20transactions%20do%20not%20interfere%0A-%20**Durability**%20%3D%20changes%20persist%20even%20in%20the%20event%20of%20failure%0A%20%0AIn%20steps%205%20and%206%20of%20my%20story%20timmy%20and%20tommy%27s%20transactions%20did%20not%20execute%20as%20if%20they%20were%20done%20sequentially%2C%20as%20if%20you%20made%20both%20of%20them%20sequential%20blocks%20the%20result%20for%20A%20would%20be%20400%20%28%3D1000-500-100%29.%20%0A%0A%0A%23%23%20References%20%0A%0A%5B1%5D%20Ramez%20Elmasri%2C%20Shamkant%20B.%20Navathe%2C%20*Fundamentals%20of%20Database%20Systems%2C%207th%20Edition*%2C%20Pearson%2C%20ISBN-10%3A%200133970779%20%u2022%20ISBN-13%3A%209780133970777%2C%202016.%0A%0A%5B2%5D%20Dr.%20Zhiyuan%20Chen.%20*transactions.pptx*.%20Presentation%20given%20for%20class%20IS%20633%2C%20Database%20Management%20Systems%2C%20at%20University%20of%20Maryland%20Baltimore%20County.%20Summer%202018.%20%0A