COMMIT and ROLLBACK

COMMIT and ROLLBACK end a unit of work. COMMIT saves all changes since the last commit. ROLLBACK throws them all away. Every DML change hangs on one of these two.

Unit of work

• A unit of work starts at the first SQL statement and ends at COMMIT or ROLLBACK.
• All INSERTs, UPDATEs, and DELETEs in the unit either all survive (COMMIT) or all disappear (ROLLBACK).
• Other programs cannot see your uncommitted changes. They see them only after your COMMIT.
• Locks on changed rows are held until COMMIT or ROLLBACK, so keep units of work short.

COMMIT

• COMMIT makes every change since the last commit permanent. It cannot be undone.
• COMMIT releases all locks, so other programs waiting on your rows can proceed.
• After COMMIT, open cursors are closed unless declared WITH HOLD.
• Batch programs typically commit every few thousand rows to bound restart work.

ROLLBACK

• ROLLBACK throws away every change since the last commit and releases the locks.
• Use ROLLBACK when any statement in the unit fails - never commit a half-finished unit.
• DB2 also rolls back automatically on deadlock (SQLCODE -911) and on program abend.
• After ROLLBACK the database looks exactly as it did at the last commit.

Example

• Transfer logic: both updates must succeed together, or neither:-