File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes EJB and other Java EE Technologies and the fly likes Synchronizing MDBs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Synchronizing MDBs" Watch "Synchronizing MDBs" New topic
Author

Synchronizing MDBs

Raymond Ong
Ranch Hand

Joined: Jul 17, 2005
Posts: 46
In relation to my other question here http://www.coderanch.com/t/302884/JDBC/java/Table-Lock, how can I synchronize my mdbs? I am using Container-managed transaction. Multiple MDBs query and update a single database table, I get SQLExceptions (due to duplicate pk error) because the select" and "update" statements of my MDBs overlap.

For example, MDB 1 checks for the existence of user A in table USERS, if it does not find it, it inserts user A in table USERS. But right before MDB 1 inserts user A, MDB 2 goes through the same process, checks for user A, and does not find it there (MDB 2 hasn't inserted it yet). Then MDB 1 inserts user A. MDB 2 tries to insert and gets an SQLException.

Due to the concurrency of the MDBs, i'm having these problems. Can anyone please advice? Btw, I'm using MySQL.

Thanks and regards,

Raymond
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

I think you are trying to fix a problem in the wrong way. MDBs are designed to allow for the concurrent processing of messages and you want to break that concurrency? Why not use an optimistic locking stategy to avoid your race condition?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
david lightman
Ranch Hand

Joined: Nov 03, 2004
Posts: 82
I agree. This is a business requirement that must be implemented with a different strategy. I would look closer at how the MDB's are receiving these messages. Perhaps for these particular transactions to be processed in order, utilize the same MDB - then for the messsage listener port and queueConnectionFactory, set the max sessions to '1' to ensure messages are processed in order. that is just one idea..
[ September 30, 2005: Message edited by: david lightman ]
Raymond Ong
Ranch Hand

Joined: Jul 17, 2005
Posts: 46
Originally posted by Paul Sturrock:
I think you are trying to fix a problem in the wrong way. MDBs are designed to allow for the concurrent processing of messages and you want to break that concurrency? Why not use an optimistic locking stategy to avoid your race condition?


Hi Paul,

I can use "INSERT ... ON DUPLICATE UPDATE...(do a dummy update like "SET name = name")". That would mean I can remove the checking of the existence of the record in the table; and an executeUpdate() result of 2 (in MySQL) would mean that the record already exists. That's a possibility I'm looking at and would work for the particular scenario I mentioned.

Correct me if I'm wrong but, "SELECT...FOR UPDATE | LOCK IN SHARE MODE" is not applicable to begin with. Consider the scenario:

MDB1 checks if RowA exists... (RowA does not exist)
MDB1 attempts to insert RowA, just as...
MDB2 checks if RowA exists... (RowA does not exist)
MDB2 attempts to insert RowA, just as...
MDB1 inserts RowA (right after MDB2 checks for RowA's existence)
MDB2 inserts RowA (and gets an SQLException)

To begin with, there is no row to select and lock. "SELECT...FOR UPDATE | LOCK IN SHARE MODE" is usd for referrencing existing rows in a table or am I missing something?

Thanks
Raymond Ong
Ranch Hand

Joined: Jul 17, 2005
Posts: 46
Originally posted by david lightman:
I agree. This is a business requirement that must be implemented with a different strategy. I would look closer at how the MDB's are receiving these messages. Perhaps for these particular transactions to be processed in order, utilize the same MDB - then for the messsage listener port and queueConnectionFactory, set the max sessions to '1' to ensure messages are processed in order. that is just one idea..

[ September 30, 2005: Message edited by: david lightman ]


Hi David,

To be exact, this is a user registration procedure. I want to prevent the same username to be inserted in a table. My processors are MDBs for concurrent processing. Although my "INSERT" statement may throw an SQLException, I can never be sure if a thrown SQLException is due to a duplicate key or PK violation, that why I'm not inclined to use that strategy. That's why I'm looking for an elegant and proper way to handle this. As I've suggested above, I can use a "SELECT...ON DUPLICATE UPDATE" (do a dummp update like "username = username") and check if the result is 1 (which means a new username was inserted) or 2 (a username exists and was updated).

Any suggestion?

The messages do not have to be processing in order by the way

Thanks
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
Why are you using MDBs?


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Raymond Ong
Ranch Hand

Joined: Jul 17, 2005
Posts: 46
Originally posted by Roger Chung-Wee:
Why are you using MDBs?


Because I have a JMS client sending the messages.

Thanks.
Raymond Ong
Ranch Hand

Joined: Jul 17, 2005
Posts: 46
...and for async concurrent processing
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Google for "optimistic locking".
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Synchronizing MDBs
 
Similar Threads
Dirty Read / Dirty Write scenarios
How to tackle simultaneous updates done by different user to a single webpage
Geting tables names from data base file
Table-level locking, how?
privileges in oracle database