Parallel inserts cause deadlock on DB2

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

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.

StackExchange Database Administrators Q#24898, answer score: 2