Skip navigation links
13.7.6 Principles and Terminology »
Section Navigation      [Toggle]

13.7.5. Transaction behavior

The IBMDB2I storage engine supports row-level transaction management. All MySQL isolation levels are supported by the engine, and where highest performance is required, transaction support can be disabled globally or per session by modifying the ibmdb2i_transaction_unsafe configuration option.

IBMDB2I uses the underlying DB2 for i transaction support to implement MySQL isolation levels. DB2 for i uses table and row locks to implement the various isolation levels, as described below:

Table 13.7. IBMDB2I Isolation Levels

Isolation level Read-only (SELECT) Read-write (UPDATE, DELETE)
  Lock enforcement Visibility of uncommitted work on behalf of other connections Lock enforcement Visibility of uncommitted work on behalf of other connections
SERIALIZABLE Table is locked until end of transaction.Other connections may read rows while locked. N/A Table is locked until end of transaction.Other connections may read rows while locked. N/A
REPEATABLE READ Rows that have been read are locked until end of transaction.Other connections may read and insert rows while locked. Rows cannot be read until work is committed. Rows that have been read are locked until end of transaction.Other connections may read and insert rows while locked. Rows cannot be read until work is committed.
READ COMMITTED Row is locked while cursor is positioned on that row.Other connections may read and insert rows while locked. Rows cannot be read until work is committed. Row is locked while cursor is positioned on that row.Other connections may read and insert rows while locked. Rows cannot be read until work is committed.
READ UNCOMMITTED Row is locked while cursor is positioned on that row.Other connections may read and insert rows while locked. Rows can be read before work is committed. Row is locked while cursor is positioned on that row.Other connections may read and insert rows while locked. Rows can be read before work is committed.
transaction_unsafe No locks Full access Table is locked until end of transaction.Other connections may read rows while locked. Unrestricted access

Attempts to access locked rows time out according to the timeout value associated with the underlying DB2 physical file. This timeout wait is 30 seconds by default. Refer to http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/dbp/rbafoconcrec.htm for more information.

Because IBMDB2I does not multi-version rows under commitment control, row lock contention may occur under certain scenarios. In particular, when multiple connections attempt to read overlapping ranges of rows while performing a statement that does updates, the connections may contend for the same row locks. This may lead to delays until the row lock timeout expires. Creating appropriate indexes and increasing query selectivity to reduce range overlap may help to alleviate this contention.