Application Handling of Database Timeouts and Deadlocks Los Angeles CA

provided by: 
Originally published at Internet.com

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. 9044: 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. "