Solve database timeouts and deadlocks in Java programs

Introduction

Every program using a relational database may encounter data deadlocks or unavailability, which need to be programmed in the Code; this article mainly introduces the retry logic concepts related to Database Transaction deadlocks and other situations. In addition, it will discuss how to avoid deadlocks and other issues. This article uses DB2 (Version 9) and Java as an example to explain.

What are database locks and deadlocks?

Locking occurs when a transaction acquires a "Lock" on a resource, other transactions cannot change the resource, this mechanism is used to ensure data consistency. When designing a program that interacts with the database, the lock and resource unavailability must be handled. Locking is a complicated concept, and it may take a lot of time. Therefore, in this article, we only consider locking as a temporary event, which means that if a resource is locked, it will always be released at a later time. A deadlock occurs when multiple processes access the same database, and the locks owned by each process are required by other processes. As a result, each process cannot continue.

How to Avoid locks

We can use the isolation level mechanism in the transaction database to avoid lock creation. correct use of the isolation level allows the program to process more concurrent events (such as allowing multiple users to access data ), it can also prevent problems such as Lost Update, Dirty Read, Nonrepeatable Read, and Phantom.

Isolation level Problems

	Loss of Modification	Read "dirty" Data	Non-repeated read	Virtual
Repeatable reading	No	No	No	No
Read Stability	No	No	No	Yes
Cursor Stability	No	No	Yes	Yes
Uncommitted read	7No	Yes	Yes	Yes

Table 1: problems with the isolation level of DB2

In read-only mode, the lock can be prevented, without the ambiguous statements at the read-only isolation level that have not been submitted. When an SQL statement uses the following commands, you should consider the read-only mode:

1. JOIN

2. SELECT DISTINCT

3. GROUP

4. ORDER

5. UNION

6. UNION ALL

7. SELECT

8. for fetch only (for read only)

9. SELECT FROM

If you include any of the preceding commands, your SQL statements are ambiguous. Therefore, locks may be the source of resource problems.

In addition, the following are some suggestions to reduce the number of locks:

1. Set CURRENTDATA to NO. This command tells DB2 that the fuzzy cursor is read-only.

2. When appropriate, try to use User Uncommitted Read (Uncommitted Read by the User ).

3. Close all cursors whenever possible.

4. There is a correct submission policy. Make sure that the program releases the resource immediately when it no longer uses the resource.

How to handle deadlocks and timeouts

You can use the retry logic in a program to handle the following three SQL error codes:

1. 904: return this code to indicate that an SQL statement has ended because the resource limit has been reached. The program can submit or roll back the changes and execute the retry logic.

2. 911: The program receives the SQL code, indicating that the maximum number of locks in the database has been reached because there is not enough memory allocated to the lock list.

3. 912: The program receives the SQL code, indicating a deadlock or timeout. Solve the problem according to the method in 904.

Below is a piece of Java code, which captures the returned-911,-912,-904 code and retries:

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.exe cuteUpdate ("UPDATE 1..."); // SQL statement 1
Stmt.exe cuteUpdate ("UPDATE 2..."); // SQL statement 2
Stmt.exe cuteUpdate ("UPDATE 3..."); // SQL statement 3
Stmt.exe cuteUpdate ("UPDATE 3..."); // SQL statement 4
// Submit all changes
Conn. commit ();
System. out. println ("the transaction has been completed. ");
// Make sure that the task is only run once.
I = MAX_RETRY_ATTEMPTS;
} Catch (SQLException e ){
/**
* If the returned SQL code is-911, the rollback is automatically completed and the program rolls back to the previous submission status.
* The program will retry.
*/
If (-911 = e. getErrorCode ()){
// Wait for RETRY_WAIT_TIME
Try {
Thread. sleep (RETRY_WAIT_TIME );
} Catch (InterruptedException e1 ){
// Try again even if the sleep is interrupted.
System. out. println ("Sleep is interrupted. ");
}
}
/**
* If the returned SQL code is-912, it indicates a deadlock and timeout.
*-904 indicates that the resource limit has been reached.
* In this case, the program will roll back and retry.
*/
Else if (-912 = e. getErrorCode () |-904 = e. getErrorCode ()){
Try {
// Rollback required
Conn. rollback ();
} Catch (SQLException e1 ){
System. out. println ("cannot be rolled back. "; Color: black '> + e );
}
Try {
// Wait for RETRY_WAIT_TIME
Thread. sleep (RETRY_WAIT_TIME );
} Catch (InterruptedException e1 ){
// Try again even if the sleep is interrupted.
System. out. println ("Sleep is interrupted. "+ E1 );
}
} Else {
// If other errors occur, no Retry is performed.
I = MAX_RETRY_ATTEMPTS;
System. out. println ("an error occurred. Error Code :"
+ E. getErrorCode () + "SQL status :"
+ E. getSQLState () + "Other information:" + e. getMessage ());
}

As you can see above, the program will retry the deadlock, timeout, and maximum number of locks for MAX_RETRY_ATTEMPTS. Secondly, when the "Maximum number of locks" occurs (-911 ), the program does not need to perform manual rollback because the rollback is completed automatically at this time. Finally, no matter when the code-911,-904,-912 is returned, the program should wait for RETRY_WAIT_TIME for a while before the next retry.

1. How to Reduce SQL Server database deadlocks
2. Is the end of the relational database ever approaching?
3. Solve the Problem of MySQL 5 database connection timeout