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.
|