I am using multiple MDB instances reading messages from a JMS Queue.
While processing these messages, each of the instances tries to read data from the same record in a table in the database(Reference number table) .
Using this reference number, each instance tries to insert data into another table, leading to a unique constraint issue since they are reading the same reference number.
For fixing this, the read from the reference number table is changed to "select using for update clause".Now I find that at any point of time, if an instance selects the reference number record for updating, until that instance commits the data to the DB, the other instances keep waiting and cannot read the reference number, essentially removing any parallelism expected from the multiple instances.
In order to avoid this wait by all instances , I am thinking of taking this reference number to some collection in memory, so that all instances can look up and update this data available in the memory.Is this approach possible at all with MDBs? If so, how can synchronization be brought about when updating this data in the collection?When can this data be commited to the Database?
It would be of great help if these queries could be clarified
In the first scenario surely the issue is just to not hold onto the lock ie get lock , get new number, release , do processing (assuming the locks purpose is to just ensure the number is unique), you could use good old JDBC to do that.
I've never played with them but would a entity with a primary key of a generated sequence number not also resolve the issue.
As regards sequencing via memory its usually the old potentially multiple JVMs, machines issue which is usually resolved by some one suggesting a plug in for a given container.
"Eagles may soar but weasels don't get sucked into jet engines" SCJP 1.6, SCWCD 1.4, SCJD 1.5,SCBCD 5
subject: Data concurrency issue with multiple MDB instances reading messages from a Queue