[Go] To view the table in the locked state: V$locked_object dba_objects v$session all_objects v$sqlarea v$lock

Oracle WebsiteWhen a user issues a select: When the for update error is ready to be modified for the returned result set, blocking occurs if the result set has been locked by another session. You need to wait until another session finishes before you can continue. You can avoid blocking by issuing a statement of SELECT ... for update nowait, and return the following error if the resource is already locked by another session: Ora-00054:resource busy and Acquire with nowait Specified.



Oracle Official website Deadlock-deadlock

Definition: A deadlock occurs when two users want to hold each other's resources.
That is, when two users wait for each other to release resources, Oracle determines that a deadlock is generated, in which case another user continues to execute at the expense of one user, and the transaction of the sacrificed user is rolled back.
Oracle WebsiteExample:
1: User 1 update to a table, no commit.
2: User 2 Update to B table, no commit.
At this point, there is no resource sharing problem with double-anti.
3: If user 2 at this time to update a table, then the block will occur, you need to wait until the end of the user one thing.
4: If user 1 then update the B table, a deadlock is generated. At this point, Oracle chooses one of the users to roll over, allowing another user to continue the operation.
Oracle WebsiteCause:
Oracle's deadlock problem is actually very rare, and if it happens, it's basically an incorrect design, and after the adjustment, it basically avoids the deadlock.

DML Lock Classification Table 1 TM lock type for Oracle
Lock mode lock description Interpreting SQL operations
0none
1NULL Empty Select
2SS (row-s) row-level shared lock, other objects can only query these data rows
Select for UPDATE, lock for update, lock row share
3SX (row-x) row level exclusive lock that does not allow DML operations before committing
Insert, Update, Delete, Lock Row share
4S (Share) shared lock CREATE INDEX, lock Share
5SSX (s/row-x) shared row-level exclusive locks lock share row exclusive
6X (Exclusive) exclusive lock
Alter table, drop able, DROP index, Truncate table, Lock exclusive





Oracle Website1. Notes on the V$lock table and related views
Columndatatypedescription
Addrraw (4 | 8) Address of Lock state object
Kaddrraw (4 | 8) Address of lock
Sidnumberidentifier for session holding or acquiring the lock
TYPEVARCHAR2 (2) Type of user or system lock
The locks on the user types is obtained by the user applications. Any process which is blocking others are likely to being holding one of these locks. The user type locks is:
Tm-dml Enqueue
Tx-transaction Enqueue
Ul-user supplied
--we focus primarily on TX and TM two types of locks
--ul lock user Defined, generally rarely defined, basically do not pay attention to
--the other is System lock, will be released automatically, do not pay attention to
Id1numberlock identifier #1 (depends on type)
Id2numberlock identifier #2 (depends on type)
---When the lock type is a TM, ID1 is the object_id of the Dml-locked object
---When lock type is TX, ID1 is Usn+slot, and Id2 is seq.
--When lock type is other, do not pay attention to
Lmodenumberlock mode in which the session holds the lock:
0-none
1-null (NULL)
2-row-s (SS)
3-row-x (SX)
4-share (S)
5-s/row-x (SSX)
6-exclusive (X)

--1. Viewing a table in a locked state
SELECT a.object_id,
A.SESSION_ID,
B.object_name
From V$locked_object A,
Dba_objects b
WHERE a.object_id = b.object_id

--2. Find information about the session that locked the object and the object name that was locked
SELECT l.session_id SID,
s.serial#,
L.locked_mode,
L.oracle_username,
L.os_user_name,
S.machine,
S.terminal,
O.object_name,
S.logon_time
From V$locked_object L,
All_objects O,
V$session s
WHERE l.object_id = o.object_id
and l.session_id = S.sid
ORDER by Sid,
s.serial#;
--2.1 Detecting session information for locked object (specify object_name)
SELECT l.session_id SID,
s.serial#,
L.locked_mode,
L.oracle_username,
L.os_user_name,
S.machine,
S.terminal,
O.object_name,
S.logon_time
From V$locked_object L, all_objects O, v$session s
WHERE l.object_id = o.object_id
and l.session_id = S.sid
and o.object_name = ' Zzom_send_email '
ORDER by Sid, S.serial#;
--3. The SID, Serial#,os_user_name, machine name, terminal, and executed statements of the session in which the lock table is isolated
-More sql_text and action than the above paragraph
SELECT l.session_id SID,
s.serial#,
L.locked_mode,
L.oracle_username,
s.user#,
L.os_user_name,
S.machine,
S.terminal,
A.sql_text,
A.action
From V$sqlarea A,
V$session S,
V$locked_object L
WHERE l.session_id = S.sid
and s.prev_sql_addr = a.address
ORDER by Sid,
s.serial#;

--4. SID of the locked table, Serial#,os_user_name, machine_name, terminal, lock Type,mode
SELECT S.sid,
s.serial#,
S.username,
S.schemaname,
S.osuser,
S.process,
S.machine,
S.terminal,
S.logon_time,
L.type
From V$session S,
V$lock L
WHERE S.sid = L.sid
And S.username is not NULL
ORDER by Sid;

V$locked_object and dba_objects can be associated with object_id, find Dba_objects.object_name,

V$session's SID and V$locked_object's session_id are linked together to find machine names from machines in V$session. And then find the machine.

Use ALTER system kill session ' sid,serial# '

Select V$session.sid,v$session.machine,dba_objects.object_name

From V$session.sid inner join V$locked_object on v$session.sid=v$locked_object.session_id

INNER JOIN dba_objects on dba_objects.object_id = v$locked_object.object_id;

[Go] To view the table in the locked state: V$locked_object dba_objects v$session all_objects v$sqlarea v$lock