Transcript Link to Slides

SQL – Rollback
What if we want to abort a transaction?
What if the power fails midway through a transaction?
What if an exception / error occurs?
What if the disk runs out of space?
If you can't complete the transaction, you need to undo it
(atomicity)!
You do that with a rollback.
Rollback
A rollback can occur for many reasons, some from the
database and some from users.
A rollback is the only way to 'fix' some problems.
When a rollback occurs, all the modifications to the database
since the start of the transaction need to be undone.
CREATE TABLE students (name TEXT);
BEGIN TRANSACTION;
INSERT INTO students VALUES ('Josh');
ROLLBACK TRANSACTION;
SELECT * FROM students; -- Empty
http://www.thebriman.com/2014/10/for-love-of-all-thats-good-and-right-in.html
Transaction 1
Transaction 2
Transaction 3
xl1(A); w1(A);
sl1(B); u1(A);
Is this schedule legal?
sl2(A); r2(A);
xl2(C); w2(C);
u2(C);
sl3(C); r3(C);
xl3(D); w3(D);
r2(A); u2(A);
u3(D); u3(C);
r1(B); u1(B);
Yes
No
Illegal but unenforced
Define "Legal"
Transaction 1
Transaction 2
Transaction 3
Which transactions need to
be rolled-back?
xl1(A); w1(A);
sl1(B); u1(A);
sl2(A); r2(A);
xl2(C); w2(C);
u2(C);
sl3(C); r3(C);
xl3(D); w3(D);
r2(A); u2(A);
u3(D); u3(C);
ROLLBACK!!!
Transaction 1
Transaction 2
Transaction 3
None
What about isolation?
Dirty Reads:
◦ If a transaction has read modified (uncommitted) data from a transaction is
being rolled back.
◦ Solution: rollback that transaction as well.
This can lead to cascading rollbacks.
But the database doesn't guarantee that transactions will always
commit.
◦ Just that transactions will either commit or be rolled back.
How to avoid cascading rollbacks?
Enforce strict two-phase locking:
◦ Locks are only released at the moment
of commit (or rollback), never before.
In the schedule from before, the
unlocks (u1(A) and u2(C)) allowed
other transactions to read
uncommitted data, which caused the
other transactions to have to be rolledback as well.
Transaction 1
Transaction 3
xl1(A); w1(A);
sl1(B); u1(A);
sl2(A); r2(A);
xl2(C); w2(C);
u2(C);
sl3(C); r3(C);
xl3(D); w3(D);
There is a tradeoff between
performance and and the possibility of
cascading rollbacks.
◦ Strict two-phase locking slows down
throughput, but stops cascades.
Transaction 2
r2(A); u2(A);
u3(D); u3(C);
ROLLBACK!!!
Implementing Rollbacks
How do you undo modifications to a database?
Two methods:
◦ Keep a copy of the old data (and replace the changed version with the original
on rollback)
◦ Make your modifications to the database on a copy (and replace the old with
the new on commit)
It is up to you which you choose to do
◦ (good luck with Project 4  )