【转载】Oracle死锁概念,阻塞产生的原因以及解决方案

时间:2022-09-21 20:24:59

参考原文:http://blog.sina.com.cn/s/blog_9d12d07f0102vu72.html

 

锁是一种机制,一直存在;死锁是一种错误,尽量避免。​

首先,要理解锁和死锁的概念:​

1、锁:

定义:简单的说,锁是数据库为了保证数据的一致性而存在的一种机制,其他数据库一样有,只不过实现机制上可能大相径庭。​

那么,锁的种类有哪些?锁的种类有很多,根据保护的对象不同,Oracle数据库锁可以分为以下几大类:DML锁(data locks,数据锁),用于保护数据的完整性;DDL锁(dictionary locks,字典锁),用于保护数据库对象的结构,如表、索引等的结构定义;内部锁和闩(internal locks and latches),保护 数据库的内部结构。​

在实际项目中遇到的最多的是DML锁,也可进一步说是行级锁。这些行级锁在程序并发访问的时候会造成程序很慢,或者直接访问不了的情况—这种现象称为阻塞。那么,产生阻塞的原因是什么呢?定义:当一个会话保持另一个会话正在请求的资源锁定时,就会发生阻塞。被阻塞的会话将一直挂起,直到持有锁的会话放弃锁定的资源为止。四个常见的DML语句会产生阻塞:

1)INSERT 2)​UPDATE 3)DELETE ​4)SELECT…FOR UPDATE​

2、死锁:

定义:当两个用户同时希望持有对方的资源时就会发生死锁。即当两个用户互相等待对方释放资源时,oracle认定产生了死锁,在这种情况下,将以牺牲一个用户为代价,另一个用户继续执行,牺牲的事物将回滚。

例子: 1:用户1对A表进行Update,没有提交。 2:用户2对B表进行Update,没有提交。 此时双反不存在资源共享的问题。 3:如果用户2此时对A表作update,则会发生阻塞,需要等到用户一的事物结束。 4:如果此时用户1又对B表作update,则产生死锁。此时Oracle会选择其中一个用户进行会滚,使另一个用户继续执行操作。​​​

起因: Oracle的死锁问题实际上很少见,如果发生,基本上都是不正确的程序设计造成的,经过调整后,基本上都会避免死锁的发生。​​

死锁产生的原因及四个必要条件:

产生死锁的原因主要是:(1) 因为系统资源不足。(2) 进程运行推进的顺序不合适。(3) 资源分配不当等。如果系统资源充足,进程的资源请求都能够得到满足,死锁出现的可能性就很低,否则就会因争夺有限的资源而陷入死锁。其次,进程运行推进顺序与速度不同,也可能产生死锁。

产生死锁的四个必要条件:(1) 互斥条件:一个资源每次只能被一个进程使用。(2) 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。(3) 不剥夺条件:进程已获得的资源,在末使用完之前,不能强行剥夺。(4) 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。这四个条件是死锁的必要条件,只要系统发生死锁,这些条件必然成立,而只要上述条件之一不满足,就不会发生死锁。

死锁的解除与预防:理解了死锁的原因,尤其是产生死锁的四个必要条件,就可以最大可能地避免、预防和解除死锁。所以,在系统设计、进程调度等方面注意如何不让这四个必要条件成立,如何确定资源的合理分配算法,避免进程永久占据系统资源。此外,也要防止进程在处于等待状态的情况下占用资源。因此,对资源的分配要给予合理的规划。

 

3、锁问题的解决:​​

产生原因:

死锁问题,oracle默认会解决,这里的锁问题主要指的是由于数据库锁机制而导致的阻塞现象,或者是,在实际操作中,由于不正当操作或者程序中的bug,当程序卡在那里的时候造成的锁表现象,或者是,事物进行回滚或者提交时发生了异常,没有回滚成功或者提交成功,导致锁表(这是一种真正意义的死亡,虽然不是死锁,但是和死锁性质一样,甚至比死锁更可怕)。​​​​

处理方法:

1)使用工具:不管任何事情提前预防总是好的,可以用Spotlight软件对数据库的运行状态进行监控。(没用过)

2)​针对本系统此问题的解决方案(类似情况都可以套用哈)

 

现场处理锁表的解决方法(治标不治本)——手动去数据库kill掉相关session:​(执行1和2就行)

--1、生成Kill Session语句

select  'alter system kill session ''' || SID || ',' || SERIAL# || ''';' 

         from (        

                select distinct a.sid,a.Serial#,status,machine,LOCKWAIT,logon_time         

                     from v$session a, v$locked_object b        

               where (a.status = 'ACTIVE' or a.status = 'INACTIVE')            

               and a.sid = b.session_id              

               and b.ORACLE_USERNAME = 'SC1109' --加上用户名是避免把其他系统的会话也关闭,以免伤及无辜       

                 );
--2、批量执行第一步生成的语句,即可。​ --3、可以找出被锁表的sid,serial#和表名语句 SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username,--数据库用户名 l.os_user_name,--操作系统用户名 s.terminal,--主机名称 o.object_name,--表名 s.logon_time--被锁定时间 FROM v$locked_object l, all_objects o, v$session s WHERE l.object_id = o.object_id AND l.session_id = s.sid ORDER BY sid, s.serial#;
--4、所有对象表 select * from all_objects a where lower(a.OWNER) = 'sc1109' and lower(a.OBJECT_TYPE)='table' and lower(a.OBJECT_NAME) like '%tmp%';--表名

--5、造成锁表的sql语句的sid select sid from v$lock where block = 1;
--6、可以找到正在被阻塞的sql语句 select s.sid, q.sql_text from v$sqltext q, v$session s where q.address = s.sql_address and s.sid = &sid --来自所有阻塞的sid order by piece;
--7、可以找到谁阻塞了谁 select s1.username || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from v$lock l1, v$session s1, v$lock l2, v$session s2 where s1.sid = l1.sid and s2.sid = l2.sid and l1.BLOCK = 1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2;