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
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.
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: - 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.
- 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.