This is a great question.
The main uses of messaging are: scaling, offloading work, integration, monitoring, event handling, routing, networking, push, mobility, buffering, queueing, task sharing, alerts, management, logging, batch, data delivery, pubsub, multicast, audit, scheduling, ... and more. Basically: anything where you need data but don't want to make a database request. (Caching is another, longer story).
Another way of looking at this is to notice that many applications used to be built by assuming that users (people) would perform actions that would be fulfilled by executing a transaction on a database (including reads, writes). But today, many actions are not user-initiated. Instead they are application-initiated. For example "tell me when the book that I want to buy is in stock". The best way to solve this class of problems is with messaging of some sort. Whether you call it middleware or web push or real time salad dressing does not matter. It's all messaging.
When you enable applications to initiate or react to events, then it is much easier to scale because your architecture can be based on loosely coupled components. It is also much easier to integrate those components if your messaging is based on a stable, scalable, serviceable tool, preferably using open standard APIs and protocols.
I hope this helps. We try to maintain a list of useful links about messaging here
Please get in touch with questions and comments on any of this, we are dead easy to find.
Rabbit's queues reside in memory and will therefore be much faster than implementing this in a database. A (good)dedicated message queue should also provide essential queuing related features such as throttling/flow control, and the ability to choose different routing algorithms, to name a couple(rabbit provides these and more). Depending on the size of your project, you may also want the message passing component separate from your database, so that if one component experiences heavy load, it need not hinder the other's operation.
As for the problems you mentioned:
polling keeping the database busy and low performing: Using Rabbitmq, producers can push updates to consumers which is far more performant than polling. Data is simply sent to the consumer when it needs to be, eliminating the need for wasteful checks.
locking of the table -> again low performing: There is no table to lock :P
millions of rows of task -> again polling is low performing: As mentioned above, Rabbitmq will operate faster as it resides RAM, and provides flow control. If needed, it can also use the disk to temporarily store messages if it runs out of RAM. After 2.0, Rabbit has significantly improved on its RAM usage. Clustering options are also available.
In regards to AMQP, I would say a really cool feature is the "exchange", and the ability for it to route to other exchanges. This gives you more flexibility and enables you to create a wide array of elaborate routing typologies which can come in very handy when scaling. For a good example, see:
(source: springsource.com)
and: http://blog.springsource.org/2011/04/01/routing-topologies-for-performance-and-scalability-with-rabbitmq/
Finally, in regards to Redis, yes, it can be used as a message broker, and can do well. However, Rabbitmq has more message queuing features than Redis, as rabbitmq was built from the ground up to be a full-featured enterprise-level dedicated message queue. Redis on the other hand was primarily created to be an in-memory key-value store(though it does much more than that now; its even referred to as a swiss army knife). Still, I've read/heard many people achieving good results with Redis for smaller sized projects, but haven't heard much about it in larger applications.
Here is an example of Redis being used in a long-polling chat implementation: http://eflorenzano.com/blog/2011/02/16/technology-behind-convore/
Best Answer
An overview -
As you asked:
When searching MOM I find info on AMQP which states is a protocol for MOM.. what does this mean?
MOM is about having a messaging middleware (middle layer) between (distributed) system components, and AMQP is protocol (set of rules) for reliably sending and receiving messages. So, a MOM implementation (i.e. Rabbitmq) may use AMQP.
What is MOM then?
Message-Oriented-Middleware - is an approach, an architecture for distributed system i.e. a middle layer for the whole distributed system, where there's lot of internal communication (a component is querying data, and then needs to send it to the other component, which will be doing some processing on the data) so components have to share info/data among them. In short it's a way to design a system, for example: depending upon the overall requirements we need to develop a distributed system, with some internal communication. The biggest advantage of MOM architecture/decision is decoupling of the components i.e. if we're going to change the data query component it'll have no effect on the data processing components, as they're communicating via MOM (e.g. Rabbitmq Cluster) - the data processing component is getting the data in form messages, which then parses and processes them.
MOM at the end is just a design decision, that we use a middleware for gluing our system (distributed) components, a middleware for handling communication between them, in the form of messages (i.e. JSON). To implement a message-oriented-middleware we need more - set of specific rules i.e. how the messages will be published, consumed, how the acknowledgement will work, the lifetime of a message is until it is consumed, the persistence of a message, etc. AMQP is basically these set of rules i.e. a standard/protocol for implementing a MOM i.e. a messaging system using AMQP, means it confines itself by the stated rules. From Wikipedia:
I also have read that RabbitMQ implements the AMPQ protocol.. so why does that make a RabbitMQ a message broker?
Yes, Rabbitmq is a message broker (publisher -> exchange -> queue -> consumer). It's an open source AMQP implementation i.e. a messaging system/broker which confines to AMQP (the AMQP rules) - one can use Rabbitmq as the middleware, hence MOM.
AMQP - is just a set of rules i.e .how messages will be published, kept (in queues), consumed, delivery acknowledgement, etc.
Are a message broker and MOM the same thing?
In simple words, Yes. If we need to go with MOM design for our distributed system, we can simply use Rabbitmq (a message broker; an AMQP implementation) as the middleware.