XML lock and XML table space lock duration
This information describes the duration of XML locks and XML table space locks.
The duration of XML locks
X-locks on XML data that are acquired for insert, update, and delete statements are usually released at commit. The duration of XML locks acquired for select statements varies depending upon isolation level, the setting of the CURRENTDATA parameter, and whether work files or multi-row fetch are used.
XML
locks acquired for fetch are not normally held until commit and are
either released at next fetch or at close cursor. Because XML locks
for updating (INSERT, UPDATE, and DELETE) are held until commit and
because locks are put on each XML column in both a source table and
a target table, it is possible that a statement such as an INSERT
with a fullselect that involves XML columns can accumulate
many more locks than a similar statement that does not involve XML
data. To prevent system problems caused by too many locks, you can:
- Ensure that you have lock escalation enabled for the XML table spaces that are involved in the INSERT. In other words, make sure that LOCKMAX is non-zero for those XML table spaces.
- Alter the XML table space to change the LOCKSIZE to TABLESPACE before executing the INSERT with fullselect.
- Increase the LOCKMAX value on the table spaces involved and ensure that the user lock limit is sufficient.
- Use LOCK TABLE statements to lock the XML table spaces.
The duration of XML table space locks
Locks on XML table spaces are acquired when they are needed. The table space lock is released according to the value specified on the RELEASE bind option (except when a cursor is defined WITH HOLD).
