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