4 ms·

So the correct version is, I guess: Open db connection 1 get data for receipt Close db connection 1 generate receipt send email open db connection 2 writ

by tormeh 10y ago

So the correct version is, I guess: Open db connection 1 get data for receipt Close db connection 1 generate receipt send email open db connection 2 write success to database close db connection 2 I guess you could speed this up a lot by doing it in bulk instead of opening and closing db connections twice for every email. Anyway, the version you wrote sounds reasonable for everything but really big operations to me, but then again I'm fairly junior.

nawitus 10y ago
You don't need to close the database connection to not have an open transaction, right?
- jedberg 10y ago
  No you don't. But depending on the ORM, some of them batch writes unless you explicitly tell them not to, but will flush when you close the connection. So some programmers will just close the connection because they don't understand the difference or because they've learned that closing the connection guarantees that the data is written.
  - mschuster91 10y ago
    > So some programmers will just close the connection because they don't understand the difference or because they've learned that closing the connection guarantees that the data is written. To be fair, this problem isn't limited to databases. Filesystems, even HDDs/SSDs, have been known to readily ignore flush() calls in order to achieve better benchmark results.
jedberg 10y ago
You can still do it in one transaction, sort of. Replace "send email" with "queue email" and then either eliminate the step that writes the success to the database (pushing that off to the queue processor, who will open or already have open a database connection) or instead write to the DB that you queued the item. The advantage you gain is that the queue is a nice buffer if something gets held up with email sending, and also, the queue processor can work in bulk, say sending 100 emails, and then opening a connection to the DB and writing them all in one statement.
viraptor 10y ago
Depending on the number of email sending workers, it could be closer to: transaction 1: mark an unsent receipt as being processed get the marked receipt send email transaction 2: mark the receipt as sent
1024core 10y ago
So if the "send email" step fails (temporarily), the next worker to come along will grab the same receipt and send it again? I think a better solution would be to use a centralized queue that actually does the mail sending, and retries in case of failure.
- srd 10y ago
  That depends on the implementation of the queue. Most queues have a "failed/retry" concept, where they mark failed jobs to be retried in some set future point in time. So one failed job does not hold up your entire queue processing.
- ChoHag 10y ago
  > a centralized queue that actually does the mail sending, and retries in case of failure. A bit like SMTP?
- lazyant 10y ago
  not necessarily, in the first write we can update receipt status as "queued" so queue system can update to "sent" or retry

by tormeh 10y ago

nawitus 10y ago

You don't need to close the database connection to not have an open transaction, right?

jedberg 10y ago
No you don't. But depending on the ORM, some of them batch writes unless you explicitly tell them not to, but will flush when you close the connection. So some programmers will just close the connection because they don't understand the difference or because they've learned that closing the connection guarantees that the data is written.
- mschuster91 10y ago
  > So some programmers will just close the connection because they don't understand the difference or because they've learned that closing the connection guarantees that the data is written. To be fair, this problem isn't limited to databases. Filesystems, even HDDs/SSDs, have been known to readily ignore flush() calls in order to achieve better benchmark results.

jedberg 10y ago

You can still do it in one transaction, sort of. Replace "send email" with "queue email" and then either eliminate the step that writes the success to the database (pushing that off to the queue processor, who will open or already have open a database connection) or instead write to the DB that you queued the item. The advantage you gain is that the queue is a nice buffer if something gets held up with email sending, and also, the queue processor can work in bulk, say sending 100 emails, and then opening a connection to the DB and writing them all in one statement.

viraptor 10y ago

Depending on the number of email sending workers, it could be closer to: transaction 1: mark an unsent receipt as being processed get the marked receipt send email transaction 2: mark the receipt as sent

1024core 10y ago

So if the "send email" step fails (temporarily), the next worker to come along will grab the same receipt and send it again? I think a better solution would be to use a centralized queue that actually does the mail sending, and retries in case of failure.

srd 10y ago
That depends on the implementation of the queue. Most queues have a "failed/retry" concept, where they mark failed jobs to be retried in some set future point in time. So one failed job does not hold up your entire queue processing.
ChoHag 10y ago
> a centralized queue that actually does the mail sending, and retries in case of failure. A bit like SMTP?
lazyant 10y ago
not necessarily, in the first write we can update receipt status as "queued" so queue system can update to "sent" or retry