ORACLE 死锁(Oracle deadlock)

1、查看数据库锁,诊断锁的来源及类型: 

select  object_id,session_id,locked_mode   from   v$locked_object

  v$locked_object包含的是当前DB中被锁住的OBJECT,session_id 可以关联V$SESSION,object_id 可以关联 dba_objects。  locked_mode:  0:none  1:null 空  2:row-s 行共享(rs):共享表锁,sub share  3:row-x 行独占(rx):用于行的修改,sub exclusive  4:share 共享锁(s):阻止其他dml操作,share  5:s/row-x 共享行独占(srx):阻止其他事务操作,share/sub exclusive  6:exclusive 独占(x):独立访问使用,exclusive

select b.owner,b.object_name,b.object_type,l.session_id,l.locked_mode 
from   v$locked_object l, 
       dba_objects b 
where  b.object_id=l.object_id

   都字面意思,owner 拥有者、object_name 项目名称、 object_type 项目类型。

select  lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username user_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#
from    v$locked_object l,        dba_objects o,        v$session s
where   l.object_id=o.object_id and l.session_id=s.sid   order by o.object_id,xidusn desc;

  查询谁锁的表及等待列表,第一行锁、其余为等待列表。

2、找出数据库的sid 和 serial#

     据说v$locked_object 行级锁以上才有记录,v$lock记录的是所有的锁,包括系统锁,利用v$lock一定要加上object_name,不然查出来一堆。

  (1)利用  v$locked_object  查询锁

select    t2.username,t2.sid,t2.serial#,t2.logon_time from      v$locked_object t1,          v$session t2 where     t1.session_id=t2.sid order by t2.logon_time;

  (2)利用 v$lock 查询锁(注意把object_name修改一下,如果是行、表锁,就是表名)

select   o.object_name,s.sid, s.serial#
from    v$lock l,       dba_objects o,       v$session s
where    l.id1 = o.object_id and l.sid = s.sid and o.object_name='object_name';

3、杀死该session

alter system kill session 'sid,serial#'

  用步骤2中查出来的记录,对应进该语句删除。如果删除的时候提示 “ORA-00027:cannot kill current session”,可能是自己窗口有数据没有提交,检查一下窗口列表,或者断开数据库连接,重新尝试。

来自 https://www.cnblogs.com/huchaoheng/p/hengc.html  修改

来自 https://www.cnblogs.com/huchaoheng/p/hengc.html  修改

————————

1. Check the database lock and diagnose the source and type of the lock:

select  object_id,session_id,locked_mode   from   v$locked_object

v$locked_ Object contains the locked object and session in the current dB_ ID can be associated with V $session and object_ ID can be associated with DBA_ objects。   locked_ Mode: 0: none 1: null empty 2: row-s row sharing (RS): shared table lock, sub share 3: row-x row exclusive (Rx): used for row modification, sub exclusive 4: share shared lock (s): prevents other DML operations, share 5: S / row-x shared row exclusive (SRX): prevents other transaction operations, share / sub exclusive 6: exclusive (x): used for independent access, exclusive

select b.owner,b.object_name,b.object_type,l.session_id,l.locked_mode 
from   v$locked_object l, 
       dba_objects b 
where  b.object_id=l.object_id

It literally means owner, owner and object_ Name: project name, object_ Type item type.

select  lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username user_name,o.owner,o.object_name,o.object_type,s.sid,s.serial#
from    v$locked_object l,        dba_objects o,        v$session s
where   l.object_id=o.object_id and l.session_id=s.sid   order by o.object_id,xidusn desc;

Query who locks the table and the waiting list. The first row is locked and the rest are waiting lists.

2. Find the Sid and serial of the database#

It is said that V $locked_ There are only records above the object line level lock. V $lock records all locks, including system locks. Object must be added when using V $lock_ Name, or you’ll find a pile.

(1) using {V $locked_ Object query lock

select    t2.username,t2.sid,t2.serial#,t2.logon_time from      v$locked_object t1,          v$session t2 where     t1.session_id=t2.sid order by t2.logon_time;

(2) use # V $lock # to query the lock (note to modify the object_name. If it is a row or table lock, it is the table name)

select   o.object_name,s.sid, s.serial#
from    v$lock l,       dba_objects o,       v$session s
where    l.id1 = o.object_id and l.sid = s.sid and o.object_name='object_name';

3. Kill the session

alter system kill session 'sid,serial#'

Use the record found in step 2 to correspond to this statement for deletion. If “ora-00027: cannot kill current session” is prompted when deleting, it may be that there is data not submitted in your own window. Check the window list, or disconnect the database and try again.

来自 https://www.cnblogs.com/huchaoheng/p/hengc.html  修改

来自 https://www.cnblogs.com/huchaoheng/p/hengc.html  修改