DB2 Locks Helpful Points to Read now

1. What are the different lock levels available?

PAGE, TABLE , TABLESPACE and ROW (new lock from DB2 v10)

2. How does DB2 determines what lock-size to use (Best Technic)?

  • Based on lock-size given while creating Table space
  • Programmer can direct the DB2 what lock-size to use
  • If lock size ‘ANY’ is specified, DB2 uses lock-size for PAGE

3. What are the disadvantages of PAGE level lock?

High resource utilization, if large updates to be done. (This lock causes high resource utilization).

4. What is lock escalation?

Promotes PAGE lock size to Table or Table Space lock-size, when a transaction has acquired more locks than specified in NUMLKTS. Locks  should be taken on objects in single tablespace for escalation to happen

5. What are the various locks available?


6. Can I use lock on a View?

No, this is not possible. But, it applies lock on base table.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.