With all the big-data postings, now something about traditional SQL, running at DB2 9. We had created a web-application, that was basically a view for a database. The application displayed content of a database after the user would enter search parameters, or everything when no parameter was entered. The database contained a few hundred-thousands records. All in all very simple.
The software worked fine at our test-environment. But when we deployed the software into production, it would hardly ever display data. In the logs we found frequent locking errors as follow:
15:23 ERROR (org.hibernate.engine.jdbc.spi.SqlExceptionHelper) – DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=68, DRIVER=4.1
Searching the web revealed what that error meant: a deadlock or timeout has occurred. We knew the same database was also accessed by another application, Activiti running processes written in BPMN. The processes in the Activiti-engine only inserted a few records per minute, so we didn’t expect that application having a big influence. Yet how can a few inserts per minute have such a big impact?
After some talking to a DBA’er and doing our own experimentation we found out about the problem, which will describe here.