StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is my recommendation and it uses a database to manage the work load.</p> <p>I'm gonna use SQL Server as an example here because that's what I've been using my self.</p> <p>What you do is that you create a stored procedure and/or designate a table as <em>the table</em> for outgoing e-mail. This table, besides that which you need to send the email, has a the following metadata.</p> <pre><code>SendDate IsSent ErrorCount Priority </code></pre> <p>At some point your going to need to fire up a thread or process that does the actual work, but how this is implemented is the interesting bit.</p> <p>With SQL Server you can write a query like this:</p> <pre><code>DELCARE @now datetime; SET @now = GETDATE(); SELECT TOP (5) * FROM OutgoingEmail WITH (ROWLOCK, READPAST, UPDLOCK) WHERE SendDate < @now AND IsSent = 0 AND ErrorCount < 5 ORDER BY Priority ; </code></pre> <p>READPAST can only be specified in transactions operating at the READ COMMITTED or REPEATABLE READ isolation levels. You will set the isolation level when establishing a new connection.</p> <p>Now what will happen is that when you use the <code>SqlCommand</code> object to create a <code>DataReader</code> SQL Server will lock those rows. No other instances will be able to get them. Which means you now effectively have a work queue for your outgoing email. It is however important to remember that you must keep the connection open while you do your processing otherwise the lock will be released.</p> <p>A couple of things to note.</p> <ul> <li>You fetch a bunch of rows and you send them. If you succeed your set the IsSent bit</li> <li>If you crash (an exception is thrown somewhere) you don't discard the email you raise the ErrorCount. You don't delete it, you just raise the count. This is important because if the email for some reason contained input (as opposed to being caused by a network connectivty issue) it could keep crashing to <em>send clients</em> and this is called poisoning and it will prevent bad data from crashing your <em>send clients</em>. Therefore you should ignore email with a high <code>ErrorCount</code></li> <li>You can also empty a rolling schedule and move the <code>SendDate</code> forward as to not atempt the same thing every now and again.</li> <li>The bottleneck will be the SmtpClient class but depending on the speed at which you want your emails to be sent you can spin up as many agents or threads as you need to process the emails in parallel.</li> <li>The priority will ensure that if you need to send high priority email a full queue won't be an issue. e.g. if you wanna send all your e-mails this way, sending a password reset or registration e-mail won't be delayed just because the queue is full, as long as it has higher priority.</li> </ul> <p>One thing to note though is that when errors happen the reason is unknown, I've seen the occasional network problem cause emails to not be sent. What generally will happen with this approach is that the email will just be sent at a later point when the network connectivity or SMTP server is back online. You can also have additional metadata to not send more than one email to a user who is trying to reset his or her password (just because for some unknown reason the emails are not being sent right now).</p> <p>One last thing. You might want to split the table into two or three identical tables. The reason for this is that if you wanna log sent emails for a period of time you don't want them to interfere with your otherwise high performing send queue so you move them to a separate table, simply to maintain a history. It's the same deal with errors, if emails end up causing errors you might wanna log that and you can do that by then moving those emails you of the send queue into an error queue.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload