[轉]查看處於被鎖狀態的表：v$locked_object dba_objects v$session all_objects v$sqlarea v$lock

標籤：

oracle官網當一個使用者發出select..for update的錯作準備對返回的結果集進行修改時，如果結果集已經被另一個會話鎖定，就是發生阻塞。需要等另一個會話結束之後才可繼續執行。可以通過發出 select… for update nowait的語句來避免發生阻塞，如果資源已經被另一個會話鎖定，則會返回以下錯誤：Ora-00054:resource busy and acquire with nowait specified.



oracle官網死結-deadlock

定義:當兩個使用者希望持有對方的資源時就會發生死結.
即兩個使用者互相等待對方釋放資源時,oracle認定為產生了死結,在這種情況下,將以犧牲一個使用者作為代價,另一個使用者繼續執行,犧牲的使用者的事務將復原.
oracle官網例子：
1：使用者1對A表進行Update，沒有提交。
2：使用者2對B表進行Update，沒有提交。
此時雙反不存在資源共用的問題。
3：如果使用者2此時對A表作update,則會發生阻塞，需要等到使用者一的事物結束。
4：如果此時使用者1又對B表作update，則產生死結。此時Oracle會選擇其中一個使用者進行會滾，使另一個使用者繼續執行操作。
oracle官網起因:
Oracle的死結問題實際上很少見，如果發生，基本上都是不正確的程式設計造成的，經過調整後，基本上都會避免死結的發生。

DML鎖分類表表1 Oracle的TM鎖類型
鎖模式鎖描述解釋SQL操作
0none 
1NULL空Select
2SS(Row-S)行級共用鎖定，其他對象只能查詢這些資料行 
Select for update、Lock for update、Lock row share
3SX(Row-X)行級排它鎖，在提交前不允許做DML操作 
Insert、Update、Delete、Lock row share
4S(Share)共用鎖定Create index、Lock share
5SSX(S/Row-X)共用行級排它鎖Lock share row exclusive
6X(Exclusive)排它鎖 
Alter table、Drop able、Drop index、Truncate table 、Lock exclusive





oracle官網1.關於V$lock表和相關視圖的說明
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 are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:
TM - DML enqueue 
TX - Transaction enqueue
UL - User supplied
--我們主要關注TX和TM兩種類型的鎖
--UL鎖使用者自己定義的，一般很少會定義，基本不用關注
--其它均為系統鎖，會很快自動釋放，不用關注
ID1NUMBERLock identifier #1 (depends on type)
ID2NUMBERLock identifier #2 (depends on type)
---當lock type 為TM時，id1為DML-locked object的object_id
---當lock type 為TX時，id1為usn+slot，而id2為seq。
--當lock type為其它時，不用關注
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.查看處於被鎖狀態的表
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.查出鎖定object的session的資訊以及被鎖定的object名
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查出鎖定的object的session資訊（指定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.查出鎖定表的session的sid, serial#,os_user_name, machine name, terminal和執行的語句
--比上面那段多出sql_text和action
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, serial#,os_user_name, machine_name, terminal，鎖的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和dba_objects用object_id可以關聯起來，找到dba_objects.object_name,

V$session的Sid和V$locked_object的session_id內聯起來，可以從V$session中的machine找到機器名。進而找到機器。

使用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;

[轉]查看處於被鎖狀態的表：v$locked_object dba_objects v$session all_objects v$sqlarea v$lock