How access paths affect locks

The access path that DB2® uses can affect the mode, size, and even the object of a lock.

For example, an UPDATE statement using a table space scan might need an X lock on the entire table space. If rows to be updated are located through an index, the same statement might need only an IX lock on the table space and X locks on individual pages or rows.

If you use the EXPLAIN statement to investigate the access path chosen for an SQL statement, then check the lock mode in column TSLOCKMODE of the resulting PLAN_TABLE. If the table resides in a nonsegmented or universal table space, or is defined with LOCKSIZE TABLESPACE, the mode shown is that of the table space or partition lock. Otherwise, the mode is that of the table lock.

Important points that you should consider when you work with DB2 locks include:

• You usually do not have to lock data explicitly in your program.
• DB2 ensures that your program does not retrieve uncommitted data unless you specifically allow that.
• Any page or row where your program updates, inserts, or deletes stays locked at least until the end of a unit of work, regardless of the isolation level. No other process can access the object in any way until then, unless you specifically allow that access to that process.
• Commit often for concurrency. Determine points in your program where changed data is consistent. At those points, you should issue:

  TSO, Batch, and CAF

  An SQL COMMIT statement

  IMS™

  A CHKP or SYNC call, or (for single-mode transactions) a GU call to the I/O PCB

  CICS®

  A SYNCPOINT command.

• Set ISOLATION (usually RR, RS, or CS) when you bind the plan or package.
  • With RR (repeatable read), all accessed pages or rows are locked until the next commit point.
  • With RS (read stability), all qualifying pages or rows are locked until the next commit point.
  • With CS (cursor stability), only the pages or rows currently accessed can be locked, and those locks might be avoided. (You can access one page or row for each open cursor.)
• You can also use an isolation clause to specify the isolation for specific SQL statements.
• A deadlock can occur if two processes each hold a resource that the other needs. One process is chosen as "victim", its unit of work is rolled back, and an SQL error code is issued.
• 
  You can lock an entire nonsegmented table space, or an entire table in a segmented table space, by the LOCK TABLE statement:
  • To let other users retrieve, but not update, delete, or insert, issue the following statement:

    LOCK TABLE table-name IN SHARE MODE

  • To prevent other users from accessing rows in any way, except by using UR isolation, issue the following statement:

    LOCK TABLE table-name IN EXCLUSIVE MODE