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 ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2127111/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2127111/