Parallel inserts cause deadlock on DB2

Question

The scenario is the following: The application attempts to insert into the same table from two parallel threads making 4000 insertions in each thread (separate transaction per thread). It causes the DB always to fail with the following exception:

com.ibm.db2.jcc.a.pn: The current transaction has been rolled back because of a deadlock or timeout. Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95

The full log is ([#1] indicates the 1st thread/transaction, [#2] is correspondingly the 2nd):

SQL: create table line (id integer generated by default as identity, line_number integer not null, constraint line_pk primary key (id))
[#1] SQL: insert into line (line_number) values (1)
[#1] SQL: insert into line (line_number) values (2)
[#2] SQL: insert into line (line_number) values (1)
[#2] SQL: insert into line (line_number) values (2)
[#1] SQL: insert into line (line_number) values (3)
[#2] SQL: insert into line (line_number) values (3)
[#1] SQL: insert into line (line_number) values (4)
[#2] SQL: insert into line (line_number) values (4)
...
[#2] SQL: insert into line (line_number) values (1608)
[#1] SQL: insert into line (line_number) values (1608)
[#2] SQL: insert into line (line_number) values (1609)
[#2] SQL: insert into line (line_number) values (1610)
[#2] SQL: insert into line (line_number) values (1611)
...
[#2] SQL: insert into line (line_number) values (1654)
[#2] SQL: insert into line (line_number) values (1655)
[#1] [org.epo.lifesciences.slice.db.DBTest] Thread #1 has failed
org.springframework.dao.DeadlockLoserDataAccessException: StatementCallback; SQL [insert into line (line_number) values (1608)]; The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95; nested exception is com.ibm.db2.jcc.a.pn: The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.52.95
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTransl
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLException
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:519)
     ...
[#2] SQL: insert into line (line_number) values (1656)
[#2] SQL: insert into line (line_number) values (1657)
...
[#2] SQL: insert into line (line_number) values (3999)
[#2] SQL: insert into line (line_number) values (4000)
[#2] Thread #2 completed

It looks like that lock space is exhausted much earlier then transaction log is exhausted. Solutions which I see (and seem to work):

• Increase lock space, in particular trick the parameters:
  • locklists – maximum storage for lock list configuration parameter
  • maxlocks – maximum percent of lock list before escalation configuration parameter
• Commit more frequently.

Both of them are not strictly acceptable because:

• One need to know in advance what should be the size of locklists which needs to be increased with the growing number of insertions. Thus DB2 cannot automatically adapt to increasing workload efficiently. Also DB instance needs to be restarted when this parameter is changed.
• Often commits also mean that there should be a way to roll back all previous commits if something goes wrong at certain point of time. That complicates the application as it needs to implement "revert" logic (savepoints won't help). Moreover intermediate commits will be visible to data requester, so application needs also to track such "uncompleted" tasks and hide the data from client.

The test application works OK both for MySQL, HSQL and MSSQL as is (with no further tricks). So I believe there should be a way to make it working on DB2 without code refactoring and staying on SQL'92 compliant level. Is it possible with enterprise-level DB2? Perhaps I miss something trivial, any feedback is welcomed.

Answer 1

Also check the LOCKTIMEOUT, DLCHKTIME, and LOCKSIZE parameters. This article from DBI has been helpful to know what to set them to. It slants toward warehousing, but does mention settings for OLTP as well.