Prevent deadlocks in multi threaded environment when using Oracle Update clause

52 Views Asked by At

I have a multi threaded application connecting to Oracle 19c DB. There is a process table where another application is constantly writing new records. Purpose of the threads in app is to pickup records to be processed in parallel, do some processing and update status in table as COMPLETED.

To ensure that each thread works on unique records from process table, we use below approach -

  1. Each thread first selects available records for processing from process table and sets a particular column named "THREAD-ID" value to its unique thread id.
  2. Next it queries records having only its thread id from process table and processes them.

This is causing deadlocks occasionally b/w threads. One approach suggested is to refactor SQL as below -

UPDATE PROCESS_TABLE SET THREAD-ID = <thread id> 
WHERE ID IN (
  SELECT ID FROM PROCESS_TABLE WHERE STATUS = 'PENDING' AND THREAD-ID = 'UNASSIGNED'
)

I do not have any other high concurrency environment apart from PROD where this deadlock issue is happening. So I cannot test conclusively if above fix will solve the issue. Can anyone advise if above approach will solve deadlock. If yes, what is the justification that can be given for above fix?

0

There are 0 best solutions below