How will you keep the locks on the resources even after a ROLLBACK?

DB2DatabaseBig Data Analytics

DB2 places locks on the resources like table, tablespace, etc., when any application is using that resource. In a COBOL-DB2 program, if we are modifying or deleting data from a table then DB2 places locks. When we give the ROLLBACK statement to revert the changes made in the database, all the locks held by the DB2 are released by default.

If we want to keep a lock on resources even after rollback, then we have to use ON ROLLBACK RETAIN LOCKS instead of ROLLBACK.

For example, consider a cursor ORDER_CUR which is declared with FOR UPDATE OF clause. Therefore, as soon as the cursor will be opened, the locks will be acquired on all the qualifying rows.

In case of above flow, we have used “SAVEPOINT A ON ROLLBACK RETAIN LOCKS”. Therefore, the lock which was placed on qualifying rows when ORDER_CUR was opened will not be released once the rollback restore the database state to savepoint A.

Published on 30-Nov-2020 13:53:24