Oracle 锁与死锁的解决

一、锁介绍

1、DML锁

数据锁：保护数据的。如表级锁（TM锁），事务锁或行级锁（TX锁）

DML的加锁方式

共享锁 ：查询 tm

独占锁（排它锁） tm

共享更新锁 tx

2、DDL锁

保护对象结构的 如表和视图相关定义

3、system锁

数据库级的 、内存级的、latch

latch：内存中的资源锁

lock：

共享锁

排它锁

二、数据库死锁的检查方法

1、用dba用户执行以下语句

select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object);

2、用dba用户执行以下语句、查看到被死锁的语句

select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));

三、死锁问题的处理

经常在oracle 的使用过程中碰到这个问题，所以也总结了一点解决方法

1、查看死锁的进程

sqlplus "/as sysdba"

select s.username,l.session_id,s.serial#,l.oracle_username,l.os_user_name,l.process from v$locked_object l,v$session s where l.session_id=s.sid;

kill 掉这个死锁的进程

alter system kill session 'sid,serial#';

select 'alter system kill session '"||sid||','||serial#||"';'"deadlock" from v$session where sid in (select sid from v$lock where block=1);

查看产生死锁的语句

select sid,serial#,username from v$session where sid in (select blocking_session from v$session);

被锁hang住的sql语句，语句多了机器负载就很高。

select l.sid,s.SERIAL#,sq.sql_text from v$lock l,v$session s,v$sql sq where l.sid=s.sid and s.sql_id=sq.sql_id and s.status='ACTIVE';

四、数据库服务器上排查

1、如果在alter日志中，能看到这样的警告，说明有死锁语句。

日志目录：$ORACLE_HOME/diag/rdbms//trace/alter_.log

根据日志提示，我们查看trace日志，能查看到发生死锁的sql语句。

-最常用的案例（必须掌握）

第1 种：操作系统某个ORACLE 进程占用CPU 高，如何快速抓取SQL 语句？

SELECT a.username,

a.machine,

a.program,

a.sid,

a.serial#,

a.status,

c.piece,

c.sql_text

FROM v$session a, v$process b, v$sqltext c

WHERE b.spid = '&spid' AND b.addr = a.paddr AND a.sql_address = c.address(+)

ORDER BY c.piece;

第2 种：业务报过来，说数据库很慢，作为DBA，怎么样知道数据库是不是很慢，然后如果慢，查出源头？

1)查看等待事件

select event,count(*) from v$session_wait group by event order by 2 desc;

2）等待时间sql的id

SELECT sw.p1,

s.sql_id,

COUNT (*),

(ratio_to_report (COUNT (*)) OVER ()) * 100 pct

FROM v$session s, v$session_wait sw

WHERE s.event LIKE '%direct path read%' AND s.sid = sw.sid

GROUP BY sw.p1, s.sql_id

ORDER BY COUNT (*) DESC;

3）通过id找sql语句

select sql_id,sql_text from v$sql where sql_id='gb82syps2pgp7';

第3 种：如果数据库hang 了，怎么快速马上恢复正常使用（如果只是恢复业务不分析问题）？

如果还有时间，先做一个hang 分析，生成dump，找到问题根源，解决根源后，就恢复正常了。

如果没办法，再来做以下操作：

先上这种（批量按用户量杀）：

SELECT 'alter system kill session '''

|| s.sid

|| ','

|| s.serial#

|| '''; -- kill -9 '

|| p.spid

FROM v$session s, v$process p

WHERE s.PADDR = p.addr AND s.username = 'TEST';

---------------------------

alter system kill session '143,33'; -- kill -9 10928

alter system kill session '139,343'; -- kill -9 16603

alter system kill session '24,423'; -- kill -9 16685

alter system kill session '23,191'; -- kill -9 16804

alter system kill session '136,663'; -- kill -9 17147

alter system kill session '20,421'; -- kill -9 17129

6 rows selected.

再上这种（杀所有连接）：

ps -ef | grep 'LOCAL=NO' |grep oracleitpuxfs | kill -9 `awk ''`

第4 种：如何查系统有死锁，并快速杀死锁？

语句1：

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"

FROM v$session WHERE sid IN (SELECT sid FROM v$lock WHERE block = 1);

sys@itpuxfs> alter system kill session '136,661';

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

语句2：

select sn.username, m.sid,sn.serial#,m.type,decode (m.lmode,0,'none',1,'null',2,'rowshare',3,'rowexcl.',4,'share',5,'srowexcl.',6,'exclusive',lmode,ltrim (to_char (lmode, '990'))) lmode,decode (m.request,0,'none',

1,'null',2,'rowshare',3,'rowexcl.',4,'share',5,'srowexcl.',6,'exclusive',request,

ltrim (to_char (m.request, '990'))) request,m.id1,m.id2 from v$session sn, v$lock mwhere (sn.sid = m.sid and m.request != 0) --存在锁请求，即被阻塞

or (sn.sid = m.sid --不存在锁请求，但是锁定的对象被其他会话请求锁定

and m.request = 0 and lmode != 4 and (id1, id2) in(select s.id1, s.id2

from v$lock s where request != 0 and s.id1 = m.id1 and s.id2 = m.id2))

order by id1, id2, m.request;

ALTER SYSTEM KILL SESSION '19,417' IMMEDIATE;

欢迎关注“自学Oracle”