Global Enqueue Services Deadlock detected

今天开发的同事反映程序不能更新数据了，登上数据库检查了一下，发现死锁了。

Global Enqueue Services Deadlock detected. More info in file
 /oracle/app/oracle/admin/ora10g/bdump/ora10g1_lmd0_11239.trc.
Mon Sep  6 11:09:28 2010
Global Enqueue Services Deadlock detected. More info in file
 /oracle/app/oracle/admin/ora10g/bdump/ora10g1_lmd0_11239.trc.
Mon Sep  6 11:09:34 2010
Global Enqueue Services Deadlock detected. More info in file
 /oracle/app/oracle/admin/ora10g/bdump/ora10g1_lmd0_11239.trc.
Mon Sep  6 11:09:54 2010
Global Enqueue Services Deadlock detected. More info in file
 /oracle/app/oracle/admin/ora10g/bdump/ora10g1_lmd0_11239.trc.
 
 
 
 
 
 
 
 
 GES: Potential blocker (pid=28465) on resource TX-00020002-0036C094;
 enqueue info in file /oracle/app/oracle/admin/ora10g/bdump/ora10g2_lmd0_7916.trc and DIAG trace file
Mon Sep  6 10:33:57 2010
GES: Potential blocker (pid=19912) on resource TX-00330019-003F6C77;
 enqueue info in file /oracle/app/oracle/admin/ora10g/bdump/ora10g2_lmd0_7916.trc and DIAG trace file
Mon Sep  6 10:34:46 2010
GES: Potential blocker (pid=27004) on resource TX-0039001B-00E80C4D;
 enqueue info in file /oracle/app/oracle/admin/ora10g/bdump/ora10g2_lmd0_7916.trc and DIAG trace file
Mon Sep  6 10:35:10 2010
GES: Potential blocker (pid=26970) on resource TX-0054001E-000B4690;
 enqueue info in file /oracle/app/oracle/admin/ora10g/udump/ora10g2_ora_6858.trc and DIAG trace file
Mon Sep  6 10:35:26 2010
GES: Potential blocker (pid=13462) on resource TX-00060017-003E7B1B;
 enqueue info in file /oracle/app/oracle/admin/ora10g/bdump/ora10g2_lmd0_7916.trc and DIAG trace file

 

 

为了快速的让程序恢复更新使用

select *　from　ｖ＄ｌｏｃｋ　where　ｂｌｏｃｋ＝１；把阻塞的进程全部杀死，是可以了但是治标不治本。找到具体的ｓｑｌ，让开发的同事改应用逻辑。

可以使用ａｗｒ找到具体的等待事件，在ｓｑｌ部分找到对应引起阻塞的ｓｑｌ。

或者使用

select sid,username,event from v$session where state in('WAITING') and wait_class!='Idle';

sid从上面的sql获得

select sid,sql_text from v$session a,v$sql b where sid in(282,496) and (b.sql_id=a.sql_id or b.sql_id=a.prev_sql_id);

找到具体sql 告诉开发同事

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16719800/viewspace-672786/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16719800/viewspace-672786/