Locks for XML data

Db2 stores XML column values in a separate XML table space. An application that reads or updates a row in a table that contains XML columns might use lock avoidance or obtain transaction locks on the base table.

Introductory concepts

Begin program-specific programming interface information. If an XML column is updated or read, the application might also acquire transaction locks on the XML table space and XML values that are stored in the XML table space. A lock that is taken on an XML value in an XML table space is called an XML lock.

In data sharing, page P-locks are acquired during insert, update, and delete operations.

In summary, the main purpose of XML locks is for managing the space used by XML data and to ensure that XML readers do not read partially updated XML data. Db2 supports multiple versions of an XML document in XML columns. The existence of multiple versions of an XML document can lead to improved concurrency through lock avoidance.

The following table shows the relationship between an operation that is performed on XML data and the associated XML table space and XML locks that are acquired. It shows the locks that are acquired for non-versioned XML data.

Table 1. Locks that are acquired for operations on XML data. This table does not account for gross locks that can be taken because of the LOCKSIZE TABLESPACE option, the LOCK TABLE statement, or lock escalation.
Operation on XML value XML table space lock XML lock Comment
Read (including UR) IS S Prevents storage from being reused while the XML data is being read. If XML versions are used, the S XML lock is acquired only for UR readers.
Insert IX X Prevents other processes from seeing partial XML data
Delete IX X To hold space in case the delete is rolled back. Storage is not reusable until the delete is committed and no other readers of the XML data exist.
Update IS->IX X Operation is a delete followed by an insert.

XML locks and uncommitted read (UR) isolation

When an application reads rows using uncommitted read or lock avoidance, no page or row locks are taken on the base table. Therefore, these readers must take an S XML lock to ensure that they are not reading a partial XML value or an XML value that is inconsistent with the base row.

When an XML lock cannot be acquired for an SQL statement with UR isolation, Db2 might need to wait for the lock. If the lock is not granted, Db2 might return SQL return code -911 or -913.

Start of changeIf a SELECT with UR isolation and an INSERT occur concurrently on an XML column, and the INSERT is rolled back, the thread that performs the SELECT abends with completion code 04E and reason code 00E70005 at location DSNNOGET:M720.End of change

Hierarchy of XML locks

Just as page locks (or row locks) and table space locks have a hierarchical relationship, XML locks and locks on XML table spaces have a hierarchical relationship. If the XML table space is locked with a gross lock, then XML locks are not acquired. In a data sharing environment, the lock on the XML table space is used to determine whether the lock on the XML must be propagated beyond the local IRLM.

When XML table space locks are not taken

A lock might not be acquired on an XML table space at all. Db2 does not access the XML table space if the application takes any of the following actions:

  • Selects an XML value that is null
  • Selects from an XML table space when XML versions are used
  • Deletes a row where the XML value is null
  • Inserts a null XML value
  • Updates an XML value to nullEnd program-specific programming interface information.