Oracle SAVEPOINT

A SAVEPOINT is a marker within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINTs to mark different points within the transaction. If we encounter an error, we can rollback to a SAVEPOINT or all the way back to the beginning of the transaction.

SQL> INSERT INTO AUTHOR
2 VALUES ('X123', 'mister',
3 'popo', '62-274-4567',
4 'jl sudirman', 'jakarta',
5 'JKBKJ','10001', '9999');

1 row created.

SQL> savepoint in_author;

Savepoint created.

SQL> INSERT INTO BOOK_AUTHOR VALUES ('X123', 'B130', .20);
1 row created.

SQL> savepoint in_book_author;

Savepoint created.

SQL> INSERT INTO BOOK
2 VALUES ('B130', 'P002', 'how to make love',
3 'miscellaneous', 9.95, 1000, 15, 0, '',
4 to_date ('02-20-2007','MM-DD-YYYY'));
1 row created.

SQL> rollback to in_author;

Rollback complete.

In the example above, I inserted a row into the AUTHOR table and created a SAVEPOINT called in_author. Next, I inserted a row into the book_author table and created another SAVEPOINT called in_book_author. Finally, I inserted a row in the BOOK table. I then issued a ROLLBACK to in_author.

Row locks are NOT released by SETTING a savepoint. Row locks are release by one of three events - commit, rollback, or rollback to savepoint. My argument is that Oracle does not handle the latter well.

1. If transaction A updates row 1, sets a savepoint, and updates row 2 (but does not as yet commit). Then transaction B wishes to update row 2.
2. Transaction B will correctly block on the commit or rollback of transaction A.
3. However, if transaction A does a rollback to savepoint, it will continue to have hold a lock on row 1 (but not row 2).
4. In fact a third transaction can now update row 2 (as it's not locked by transaction A).
5. However, our poor transaction B, is still waiting (incorrectly) for transaction A to commit or rollback.

The problem is that Oracle provides no way of waiting on a row - you can only wait on a transaction - and sometimes transactions (through the use of rollback to savepoint) release rows WITHOUT committing or aborting.

Imagine what could happen when by issuing a savepoint the lock of an standing transaction would be released, and another transaction would change 'my' row, and then I do a full rollback.