How to avoid deadlocks and lock timeouts in the WebSphere JDBC adapter

Introduction: When using WebSphere JDBC Adapter in WebSphere Process Server (WPS), you interact with the database frequently, performing database operations, transaction usage, and administration. In the case of a high load scenario, we found that a database server reported a deadlock and lock timeout. In order to solve this problem, this paper first introduces the scene of deadlock and lock timeout in the process of using WebSphere JDBC Adapter in WPS, then introduces the management of the transaction in WPS, and then further analyzes why these scenarios produce deadlock and lock timeout. Finally, a solution is given to avoid deadlocks and lock timeouts.

Introduction

The WebSphere JDBC Adapter is a resource adapter that provides connectivity between the Java application and the database vendor's EIS solution, and is a WebSphere Adapter built on top of the WebSphere Process Server. Without application data interchange occurs at the database level, Adapter uses SQL statements or stored procedures to transfer data in the form of a business object (Business object, BO), which integrates the database with other application systems.

WebSphere JDBC Adaper can integrate any enterprise application built on a database that uses the JDBC driver (JDBC2.0 version), implement the JDBC API based on the Java-EE JCA technology, and provide Inbound and outbound two operations to connect Connect to the database. In which, under the outbound operation, Bo from the application system to the database, according to the action specified in the Bo (Create, Update, Delete, Retrieve,, Retrieveall, Execute, Exists), Bo as a please To send to Adapter, Adapter passes the BO to a table in a specific database application. If necessary, it can be further transferred to other application systems built on the database and processed accordingly. In the outbound operation, the transaction processing of the database will be involved, which will result in deadlock and lock timeout problem of the database. Next, see how deadlocks and lock timeouts will be created in what scenario.

Concurrent calls to the WebSphere JDBC Adapter scenario cause a deadlock phenomenon

A deadlock is most likely to occur when the WebSphere JDBC Adapter outbound components are concurrently invoked. In Figure 1, we can see that the create,delete operation of the outbound component is called many times by concurrent calls. More generally, when the user request is initiated multiple times, it can lead to multithreaded scenarios. For example, the outbound component is used in a workflow, and the user's multiple requests cause multiple workflows to run concurrently, resulting in a concurrent invocation of the outbound component.

Figure 1. Concurrency in BPEL

When the outbound component is higher in the concurrent invocation, the exception information for the following deadlock is thrown:

Listing 1. Deadlock exception information

[12/30/09 13:23:22:078 CST] 00000055 FFDC
Z com.ibm.ws.sca.internal.j2c.J2CMethodBindingImpl
COM.IBM　. ws.sca.internal.j2c.j2cmethodbindingimpl#0x04 Exception:
Javax.resource.ResourceException:　Javax.resource.ResourceException:
DB2 SQL error:sqlcode=-911, sqlstate=40001,
sqlerrmc=2, driver=3.50.152,　Error code: -911
at Com.ibm.j2ca.jdbc.commands.JDBCDeleteCommand.execute (jdbcdeletecommand.java:379)
at　Com.ibm.j2ca.extension.commandpattern.CommandForCursor.execute
(commandforcursor.java:68)
at Com.ibm.j2ca.extension.commandpattern.Interpreter.
Executewithchildren (interpreter.java:96)
at Com.ibm.j2ca.extension.commandpattern.Interpreter.execute (　interpreter.java:77)
at Com.ibm.j2ca.jdbc.JDBCInteraction.executeInternal (jdbcinteraction.java:358)
at　Com.ibm.j2ca.jdbc.JDBCInteraction.execute (jdbcinteraction.java:139)
at Com.ibm.ws.sca.internal.j2c.J2CMethodBindingImpl.invoke (
J2cmethodbindingimpl.java:242)