5

Multiple clients can modify the DB and all need to be notified if a DB change occurs. Where I work, this is implemented with application threads that poll the DB every second or so. This doesn't strike me as a good way to do it, so I was wondering how do people usually handle this?

Grant Palin
  • 1,721
  • 2
  • 14
  • 28
patrick
  • 1,028
  • 1
    There is nothing to provide for this in ANSI standards but there are some platform-specific features that may help, which database are you using? – Jeremy Jul 08 '11 at 16:58
  • @Jeremy SQL Server – patrick Jul 08 '11 at 17:00
  • It depends how important it is to have up to date data and how many clients there are. A couple of clients hitting a database to check for changes is a different load than thousands of clients. I do not know of a single solution that will always work best in every situation. You may want to refine your question to define the scope a bit tighter. – SoylentGray Jul 08 '11 at 17:03
  • http://stackoverflow.com/questions/2267313/message-from-sql-server-database-to-application-when-something-changes-in-databas – André Paramés Jul 08 '11 at 17:03
  • 1
    You may also want to look at DDL Triggers http://msdn.microsoft.com/en-us/library/ms190989.aspx (PS: I think this belongs on SO) – Brook Jul 08 '11 at 18:09
  • @Brook It's fine here as a question of practices rather than specific implementation details. – Adam Lear Jul 08 '11 at 18:20
  • This question appears to be off-topic because it is a poll based question. Multiple answers could address this question equally well, which makes it a poor fit the for StackExchange Q&A format. –  Dec 22 '13 at 14:13

2 Answers2

5

Polling is the most common solution; the other is to have your middle-tier handle the notifications to other services when updates are sent through them. If you have a bunch of clients sharing a database and you can't change them all then for SQL Server you could probably do this with triggers that input messages to the Service Broker and configure other listener actions to notify your clients. I wouldn't start down that road unless you were really confident you need to do that, polling is likely to be better for most projects. You have to consider how this state gets reflected in your clients; most web and client/server technologies are more or less a request/response model so even updating a middle-tier would not matter until your client polled that middle-tier for status.

Jeremy
  • 4,597
3

Databases are representers of facts. They are not message queues. If you want to know what the current state of your interests, you should consult a database. If you need to know when the state of your interests have changed, you should instead subscribe to a messaging system which the parties that make changes publish to.

At our organization, we use RabbitMQ, an AMQP broker. Others exist, such as ActiveMQ (for amq) and ejabberd (for xmpp).

Using such a solution obviously requires that changes are fed through the message queue in addition to being persisted to the database. A reasonable implementation might look like this:

Actor1 publishes change data to "changeRequest" exchange

ChangeDelegate subscribes to "changeRequest" queue
   upon recipt, ChangeDelegate performs the change transaction
      - if the transaction was successful, ChangeDelegate publishes 
        change data to the "changeNotification" exchange
      - if the transaction failed, ChangeDelegate publishes change data
        to the "changeFailed" exchange

Actor2 subscribes to the "changeNotification" and/or "changeFailed" queues.

You will be very happy with the results of integrating a proper messaging queue into your ecosystem. It makes adding and removing functionality, and adapting new functionality much easier. For example, if the transaction to update the database is slow, but reliable, another actor could instead subscribe to the "changeRequest" queue and get early notification about the change, even before it's happened (although it may ultimately fail)

SingleNegationElimination
  • 4,717