Troubleshooting database Timeouts and deadlocks in Java programs

Resolving database timeouts and deadlocks in Java programs 2011-06-07 11:09 anonymous help test networkfont Size:T | T

The Java program resolves database timeouts and deadlocks, and each program that uses a relational database may experience data deadlock or unusable situations that need to be programmed in the code to resolve. This article mainly introduces the concept of retry logic related to the database transaction deadlock.

AD:

In Java programs to resolve database timeouts and deadlocks, each program that uses a relational database may experience data deadlock or unusable situations that need to be programmed in the code to resolve; This article mainly introduces the concept of retry logic related to the database transaction deadlock, in addition, We will also explore how to avoid deadlocks, such as DB2 (version 9) and Java as an example.

Brief introduction

Each program that uses a relational database may experience data deadlock or unusable situations that need to be programmed in the code to resolve the retry logic concepts related to database transaction deadlock, and also to explore how to avoid deadlocks and other issues, article with DB2 (version 9) And Java as an example to explain.

What is a database lock and deadlock

Lock (Locking) occurs when a transaction obtains a "lock" on a resource, in which case the other transaction cannot change the resource, the mechanism exists to ensure data consistency, and when designing a program that interacts with the database, the lock and resource unavailability must be handled. Locking is a relatively complex concept, and it may take a lot of thought to say it, so in this article, locking is considered only as a temporary event, which means that if a resource is locked, it will always be released at a later time. Deadlocks occur when multiple processes access the same database, and each of these processes has locks that are required by other processes, resulting in the inability of each process to continue.

How to avoid locks

We can use the isolation level mechanism in the transactional database to avoid lock creation, using the isolation level correctly to enable the program to handle more concurrent events (such as allowing multiple users to access data), as well as to prevent loss of modifications (Lost Update), read "Dirty" data (Dirty read), non-repeatable reads ( Nonrepeatable Read) and "virtual" (Phantom) and other issues.

In read-only mode, you can prevent locks from occurring, rather than ambiguous statements that do not commit read-only isolation levels. An SQL statement when you use one of the following commands, you should consider read-only mode:

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

If you include any of these commands, you can say that your SQL statement is ambiguous, so a lock may be the source of the resource problem.

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

1, set the CurrentData to No. This command tells DB2 that the blur cursor is read-only.

2. When appropriate, use user uncommitted read (user uncommitted read) whenever possible.

3. Close all cursors as much as possible.

4, have a correct submission strategy. Make sure that the program frees the resource as soon as it is no longer in use.

How to handle deadlocks and timeouts

Using retry logic in your program, you can handle the following three types of SQL error codes:

1, 904: Return This code indicates that an SQL statement ended because the resource limit has been reached. The changes can be committed or rolled back in the program, and the retry logic is executed.

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

3, 912: The program received this SQL code, indicating a deadlock or timeout, according to the method in 904 to resolve.

The following is a Java code that captures the returned 911,-912,-904 code and retries:

2. for (int i = 0; i < max_retry_attempts; i++) {
4. The following code simulates a transaction
6. try {
8. stmt = Conn.createstatement ();
10. System.out.println ("Transaction started ...");
12. Stmt.executeupdate ("UPDATE 1 ..."); //sql Statement 1
14. Stmt.executeupdate ("UPDATE 2 ..."); //SQL statement 2
16. Stmt.executeupdate ("UPDATE 3 ..."); //SQL statement 3
18. Stmt.executeupdate ("UPDATE 3 ..."); //SQL statement 4
20. Commit All Changes
22. Conn.commit ();
24. System.out.println ("The transaction is complete.  ");
26. Make sure that it runs only once.
28. i = max_retry_attempts;
30. } catch (SQLException e) {
32. /**
34. * If the returned SQL code is-911, the rollback is completed automatically and the program rolls back to the previous commit state.
36. * The program will retry.
38. */
40. if (-911 = = E.geterrorcode ()) {
42. Waiting for Retry_wait_time
44. try {
46. Thread.Sleep (Retry_wait_time);
48. } catch (Interruptedexception E1) {
50. Even if hibernation is interrupted, try again.
52. System.out.println ("hibernation is interrupted.")  ");
54. }
56. }
58. /**
60. * If the returned SQL code is-912, the deadlock and timeout are indicated.
62. * If it is-904, the representative has reached the resource limit.
64. * In this case, the program will roll back and retry.
66. */
68. else if (-912 = = E.geterrorcode () | |-904 = = E.geterrorcode ()) {
70. try {
72. Need to roll back
74. Conn.rollback ();
76. } catch (SQLException E1) {
78. System.out.println ("cannot be rolled back. ";  Color:black ' > + e);
80. }
82. try {
84. Waiting for Retry_wait_time
86. Thread.Sleep (Retry_wait_time);
88. } catch (Interruptedexception E1) {
90. Even if hibernation is interrupted, try again.
92. System.out.println ("hibernation is interrupted.")  "+ E1);
94. }
96. } Else {
98. If there are other errors, no retries are made.
100. i = max_retry_attempts;
102. SYSTEM.OUT.PRINTLN ("error occurred, error code:"
104. + e.geterrorcode () + "SQL Status:"
106. +e.getsqlstate () + "Other information:" + e.getmessage ());
108. }

As can be seen from the above, the program on the deadlock, timeout, the maximum number of locks will be max_retry_attempts retries, second, when the "Maximum number of locks" occurs (-911), the program does not have to manually rollback, because at this time the rollback is automatic, and finally, whenever the return of 911 ,-904,-912 code, the program should wait retry_wait_time for a period of time before the next retry.

Troubleshooting database Timeouts and deadlocks in Java programs