select 'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid from dba_lock_internal l, v$session s where l.LOCK_ID1 like '%ADVICES%' and l.lock_type like '%Body Definition Lock%' and l.session_id = s.sid;
BREAK ON sid ON lock_id1 ON kill_sid
COL sid FOR 999999
COL lock_type FOR A38
COL mode_held FOR A12
COL mode_requested FOR A12
COL lock_id1 FOR A20
COL lock_id2 FOR A20
COL kill_sid FOR A50
SELECT s.sid,
l.lock_type,
l.mode_held,
l.mode_requested,
l.lock_id1,
'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM dba_lock_internal l,
v$session s
WHERE s.sid = l.session_id
AND UPPER(l.lock_id1) LIKE 'Procedure Name'
AND l.lock_type = 'Body Definition Lock';
select c.owner, c.object_name, c.object_type, b.sid s, b.serial#, b.status, b.username , b.osuser, b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;
set lines 1000
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
select blocking_session, sid, serial#, wait_class, seconds_in_wait
from v$session
where blocking_session is not NULL
order by
blocking_session;
select sid, sql_text
from v$session s,
v$sql q
where sid in (select sid
from
v$session
where state in ('WAITING')
and wait_class != 'Idle'
and event='enq: TX - row lock contention'
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id));
select 'alter system kill session '''||sid||','||serial#||''''||'; ' from v$session where blocking_session is not null;
SELECT distinct a.sql_id, a.blocking_session blocker_ses, a.blocking_session_serial# blocker_ser, a.user_id, s.sql_text, a.module, a.sample_time
FROM V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
and to_char(a.sample_time,'DD-MON-YYYY HH') = '06-SEP-2016 12';
SELECT distinct a.session_id, a.session_serial#,a.sql_id, a.blocking_session blocker_ses,a.blocking_session_serial# blocker_ser,a.user_id, s.sql_text, a.module, a.sample_time
FROM V$ACTIVE_SESSION_HISTORY a,
v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 -- exclude SYS user
and a.session_id = 520
and to_char(a.sample_time,'DD-MON-YYYY HH') = '05-MAY-2016 12'
order by 1;
This is a very special case of deadlock, which occurs during inserts; not updates or deletes. This is probably the only case where inserts cause deadlocks. When you insert a record into a table but not commit it, the record goes in but a further insert with the same primary key value waits. This lock is required for Oracle because the first insert may be rolled back, allowing the second one to pass through. If the first insert is committed, then the second insert fails with a PK violation. But in the meantime-before the commit or rollback is issued-the transaction causes the second insert to wait and that causes deadlock. Let's examine the scenario:
Scenario Deadlock Graph The deadlock graph looks like the following.
The key clues are:
The subsequent parts of the tracefile don’t show any row information.
However, the latter parts of the tracefile shows the SQL statement, which should be able to point to the cause of the deadlock as the primary key deadlock. Remember, this may be difficult to diagnose first since there is no row information. But this is probably normal since the row is not formed yet (it's INSERT, remember?).
Oracle Locks, blocks, and deadlocks are very frequent on OLTP databases, and every time they happen, you can see frustrated people. A few seconds later you will hear a guy saying the database is not working properly or something worse the database is blocking us as if the database were conspiring against them. Hey guys! You have chosen a database which prioritizes data concurrency, consistency, and integrity, so now it is helping your data remain consistent.
Many people use the words lock, block and deadlock interchangeably, which is wrong. One of the more important things when resolving an issue is to be on the same page, so let us discuss what these concepts mean. What is the meaning of each word? When should we use each one? And the most crucial question: how to avoid each scenario?
Oracle describes this action as: A lock is a mechanism that prevents negative interaction between transactions accessing a shared resource. Locks help ensure data integrity while allowing maximum concurrent access to data.
In other words, every time you access some piece of data, some kind of magic inside Oracle is acquiring a lock in your stead and preventing another process to modify the same piece of data (or a part of them) and guaranteeing your access. You have two basic types of locks: shared and exclusive locks.These kinds of locks are more often associated to read operations, and their main purpose is mainly to keep the same data structure through the entire process. Imagine you are performing a full table scan over a big table of hundreds of gigabytes. Even if you had the fastest disks available on your storage, the scan would take several minutes to retrieve all the information, reading one block at a time. Now, imagine another user is performing a DDL at the same time and drops a column before your process had a chance to read these blocks. Something will go wrong. Shared locks have been designed to prevent these scenarios.
Actually, you may find some situations in which shared locks operate over DML transactions, but they depend on the isolation level of each type of transaction. This is a complex subject that exceeds the purpose of this paper.
Every time you try to modify a row or a set of rows through write operations, such as an insert, update, delete or merge statement, you will be involved in two types of lock: a row-level exclusive lock over all rows involved in your statement, and a (shared) table lock to prevent your table structure to be modified while your DML remains running. This way, Oracle prevents other processes from modifying the same rows you are working on or modifying your table structure until you release the lock through a commit or rollback statement.
So, only one exclusive lock can be acquired over a single resource at the same time.Each of these single locks could hold a lot of processes until each lock is released and the processes that were held can resume their operation. On the other hand, several processes can acquire shared locks at the same time over the same resource.
What is a deadlock? It is an odd situation in which two or more processes could be fighting each other in an endless struggle. Each process is at a stage that requires the row-level exclusive lock acquired for the other process so that no process can finish their job.
Imagine a point in time named t1, at this point the process p1 acquires a row-level exclusive lock for the first row in the table. At the same time, another process named p2 acquires a row-level exclusive lock for the second row on the same table. A few cycles later, p1 needs to modify the second row in the table which is currently locked by p2. At the same time, p2 needs to modify the first row in the same table which is currently locked by p1. This constitutes a deadlock.
Given this situation, Oracle will detect the deadlock and resolve the issue by rolling back one of the processes. Of course, not all deadlocks are as simple as the mentioned, there could be several processes and many tables involved in a single deadlock.
What about blocking locks? As we said, in OLTP databases, it is highly likely that two or more processes will compete for the same resources. The database will do its magic in pursuit of keeping your data consistent and intact acquiring the right locks according to the particulars of each situation. But, what happens with the other processes that need to modify the same resource while a lock persists? They will remain in the wait state of course, and this wait is what some people call blocks inaccurately.
So, in this scenario, some processes will halt others. These so-called blocking locks are abstract concepts resulting from locks. When they appear, there is not much you can do to fix them. You can wait until the process that placed the lock releases it or you can identify the process that placed the lock, kill it and wait until the whole transaction is rolled back and the lock is released.
The most important question is how to avoid these blocking locks. Technically, a process that is blocking another one, does not constitute a database error, even when there are many of them, and you are notified with an ORA-00060: deadlock detected while waiting for resource message. These come from common mistakes in application design.
My best advice is simple and should be obvious: if you are working in an OLTP you cannot just add new flows and associated code without analyzing the whole number of processes that will access the same resources concurrently (even when the possibility of occurrence is low). You will need to plan and serialize your code; you will need to split your transactions to make them as small as they can be and release the locks as fast as possible. You will need to use indexes, access the rows you want to modify by unique identifier if possible, and make small chunks for massive modifications. You will need to be smart and creative.
Your main reference to understand how your Oracle database works should always be its documentation. Here is the specific link to the concurrency topics on Oracle 12cR2:
The query below identifies these foreign key columns without indexes:
select * from ( select ct.table_name, co.column_name, co.position column_position from user_constraints ct, user_cons_columns co where ct.constraint_name = co.constraint_name and ct.constraint_type = 'R' minus select ui.table_name, uic.column_name, uic.column_position from user_indexes ui, user_ind_columns uic where ui.index_name = uic.index_name ) order by table_name, column_position;
If you encounter a lock related hang scenario the following SQL statements can be used to help isolate the waiters and blockers:
SELECT event, p1, p2, p3
FROM v$session_wait
WHERE wait_time= 0
AND event like 'enq%';
SELECT sid, p1raw, p2, p3
FROM v$session_wait
WHERE wait_time = 0
AND event like 'enq: TX%';
SELECT * FROM v$lock WHERE type='TX' AND request>0;
SELECT * FROM v$lock WHERE type='TX' AND lmode > 0;
SELECT owner, object_name, subobject_name, value
FROM v$segment_statistics
WHERE statistic_name='row lock waits'
AND value > 0
ORDER BY 4 DESC;
Wait about ten minutes or so, and then run the script again. You may compare the differences of corresponding entries in the VALUE column to see which object(s) has undergone the most row lock contention.
SELECT t.owner,
t.object_name,
t.object_type,
t.statistic_name,
t.value
FROM v$segment_statistics t
WHERE t.statistic_name = 'ITL waits'
AND t.value > 0;
In earlier releases of Oracle Database, the MAXTRANS parameter limited the number of transaction entries that could concurrently use data in a data block. This parameter has been deprecated in 10g and higher. Oracle Database now automatically allows up to 255 concurrent update transactions for any data block, depending on the available space in the block.
ITL contention is likely to generate waits for the 'enq: TX - allocate ITL entry' wait event. If need be, increase INITTRANS and MAXTRANS to resolve this.
ALTER SESSION SET nls_timestamp_format='DD-MON-RR HH24:MI';
SELECT P.snap_id,
P.begin_interval_time,
O.owner,
O.object_name,
O.subobject_name,
O.object_type,
S.row_lock_waits_delta
FROM dba_hist_seg_stat S,
dba_hist_seg_stat_obj O,
dba_hist_snapshot P
WHERE S.dbid =O.dbid
AND S.ts# =O.ts#
AND S.obj# =O.obj#
AND S.dataobj# =O.dataobj#
AND S.snap_id =P.snap_id
AND S.dbid =P.dbid
AND S.instance_number =P.instance_number
AND S.row_lock_waits_delta > 0
AND P.snap_id BETWEEN begin_snap AND end_snap
ORDER BY 1,3,4;
HOME