5

I have an requirement where I have to send the alerts when the record in db is not updated/changed for specified intervals. For example, if the received purchase order doesn't processed within one hour, the reminder should be sent to the delivery manager.

The reminder/alert should sent exactly at the interval (including seconds). If the last modified time is 13:55:45 means, the alert should be triggered 14:55:45. There could be million rows needs to be tracked.

The simple approach could be implementing a custom scheduler and all the records will registered with it. But should poll the database to look for the change every second and it will lead to performance problem.

UPDATE:

Another basic approach would be a creating a thread for each record and put it on sleep for 1 hour (or) Use some queuing concept which has timeout. But still it has performance problems

Any thoughts on better approach to implement the same?

Sivasubramaniam Arunachalam
  • 6,984
  • 15
  • 71
  • 126
  • 1
    is it possible to add a trigger for new puchace-order (inserted) and processed-order (updated) to help tracking? when inserted, also insert to track table in the trigger; when order is processed, then delete from the track table. then you can just monitor the track table which is much smaller than original table. – LiuYan 刘研 Aug 01 '12 at 06:29
  • @LiuYan刘研 even with that, the application needs to poll the database to know which orders must be processed, this strategy is OP question. – Luiggi Mendoza Aug 01 '12 at 06:38

4 Answers4

2

probably using internal JMS queue would be better solution - for example you may want to use scheduled message feature http://docs.jboss.org/hornetq/2.2.2.Final/user-manual/en/html/examples.html#examples.scheduled-message with hornetq.

You can ask broker to publish alert message after exactly 1h. From the other hand during processing of some trading activity you can manually delete this message meaning that the trade activity has been processed without errors.

Andrey Borisov
  • 3,030
  • 15
  • 18
2

Use Timer for each reminder.i.e. If the last modified time is 17:49:45 means, the alert should be triggered 18:49:45 simply you should create a dynamic timer scheduling for each task it'll call exact after one hour.

Saunik Singh
  • 988
  • 9
  • 19
2

It is not possible in Java, if you really insist on the "Real-timeness". In Java you may encouter Garbage collector's stop-the-world phase and you can never guarantee the exact time.

If the approximate time is also permissible, than use some kind of scheduled queue as proposed in other answers, if not, than use real-time Java or some native call.

malejpavouk
  • 3,858
  • 3
  • 36
  • 64
0

If we can assume that the orders are entered with increasing time then:

You can use a Queue with elements that have the properties time-of-order and order-id.

Each new entry that is added to the DB is also enqueued to this Queue.

You can check the element at the start of the Queue each minute.

When checking the element at the start of the Queue, if an hour has passed from the time-of-order, then search for the entry with order-id in the DB.

If found and was not updated then send a notification, else dequeue it from the Queue .

Avi Cohen
  • 2,254
  • 1
  • 21
  • 24