I have a several instances of Message Driven Beans accessing a single MySQL table. My first procedure "selects" a user id, if the user id does not exist it then executes an "insert" statement. Problem is, with my multiple MDB instances, there is a slight overlap when two identical user id's are inserted. The first MDB attempts to insert the user id, just after the second MDB finished checking if the same user id exists. when the second MDB tries to insert, of course it gets a duplicate pk error. My question is how to do table locks in MDBs or MySQL.
I don't think this is possible. I think you will need to use the facilities of the database to generate new unique numbers and the like. Check the MySQL Java documentation. Its probably described with the driver.
You shouldn't be using table locks - why lock the entire table when you only (possibly) need to lock the row?
MySQL should support row locking. You could use SELECT ... FOR UPDATE to lock it. But I think what you need is a proper key generation strategy rather than relying on locks. Or use an optimistic locking pattern. [ September 30, 2005: Message edited by: Paul Sturrock ]
0
