Lock

Lock: A lock is a mechanism used by Oracle that protects system resources, such as data, from being modified by more than one user/process at a time.
-ddl lock
-dml lock
-Internal locks and latches
-Breakable Parse Locks

Common database lock types are:
Shared, eXclusive, Row Share, Row eXclusive, etc.

Transaction: A transaction is what initiates the lock on a row, multiple rows, or an entire table.

Locks are released when COMMIT or ROLLBACK is issued at the end of a transaction.

Query To Find Out the Lock:

SQL> select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS "Blocking Status"
,s1.serial# "Blocking Session's Serial"
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 ;

SQL> SELECT a.owner,a.object_name,b.sid,b.serial#,b.username,b.status
FROM dba_objects a, v$session b, v$locked_object c
WHERE a.object_id = c.object_id AND b.sid = c.session_id;

SQL> select sid,type,id1,lmode,request from v$lock;

Here, ID1 is the object_id.

Identifying the locked object:

SQL> select object_name from dba_objects where object_id=52420 ;

Identifying the locked row:

select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid=483;

Q: How to manage table lock?

Step-1: Kill all the locking Session:

ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

Step-2: run commit/rollback


Hanging: Sometimes the session hangs until the session that has put a lock on the row in question commits (or rollbacks). This waiting is recorded in v$session_wait:

SQL> select event, seconds_in_wait, sid from v$session_wait where sid in (483,452);