Index design | Performance best practices for SQL queries

12.2.3 Avoiding deadlocks

Good initial design of commands to break down business logic into short transactions, using efficient queries to access information, and defining useful indexes will all help to minimize deadlock.

However, the most important design consideration to avoid deadlock is to perform database access on tables in the same order across all areas of an application. If all processes access table A, B, and C in that order each and every time, it is not possible to create a deadlock condition. Deadlocks occur because another process accesses the tables in the order C, B, A. This requires an understanding of the SQL that is being invoked within your transaction, as well as SQL invoked by other transactions or processes.

The following tips that can help in avoiding deadlocks:

• Avoid using Select for update.

• Make your transactions short and issue the update or delete statements at the end of your EJB transactions.

• Index the columns that appear in the Where clauses in your SQL queries, especially if the tables you are selecting from are large tables by nature.