Controlling concurrent access to tables

You can use LOCK TABLE statements and ISOLATION bind options to prevent other application processes from changing or reading rows in a table or partition while your application is accessing it.

About this task

Begin general-use programming interface information.

You might want to lock a table or partition so that a single application thread has exclusive access to the contents of an entire table throughout a unit of work, and all concurrent changes are prevented.

Procedure

To control concurrent access to tables, use any of the following approaches:

  • Issue LOCK TABLE statements for the table. A new lock is requested immediately when the LOCK TABLE statement is issued, unless a suitable lock already exists. The RELEASE bind option determines when locks that are acquired for LOCK TABLE statements are released.

    Share mode is recommended when your application needs to prevent changes to the entire table but other processes can be allowed to read the data.

    Applications that use the CURRENTDATA(NO) bind option might still be able to read the data, even when exclusive mode locks are used.

    When the goal is to prevent timeouts caused by contention with other applications, either share or exclusive mode can be used. However, exclusive mode is recommended when your application updates the data.

    Start of change

    The locks that are acquired when you issue a LOCK TABLE statement depend on the mode that is requested and the type of table space. The following table shows the modes of locks acquired in segmented and nonsegmented table spaces for LOCK TABLE statements.

    Table 1. Modes of locks acquired by LOCK TABLE. LOCK TABLE on partitions behave the same as nonsegmented table spaces.
    LOCK TABLE In Nonsegmented or Universal Table Spaces Segmented Table Space Tables Segmented Table Space Table Spaces
    EXCLUSIVE MODE X X IX
    SHARE MODE S or SIX1 S or SIX1 IS
    Note:
    1. The SIX lock is acquired if the process already holds an IX lock. SHARE MODE has no effect if the process already has a lock of mode SIX, U, or X.

    For segmented table spaces that are not partitioned, the LOCK TABLE statement applies to individual tables. For all other table space types, the LOCK TABLE statement applies to the table space or to the specified partition.

    End of change
  • Use application BIND options to prevent access to the table. You can use the following approaches:
    • Start of changeBind the application with the ISOLATION(RR) bind option. For table space scans, this option acquires gross locks on the accessed tables, and might impact the concurrency of the application.End of change
    • Design the application to use separate packages and access the table from only a few of the packages. Then bind only those packages with the ISOLATION(RR) or ISOLATION(RS) bind options, and bind the plan with the ISOLATION(CS) option.
  • Use LOCKSIZE TABLESPACE for tables that require read-only access. This approach is best for tables that contain relatively static data that is updated only infrequently and only by a process that requires exclusive control over the table.
    End general-use programming interface information.