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”

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180421G0CK3Y00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。

同媒体快讯

扫码关注云+社区

领取腾讯云代金券