What is Blocking Session??
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work. It mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.
How to Find Blocked/Blocking other sessions??
What is TM lock - TM (DML enqueue lock)- This is a general table lock. Every time a session wants to lock a table (for an UPDATE, INSERT, or DELETE), a TM enqueue is requested.
SELECT sid, id1 FROM v$lock WHERE TYPE='TM'
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another sessions wants to update the same data. This will block the second until the first one has done its work. It mainly happens when a session issues an insert, update or delete command that changes a row. When the change occurs, the row is locked until the session either commits the change or rolls the change back.
How to Find Blocked/Blocking other sessions??
select
t.process,
t.sid,
t.SERIAL#,
t.blocking_session,
t.USER#,
t.USERNAME
from
v$session t
where blocking_session is not null;
Identify blocked objects
If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other.What is TM lock - TM (DML enqueue lock)- This is a general table lock. Every time a session wants to lock a table (for an UPDATE, INSERT, or DELETE), a TM enqueue is requested.
SELECT sid, id1 FROM v$lock WHERE TYPE='TM'
The ID you get from this query refers to the actual database object which can help you to identify the problem
No comments:
Post a Comment