Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I'd be wary of any engine in which your scenario does NOT end with a deadlock. –  Erwin Smout Sep 25 '12 at 20:50
    
Where to you see a deadlock (check SQL log again)? MySQL managed to complete the test application in 4 seconds. Another run with 10 parallel threads, each inserted 1.000.000 lines (uhh, tough), completed in 1 hour. –  dma_k Sep 26 '12 at 14:07
    
Have you looked at the diaglog or run db2pd or db2dart to analyze why the locks are happening? Usually there is a reason. Also to note, if you have bad indexes (ie, unused) this could play into this as well as DB2 will lock things until the index is updated (which will happen on inserts). Remove any bad indexes. –  Chris Aldrich Oct 8 '12 at 15:57
    
@ChrisAldrich: Have a look at DDL statement. The table has only two columns, no extra indices (only PK). I don't have a admin access to DB, but out DBA says that locks are exhausted. –  dma_k Oct 12 '12 at 13:53
    
Is there the concept of a savepoint in DB2? If it is present can't you use it in your case... just check whether it solves your problem. –  user14461 Oct 15 '12 at 6:53
show 6 more comments

2 Answers

The deadlocks are probably happening on the index. Can you try to drop the indexes on the table before loading and recreate them later?

share|improve this answer
    
In my question in code block there is {{create table}} statement. From it you can see that there is only one index (for primary key). Unfortunately I cannot drop primary key for bulk inserts, as I want DB to check the consistency. Also it makes everything complicated: how do I decide which transaction drops the index and which re-enables it? –  dma_k Oct 7 '13 at 10:45
add comment

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.

share|improve this answer
    
Thanks for reply. I cannot test it though as we have migrated to MS SQL. My feeling is that it won't help: DB2 will wait for LOCKTIMEOUT and fail anyway. And LOCKSIZE TABLE is certainly not an option. Parameters LOCKTIMEOUT and DLCHKTIME just delay the convulsion before death. However if you can run the scenario over your instance of DB2 I will appreciate. I can send you the test script (Java). –  dma_k Nov 19 '12 at 20:35
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.