About Oracle SAVEPOINTA SAVEPOINT is a er within a transaction that allows for a partial rollback. As changes are made in a transaction, we can create SAVEPOINTs to 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 ('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.
About Oracle SAVEPOINTA SAVEPOINT is a er within a transaction that allows for a partial rollback.
As changes are made in a transaction, we can create SAVEPOINTs to 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 ('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.