CODEWITHSUNDEEP
oracle10g
Alex
Alex

Reputation: 4473

wait/notify mechanism for multiple readers in Oracle sql?

We have multiple processes which read one database table, get available record and work with it. It works fine.

When there is no record in this table each process waits 5 seconds and reads it again. So, record could idle in the table for 5 seconds which is not good.

What would be recommended solution to eliminate such waiting and proceed immediately when record is created? One solution could be trigger which does something when record created. But this solution requires knowledge of working processes to deliver record to the one of idle processes.

It looks that ideal solution would be when each process starts to read via SQL from something and when record is created one of waiting processes will have it record and other will continue to wait.

Does Oracle 10 provide such or similar mechanism?

Upvotes: 0

Views: 74

Answers (1)

Jon Heller
Jon Heller

Reputation: 36808

Look at Database Change Notification in 10g, which has since been renamed Continuous Query Notification.

I normally like to include an example but it's hard to find a 10g instance these days, and even a short example requires a lot of code. The process looks complicated, it might be better off to use triggers as you suggested, and deal with the tight coupling.

Upvotes: 1

Related Questions