Bookmark Topic Watch Topic
  • New Topic

Table Lock

 
Raymond Ong
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Report post to moderator
Hi,

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.

Thanks in advance lot for the help
 
Mr. C Lamont Gilbert
Ranch Hand
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Report post to moderator
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 ]
 
Raymond Ong
Ranch Hand
Posts: 46
  • Mark post as helpful
  • send pies
  • Report post to moderator
Hi Paul, let me answer you in the other thread instead.

http://www.coderanch.com/t/317089/EJB-JEE/java/Synchronizing-MDBs

Thanks
 
    Bookmark Topic Watch Topic
  • New Topic