deadlock的概念(产生的原因)
当二个SESSION都持有对方等待的资源，就会产生死锁，简单点说，有 SESSION A 和 SESSION B 二个 SESSION，SESSION A 要继续下面的操作必须等待 SESSION B完成，而 SESSION B 也一样要等待 SESSION A。对于 deadlock 来说，oracle认为这不是 oracle 的错误，而是应用程序(开发编码不当或者架构上设计不当)而引起的，所以 deadlock 一般情况下很少产生。所以当发生 deadlock(ORA-00060) 时 ORACLE 会在ALERT.LOG中记录下这个错误，并且会生成个相应的 TRACE 文件。
模拟deadlock
scott@HUIYI(192.168.0.222)> create table t01 as select 1 col01 from dual;
Table created.
Elapsed: 00:00:00.25
scott@HUIYI(192.168.0.222)> create table t02 as select 1 col02 from dual;
Table created.
Elapsed: 00:00:00.06
scott@HUIYI(192.168.0.222)> select * from t01;
COL01
———-
1
Elapsed: 00:00:00.03
scott@HUIYI(192.168.0.222)> select * from t02;
COL02
———-
1
Elapsed: 00:00:00.02
– session a(1)
scott@HUIYI(192.168.0.222)> update t01 set col01 = 5;
1 row updated.
Elapsed: 00:00:00.02
– session b(2)
scott@HUIYI(192.168.0.222)> update t02 set col02=5;
1 row updated.
Elapsed: 00:00:00.02
– session a(3)
scott@HUIYI(192.168.0.222)> update t02 set col02=5;
进程挂住，因为在第二步里锁住了t02表，所以第三步在等第二步做出决定(rollback/commit)，才能正常执行。
– session b(4)
scott@HUIYI(192.168.0.222)> update t01 set col01=5;
进程也挂住，原因同上。
同时，session a 会报 ORA-00060
scott@HUIYI(192.168.0.222)> update t02 set col02=5;
update t02 set col02=5
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Elapsed: 00:01:06.10
查看 alert.log 以及相应的 trace 文件
scott@HUIYI(192.168.0.222)> show parameter background_dump
NAME TYPE VALUE
———————————— ———– ——————————
background_dump_dest string /u01/app/oracle/admin/huiyi/bd
ump
scott@HUIYI(192.168.0.222)> !
[oracle@huiyi admin]$ cd /u01/app/oracle/admin/huiyi/bdump/
[oracle@huiyi bdump]$ cat alert_huiyi.log
Wed Jul 30 10:49:12 2008
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/admin/huiyi/udump/huiyi_ora_32667.trc.
trace 文件只取些基本的一些信息(内容太多)
/u01/app/oracle/admin/huiyi/udump/huiyi_ora_32667.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.1.0/db_1
System name: Linux
Node name: huiyi.srs2.com
Release: 2.6.9-42.EL
Version: #1 Wed Jul 12 23:16:43 EDT 2006
Machine: i686
Instance name: huiyi
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 32667, image: oracle@huiyi.srs2.com (TNS V1-V3)
*** 2008-07-30 10:57:47.674
*** ACTION NAME:() 2008-07-30 10:57:47.672
*** MODULE NAME:(SQL*Plus) 2008-07-30 10:57:47.672
*** SERVICE NAME:(SYS$USERS) 2008-07-30 10:57:47.672
*** SESSION ID:(152.11) 2008-07-30 10:57:47.671
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
update t02 set col02=5
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
———Blocker(s)——– ———Waiter(s)———
Resource Name process session holds waits process session holds waits
TX-00080027-000000fa 15 152 X 17 159 X
TX-00090001-00000123 17 159 X 15 152 X
session 152: DID 0001-000F-00000017 session 159: DID 0001-0011-0000000E
session 159: DID 0001-0011-0000000E session 152: DID 0001-000F-00000017
Rows waited on:
Session 159: obj – rowid = 0000CD1B – AAAM0bAAEAAAAGkAAA
(dictionary objn – 52507, file – 4, block – 420, slot – 0)
Session 152: obj – rowid = 0000CD1C – AAAM0cAAEAAAAGsAAA
(dictionary objn – 52508, file – 4, block – 428, slot – 0)
Information on the OTHER waiting sessions:
Session 159:
pid=17 serial=24 audsid=107 user: 54/SCOTT
O/S info: user: oracle, term: pts/1, ospid: 32668, machine: huiyi.srs2.com
program: sqlplus@huiyi.srs2.com (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
Current SQL Statement:
update t01 set col01=5
End of information on OTHER waiting sessions.
– TRACE 文件行数(比想象中多多多了)
[oracle@huiyi udump]$ wc -l huiyi_ora_32667.trc
3616 huiyi_ora_32667.trc
--转自
该贴由koei123转至本版2015-6-1 14:57:12
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2015-3-26 10:14:05 |
赞(
操作
