Learning Visually with Examples [Electronic resources] نسخه متنی

11.7. Techniques to Avoid Locking

It is good to know how to diagnose locking problems, but it is even better to know how to prevent them. Avoiding locking problems requires a good application design. The following is a list of items you should consider when developing your applications. For a detailed explanation of these and other techniques, refer to the

DB2 UDB Administration Guide .

• Choose the appropriate isolation level: UR, CS, RS, or RR. As discussed earlier, UR allows for the most concurrency and the least number of locks required, while RR allows for the least concurrency and the most number of locks required. For example, if your application is used for estimation purposes and the exact value of columns is not needed, isolation UR should be used. Choosing the right isolation level guarantees that DB2 takes the right amount of locks that your application requires.

• Issue

  COMMIT statements as frequently as the application logic allows. Issuing a

  COMMIT incurs I/O costs because data is flushed to disk, but it releases locks allowing for more concurrency. Issue

  COMMIT statements even for read-only applications, since S locks are taken (unless using UR Isolation).

• Specify the

  FOR FETCH ONLY clause in the

  SELECT statement. This clause prevents exclusive locks from being taken. The

  FOR READ ONLY clause is equivalent.

• Perform

  INSERT, UPDATE , and

  DELETE statements at the end of a unit of work if possible. These operations require exclusive locks, and they are kept until the end of the UOW (commit/roll back). Putting these statements at the end of a UOW allows for maximum concurrency

• Avoid lock escalations impacting concurrency by tuning the LOCKLIST and MAXLOCKS database configuration parameters.

• When declaring cursors, be specific about their use. If the cursor is to be used only for reads, include the

  FOR READ ONLY clause in the declaration; if the cursor is to be used for updates, include the

  FOR UPDATE clause. In addition, you can specify the columns to be updated in the

  FOR UPDATE clause. For example:

DECLARE mycur1 CURSOR FOR 
SELECT * FROM employee WHERE salary > 10000 
FOR UPDATE OF firstnme, lastname 

By explicitly declaring the use of the cursor, DB2 will choose the correct locks.