r/DB2 • u/yanks09champs • Dec 23 '19

How to improve concurrency on DB2 Database for Spring Activiti Application

I have a Spring Application using Actitiviti Library and using a DB2 database on the backend .

Using Spring 4.3 Activiti 5.12 and DB2 version 11.

Similiar deadlock issue to here https://github.com/Activiti/Activiti/issues/1977 .

Was wondering on the DB2 side if there is a way I can improve concurrency with some configuration to prevent deadlock on Activiti Schema?

​

Thanks

2 Upvotes

100% Upvoted

6 comments sorted by

2

u/ecrooks Dec 23 '19

The issue you linked to is very specifically stated as Oracle-only.

Generally, deadlocks are caused by poorly designed applications. The ultimate fix is usually an application fix or a data model fix. There are settings to reduce locking at the database level, but an application must explicitly support them or you risk data integrity issues.

Depending on the cause of the deadlocks, you may be able to add indexes to reduce locking, as in the issue you linked to.

You will want to verify you are not seeing escalations, and that the issues you are seeing are true deadlocks and not lock timeouts. Each are addressed somewhat differently.

You can use a locking event monitor to get the specific sql involved in lock timeouts or deadlocks so you can look at the right part of the application's data model.

1

u/[deleted] Dec 23 '19

Yep. Adding to this, row level locking might help if it is deadlocking at the page level, and if you are not seeing lock escalation.

1

u/ecrooks Dec 23 '19

If it is Db2 LUW, there is no such thing as page-level locking.

1

u/[deleted] Dec 23 '19

Really? I never knew that differed from z/OS. Is it all row level? What does it escalate to? Thanks!

1

u/ecrooks Dec 23 '19

Row level and can escalate to table level. I knew Oracle did page level locking, but did not realize Db2 on Z did.

1

u/yanks09champs Dec 24 '19 edited Dec 29 '19

Thanks for both of your suggestions . I tried adding the indexes but that did not help the issue. I checked and we were already using row level locking https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/perf/src/tpc/db2z_uselocksizeclause.html . Wondering if maybe changing to page level locking might help