IS633 Discussion 10
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 :)
Please decide whether the following schedule gives correct result and explain why. Initially in the database A=B=1000
|step||T1 (Timmy)||T2 (Tommy)|
|2||A := A – 500|
|8||B : = B + 500|
To make this more relateable let me try to explain it as a story.
- Timmy reads the value of A = 1k
- Timmy sets A = 500
- Tommy reads the value of A = 1k (timmy hasn’t committed!)
- Tommy sets A = 900
- Timmy writes A := 500
- Tommy writes A := 900 (oops! he overwrote Timmy!!!)
- Timmy sets B = 1.5k
- 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 .
To solidify our understanding let’s review ACID ;
- 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).
 Ramez Elmasri, Shamkant B. Navathe, Fundamentals of Database Systems, 7th Edition, Pearson, ISBN-10: 0133970779 • ISBN-13: 9780133970777, 2016.
 Dr. Zhiyuan Chen. transactions.pptx. Presentation given for class IS 633, Database Management Systems, at University of Maryland Baltimore County. Summer 2018.