Process Database timeouts and deadlocks in Java programs

Each Program May encounter data deadlocks or unavailability, and these situations need Code This article mainly introduces the retry logic concepts related to Database Transaction deadlocks, and discusses how to avoid deadlocks, Article Take DB2 (Version 9) and Java as examples.

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.

 
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 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 roll back.  " ; Color: Black  '  > + E );    }  Try  {  //  Wait for retry_wait_time    Thread. Sleep (retry_wait_time );}  Catch  (Interruptedexception E1 ){  //  Try again even if 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 (  "  Error occurs. 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.