Section 11.8. Case Study

11.8. Case Study

On a Monday morning, a developer calls you and requests assistance to resolve a deadlock problem. You find that two identical applications are being executed concurrently and receive this SQL0911 error:

[View full width]
 SQL0911N The current transaction has been rolled back because of a deadlock or timeout.   Reason code "2". 

Reason code 2 indicates that a deadlock was encountered and the transaction was rolled back. Few tools are available to diagnose deadlock problems and you choose to use the deadlock Event Monitor.

Data from the deadlock Event Monitor shows that:

• Application A updated a row in the employee table.

• At the same time, application B updated another row in the employee table as well.

• Before both applications commited their updates, they queried the employee table again for some additional information.

This caused a deadlock like the one in Figure 11.25. To resolve the locking problem, you enable lock deferral with:

 db2set DB2_EVALUNCOMMITTED=YES 

That same day, an application is promoted from the Development environment to the Test environment. The application is a reservation system that is expected to handle requests from at most 200 users concurrently. On the very first run of the application, there are many locking issues, so you are asked to review the application design. Using monitoring information like snapshots (discussed in Chapter 16, Database Performance Considerations), you first note that there is an incredible number of lock escalations. You review the LOCKLIST and MAXLOCKS parameters and decide to increase LOCKLIST by 50 percent. A second run of the application performs a lot better, and the snapshots for this run show there are no longer lock escalation problems, but still there are locking issues.

Next, you review the SQL issued by the application using the snapshots. All the cursors defined in the application are ambiguous; that is, they have not been defined with a FOR READ ONLY or FOR UPDATE clause, so DB2 may not be choosing the correct locking. You also note that the transactions are very long; in other words, COMMIT statements are not issued frequently enough. You voice these concerns to the application developers, who decide to stop the testing while they implement your suggestions.