How to Code Correctly to Avoid Locking in DB2

Here are the DB2 concepts to improve performance and avoid locking. SQL failures degrade performance. Below is the list of Technics avoid locking. You may also like DB2 Complex Interview Questions.

---

These Four Technics Avoid Locking in DB2

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.

Related Posts