CAPABILITY and NOTIFICATIONS table access with Empty values causes (Oracle) Deadlock
Symptoms
A Bamboo instance running on Oracle deadlocks periodically. Errors like the following appear in the logs:
2010-03-03 14:17:58,361 WARN [BAM::Events:pool-1-thread-213] [JDBCExceptionReporter] SQL Error: 60, SQLState: 61000
2010-03-03 14:17:58,361 ERROR [BAM::Events:pool-1-thread-213] [JDBCExceptionReporter] ORA-00060: deadlock detected while waiting for resource
2010-03-03 14:17:58,361 ERROR [BAM::Events:pool-1-thread-213] [SessionImpl] Could not synchronize database state with session
2010-03-03 14:17:58,367 ERROR [BAM::Events:pool-1-thread-213] [NamedThreadFactory] Uncaught exception in thread BAM::Events:pool-1-thread-213
org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: could not update: com.atlassian.bamboo.v2.build.agent.capability.CapabilityImpl#196609; uncategorized SQLException for SQL []; SQL state [61000]; error code [60]; ORA-00060: deadlock detected while waiting for resource
; nested exception is java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource
Caused by: java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
Issue Details
Issue: BAM-5456 - Getting issue details... STATUS
Fixed In: 2.6
Issue https://extranet.atlassian.com/jira/browse/BDEV-3741, fixed in 5.3
Cause
There is a known bug that occurs when there are capabilities or notifications that have NULL values
The issue seems to occur only on Oracle database, as it stores empty string values as NULL.
Resolution
As this bug is now fixed we recommend upgrading Bamboo to at least 5.3.
Otherwise, you can look for null values or empty strings in the Capability and Notifications tables, and then either delete the values or set them to a non-null value.