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.