I'm writing a process that will run in parallel in many app server instances. Al processes are equal.
Each process needs to read X rows from a table and then process them. No row should be processed twice (or more than once). At the same time this processes read the table, anothe process adds new rows to it, so no full-table-locking mechanism can be used. Each row has a unique ID and i can add as many columns as needed.
Since processes do not communicate with each other i'm worried about locking issues. Im using Oracle DB 126.96.36.199
What is the best way to guarantee that no row will be processed twice and to avoid deadlocks and minimize locking?
I tried using ISOLATION LEVEL SERIALIZABLE but it doesnt work because i can't add new rows while selecting, and if 2 processes select at the same time then a "cant serialize.." error occurs.
I wanted to try
update mytable set process_id_mark = <the_current_process_id> where process_id_mark is null and rownum < X
select * from mytable where process_id_mark = <the_current_process_id>
but someone told me that solution has deadlock problemas.
We were also considering using an extra table as a "semaphore". This table would have the last ID processed and all processes would look there for the "first" id to get. We can guarantee this way that the database is consistent, but the coding process is much more complicated (because of retries and possible failures, etc.)
Thanks in advance