what are queue access concurrency solutions?

Question

I am trying to find out the difficulty of implementing a queue system. I know how to implement a basic queue, so i'll explain a little about what i'm after with some background:

I will be implementing a queue where messages will be placed, this will come from several users, the messages will be scheduled to be posted at user defined times (multiple occurrences are allowed with the precision of Minutes, from a UI perspective i will be restricting: "every minute or every hour" occurrences but id like the system to still be able to handle this).

Here is where my question comes in: Eventually I may be in a situation (and maybe not) where MANY messages need to be posted at the current time, I'd like to have several processes (multiple instances of a script) running to fetch [x,10,25] number of messages from the queue at a time and process them. The problem is: how to do this so that each instance processes unique messages (without processing something that is already being processed by another instance)? I'm worried about current connections, how to lock records, and anything else i may not be thinking about.

Technologies I will be using are PHP and MySQL. I am looking for some solutions to the above, terms I should be using in my searches, real world examples, thoughts, comments and ideas?

Thanks you all!

One solution i came across was using Amazon Simple Queue Service ... it promises unique message processing/locking http://aws.amazon.com/sqs/

Answer 1

Well, I'd do it like this:

Make your table for messages and add two more fields - "PROCESS_ID" and "PROCESS_TIME". These will be explained later.

Give each process a unique ID. They can generate it at the startup (like a GUID), or you can assign them yourself (then you can tell them apart more easily).

When a process wants to fetch a bunch of messages, it then does something like this:

1. UPDATE messages SET process_id=$id, process_time=now() where process_id is null LIMIT 20
2. SELECT * FROM messages WHERE process_id=$id

This will find 20 "free" messages and "lock" them. Then it will find the messages that it locked and process them. After each message is processed, DELETE it.

The UPDATE statement should be pretty atomic, especially if you use InnoDB, which wraps each such statement in a transaction automatically. MySQL should take care of all the concurrency there.

The PROCESS_TIME field is optional, but you can use that to see when a process has hanged. If a message is locked for too long, you can conclude that something went wrong and investigate.

Answer 2

Hi Farinspace,

Couple of online solutions:

1. Amazon SQS.
2. Google appengine queue system

I guess the google solution is much cheaper (Could even be free if not using much).

I have also been thinking about implementing queue in PHP/MYSQL and thought of using:

1. mysql get_lock to implement some sort of lock.
2. Put the queue in MYSQL memory heap datastorage, because in memory queue is much faster then on disc queue. But you have the risk of losing data when computer crashes.
3. Use named pipes to communicate with processes.

Answer 3

You could turn the problem around.

Instead of having the problem of getting things out of the queue at the same time. Publish all info as soon as you get it. But publish it with a rule that it is not suposed to be visible until a certain time. Doing things in this way could help you avoid locking / contention problems.

Answer 4

Have a look at the Beanstalkd message queue. There are PHP clients for it. One of the nice features of Beanstalkd (as opposed to e.g. dropr) is that you can delay messages. That is, you can post a message to the queue and it will not be delivered to a client until X seconds have passed.

Beanstalkd does have one big downside though: It's an in-memory queue. That means if it (or your machine) crashes then the queue is empty and the contents lost. Persistence is a feature planned for the next version of beanstalkd.