一、锁介绍
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”
领取专属 10元无门槛券
私享最新 技术干货