Would it be possible to find out which
line of a stored procedure is causing
these row lock contentions?
Not exactly but you can get the SQL statement causing the lock and in turn identify the related lines in the procedure.
SELECT sid, sql_text
FROM v$session s
LEFT JOIN v$sql q ON q.sql_id=s.sql_id
WHERE state = 'WAITING' AND wait_class != 'Idle'
AND event = 'enq: TX - row lock contention';
What would be the general guideline to
reduce/avoid/eliminate such problems
with coding?
The Oracle Concepts Guide section on locks says, "A row is locked only when modified by a writer." Another session updating the same row will then wait for the first session to COMMIT
or ROLLBACK
before it can continue. To eliminate the problem you could serialize the users, but here are some things that can reduce the problem perhaps to the level of it not being an issue.
COMMIT
more frequently. Every COMMIT
releases locks, so if you can do the updates in batches the likelihood of another session needing the same row is reduced.
- Make sure you aren't updating any rows without changing their values. For example,
UPDATE t1 SET f1=DECODE(f2,’a’,f1+1,f1);
should be rewritten as the more selective (read fewer locks) UPDATE t1 SET f1=f1+1 WHERE f2=’a’;
. Of course if the changing the statement will still lock the majority of rows in the table then the change will only have a readability benefit.
- Make sure you are using sequences rather than locking a table to add one to the highest current value.
- Make sure you aren’t using a function that is causing an index to not be used. If the function is necessary consider making it a function based index.
- Think in sets. Consider whether a loop running a block of PL/SQL doing updates could be rewritten as a single update statement. If not then perhaps bulk processing could be used with
BULK COLLECT ... FORALL
.
- Reduce the work that gets done between the first
UPDATE
and the COMMIT
. For example, if the code sends an email after each update, consider queuing the emails and sending them after committing the updates.
- Design the application to handle waiting by doing a
SELECT ... FOR UPDATE NOWAIT
or WAIT 2
. You can then catch the inability to lock the row and inform the user that another session is modifying the same data.
OBJ#=-1 means either means that the object number is missing or it's not related to an object.
The best idea is to go into V$active_session_history and find the SQL_ID then get the SQL text via the SQL_ID and look at the object sin the SQL and what the SQL was doing.
As far as people suggesting INIT TRANS, that is unlikely as there is a separate wait event for INIT TRANS.
USN, SLOT and SEQUENCE map the UNDO entry for the transaction.
They could be used to map back to activity in the UNDO but it's probably easier to get the transaction id the XID and use VERSIONS_XID on the objects involved.
see http://www.slideshare.net/khailey/ukoug-oracle-transaction-locks
Example ASH query
col object for A15
col otype for A10
select
substr(event,0,20) lock_name,
ash.session_id waiter,
mod(ash.p1,16) lmode,
--ash.p2 p2,
--ash.p3 p3,
o.object_name object,
o.object_type otype,
CURRENT_FILE# filen,
CURRENT_BLOCK# blockn,
ash.SQL_ID waiting_sql,
BLOCKING_SESSION blocker
--,ash.xid
from
v$active_session_history ash,
all_objects o
where
event like 'enq: TX - index contention'
and o.object_id (+)= ash.CURRENT_OBJ#
/
Best Answer
You are holding locks due to an
UPDATE
statemnt. Those locks are released when you commit your transaction.To solve this issue, make sure you commit your transactions as soon as possible.