Monday, August 12, 2002 05:54 PM
The problem, as I see it, would be how to serialize access so that a "later" input identical to an "earlier" access is blocked.
First, the easy way:
If you have no primary key you could create one. Most SQL db's have a way to use the row number for a key. Then you could look up the row number using your unique key to see if something exists. If it doesn't, create the row using the unique key info and then update it. Put the check-create logic into a database procedure and run it as an atomic transaction.
Problems with the easy way:
db procedure languages are slow if you are talking a high volume application. Running them inside a transaction makes them even slower. Forcing you to update a row for every row transaction can make things slow is row creation is a common part of your application's work.
A more code intensive way:
Use JMS to communicate with a persistant process within your server. The process will receive messages from all beans that want to create or check the existance of rows. The JMS queue will provide synchronization and serialization of the messages so your process need only run a loop of read queue, check DBMS, if needed create row, open return queue, write result. The last action is either to close the queue to the caller or to cache the queue. My suggestion, if high volume is important, and a subset of your possible callers provide the majority of the calls, is to cache the JMS queue in a priority queue structure. When a calling process's queue is desired find the JMS queue reference in the priority queue, remove it, then insert it at the head of the priority queue. This way active callers are found quickly.
In my experience the more code intensive way ran 5 to 10 times faster than the way that was quicker to write.