oracle deadlocks detected
Description
Activity
Zhen Qian June 13, 2014 at 10:13 AM
Please see this blog post about details on ORA-00060 deadlock analysis: http://oracle-error.blogspot.com/2008/10/ora-00060-deadlock-detected-while_20.html
=======================================
In summary
"The session on which ORA-00060 occurred encountered the dead lock and Oracle automatically rolled back the statement that caused the dead lock. No other session got affected, so other sessions functions as usual.
In the affected session, the rolled back statement needs to be re-executed once the resources are available ie. When no other session is competing for the same resource.
Probably it will be a good idea to analyze oracle trace file and then modify the application to avoid this situation.
To avoid deadly embrace dead locks, review the application transaction logic thoroughly in the design phase it-self and ensure tables are ordered with in the transactions/applications in such a way that resource contention not occurs among transactions/applications.
"
=======================================
1. The query "update dash_calendar_item set title=? where id=?" is triggered whenever there is a title change in assignment, schedule or announcement item. More investigation is needed there to locate the update conflict.
2. The other query "delete from dash_news_item item where not exists (select * from dash_news_link link where item.id=link.item_id)" is related with "Dashboard Expire Purge Job" Quartz job. A quick scan in out prod db shows the query has run successfully recently.
select count:yellow_star: from dash_news_item item where not exists (select * from dash_news_link link where item.id=link.item_id)
0
select count:yellow_star: from dash_news_item item where exists (select * from dash_news_link link where item.id=link.item_id)
154876
The incident of deadlock related to this delete query might be other db session was still using the dash_news_item record while a delete attempt was made.
Beth Kirschner June 12, 2014 at 9:11 AM
Note that these oracle deadlocks resolved themselves
Details
Assignee
UnassignedUnassignedReporter
Zhen QianZhen QianLabels
Components
Affects versions
Priority
Critical
Details
Details
Assignee
Reporter
Labels
Components
Affects versions
Priority
Critical
We've been got one deadlock message recently in the server log file:
2014-03-25 11:30:21,938 [Dashboard Event Processing Thread] WARN org.sakaiproject.dash.dao.impl.DashboardDaoImpl - updateCalendarItemTitle: Error executing query: class org.springframework.dao.DeadlockLoserDataAccessException:PreparedStatementCallback; SQL [update dash_calendar_item set title=? where id=?]; ORA-00060: deadlock detected while waiting for resource
; nested exception is java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource
The problem was on one server, and with only one error message.
It is unclear about the root cause.
-----------------------
We got two new instances of dashboard deadlocks today on two app servers, each with one occurrence.
Traverse:
2014-04-07 07:30:23,495 [QuartzScheduler_Worker-3] WARN org.sakaiproject.dash.dao.impl.DashboardDaoImpl - deleteNewsItemsWithoutLinks: Error executing query: class org.springframework.dao.DeadlockLoserDataAccessException:StatementCallback;
SQL [delete from dash_news_item item where not exists (select * from dash_news_link link where item.id=link.item_id)]; ORA-00060: deadlock detected while waiting for resource
; nested exception is java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource
Tempest:
2014-04-07 07:30:16,382 [Dashboard Event Processing Thread] WARN org.sakaiproject.dash.dao.impl.DashboardDaoImpl - updateCalendarItemTitle: Error executing query: class org.springframework.dao.DeadlockLoserDataAccessException:PreparedStatementCallback;
SQL [update dash_calendar_item set title=? where id=?]; ORA-00060: deadlock detected while waiting for resource
; nested exception is java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource