How to test for 2-phase commit behavior on a stored procedure?

Question

this particular stored procedure does an archive purge:
1) select tokens from transactional db
2) put tokens in temp table
3) loop through tokens:
3.1)using tokens from temp table, retrieve data from transactional tables and insert to tables in a separate archive db (via federation)
3.2) COMMIT inserts.
3.3) then using same token this time delete the data from the transactional
3.4) COMMIT deletes.

2 phase commit allows us to have just one commit at the end of the loop

my question is how to simulate scenarios to make proc fail in the insert phase or delete phase? this is to ensure that even though run has failed, data retains integrity - no half-processed tokens or such.

Answer 1

to force a run-time error, I usually put in a SELECT 0/0 in the code. just put this in before the COMMIT of your choice and watch the fireworks that result!

Answer 2

If you have unique keys involved you can put a record in place that would cause a duplicate key violiation.

Answer 3

Hope this will help somebody else! I just recently found that the best way was via signals. In the middle of the delete phase, I put in an error signal so process would fail on that token and exit the loop, so it should rollback whatever it has inserted in the insert phase for that token.

DECLARE rollback_on_token_101 CONDITION FOR SQLSTATE '99001';

inside the loop middle of delete phase

IF TOKEN_SUCCESS_COUNT=100 THEN
  SIGNAL rollback_on_token_101 
  SET MESSAGE_TEXT = 'rolling back on mid-delete phase on token # 101 ';
END IF;