Intent Locks (IN, IS, IX, SIX)

The intent mode locks are used at the table, block (for MDC tables) and Data Partitions (for Range based Table partitioning) locks. These are not used at the row level.

•  The Intent None (IN) locks are used for uncommitted read applications.

   The Intent locks allow other transactions to perform reads and writes to the table,

   except for the SIX lock, which only allows others to perform reads.

•  The Intent Share (IS) and Intent Exclusive (IX) are used at the table, block and data

   partition level, but require transactions to lock lower granular objects in the hierarchy

   when reading data. For example, an application using Cursor Stability might acquire an

   IS lock for a table and then get NS locks at the row level.

• The Intent Exclusive (IX) and SIX locks allow transactions to update lower granular

   objects, but require additional locking at the lower levels.

Non-intent Locks

       Of the non-intent locking modes, DB2 can use NS, S and U locks for read requests. The NS locks are only used at the row level for applications using CS and RS isolation levels. Repeatable Read isolation level uses the S lock at the row level.

• The U mode lock is used for intent to update locking. It allows deadlock prevention as it is not compatible with itself. DB2 could acquire a U lock for application 1, when a row is retrieved for a cursor with the FOR UPDATE clause. If application 2 also tries to access the same row using a cursor defined FOR UPDATE, the request for a U lock for the row will force application 2 to wait for the first application to release the lock. DB2 utilities like LOAD and REORG use table level U locks to allow concurrent read activity but prevent any concurrent updates.
  

• DB2 uses the non-intent mode locks Exclusive (X), Super exclusive (Z) and Next Key Weak(NW) for write requests.
        
         1 - A Z mode lock is super-exclusive, no access by any other transaction. It is mainly used by DB2 utilities and DDL statements like ALTER TABLE, to insure operations are performed with no concurrent access.
         2 - The Exclusive, X mode locks are used for writes, but allow UR isolation level applications to process read activity concurrently.
         3 - The NW mode lock is used only to lock the next key in an index when inserting a key whenthe next key is already locked by an application scanner using the RR isolation level.

LOCK COMPATIBILITY MATRIX

	IN	IS	NS	S	IX	SIX	U	NW	X	Z
IN	ok	ok	ok	ok	ok	ok	ok	ok	ok	x
IS	ok	ok	ok	ok	ok	ok	ok	x	x	x
NS	ok	ok	ok	ok	x	x	ok	ok	x	x
S	ok	ok	ok	ok	x	x	ok	x	x	x
IX	ok	ok	x	x	ok	x	x	x	x	x
SIX	ok	ok	x	x	x	x	x	x	x	x
U	ok	ok	ok	ok	x	x	x	x	x	x
NW	ok	x	ok	x	x	x	x	x	x	x
X	ok	x	x	x	x	x	x	x	x	x
Z	x	x	x	x	x	x	x	x	x	x

The table shows which lock modes are compatible with other locking modes.
This shows that an application using Uncommitted Read isolation, that needs a Intent None
(IN) table lock can acquire the lock with any of the other lock modes already held by
another application, with the exception of the Super Exclusive Z lock. An offline REORG
utility uses the Z lock at the table level when it needs to insure there are no applications
with access to the table. Notice that the table shows no lock mode is compatible with a Z
lock.