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.

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.

  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.

  For most table space types, the LOCK TABLE statement obtains locks on the table space or a specific partition. For segmented (non-UTS) table spaces, the LOCK TABLE statement applies to individual tables.

  The following table shows the modes of locks acquired in segmented and non-segmented 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	UTS or Non-Segmented Table Space	Segmented Table Space	Table in Segmented Table Space
  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.
• Use application BIND options to prevent access to the table.
  You can use the following approaches:

  • Bind 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.
  • 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.