Wednesday, September 03, 2008

enq: TX - row lock contention

enq: TX - row lock contention -> that is type of TX enqueue what mode on TX about it:

- Waits for TX in mode 6: occurs when a session is waiting for row level lock that is already held by another session, some users is updating or deleting a row, which another session need to update or delete.

Solve -> the first session already holding the lock perform COMMIT/ROLLBACK.

- Waits for TX in mode 4: if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value.

Solve -> the first session already holding the lock perform COMMIT/ROLLBACK.

- Waits for TX in mode 4: if the session is waiting due to shared bitmap index fragment.

Solve -> the first session already holding the lock perform COMMIT/ROLLBACK.

The "ENQ: TX", often wait for an ITL (interested transaction list) slot in a block.
"enq: TX - allocate ITL entry", when session need to lock row in the block, but one or more other sessions have rows locked in the same block

this case we need to -> change INITRANS or MAXTRANS for table.

> ALTER TABLE TABLE_NAME INITRANS X;

No comments: