Synchronizing MDBs

[Raymond Ong]

In relation to my other question here https://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]

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?

[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 ]

[Raymond Ong]

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]

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]

Why are you using MDBs?

[Raymond Ong]

Originally posted by Roger Chung-Wee:
Why are you using MDBs?

Because I have a JMS client sending the messages.

Thanks.

[Raymond Ong]

...and for async concurrent processing

[Paul Sturrock]

Google for "optimistic locking".