I think I have a misunderstanding about how to use Savepoints. Perhaps someone can clear it up for me. I present my example as what I am trying to do, and what I have experienced.
My app is doing a certain procedure. Before that procedure (and associated DB operations) I create a savepoint. During that procedure, I initiate a select for update, which creates a number of locks:
lock1 - duration=transaction, class=row, type=intent row=big number
lock2 - duration=transaction, class=row, type=WriteNoPK row=big number
Should that java procedure succeed, the associated DB transaction is completed via a commit. However, if the java procedure fails, I want also to rollback any associated DB operations. I have been attempting this by:
conn.rollback(mySavepoint);
However, this has not been releasing the table locks created (above) by the DB operations (that I thought I just rolled back by conn.rollback(mySavepoint);)
I have tested this behaviour with two databases: Sybase, and Derby.
Why is this the case?
Do I really need to commit after the conn.rollback(mySavepoint) ??? It just seems a bit counter-intuitive.