Explain the difference between a nonrepeatable read and a phantom. Specifically, give an example of a schedule of the SQL statements of two transactions that illustrate the two cases. Specify an isolation level in each case.
What will be an ideal response?
To illustrate a non-repeatable read consider two transactions running at READ COMMITTED:
SELECT1(get sum of balances in all of Mary’s accts);
UPDATE2(post interest in all accts);
Commit2;
SELECT1(get sum of balances in all of Mary’s accts);
To illustrate a phantom consider two transactions running at REPEATABLE READ:
SELECT1(get sum of balances in all of Mary’s accts);
INSERT1(newaccount for Mary);
COMMIT2;
SELECT1(get sum of balances in all of Mary’s accts);
In the first case, the second SELECT returns the same set of tuples as the first, but their values have changed. In the second case, the second SELECT returns a newtuple that satisfies the read predicate, but the values of the old tuples that satisfy that predicate have not changed.
Computer Science & Information Technology
You might also like to view...
When working on a slide show, consider the ________ as you add audio to your presentation
A) technology B) volume C) user D) audience
Computer Science & Information Technology
Redirect standard output of cat to create a file named days that holds the names of the days of the week in chronological order, one per line. Do not redirect standard input to cat; it will come from the keyboard. Remember to press CONTROL-D on a line by itself to exit from cat.
What will be an ideal response?
Computer Science & Information Technology