How to Write DB2 Program That Avoids Locking

Here are the DB2 program ideas. These avoid locking in DB2. Here is a post on DB2 Complex Interview Questions.

DB2 Four Technics to Avoid Locking

You aware that locking mechanism maintains data concurrency . However, unnecessary locking causes other applications to wait. Here are technics to avoid extra locking and to improve DB2-performance.

1. COMMIT

Issue COMMIT statements, even for READ, UPDATE, DELETE and INSERT operations of SQL. If you issue the COMMIT, it releases exclusive locks.

2. FOR READ ONLY or FOR FETCH ONLY

Use these phrases even in the SELECT statement. So that it avoids exclusive locking. This improves concurrency.

3. INSERT, DELETE, and UPDATE

Use these operations at the end of the particular work. So that it releases exclusive locks. Because these operations need exclusive lock. Here you can read UOW.

4. FOR READ ONLY or FOR UPDATE

Declare these clauses in the CURSORS. These will avoid unnecessary exclusive locks. In the FOR UPDATE, you need to give the columns, you are going to update. Else, the exclusive lock applies to all the columns.