What are Row Locks?

Row locks (also referred to as transaction (TX) locks) indicate multiple users try modifying the same row of a table (row-level-lock) or a row that is covered by the same bitmap index fragment, or a session is waiting for an ITL (interested transaction list) slot in a block, but one or more sessions have rows locked in the same block, and there is no free ITL slot in the block.

What can I do?

In the first case, the first user has to COMMIT or ROLLBACK to solve the problem. If multiple transactions concurrently hold share table locks for the same table, no transaction can update the table (even if row locks are held as the result of a SELECT... FOR UPDATE statement). Therefore, if concurrent share table locks on the same table are common, updates cannot proceed and deadlocks are common.

In the second case, increasing the number of ITLs available is the answer - which can be done by changing either the INITRANS or MAXTRANS for the table in question.

One more possible reason for row lock waits are INSERT and UPDATE statements on a child table waiting for row locks on the parent table to clear (foreign key). In this case, the only thing that can be done is causing the transaction that holds the locks on the parent table to COMMIT or ROLLBACK - or to wait until it does so unsolicitedly.