tag:blogger.com,1999:blog-15136575.post7491835608931320998..comments2023-10-17T12:00:16.772+01:00Comments on Code rant: The Database As Queue Anti-PatternMike Hadlowhttp://www.blogger.com/profile/16441901713967254504noreply@blogger.comBlogger56125tag:blogger.com,1999:blog-15136575.post-13965302269712559872019-11-04T07:34:45.662+00:002019-11-04T07:34:45.662+00:00You really want to make sure that both the data yo...You really want to make sure that both the data you are storing and sending the message (or storing a message to be sent) are in the same TRANSACTION, otherwise you might end up storing data and the process never sends the message (or vice versa).<br /><br />Here is a great presentation on the topic:<br /><br />https://vimeo.com/111998645Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-1101821636945923902019-10-18T18:00:29.617+01:002019-10-18T18:00:29.617+01:00The article is bang on - it only looks easy to cre...The article is bang on - it only looks easy to create queuing systems in a database. <br /><br />Message brokers are not storing messages in a single shared table and performing searches through them - messages are going to specific queues. In other words, a broker does not have to search for messages to process.<br /><br />A message broker can be used as a front-end to a database. This provides the ability to disconnect your applications performance from user load - if all of your database operations are driven out of message queues, your application will perform at a consistent level regardless of workload. Even better is if all of your workload, not just database operations, is driven out of message queues.<br /><br />Monitoring progress using a message queue is super easy - have the processing jobs post messages to a monitoring queue which is consumed by your monitoring application.<br /><br />Finally, using a message broker allows you to perform all processing asynchronously, yet allows callers to simulate synchronous calls if they would like to. If a client wants to execute a job synchronously, the producer for that job creates a callback queue and a consumer to monitor it. It then passes the callback queue name into the request. The consumer of that job sends completion messages to that callback queue on completion. When done properly, the resulting data can be fed back into the callers output stream - in other words, the caller appears to have made a synchronous call, but it actually was processed asynchronously. <br /><br />The hardest part about adopting a message broker is convincing people that it is easier than creating your own. And it is actually is easier and far more useful in the long run. A message broker will quickly become a central aspect of almost all of your most complex processing problems.<br /><br />Try it, you will like it!rpbarbatihttps://www.blogger.com/profile/04645764973907648320noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-71515978820860714772016-11-11T19:15:42.298+00:002016-11-11T19:15:42.298+00:00That is exactly my thoughts on the subject. Unfort...That is exactly my thoughts on the subject. Unfortunately I've inherited a terrible J2EE app that does exactly this. What a nightmare to fix and maintain!Eric des Courtishttps://www.blogger.com/profile/12068965860165661386noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-49378393246297610162016-02-05T15:36:17.788+00:002016-02-05T15:36:17.788+00:00Anonymous:
'Messaging systems take far more ti...Anonymous:<br />'Messaging systems take far more time to implement (read: have a higher impact on time to market) than simply doing it in the database, and if you're using a database engine that doesn't suck you're not going to have a performance problem.'<br /><br />You refer to 'SQL Server' as the DB that sucks, yet you sing the praises of MySQL - I fell out of my seat laughing at that one.<br /><br />Both your comment and the post seem based on outdated knowledge of SQL Server, and as others have pointed out most people with a modicum of knowledge would know to use Message Broker (or other message queue) for this sort of thing, and as has been pointed out, it doesn't rely on the definitely not good method of polling like some annoying kid wondering 'are we there yet'.SDChttps://www.blogger.com/profile/06074492906736058141noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-43248586045945029022015-06-05T21:40:57.053+01:002015-06-05T21:40:57.053+01:00Almost only quotes
"When all you know" d...Almost only quotes<br />"When all you know" deeply doesn't include "SQL Server" you can be sure that <br />"SQL Server is very efficient at insertions, or updates, or queries, but rarely all three on the same table."<br />and<br />"sharing a database between applications (or services) is a bad thing"<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-57670547177468251802015-05-18T21:51:56.100+01:002015-05-18T21:51:56.100+01:00Made a queue in postgres the other day, spawned 10...Made a queue in postgres the other day, spawned 100 workers, pdo errored on to many connections and everything locked .. googled .. found this.. Spot on..Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-51938689572153854272015-03-01T12:38:39.103+00:002015-03-01T12:38:39.103+00:00If your message needs to belong to a particular un...If your message needs to belong to a particular unit of work you must store it with that unit of work and forward it unless your messaging service supports distributed transactions.. Even then distributed transactions can be kinda gross under failure scenarios. Even in this case you may still want to forward into a messaging service for aggregation, routing, and integration. But yeah, as others have pointed out, sometimes you problem IS a nail and you need the RDBMS hammer.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-31373407961174938362014-09-17T04:10:32.138+01:002014-09-17T04:10:32.138+01:00Your mom is an anti-patternYour mom is an anti-patternAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-14898608058832911182014-09-16T13:12:05.120+01:002014-09-16T13:12:05.120+01:00While I agree that it is certainly overused, for E...While I agree that it is certainly overused, for ETL code, it is generally preferable that the code runs entirely self-contained. Recently, I wrote some speed improvements that replaced a CURSOR loop with a table and MSSQL Jobs enabling the task to run in parallel for the Staging part. The request database, which we have no control over, force us to iterate over the inputs. By using a table as a task, it ensures that the ETL is self-contained and mobile - I can be up and running in a minutes and be almost guaranteed it will work. <br /><br />For client applications, I prefer queues and please don't mention MSMQ, that thing is the anti-Christ.That Guyhttps://www.blogger.com/profile/03070119808734227297noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-496390676787989942014-09-16T08:21:18.748+01:002014-09-16T08:21:18.748+01:00I don't agree in general. Oracle AQ is a table...I don't agree in general. Oracle AQ is a table-based queue implementation that uses some advanced internal features that have been made available through general SQL in 10g (I believe) via the FOR UPDATE SKIP LOCKED clause.<br /><br />While I wouldn't pass millions of message types through AQ, AQ is certainly capable of providing an easy transactional queue implementation for basic needs.Lukas Ederhttps://www.blogger.com/profile/02028559621679932902noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-8017120375808226812014-01-30T09:44:43.472+00:002014-01-30T09:44:43.472+00:00I recently posted an answer to a question on stack...I recently posted an answer to a question on stackoverflow in defense of messaging vs db integration if you’re interested:<br /><br />http://stackoverflow.com/a/19229234/569662<br /><br />It’s the first time I’ve been able to clearly articulate it. <br />Tomohttps://www.blogger.com/profile/04566644259042307988noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-63300496026356176752013-12-17T18:57:37.062+00:002013-12-17T18:57:37.062+00:00RE: “When all you have is a hammer, every problem ...RE: “When all you have is a hammer, every problem looks like a nail.”<br /><br />A good RDBMS is a toolbox. An ignoramus sees a hammer.Sylnsrhttps://www.blogger.com/profile/14043341124947887392noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-24549953832642339352013-07-01T18:06:12.374+01:002013-07-01T18:06:12.374+01:00oversimplification aside, did you just equate work...oversimplification aside, did you just equate workflow to queue management?<br /><br />Queues can be a way to implement portions of workflows, but semantically speaking, a workflow is more than just an assembly of queues.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-31537463558986376022013-03-10T07:12:57.273+00:002013-03-10T07:12:57.273+00:00There are at least two poor arguments against this...There are at least two poor arguments against this article:<br /><br />1. A message queue needs a persistent store, therefore using a message queue means I am using an RDBMS. <br /><br />Of the 500 ways to poke a hole in that argument, I will choose the easiest. Some message queues do *allow* you to use an RDBMS as the backing store (for instance, ActiveMQ on MySQL), it is always a less efficient choice than highly tuned persistence mechanisms you could be using (like KahaDB). RabbitMQ doesn't allow you to even make a choice of your store. Making the assumption that data storage for message queues is similar to data storage in a typical database is why database-as-queue is an anti-pattern.<br /><br />2. If you are saying "well, I can design around this limitation or plan for this scale," you are missing the point. Yes, you *can* be successful using a database as a queue. You *can* put months of effort into tuning and plenty of money scaling up hardware. You *can* design an elegant retry mechanism, the ability to wiretap messages, apply messaging patterns, content-based-routing, or whatever you can imagine. Does it mean you should? By the time you are done(and have spent precious time fixing the bugs), you end up with what a message queue would have given you off the bat. <br /><br />Put simply, an RDBMS is the right tool for some problems, but an insufficient solution for others. A message queue is a solution designed to fix a problem similar to the one described. To attempt to solve it with an RDBMS is not necessarily doomed to failure, but you are not using the tool best suited to the problem.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-23975114742528909022013-01-17T11:45:38.467+00:002013-01-17T11:45:38.467+00:00So, the next step is to draw up a Data Flow Diagra...So, the next step is to draw up a Data Flow Diagram to understand how the system should be refactored :)hjqw77https://www.blogger.com/profile/01849822112893306922noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-13377565243029642582013-01-15T16:34:16.246+00:002013-01-15T16:34:16.246+00:00Forgot to subscribe to replies...Forgot to subscribe to replies...Brad Woodhttps://www.blogger.com/profile/12704608314449183440noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-12152986831344525102013-01-15T16:33:06.929+00:002013-01-15T16:33:06.929+00:00"...the fastest route through your system is ..."...the fastest route through your system is the sum of all the (long) intervals."<br /><br />Shouldn't that read the *slowest* possible route through your system. That's analagous to hitting every red light on the way to work right as they turn red.<br />The fastest possible route would be no wait at all, like hitting every single green light on the way to work.<br /><br />I would say, the much more useful statistic is that the average time through the system would be the sum of half of each polling interval.<br /><br />Regarding performance of a DB-driven queue, why is there an assumption that every exclusive lock will be a table lock? SQL Server's default behavior is row locks until the lock escelation threshold is met (default 1024). That means dozens of updates could be performed on a table, and dozens of other processes could be reading from it at the same time. WITH(READPAST) will ignore rows with locks on them.<br /><br />Realisitically speaking, I rarely have more than one consumer process looking at the table anyway so locks are a non-issue. All the examples I've seen seem to imply that most database queues will have many concurrent workers. Is that usually the case for others?Brad Woodhttps://www.blogger.com/profile/12704608314449183440noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-34332070860876979092012-11-06T09:30:35.584+00:002012-11-06T09:30:35.584+00:00I think the people talking about how you could use...I think the people talking about how you could use an RDBMS for queuing, or that some messaging systems use an RDBMS for persistence have missed the point somewhat. In those cases your queuing infrastructure is encapsulated and extracted away from your business logic. In the example given your business logic is directly coupled to your queuing mechanism.<br /><br />These two things are separate concerns and should be treated as such. It's easy to arrive at this design as it makes so much sense when you first start building your system. Things (let's say orders) arrive in the system and have a status of 0. You pick up all the orders at 0 and do something with them and update them to status 10. Repeat until they're at status 100 aka 'complete'.<br /><br />That could even work long-term if you don't need to scale. If however the number of orders (or whatever it is you're processing) increases you will hit a wall. It's very likely that this kind of table will require multiple indices on it, it's also certain that it will be being constantly inserted into, updated and read from. That is not a good combination for database performance. The article mentions 'clearing down' records, but often in these scenarios it's extremely unclear when this should happen as there are often business requirements about needing access to these records for a long time ('our customers need to be able to request a refund on their orders after they are complete').<br /><br />I think though that the killer blow in the article is this line 'sharing a database between applications (or services) is a bad thing.' Yes, yes it is. This will slow your development and change becomes harder and harder as your applications ossify from the shared schema up.Steve Friendhttps://www.blogger.com/profile/01082676456418254427noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-42113821012150267372012-10-31T20:54:34.295+00:002012-10-31T20:54:34.295+00:00How do you think persistent queues are implemented...How do you think persistent queues are implemented ? I am marking this article as a FAIL!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-67917790734105424472012-05-29T02:33:59.926+01:002012-05-29T02:33:59.926+01:00Thomas Kejser, formerly of the SQLCAT team, blogge...Thomas Kejser, formerly of the SQLCAT team, blogged about this and how to eliminate the problems for creating queue tables in SQL Server:<br /><br />http://blog.kejser.org/2012/05/25/implementing-message-queues-in-relational-databases/<br /><br />It's a good read for anyone that is interested in how to scale this.Jonathan Kehayias (MCITP, MVP)https://www.blogger.com/profile/01952834570171793587noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-40785713595358713672012-05-14T05:52:28.224+01:002012-05-14T05:52:28.224+01:00Using a database for *integration* isn't alway...Using a database for *integration* isn't always a good idea but using it as a queue may be fine.<br /><br />It has to do with *how* you use it. What you are describing is most definitely not the correct way to implement the 'queue' concept.<br /><br />However, using a single table to store messages and having a single process access that queue (even with threading) in a FIFO way where messages being processed are removed from the queue would work just fine. And it would be using the table as q real queue.<br /><br />A queue should *never* be queried. Another reason why your example isn't really a queue.<br /><br />The workflow type scenario you describe is fine if the processing of 'messages' does not happen based on the quasi-queue table. The table you have represents the state, which is fine. However, proper queueing techniques should be used for the actual messages even if the transport is a sql table.Eben Rouxhttp://www.ebenroux.co.zanoreply@blogger.comtag:blogger.com,1999:blog-15136575.post-2145091936019604872012-05-11T14:56:27.172+01:002012-05-11T14:56:27.172+01:00Moreover, SQL Server has special support for "...Moreover, SQL Server has special support for "queuing tables": WITH(READPAST, UPDLOCK) helps you run multiple "consumers" on a queue.<br /><br />On the other hand, as we found out, SQL Server's default statistics update is very bad for this sort of behavior: If a table goes from zero to more than zero records or vice versa, this will *always* trigger a statistics recomputation, and all your plans are gone ... So, disabling auto statistics on such tables is a must.Harald M.https://www.blogger.com/profile/01133116976966884015noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-53623936442094763792012-05-02T23:58:40.245+01:002012-05-02T23:58:40.245+01:00hmm... and a reliable queue has to use something ...hmm... and a reliable queue has to use something transactional and durable in which to keep the state of the queue ! The point might be to delegate the use of the database to the queue-ing system instead of trying to do it yourself (b/c it's a lot more involved than it seems at first)thorickhttps://www.blogger.com/profile/11415143066331358606noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-59562699661131452932012-05-01T18:50:53.154+01:002012-05-01T18:50:53.154+01:00Rant indeed. No, this is a very useful pattern. ...Rant indeed. No, this is a very useful pattern. What you have posted is as though you just discovered proper message queues. The other commenters have pretty well documented some of the advantages of this GOOD pattern.M Whitenerhttps://www.blogger.com/profile/07768553661279583210noreply@blogger.comtag:blogger.com,1999:blog-15136575.post-64785579206847683052012-05-01T14:18:58.454+01:002012-05-01T14:18:58.454+01:00We all do not have the luxury of having access to ...We all do not have the luxury of having access to Messaging, Memcached and other integration solutions so we have to stick with a database.<br /><br />We have used the "Poor Man's Job Queue" (http://ssmusoke.wordpress.com/2012/04/10/the-poor-mans-job-queue/) and it has held up to some peak uploadsAnonymousnoreply@blogger.com