14.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 14.8 IBMDB2I Isolation Levels

Isolation levelRead-only (SELECT)Read-write (UPDATE, DELETE)
 Lock enforcementVisibility of uncommitted work on behalf of other connectionsLock enforcementVisibility of uncommitted work on behalf of other connections
SERIALIZABLETable is locked until end of transaction.Other connections may read rows while locked.N/ATable is locked until end of transaction.Other connections may read rows while locked.N/A
REPEATABLE READRows 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 COMMITTEDRow 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 UNCOMMITTEDRow 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_unsafeNo locksFull accessTable 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.