Database deadlocks and transaction timeouts are the result
of contention between two or more clients attempting to access the
same database resource. Deadlock is a special case where a circular
blocking condition between two or more clients, each blocked by the
others, but no one can proceed. Usually these phenomena are not programming
errors. They are caused by business logic accessing database resources
in a complex and inter-depending fashions.
About this task
By using the following best practices and strategies,
these conditions can be minimized.
Procedure
- Identify the affected database resources that caused the
error.
- Examine the exceptions (if any) when the error occurs
can give you clues on the failing entities that caused in the failing
condition.
- If the WebSphere® Application Server default
JPA provider is used, you can enable the product trace group "openjpa.jdbc.SQL=all"
to collect the SQL statements and thread information of the database
resources that are in question. By collating information,
you can find which clients and entities are the artifacts that caused
the failing condition.
- For more complex usage scenario, you can consult the
database documentation for any specialized tool or techniques that
can help identify the objects and transaction that are causing the
data contention issues.
- When the problems are identified, examine the business
logic that uses these resources to determine that their usage does
not cause any prolong contention. There is no one prescribed
solution to resolve deadlock and timeout problems. It highly depends
on the complexity of the business logic and entity relationships.
The basic concept to resolve deadlocks and timeouts is to minimize
the time of resources being held more than it must and allow other
clients to access the same resource. If contention is unavoidable,
application must establish means for recovery when these failing conditions
occur. The following are strategies that might help you minimize the
problem conditions and determine if you can apply one or more to resolve
the problem:
- Make sure that the business logic does not lock entities
more than it must. A JPA application that are mostly read-only
should use optimistic lock semantics offered by JPA by default. JPA
2.0 introduces pessimistic locking functions that allow applications
to explicitly lock entity pessimistically on demand. You should optimize
the number of pessimistic locks that are applied at any one time.
Over locking increases the chance of deadlocks and timeouts, and degrades
application concurrency and throughput.
- Avoid setting data source connection isolation level
more restrictive than it requires. Isolation level has
the same affect that pessimistic lock semantics do at the connection
level. JPA requires the minimum of "read-committed" isolation level
to achieve basic data integrity objective. WebSphere Application Server connection manager
default isolation level is database-specific. See the Programming
interfaces topic for the JDBCConnectionSpec API specification information.
- Minimize the duration of any active transaction. Extended active transaction has the potential of holding out
resources that are required by other clients. Commit any transaction
as soon as it has all the resources completed.
- Optimize business logic to avoid entity inter-dependency. If two sets of business logic uses similar resources, consider
updating the resources in the same order to void the classic deadlock
circular dependency. This depends on the isolation level being used.
It has the same effect as applying implicit lock to the resources
as describe in the next strategy.
- Use pessimistic lock to synchronize concurrent access
to common resource. When the previous strategies do not
apply to your situation, pessimistic locking is an alternative to
synchronize access to common resources used by different concurrent
clients. The application can use JPA 2.0 pessimistic locking functions
to block other clients from accessing the common resource until the
transaction is committed or rolled back. Increase of pessimistic
lock usage can affect concurrency and throughput. WebSphere Application Server JPA Access Intent
also provides another alternative to lock data resources based on
EJB invocation or user-defined task name.
- Handle and recover from deadlock and timeout exception. Most database servers have a built-in deadlock prevention mechanism.
When it detects a deadlock condition, the typical strategy used by
the database is to cancel one of the requests and let the other one
succeed. However the request that is terminated is database-specific
and non-deterministic. If an application allows, it is a good practice
to recover the deadlock condition and attempt to retry the operation.
Results
For more information, review the Apache OpenJPA User Guide.