1

I'm writing a distributed program that needs to manage exclusive access to some database rows. Clearly, numerous techniques exist for this case, but trying to KISS. Will this ever scale massively to users? dunno, but I want to plan for some future capacity.

I can't necessarily employ a classic PtP or PnS queue model because this processing will be scatter-gather and optionally synchronous. Layering atop the classic queue models would be possible, but I fear more complicated than necessary. Can you recommend one of the following and why to implement some lower-level control? I have a single DB and n processing nodes.

  1. Poll via select for update (or equivalent) for the DB records. There are some who are expressing concern with A) the database contention and B) that polling is still happening.
  2. Network synchronized resource lock, persistent connection waiting for processing notifications (been debating an HTTP server push-style...maybe more complicated than layering atop MQ?),
  3. UDP message push (with TTL) by single dispatcher?

Any pros/cons or how you've implemented the same in past solutions and did you have the same concerns at the DB sync layer?

Jé Queue
  • 10,359
  • 13
  • 53
  • 61
  • 1
    Yeah for Apple //c in your photo! – sdg Jan 18 '11 at 14:32
  • What is the purpose of the exclusive access - to avoid edit collisions? I also don't understand your scatter-gather statement, is the database distributed? – sdg Jan 18 '11 at 14:35
  • Single DB. I have telemetry data that needs to be routed to either a specific endpoint OR a set of endpoints. These endpoints may or may not be configured to be "synchronous" in that I will need to wait for a response. The purpose of the exclusive access is that I only want one process|thread|whatever to be able to route any given message, and there will be hundreds of concurrent routing processes as there will be a lot of messages. Classic MQ, but I can't change the telemetry devices to conform to MQ patterns (sending back responses to the MQ), I need to control them directly. – Jé Queue Jan 18 '11 at 18:03

1 Answers1

0

Ok, just saw this.

"table as a queue" is quite straightforward. Just use lock hints to control behaviour

There are some subtleties related to what you want to do, but generally a single update with assign or OUTPUT will solve the concurrency bit for you. It works and scales well.

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676