Will frequent INSERT/DELETE destroy DB2 table/index?

Question

My application needs to process and update e.g. 10 millions records in MAIN_TB table. To improve the performance I run my app on four clients that access my DB2 database through JDBC driver. I don't know how to split records between these clients so I decided to have LOCK_TB table that keeps info about "locked" records in MAIN_TB table.

So the client puts a "lock" record in LOCK_TB table before it tries to work with/update record in MAIN_TB table. Then the client unlocks it. Here is the command:

INSERT INTO LOCK_TB
      (doc_id, locked_on, locked_by) 
VALUES (111, '2017-01-01', 222)

DOC_ID is a primary key and it has foreign key to DOC_ID column in MAIN_TB table.

So if INSERT fails that means the record is already exists (locked) and client skips the record in MAIN_TB table. If it doesn't fail that means new lock record inserted and client can work with data in MAIN_TB table. Once it done it's releasing the lock:

DELETE FROM LOCK_TB WHERE doc_id=111

(obviously there will be not more than four records in LOCK_TB because there are four clients)

So what will happen if these four clients request to INSERT/DELETE into LOCK_TB table at the same time (high requests impact for a short period of time)?

What is the best practice to split work between clients? I'm OK with the model I described above but will it hurt anything (either table, db or server)?

DB2_Philip DB2_Philip · Accepted Answer · 2017-02-01T07:59:02

Technically no 2 threads can request a lock on an object at the same time. The CPU clock alternates cycles to threads.

The thread that request the lock on the table first will get the lock. Subsequent threads will wait until the lock is released. If the lock is waited on longer than the LOCKTIMEOUT database configuration parameter the requestor will timeout with an SQL code 911 reason code 68

This will not damage your table or index.

For a LOT more on locking see http://db2portal.blogspot.co.za/search?q=DB2+Locking

Will frequent INSERT/DELETE destroy DB2 table/index?

1 Answers