Oracle SAVEPOINT

SQL> INSERT INTO AUTHOR
2 VALUES ('A11l', 'john',
3 'garmany', '123-345-4567',
4 '1234 here st', 'denver',
5 'CO','90204', '9999');

1 row created.

SQL> savepoint in_author;

Savepoint created.

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

SQL> savepoint in_book_author;

Savepoint created.

SQL> INSERT INTO BOOK
2 VALUES ('B130', 'P002', 'easy oracle sql',
3 'miscellaneous', 9.95, 1000, 15, 0, '',
4 to_date ('02-20-2005','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

5. transaction A).

6. 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.