Application Handling of Database Timeouts and Deadlocks

Introduction

Every application that utilizes a relational database can encounter situations where data is deadlocked or unavailable. These situations need to be handled programmatically within your code. This article is designed to introduce the concepts of retry logic related to database transaction locking and unavailability. Furthermore, this article will explain how to avoid locking issues.

Even though the concepts discussed in this article pertain to almost all transactional databases and client applications that use them, do keep in mind that this article uses DB2 (version 9) and Java as points of reference.

What Are Database Locking and Deadlocks?

Locking occurs when one transaction obtains a lock on a resource so that another transaction cannot modify this resource. This mechanism exists to preserve data consistency. Applications that interact with the database must be designed to handle locks and resource unavailability situations. Locking is a complex subject that requires a separate discussion, but for the purpose of this article, I will say that locking is supposed to be a temporary event—this means that if a resource is locked now, it will be released after some time. Deadlocks are situations in which multiple processes accessing the same database each hold locks needed by the other processes in such a way that none of the processes can proceed.

How to Avoid Lock Issues

The creation of locks can be avoided by using an isolation level mechanism available in all transactional databases. The correct use of isolation levels allows your application to be more concurrent (allows multi-user access to the data) and prevents against such database phenomena as Lost Updates, Dirty Reads, Nonrepeatable Reads, and Phantoms (you can read more about these topics in my article "Database Isolation Levels").

Table 1: DB2 Isolation Levels and the Phenomena That Can Occur When Each Is Used

Locking can be prevented in read-only mode, not ambiguous statements using the Uncommitted Read Isolation Level.

An SQL statement is considered read-only when it uses any of the following:

1. JOIN
2. SELECT DISTINCT
3. GROUP BY
4. ORDER BY
5. UNION
6. UNION ALL
7. SELECT
8. FOR FETCH ONLY (FOR READ ONLY)
9. SELECT FROM

Your SQL statement is said to be ambiguous if it does include any of the above statements, Therefore, the lock might contain issues against the resource involved in the statement.

Here are four more recommendations for reducing the number of locks:

1. Set CURRENTDATA to NO. This command tells DB2 that the ambiguous cursor is read-only.
2. Use User Uncommitted Read as much as possible (if appropriate).
3. Close all cursors as soon as possible.
4. Have a correct commit strategy. Make sure your application releases resources as soon as it is deemed appropriate.

How to Dandle Deadlocks and Timeouts

There are three SQL codes that your application can handle using retry logic:

1. 904: This SQL code is returned when a SQL statement was terminated because the resource limit was exceeded. The application code can submit or rollback changes and executes retry logic.
2. 911: The application receives this SQL code when the maximum number of locks for a database was reached because insufficient memory was allocated to the lock list.
   The application code does not need to roll back because this SQL code causes the transaction to be rolled back. The application can execute retry logic.
3. 912: The application receives this SQL code when there is a deadlock or timeout.
   The application code can submit or rollback changes and executes retry logic.

The following is a sample Java code to catch and retry -911, -912, and -904 SQL Return Codes:

for (int i = 0; i < MAX_RETRY_ATTEMPTS; i++) {
   // the following code simulates a transaction
   try {
      stmt = conn.createStatement();
      System.out.println("Transaction started...");
      stmt.executeUpdate("UPDATE 1...");    // sql
      // statement 1
      stmt.executeUpdate("UPDATE 2...");    // sql
      // statement 2
      stmt.executeUpdate("UPDATE 3...");    // sql
      // statement 3
      stmt.executeUpdate("UPDATE 3...");    // sql
      // statement 4
      // commit all updates
      conn.commit();
      System.out.println("Transaction completed.");
      // make sure we run thru the look only once
      i = MAX_RETRY_ATTEMPTS;
   } catch (SQLException e) {
   /**
    * Under SQL code -911, the rollback is automatically issued -
    * the application is rolled back to a previous commit.
    * Under this SQL return code, the application will retry.
    */
   if (-911 == e.getErrorCode()) {
      // wait for RETRY_WAIT_TIME
      try {
         Thread.sleep(RETRY_WAIT_TIME);
      } catch (InterruptedException e1) {
      // we still want to retry, even though sleep was
      // interrupted
      System.out.println("Sleep was interrupted.");
      }
   }
   /**
    * Under SQL code -912, there is deadlock or timeout.
    * Under SQL code -904, the resource limit was exceeded.
    * Under this SQL return code, the application will roll back
    * and retry.
    */
   else if (-912 == e.getErrorCode() || -904 == e.getErrorCode()) {
      try {
         // we need to roll back
         conn.rollback();
      } catch (SQLException e1) {
         System.out.println("Could not rollback. "; color:black'> + e);
      }
      try {
      // wait for RETRY_WAIT_TIME
         Thread.sleep(RETRY_WAIT_TIME);
      } catch (InterruptedException e1) {
      // we still want to retry, even though sleep was
      // interrupted
      System.out.println("Sleep was interrupted." + e1);
      }
   } else {
      // do not retry if we get any other error
      i = MAX_RETRY_ATTEMPTS;
      System.out.println("Error has occured - Error Code: "
      + e.getErrorCode() + " SQL STATE :"
      + e.getSQLState() + " Message : " + e.getMessage());
   }

As you can see from the above example, the application will retry deadlocks, maximum locks, and timeouts MAX_RETRY_ATTEMPTS times. Furthermore, when a "maximum locks" (-911) situation occurs, the application does not need to roll back manually because the rollback occurs automatically. And finally, whenever -911, -904, or -912 occur, the application waits for RETRY_WAIT_TIME before the next retry is executed.

Conclusion

In this article, you have learned how to minimize the occurrence of locks and how to handle situations in your code when your database returns error codes that constitute deadlocks and timeouts.

References

• IBM DB2 Version 9 for Linux, UNIX, and Windows Performance Guide.
• www.ittia.com/dbstar/manual/UG_Gloss.htm

About the Author

Aleksey Shevchenko has been working with object-oriented languages for over seven years. For the past four years, he has served as a technical lead and a project manager. Aleksey has been implementing Enterprise IT Solutions for Wall Street and the manufacturing and publishing industries.