Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

closed as too broad by gnat, GlenH7, MichaelT, World Engineer Dec 23 '13 at 20:38

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

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 8 '11 at 16:58
@Jeremy SQL Server – iterationx Jul 8 '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. – Chad Jul 8 '11 at 17:03
You may also want to look at DDL Triggers (PS: I think this belongs on SO) – Brook Jul 8 '11 at 18:09
up vote 5 down vote accepted

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.

share|improve this answer

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)

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.