DB2 Locking

When many programs use the same DB2 table at the same time, DB2 has to stop them from stepping on each other's data. Locking is the mechanism that does this.

What is locking

• Locking stops one program from reading or changing data that another program is in the middle of changing.
• DB2 takes locks automatically whenever your SQL touches a row, page or table. You do not code locks in normal programs.
• Locking is what keeps the database consistent when hundreds of users and batch jobs run together. This is called concurrency control.
• A real lock is managed by an external component called IRLM (Inter Region Lock Manager). When DB2 locks a page internally without going to IRLM, it is called a latch.

Lock size (granularity)

• DB2 supports locking at the row, page, table and tablespace level. The lock size can be ROW, PAGE, TABLE or TABLESIZE.
• Small locks (row, page) let more programs work at the same time, but DB2 has to manage more locks.
• Big locks (table, tablespace) are cheaper to manage, but they block other programs from the whole object.
• You control the lock size with the LOCKSIZE parameter while creating the tablespace.
• You can also lock a whole table yourself with the LOCK TABLE statement when you need exclusive control, for example before a big batch update.
• Example:-
  LOCK TABLE EMP IN EXCLUSIVE MODE; -- create a tablespace with row level locking CREATE TABLESPACE TSROW IN DBONE USING STOGROUP SGONE PRIQTY 100 SECQTY 50 LOCKSIZE ROW;

Lock duration

• How long a lock is held is controlled by the ACQUIRE, RELEASE and ISOLATION parameters of the BIND command.
• ACQUIRE(USE) / RELEASE(COMMIT) is the common setting. The lock is taken when the table is first used and released when the program issues a COMMIT or ROLLBACK.
• ACQUIRE(ALLOCATE) / RELEASE(DEALLOCATE) takes the lock when the plan starts and keeps it until the program ends. Use it only when you must hold locks across commits.
• Short transactions with frequent COMMITs release locks early and keep other programs from waiting.