Oracle死锁定位及排查解决方案

数据库的锁技术，是很多人头痛的部分，因为概念实在是抽象，如果没有一个深入浅出的道理去理解，光靠看各种搜索出来的文章，会各种云里雾里，下面我从个人的一些经验出发，和大家一起聊一聊锁。

锁：锁在SQL语句开始它们与数据的相互作用时获得，并在事务的整个过程中有效。

一个简单的理解：你要进家门，关门，进门拿东西等，必须先要获得开锁权和关锁权，否则没有办法完成你要做的事。

至于事务：你首先要具备的知识点（如果你还不了解事务，那么谈锁和后面的内容将各种看不透）。

常见类型：

独占模式（排他）：不允许其他任何并发会话以任何方式共享锁定的资源，修改数据时需要这种锁。（房间钥匙只有一把，我拿到进了门，其他人不能再进入且尝试修改数据）。

共享模式：允许对同一块数据的并发读访问。在更改数据时，上升为独占模式。（教室的窗户开着，任何人可以趴在窗户上听教室内老师的上课（读操作），但是想让老师帮你检查作业或去和教室内的学生讲话，就办不到）。

1、行级锁

insert update delete #隐式加行锁(排他)

update emp set ename='Joke' where empno=7499;

在锁释放之前，其他用户不可以对锁定的数据行进行(修改，删除)操作，查询可以

select ... for update #显示加行锁(共享)

select * from emp where deptno=30 forupdate

select ...for update #用于显示锁定将要更新的数据行，防止其他用户在更新之前操作此行。

假如有其他用户要锁定同一资源：可以使用wait子句对锁的等待时间控制

如：在另一用户中：select * from emp where deptno=30 for update wait 2 (等待2秒)

如2秒钟还未释放资源，系统将会给出提示信息。

2、表级锁

锁定表的通用语法：

lock table表名in;

1）共享模式（in share mode)

不允许其他用户插入，更新和删除行。多个用户可以同时在同一表上设置共享锁，这样设置锁的多个用户都只能执行查询。

例：lock table emp in share mode;

2）共享更新模式（in share update mode)

允许多个用户同时锁定表的不同行,允许其他用户进行DML(insertupdate delete)操作,除了已锁定的行。

例：lock table emp in share update mode;

select * from emp where deptno=30 for update //锁定的行

其他用户不能delete ,update部门30的雇员信息

其他用户可以查看锁定的行: select * from empwhere deptno=30

3）排他锁模式(限制性强)

不允许其他用户插入，更新和删除行,允许查看数据，但只有一个用户可以在表中放置排他锁。

例：lock table emp in exclusive mode;

3、死锁

简单的说，进程A等待进程B释放他的资源，B又等待A释放他的资源，这样就互相等待就形成死锁。

比方说：

1:User1修改tab1

2:user2修改tab2

此时两者都没反生死锁，但各自在表建立了排它锁

3:当user1去修改tab2时，出现阻塞情况（等待）

4:当user2再去修改tab1时，出现死锁

（因为user1要commit时，需要user2先提交，但user2提交又需要user1提交）

USERA: lock table scott.emp in share mode;

USERB: lock table scott.emp in share mode ;

USERA: update scott.emp set ename='Smith'where empno=7369;

USERB: update scott.emp set job='CLERK'where empno=7521;

发生死锁

1、数据库死锁的现象：程序在执行的过程中，点击确定或保存按钮，程序没有响应，也没有出现报错。

2、死锁的原理：当对于数据库某个表的某一列做更新或删除等操作，执行完毕后该条语句不提交，另一条对于这一列数据做更新操作的语句在执行的时候就会处于等待状态，此时的现象是这条语句一直在执行，但一直没有执行成功，也没有报错。

3、死锁的定位方法：通过检查数据库表，能够检查出是哪一条语句被死锁，产生死锁的机器是哪一台。

用dba用户执行以下语句

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

如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：

username：死锁语句所用的数据库用户；

lockwait：死锁的状态，如果有内容表示被死锁。

status：状态，active表示被死锁

machine：死锁语句所在的机器。

program：产生死锁的语句主要来自哪个应用程序。

用dba用户执行以下语句，可以查看到被死锁的语句。

select sql_text from v$sql where hash_value in (selectsql_hash_value from v$session where sid in (select session_id fromv$locked_object))

4、死锁的解决方法

一般情况下，只要将产生死锁的语句提交就可以了，但是在实际的执行过程中。用户可能不知道产生死锁的语句是哪一句。可以将程序关闭并重新启动就可以了。

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

1）查找死锁的进程：

sqlplus "/as sysdba" (sys/change_on_install)

SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,

l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROMV$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

2）kill掉这个死锁的进程：

alter system kill session ‘sid,serial#’; （其中sid=l.session_id）

3）如果还不能解决：

select pro.spid from v$session ses,v$process pro whereses.sid=XX and ses.paddr=pro.addr，其中sid用死锁的sid替换: exit

ps -ef|grep spid，其中spid是这个进程的进程号，kill掉这个Oracle进程

select A.SQL_TEXT, B.USERNAME, C.OBJECT_ID,C.SESSION_ID, B.SERIAL#,C.ORACLE_USERNAME,C.OS_USER_NAME,C.Process,''''||C.Session_ID||','||B.SERIAL#||''''

from v$sql A, v$session B, v$locked_object C

where A.HASH_VALUE = B.SQL_HASH_VALUE and

B.SID = C.Session_ID

为了监控Oracle系统中锁的状况，我们需要对几个系统视图有所了解：

1、v$lock视图

v$lock视图列出当前系统持有的或正在申请的所有锁的情况，其主要字段说明如下：

表四：v$lock视图中ID1与ID2字段取值说明

2、v$locked_object视图

v$locked_object视图列出当前系统中哪些对象正被锁定，其主要字段说明如下：

表五：v$locked_object视图字段说明

3、监控脚本

根据上述系统视图，可以编制脚本来监控数据库中锁的状况。

showlock.sql

第一个脚本showlock.sql，该脚本通过连接v$locked_object与all_objects两视图，显示哪些对象被哪些会话锁住：

/* showlock.sql */

column o_name format a10

column lock_type format a20

column object_name format a15

select rpad(oracle_username,10)o_name,session_id sid,

decode(locked_mode,0,'None',1,'Null',2,'Rowshare',

3,'Row Exclusive',4,'Share',5,'Share RowExclusive',6,'Exclusive') lock_type,

object_name ,xidusn,xidslot,xidsqn

from v$locked_object,all_objects

wherev$locked_object.object_id=all_objects.object_id;

showalllock.sql

第二个脚本showalllock.sql，该脚本主要显示当前所有TM、TX锁的信息；

/* showalllock.sql */

select sid,type,id1,id2,

decode(lmode,0,'None',1,'Null',2,'Rowshare',

3,'Row Exclusive',4,'Share',5,'Share RowExclusive',6,'Exclusive')

lock_type,request,ctime,block

from v$lock

where TYPE IN('TX','TM');

点击屏幕右上方分享给好友

让阅读分享成为一种习惯