Oracle数据库锁管理
|
Oracle数据库锁管理 1.锁的概念 数据库是一个多用户使用的共享资源，当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作 不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。 加锁是实现数据库并发控制的一个非常重要的技术，当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该 数据对象有了一定的控制，在该事务释放锁之前，其它的事务不能对此数据对象进行更新操作。 可防止多个会话同时更改同一数据 是在指定语句的最低可能级别自动获取的 不会升级 锁的分类 1)按操作方式分类： DML锁 数据锁：保护数据，修改数据：表级锁（TM锁），事务锁或行级锁（TX锁） DML锁的加锁方式： 共享锁（share）:主要用于TM锁-表级锁 独占锁（exclusive ）:主要用于TM锁-表级锁 共享更新锁（share update）：主要用于TX锁-行级锁
锁模式 row share:行级锁，允许对表的并发访问，但不能对整个表锁定 row exclusive:允许多个进程读取，但只有一个进程写入。 share row exclusive:用于查询整个表，允许其它人查询表中的行，但不允许其它插入更新表里的行。 DDL锁 保护structure schema objects: 表和视图相关的定义。修改结构 SYSTEM锁 系统锁 数据库级的，内存级的，Latch
latch:内存中的资源锁 lock:数据库对象（表、索引等）的锁 导致latch争用而等待的原因非常多，内存中很多资源都可能存在争用，最常见的两类latch争用如下： 1)共享池中的latch争用。 sharpoll 2)数据缓冲池中的latch争用. data cache
最常见的集中共享池里的latch是library cache select * from v$latchname where name like 'library cache%';
资源的争用可以通过如下sql来查看： select event,count(*) from v$session_wait group by event;
数据缓冲池latch争用，最常见的latch争用有： 1) buffer busy waits 2) cache buffer chain
cache buffer chain产生的原因： 当一个会话需要去访问一个内存块时，它首先要去一个像链表一样的结构中 去搜索这个数据块是否在内存当中，当会话访问这个链表的时候需要获取一个 latch,如果获取失败，将会产生 latch cache buffer chain 等待，导致这个等待的 原因是访问相同的数据块的会话太多或者这个列表太长（如果读到内存中的数据太多， 需要管理数据块的hash 列表就会很长，这样会话扫描表表的时间就会增加，持有 chache buffer chain latch的时间就会变长，其它会话获得这个latch的机会就会 降低，等待就会增加）
buffer busy waits产生的原因： 当一个会话需要访问一个数据块，而这个数据块正被另一个用户从磁盘读 取到内存中或者这个数据块正在被别一个会话修改时，当前的会话就需要 等待，就会产生一个buffer busy waits等待。
查看latch的相关sql 查看造成latch buffer cache chains等待事件的热块 select distinct a.owner,a.segment_name from dba_extents a, ( select dbarfil,dbablk from x$bh where hladdr in ( select addr from ( select addr from v$latch_children order by sleeps desc ) where rownum < 20 ) ) b where a.relative_fno = b.dbarfil and a.block_id <= b.dbablk and a.block_id + a.blocks > b.dbablk
2.介绍导致阻塞的原因 所谓阻塞，就是系统如果平时运行正常，突然会停止不动，多半是被阻塞（blocked）住了。 v$lock 这张视图
我们关注的比较多的是 request和block字段，如果某个request列是一个非0值，那么它就是在等待一个锁， 如果block列是1，这个sid就持有了一个锁，并且阻塞别人获得这个锁
存在锁请求其实就是被阻塞： 1) DML语句引起的阻塞 insert update delete select ... for update 2)外键没有创建索引 外键没有创建索引 如果系统中有主键/外键引用关系，并且满足一下三个条件中的任意一个，那么就应该考虑给外键字段创建索引，否则系统的性能可能会 下降甚至阻塞。 1) 主表上有频繁的删除操作 2) 主键上有频繁的修改操作 3) 业务上经常会出现主表和从表做关联查询的情况 3.死锁的检查方法
一。数据库死锁的现象 程序在执行过程中，点击确定或保存按钮，程序没有响应，也没有出现报错。
二、死锁的原理 当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提交， 另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，此时 的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。
三、死锁的定位方法 通过检查数据库表，能够检查出是那一条语句被死锁，产生死锁的机器是那一台。 1) 用dba用户执行以下语句 select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object)
如果有输出结果，则说明有死锁，且能看到死锁的机器是那一台，字段说明： username :死锁语句所用的数据库用户。 lockwait: 死锁的状态，如果有内容表示被死锁。 status:状态，active 表示被死锁 machine:死锁语句所在的机器。 program:产生死锁的语句主要来自那个应用程序。
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.suername, l.object_id, l.session_id, s.serial#, l.oracle_username, l.os_user_name, l.process from v$locked_object l,v$session schema where l.session_id = s.sid
2)kill掉这个死锁的进程： alter system kill session 'sid,serial#'; (其中sid=l.session_id)
批量用这条sql（查锁后再杀锁） select 'alter system kill session' "||sid|| ','||serial#||"';' "deadlock" from v$session where sid in ( select sid from v$lock where block=1); 注意：应当注意对于sid在100以下的应该谨慎，可能该进程对应某个application，如对某个事务，可以kill 3) 如果还不能解决： select pro.spid from v$session ses,v$process pro where ses.sid=xx and ses.paddr=pro.addr; select pro.spid from v$session ses,v$process pro where ses.sid=8888 and ses.paddr=pro.addr; 其中sid用死锁的sid替换
exit
ps -ef | grep spid
sql>!ps -ef| grep 3734 其中spid是这个进程的进程号kill掉这个oracle进程 4、死锁模拟与死锁问题处理 模拟步骤: > startup > create table itpux1 as select * from dba_objects; > select sid from v$mystat where rownum=1; > update itpux1 set object_id=100 where object_id=20; 注意：此窗口不提交
在另外一个窗口会话中 > update itpux1 set object_id=100 where object_id=20; 因为1窗口没提交，所以会卡住 此时如果提交第一会话，则会话2开始运行。
查看那个会话被hang住(查根源者) > select * from dba_blockers; 注：例如这里查出为125
在别一窗口 > select sid,serial# from v$session where sid=125; （查会话的进程号） > alter system kiall session '125,5' immediate;
查找锁和被锁者 select sn.username, m.sid, sn.serial#, m.type, decode (m.lmode, 0, 'none', 1, 'null' 2, 'rowshare', 3, 'rowexcl', 4, 'share', 5, 's/rowexcl', 6, 'exclusive', request, m.id1, m.id2 from v$session sn,v$lock m where (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; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |