The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database design for messaging queues

Is this bad design?  For example, I have many different clients and written in different languages and they need to communicate asynchronously, I was considering using a message queue, ie OpenJMS(Java MS), but interopability is basically using other java libraries and couldnt figure out the tcp/ip messaging.

Using a database, inserting a record then accessing and then deleting the record seems valid?  Am I wrong?  Of course I would have to clean the database pretty often.  I guess I could use a cron job.
Berlin Brown Send private email
Thursday, September 15, 2005
 
 
Usually for a queue you take the top entry. Is that efficient with a RDBMS? The code I normally see for this looks quite ugly. If you are accessing by a message ID it seems reasonable.
son of parnas
Thursday, September 15, 2005
 
 
The performance hit could be excessive, both for the clients and the server side. If I were you, I would put another try into getting messaging to work. This is a very common way to build systems.
.
Thursday, September 15, 2005
 
 
This is a very common way to build systems.

What is this in this context.  It would be nice if there was a simple messaging framework that doesn't use JMS
Berlin Brown Send private email
Thursday, September 15, 2005
 
 
Try http://sourceforge.net/projects/jace/

I've never used it, but people say it's ok.
Dino Send private email
Thursday, September 15, 2005
 
 
What database are you using ??


I seem to recall Tom Kyte (an pretty good Oracle guy) writing about this.
Basically he said that it is very very hard to do this correctly in the database due to synchronization and lock issues but that Oracle has an inbuilt table type for this in some never version (9 or 10 I think).

P.S.
This is all from rather sketchy memory :)
anon from Iceland
Thursday, September 15, 2005
 
 
JMS is really just an API; the underlying TCP/IP protocols could be anything really.

In the Microsoft world, look at MSMQ. local queuing is built into the OS from w2k up. The API's from .NET are dirt simple. The API's from unmanaged code, unfortunately, are not.

There's also IBM MQSeries, and SonicMQ, which I have no experience with other than that they exist.

Unless you're really, really on a tight budget, you're probably best off buying one of these rather than trying to roll your own. Unless you want to spend at least a year dealing with things like transactional queues, retries, dead-letter queues, poison messages, and other such esoterica.
Chris Tavares Send private email
Thursday, September 15, 2005
 
 
I've done it with various flavors of this strategy:

1. a column, say "lockid" that is a guid or a varchar(32) or varchar(40) allowing nulls.

2a. To get a "batch" of records:
    I. Generate a GUID and store in myvar.
    II. Update table set lock='myvar' where lock is null
    III. select from table where lock='myvar'
    You can have multiple threads polling on the table.
2b. in mysql you can get a SINGLE record if
    you us UPDATE Update table set lock='myvar' where lock is null limit 1
  not sure if SQL Server lets you do "Top 1".

I've used this successfully to run queues (for sending emails), as well as handle inventory (one record per item). Works VERY well.
Michael Johnson Send private email
Thursday, September 15, 2005
 
 
"Basically he said that it is very very hard to do this correctly in the database due to synchronization and lock issues but that Oracle has an inbuilt table type for this in some never version"

A lot easier than writing my own, I am sure I will fail that way.
Berlin Brown Send private email
Friday, September 16, 2005
 
 
> I am sure I will fail that way

Why?
son of parnas
Friday, September 16, 2005
 
 
Well this seems a lot easier than having to hack together a messaging queue service.

-------------------------

Sender:

insert into TABLE 'message1'

Receiver:

select * from TABLE

delete from TABLE

-------------------------

or whatever
Berlin Brown Send private email
Friday, September 16, 2005
 
 
If you have Oracle available, you could use Oracle Advanced Queuing and let the database handle it.

Failing that, and again using Oracle syntax,
the reader can safely select and delete:

DECLARE
  CURSOR c_qrec IS
    SELECT message
    FROM message_tab
    ORDER BY entry_time DESC;
    FOR UPDATE;
    v_qrec c_qrec%ROWTYPE;
    v_found BOOLEAN;
BEGIN
  OPEN c_qrec;
  FETCH c_qrec INTO r_qrec;
  v_found := c_qrec%FOUND;
  IF c_qrec%FOUND THEN
    DELETE FROM message_tab
    WHERE CURRENT OF c_qrec;
  CLOSE c_qrec;
  IF v_found
    do_something(r_qrec.message);
  END IF;
END;
/
Send private email
Friday, September 16, 2005
 
 
Left out an end if:

DECLARE
  CURSOR c_qrec IS
    SELECT message
    FROM message_tab
    ORDER BY entry_time DESC;
    FOR UPDATE;
    v_qrec c_qrec%ROWTYPE;
    v_found BOOLEAN;
BEGIN
  OPEN c_qrec;
  FETCH c_qrec INTO r_qrec;
  v_found := c_qrec%FOUND;
  IF c_qrec%FOUND THEN
    DELETE FROM message_tab
    WHERE CURRENT OF c_qrec;
  END IF;
  CLOSE c_qrec;
  IF v_found
    do_something(r_qrec.message);
  END IF;
END;
/
George Jansen Send private email
Friday, September 16, 2005
 
 
Queues distribute your system. If your system is transactional, you need support for distributed transactions (XA) (e.g. JMS, tuxedo, etc).
Dino Send private email
Friday, September 16, 2005
 
 
Trying to use tables as message queues is likely to be a bad design decision unless your needs are pretty small-scale.

If you're on Oracle I'd strongly second the advice to use Oracle/AQ.

It's my belief there's plenty of interoperability possible between MQ Series (Websphere MQ), MSMQ and Oracle/AQ.  Also I'm guess most J2EE app servers would include an implementation to underly JMS.

The book "Enterprise Integration Patterns" by Hohpe & Woolf is truly excellent on MQ in general if you don't have a copy.
Abstract
Monday, September 19, 2005
 
 
Agreed on the oracle streams AQ if you are on that database.  And by the way, it uses tables internally.  But all you have to do to use it is call a few built in packaged procedures.
Scot Send private email
Friday, September 30, 2005
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz