Oracle Select Query For Simultaneous Access By Applications

Question

Currently working on an application where a same table will be accessed across multiple applications. Process Message_Processor will be accessing a table called Message_Details. Structure of Message Details is as follows

Order Id	Processed_By	Processed_Flag
O1	MP1	Y
O2	MP3	Y
O3	MP2	Y
O1		N
O2		N
O1		N
O4		N

Order Id -> Represents the identifier of an order . A same order can be repeated and hence you see similar multiple id's.

Processed By -> Represents the process which has executed a particular Order . An order can only be processed by a process if either an earlier similar order was executed by the same process or the order has not been processed at all by any message processor. In above example O1 has been processed by MP1 , so any further instance of O1 will only be processed by MP1. Similarly O4 has not been processed by any processor, so it can be processed by any Message processor.

Processed Flag -> Set to Y if processed and N if not processed.

Requirement is to write a query where a processor say MP1 is able to identify which order from the above unprocessed messages can be processed by it. From above table MP1 can only process either O1 or O4 ( Not O2/O3 as they have ben processed by MP3/MP2).

One way is to fire individual query to get the order id that have not been processed and then check whether they have been processed by MP1 earlier or to check whether they have not been processed by any processor till now. However its not a very efficient way to do it.

Can someone help in how can this be achieved in a very efficient manner.

Answer 1

If you have an index on (PROCESSED, PROCESSED_BY) then query which is:

select * ... 
where processed = 'N'
union all
select * ... 
where processed = 'Y' and processed_by = :me

will be very efficient.

A bigger challenge will be that multiple processors will all see those rows with proceesed being "N". Check out the SKIP LOCKED syntax to build some facilities managing this.