Good strategy to implement a listener processing tasks provided by the database

Question

Let's assume this conceptual scenario:

The database is constantly populated with "a ton" of tasks named PleaseSendAnEmail.

A scheduler/listener in background is aimed to handle those tasks.
Of course, rules are that a specific mail shouldn't be sent more than once and that the handling should be the fastest possible.
Possible status for the task are: New, InProgress(facultative regarding the strategies below), Complete and Fail.

Various strategies for the listener (I present one process iteration for each):

Single threaded solution:

• Querying one task whose status is "New".
• Sending the task to the worker EmailSender.
• When the worker did the job, passing the status to Complete

Pros of this solution: Safety !
Cons: Slow, very slow..

Multi-threaded solution:

• Querying several tasks at once whose status is "New".
• Multiple EmailSender workers are instantiated: Dispatching all the tasks to them
• As soon as a worker has handled one occurrence, its corresponding status is set to Complete.
• Meanwhile the workers are processing, a new query is processed, then tasks dispatched to the worker queues etc.. the whole being asynchronous.

Pros: Wow! Fast!
Cons: Potential race conditions, since a same task being currently processed could be retrieve again by the next query since the current worker processing it might not completed yet => More than one mail occurrence could be sent! Bad!

Multi-threaded solution involving the InProgress status:

• Querying several tasks at once whose status is "New".
• Updating immediately those tasks with the status InProgress.
• Multiple EmailSender workers are instantiated: Dispatching all the tasks to them
• As soon as a worker has handled one occurrence, its corresponding status is set to Complete.
• Meanwhile the workers are processing, a new query is processed, then tasks dispatched to the worker queues etc.. the whole being asynchronous.

Pros: multi-threaded and safety! No more mails than expected!
Cons: Increasing access to database...to mark status as InProgress..possible contention on database with a huge number of events.

Multi-threaded solution avoiding queries while at least one worker is still processing:

• Querying several tasks at once whose status is "New".
• Multiple EmailSender workers are instantiated: Dispatching all the tasks to them
• As soon as a worker has achieve one task, its corresponding status is set to Complete.
• Meanwhile the workers are processing, a new query is processed, then tasks dispatched to the worker queues etc.. the whole being asynchronous.

Pros: Multi-threaded, safety! No need to involve a locking status as InProgress!
Cons: If for any reasons, one mail takes 8 seconds to be sent, it will delay the next query! Many jobs could be seen as idle, awaiting for the next query...

Which solution should I use?

Is there any other efficient solution?

Answer 1

There are two key questions to consider. Once you answer those, the rest should fall into place.

First is: who blocks? In the ST solution, the scheduler queue blocks waiting for a single server to complete. In the MT solution the servers block when they run out of work to do and the listener blocks only when there is nothing for it to do. Maximum throughput requires MT plus excess servers. Other considerations may limit how many.

Second is: who knows? If the task status is public, known to all and updated by tasks then your database accesses will be limiting. If the task status is private, known only to and updated only by the listener then database updates are no longer a bottleneck (you can update them occasionally to tell the outside world).

So one listener with a private task status (in memory data structure) and enough email server tasks to minimise waiting times should meet all the requirements you've stated. No race conditions, no unnecessary waiting, completely safe, minimal controlled access to the database. Joy.