DML的鎖，修改表經常遇到的的場景

查詢有鎖的會話和物件，下面這個語句查出很多的結果都是正常的，並不是真正的鎖，因為update等動作都會對錶或行加鎖，就算條件是locked_mode=6也不行，修改表當然會有排他鎖，但不代表它就一點堵塞別的會話

select l.session_id,o.owner,o.object_name,s.serial#,s.LOGON_TIME,s.machine,p.spid,p.terminal from gv$locked_object l,dba_objects o ,gv$session s,gv$process p where l.object_id=o.object_id and l.session_id=s.sid and  s.paddr=p.addr and l.locked_mode>0

 

 

最簡單的查詢死鎖堵塞的sql如下

select * from V$SESSION_BLOCKERS

V$SESSION_BLOCKERS displays the blocker sessions for each blocked session

或

select * from V$TRANSACTION_ENQUEUE

V$TRANSACTION_ENQUEUE displays locks owned by transaction state objects.

 

 

最常用的查詢死鎖堵塞的sql如下

查詢死鎖會話sid（物件鎖被釋放的等待者），及引起死鎖的堵塞者會話blocking_session（物件加鎖者）

select sid,status,LOGON_TIME,sql_id,blocking_session "死鎖直接源",FINAL_BLOCKING_SESSION "死鎖最終源",event,seconds_in_wait "會話鎖住時間_S",LAST_CALL_ET "會話STATUS持續時間_S" from v$session where event like 'enq%' and state='WAITING' and BLOCKING_SESSION_STATUS='VALID' and FINAL_BLOCKING_SESSION_STATUS='VALID';

BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if BLOCKING_SESSION_STATUS has the value VALID.

FINAL_BLOCKING_SESSION:Session identifier of the blocking session. This column is valid only if FINAL_BLOCKING_SESSION_STATUS has the value VALID.

可以在v$session.LOGON_TIME上看到引起死鎖的堵塞者會話比等待者要早

查詢鎖住的物件，把上面的sid代入下面sql
select a.object_name,b.SESSION_ID from dba_objects a,v$locked_object b where a.object_id=b.object_id and b.session_id=XX

 
select s.sid,s.username,d.owner,d.object_name,s.row_wait_obj#,s.row_wait_row#,s.row_wait_file#,s.row_wait_block# from v$session s,dba_objects d where s.row_wait_obj#=d.object_id and s.sid in(XX,XX)

 

查詢死鎖的會話和引起這個死鎖的會話(最準確的一個sql)

select

   (select username from v$session where sid=a.sid) blocker_鎖源,

   a.sid "鎖源會話",

   a.id1,

   a.id2,

   (select username from v$session where sid=b.sid) blockee_被鎖者,

   b.sid "被鎖會話"

from v$lock a, v$lock b

where a.block > 0

     and b.request > 0

     and a.id1 = b.id1

     and a.id2 = b.id2;

REQUEST NUMBER Lock mode in which the process requests 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)

BLOCK NUMBER Indicates whether the lock in question is blocking other processes. The possible values are:

 0 - The lock is not blocking any other processes

 1 - The lock is blocking other processes

 2 - The lock is not blocking any blocked processes on the local node,

but it may or may not be blocking processes on remote nodes. This

value is used only in Oracle Real Application Clusters (Oracle RAC)

configurations (not in single instance configurations).

查詢死鎖會話和死鎖引起者會話的詳細資訊，除非死鎖這個會話比這個引起者會話重要，才會去殺掉這個引起者會話

select ses.sid,ses.serial#,ses.logon_time,proc.spid,ses.status,ses.module,ses.program,ses.process "client_process",ses.action,ses.client_identifier

from v$session ses, v$process proc

where ses.paddr = proc.addr and ses.sid in (XX, XX)

order by ses.logon_time ;