死锁的几种情况：
一.ORA-00060 deadlock detECted while wAIting for rESource.
Cause: Your session and another session are waiting for a resource locked by the other.
This conDItion is known AS a deadlock. To resolve the deadlock,
one or more STatements were rolled back for the other session to continue work.
Action Either:
1.Enter a ROLLBACK statement and re-execute all statements since the last commit or
2.Wait until the lock is released, pOSsibly a few minutes, and then re-execute the rolled back statements.
RA-00060:等待资源的时候检测到死锁。
原因:A进程和B进程同时等待彼此所占用的资源，这就是所谓的死锁。解决死锁的思路是，回滚A进程的一条或多条语句，等待B进程完成后再执行。
方案(两者皆可):回滚至最近一次提交，重新执行所有语句。或者，等待自动解锁，这个可能需要一些时间，解锁后，再执行语句。
其实，ORACLE提供的方案，多少有点保守，一般惯用方法：找到这个进程，直接kill掉！
1.模拟死锁实验:
1.1 主表
create table WDZ1
(WDZ1ID NUMBER not null,
MEMO VARCHAR2(20));
alter table WDZ1
add constraint solo1 primary key (WDZ1ID);
1.2 从表(没有外健的索引)
create table WDZ2
(WDZ2ID NUMBER not null,
WDZ1ID NUMBER,
MEMO VARCHAR2(20)
);
alter table WDZ2
add constraint solo2 primary key (WDZ2ID);
alter table WDZ2
add constraint solo3 foreign key (WDZ1ID)
references WDZ1 (WDZ1ID);
1.3 插入数据表到主表
begin
insert into wdz1 values (1,'aa');
insert into wdz1 values(2,'aa2');
insert into wdz1 values (3,'aa3');
insert into wdz2 values(10,3,'wdz3--1');
commit;
end;
1.4 在一个数据库seeesion里面插入数到从表，但是不提交事务
begin
update wdz2 set memo='update wdz2 momo'
where wdz2id=10;
insert into wdz2 values(20,2,'wdz2--1');
end;
对从表进行插入/修改记录，施加的锁也就是行级锁
1.5 在另外一个数据库seeesion里面删除主表数据
delete from wdz1 where wdz1id=1
这时候，程序会死锁，报错ORA-00060，除非上面的对从表的数据操作提交事务或者回滚事务。
2. 具体原因分析
一个数据表的外键主要有3种方式来维护它自己和主表数据的一致性。
(1)delete caSCAde
example:
alter table WDZ2
add constraint XXX foreign key (WDZ1ID)
references WDZ1 (WDZ1ID) on delete cascade;
(2)Set null
example:
alter table WDZ2
add constraint XXX foreign key (WDZ1ID)
references WDZ1 (WDZ1ID) on delete set null;
(3)No action
注意，这是oracle外键使用时候的默认选项。
for example:
alter table WDZ2
add constraint XXX foreign key (WDZ1ID)
references WDZ1 (WDZ1ID);
以前出现死锁主要是我们认为，上例中会对数据表wdz2进行施加行级锁，但是从表(wdz2)的外健是No action，
oracle在删除主表的时候会去寻找所有以主表的主键作为外键的数据表，
然后去看从表是否有该外键的索引，如果没有则会对整个从表施加表级锁，然后对从表进行全表扫描。当然如果从表存在外键的索引，会去访问对应的索引，而不会对从表本身进行加锁。所以死锁头号原因：就是外健不存在索引
二.另外一个模拟死锁环境测试
create table eg_60 ( num number, txt varchar2(10) );
insert into eg_60 values ( 1, 'First' );
insert into eg_60 values ( 2, 'Second' );
commit;
select rowid, num, txt from eg_60;
ROWID NUM TXT
------------------ ---------- ----------
AAAAv2AAEAAAAqKAAA 1 First
AAAAv2AAEAAAAqKAAB 2 Second
Ses#1: update eg_60 set txt='ses1' where num=1;
Ses#2: update eg_60 set txt='ses2' where num=2;
update eg_60 set txt='ses2' where num=1;
> Ses#2 is now waiting for the TX lock held by Ses#1
Ses#1: update eg_60 set txt='ses1' where num=2;
> This update would cause Ses#1 to wait on the TX lock
> held by Ses#2, but Ses#2 is already waiting on this session.
> This causes a deadlock scenario so one of the sessions
> signals an ORA-60.
Ses#2: ORA-60 error
Ses#1: Still blocked until Ses#2 commits or rolls back as ORA-60
only rolls back the current statement and not the entire
transaction.
an ORA-60 error does not write information to the alert log
the user that gets the ORA-60 error writes information to their trace file.
my trace file note :
*** 2008-07-18 16:41:45.212
*** SERVICE NAME:(SYS$USERS) 2008-07-18 16:41:45.203
*** SESSION ID:(155.2375) 2008-07-18 16:41:45.203
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
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-0003000e-00000303 16 155 X 15 153 X
TX-00010011-0000028c 15 153 X 16 155 X
session 155: DID 0001-0010-00000028 session 153: DID 0001-000F-00000013
session 153: DID 0001-000F-00000013 session 155: DID 0001-0010-00000028
Rows waited on:
Session 153: obj - rowid = 0000CC6A - AAAMxqAABAAAPR6AAB
(dictionary objn - 52330, file - 1, block - 62586, slot - 1)
Session 155: obj - rowid = 0000CC6A - AAAMxqAABAAAPR6AAA
(dictionary objn - 52330, file - 1, block - 62586, slot - 0)
Information on the OTHER waiting sessions:
Session 153:
pid=15 serial=1478 audsid=-1 user: 0/SYS
O/S info: user: solo-PC\solo, term: SOLO-PC, ospid: 4864:2468, machine: WORKGROUP\SOLO-PC
program: sqlplus.exe
application name: sqlplus.exe, hash value=0
Current SQL Statement:
update eg_60 set txt=:"SYS_B_0" where num=:"SYS_B_1"
End of information on OTHER waiting sessions.
Current SQL statement for this session:
update eg_60 set txt=:"SYS_B_0" where num=:"SYS_B_1"
说明：
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0003000e-00000303 16 155 X 15 153 X
TX-00010011-0000028c 15 153 X 16 155 X
-----------------------------------------------------------------------
This shows who was holding each lock, and who was waiting for each lock.
The columns in the graph indicate:
Resource Name Lock name being held / waited for.
process V$PROCESS.PID of the Blocking / Waiting session
session V$SESSION.SID of the Blocking / Waiting session
holds Mode the lock is held in
waits Mode the lock is requested in
So in this example:
SID 155 holds TX-0003000e-00000303 in X mode
and wants TX-00010011-0000028c in X mode
SID 153 holds TX-00010011-0000028c in X mode
and wants TX-0003000e-00000303 in X mode
The important things to note here are the LOCK TYPE, the MODE HELD and
the MODE REQUESTED for each resource as these give a clue as to the
reason for the deadlock.
-----------------------------------------------------------------------
Rows waited on:
Session 153: obj - rowid = 0000CC6A - AAAMxqAABAAAPR6AAB
Session 155: obj - rowid = 0000CC6A - AAAMxqAABAAAPR6AAA
-----------------------------------------------------------------------
If the deadlock is due to row-level locks being obtained in different
orders then this section of the trace file indicates the exact rows that
each session is waiting to lock for themselves. Ie: If the lock requests
are TX mode X waits then the 'Rows waited on' may show useful information.
For any other lock type / mode the 'Rows waited on' is not relevant and
usually shows as "no row".
In the above example:
SID 153 was waiting for ROWID 'AAAMxqAABAAAPR6AAB' of object 0xBF6
(which is 3062 in decimal)
SID 155 was waiting for ROWID 'AAAMxqAABAAAPR6AAA' of object 0xBF6
SELECT owner, object_name, object_type
FROM dba_objects WHERE object_id = 52330;
Owner Object_Name Object_Type
------- --------------- ---------------
SYS EG_60 TABLE
SQL> select * from sys.EG_60 where rowid='AAAMxqAABAAAPR6AAB';
NUM TXT
---------- ----------
2 ses1
SQL> select * from sys.EG_60 where rowid='AAAMxqAABAAAPR6AAA';
NUM TXT
---------- ----------
1 ses1
三. DEADLOCK DETECTED ON CONCURRENT INSERTS
To explain one cause of an ora-60.
SCOPE & APPLICATION
-------------------
External use - for Database Administrators.
ORA-60 DEADLOCK DETECTED ON CONCURRENT INSERTS
----------------------------------------------
Most often an ora-60 results from application code problems, but the cause can
also be related to the way transaction layer storage parameters were set for
the database objects.
Oracle database blocks have a transaction layer and a data layer.
Page 12-9 of the Oracle 8i Administrator's Guide explains how to configure
storage for the transaction layer:
"Transaction entry settings for the data blocks allocated for a table, cluster,
or index should be set individually for each object based on the following
criteria:
* the space you would like to reserve for transaction entries compared to
the space you would like to reserve for database data
* the number of concurrent transactions that are likely to touch the same
data blocks at any given time
For example, if a table is very large and only a small number of users simultaneoulsy
access the table, the chances of multiple concurrent transactions requiring access
to the same data block is low. Therefore, INITRANS can be set low, especially
if space is at a premium in the database.
Alternatively, assume that a table is usually accessed by many users at the
same time. In this case, you might consider preallocating transaction entry
space by using a high INITRANS (to eliminate the overhead of having to allocate
transaction entry space, as required when the object is in use) and allowing
a higher MAXTRANS so that no user has to wait to access necessary data blocks."
Its possible to get an ora-60 deadlock when more than one transaction does DML
on a block if INITRANS was set too low. If there are more simultaneous
transactions than slots then the transaction layer needs to grow from INITRANS
towards MAXTRANS, but it can't if the rest of the block is packed with data. In
this case the transaction that could not get a slot will either hang or get an
ora-60 depending on the particular circumstances. This is an issue with tables,
indexes, and clusters.
方法：（-------如果表不大，并发访问的用户多，可以试试下面的步骤）
--（alter index <... index name for the constraint ...> rebuild initrans 5;）
1. Recreate tables and its indexes with inittrans = 5
2. Import data back to tables.
案例:并行运行某个特定的批处理时发生死锁错误
描述:我的E10000 SUn /Solaris 机器有24个处理器。当并行运行某个特定的批处理的时候，总是在同一个时间点上发生死锁的错误(ora-60)。
看起来，毫无疑问这是itl的问题(两个进程在等待共享锁的时候，都持有排他锁，因为initrans等于1)。我们有20个处理器在并行工作，
但是Oracle总是在同一个更新语句上停滞不前。所以，解决方案看起来就是删除掉这个表，然后重新创建拥有更高initrans 的n个initrans 的表
(高到什么程度?)。一些人说这可能是锁的密度带来的负面影响，所以解决方法就是增加pctfree 。你认为这个主意怎么样?选择prctfree
或者是initrans ，在哪种情况下密度会由于这些选项而产生问题。还有，我想要知道更多有关如何读取Oracle在发生死锁的时候创建的dump的信息。
我如何解释这些dump，特别是，如何识别被停止的进程拥有，
同时又被其他会话等待的资源?我觉得这可能会帮助我区别资源的竞争和中断的语句明显显示出来的现象。
解答:ITL用于控制那些想要修改块的事务。有两个参数可以控制ITL。他们分别是INITRANS 和 MAXTRANS 。
INITRANS 制定了ITL可以追踪的起始事务数量。对于表来说，这个参数的默认值是1。
对于索引来说，这个参数默认值是2。
MAXTRANS 制定了ITL可以追踪的最大的事务数量。对于表和索引来说，默认值都是255。
由于越来越多的事务都要修改块(表或者索引)，ITL的长度从INITRANS 个项增长到MAXTRANS 个项。
除了在最极端的情况下，你不需要改变这些参数的默认值。在一些很罕见的情况下，有大量的事务争夺同一个块，
你可以通过增加INITRANS 来加速事务的执行。通过这种方式，ITL不必增长，因为它已经被分配得更大了。
但是就像我说的，这是很少见的情况，我怀疑这个是否会对你有帮助。
设置一个较高的PCTFREE 意味着在你的块中存储的行数更少了。这会将你的数据行分配在更多的块中。
同时它还意味着你要浪费更多的空间。通过将行分配到更多的块中去，你的事务也会跨越更多个块。
出现死锁的情况是因为事务A持有一个资源(resource_1)的锁，同时还在请求另一个资源(resource_2)的锁。
事务B持有第二个资源(resource_2)的锁，同时申请第一个资源(resource_1)的锁。事务A正在等待事务B放开它的锁。
与此同时(这是个关键，“与此同时”)，事务B也在等待事务A释放它的锁。这两个事务就叫做死锁。
换句话说，他们都在等待对方释放锁。同时他们在没有完成之前，又不会释放自己的锁。他们只有当获得另外一个锁的时候，才会完成。
此时，Catch 22就会起作用了。Oracle的关系型数据管理系统自动的检测死锁，并且回滚第一个检测到死锁的过程。
所以，你现在可以看到，当试图获得表中数据行或者索引中条目的锁的时候，死锁就发生了。
这与ITL(还有INITRANS)或者块中剩余空间的大小(还有PCTFREE)毫无关系。调整这些数值不会解决你的死锁问题。
并且这是非常容易测试的。重新构建具有不同数值的表并以与以前完全相同的方式重新运行你的应用程序。你还会看到死锁。
这通常发生在并行运行同一个操作的时候。
现在你可以看一下应用程序，看看它是如何引起死锁的。也许想要实现不同的事务控制(例如串行化事务)来防止死锁情况的再一次发生。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/92530/viewspace-450381/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/92530/viewspace-450381/
扫一扫
9400
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
