Hacker News new | ask | show | jobs
by jgrahamc 5162 days ago
A lot of that post is specific to SQL Server. I used a MySQL table as a queue as part of Signal Spam (https://www.signal-spam.fr/) and it worked flawlessly. Polling was at 1 minute intervals and queue entries that were completed were simply deleted.
1 comments
jpitz 5162 days ago
That particular use-case is such an anti-pattern in SQL Server ( because SQL Server isn't or hasn't until recently been MVCC ) that Microsoft added Service Broker to the product.

It isn't nearly the anti-pattern, at least for many loads, in an MVCC database engine.

link
endersshadow 5162 days ago
SQL Server has been MVCC for seven years[1]...

And anyway, I disagree with that it's an anti-pattern. If you want to capture information about each step of a process, SQL Server (or any RDBMS) is great at that. However, if what you want is a workflow that your RDBMS is facilitating, and you really don't care about the intermediate steps, you just want to make sure it gets done, then there are a lot of workflow systems out there that do a great job. I'm also a big fan of combining workflow software with logging the steps to SQL Server for a straight-forward process.

But again, it depends on what data's being stored and what types of queuing you're doing. One project I've done that comes to mind is to take multiple sources, grab the changes, then store the combined, conformed data in an operational data store, while flagging the rows that were changed. Downstream systems then pick up the changed rows based on their last run and pull those changes down--completely oblivious to the fact that multiple sources are coming together. In this case, you could abstract it away to the fact that it's a queuing system, but there are multiple inputs and multiple endpoints, so a persistent store with that data is paramount.

[1]: http://en.wikipedia.org/wiki/Multiversion_concurrency_contro...

link
jpitz 5161 days ago
>SQL Server has been MVCC for seven years

You're technically correct, which is the best kind of correct to be. Still, there are nuances: snapshot isolation is not the default, and not many people are aware of it, much less use it.

So far as the pattern/antipattern - I agree wholeheartedly with you. I would put it in the category of flagged patterns - the usage characteristics and especially the usage growth need to be carefully understood when doing this.

link