Issues with EntityManger.lock() + mySQL

[atchijov-vgw]

Hi,

     I am having strange problems with locking and wonder if some one can shade some light on it.

     In our system we have an API which deals with a record in a table. Theoretically this API should be called once per record but it is possible that  it gets invoked more than once. To avoid concurrent updates, we are using EntityManager.lock() to lock the target record. And this is where I am having problem number 1.

     I was under assumption that if more than one thread trying to lock the same record at the same time, only one of them will succeed and rest will have to wait. However it is not happening. What I see is:
     -- if I lock the record via musql command line  ( START TRANSACTION; SELECT ... FOR UPDATE... ), than all threads which try to lock the same record will block until I release the lock from command line ( either COMMIT or ROLLBACK)
     -- once I release the lock from command line... ALL blocked thread continue as if all of them manager to perform EntityManger.lock()

-- the record is locked in mysql client   

14:35:37,009 DEBUG [org.hibernate.SQL] (default task-43) select ID from transactions where ID =? for update

14:35:37,016 DEBUG [org.hibernate.SQL] (default task-44) select ID from transactions where ID =? for update

14:35:37,019 DEBUG [org.hibernate.SQL] (default task-45) select ID from transactions where ID =? for update

-- at this point I released lock in mysql client

14:35:52,745 WARN  [co.vgw.chumba.domain.dao.generic.GenericDaoImpl] (default task-45) Locked : co.vgw.chumba.domain.model.Transaction - 5622350 using : {javax.persistence.lock.timeout=0}

14:35:52,745 WARN  [co.vgw.chumba.domain.dao.generic.GenericDaoImpl] (default task-44) Locked : co.vgw.chumba.domain.model.Transaction - 5622350 using : {javax.persistence.lock.timeout=0}

14:35:52,745 WARN  [co.vgw.chumba.domain.dao.generic.GenericDaoImpl] (default task-43) Locked : co.vgw.chumba.domain.model.Transaction - 5622350 using : {javax.persistence.lock.timeout=0}

-- each thread continue execution

14:35:52,745 DEBUG [org.hibernate.SQL] (default task-44) select session0_.ID as ID1_24_, session0_.CLOSED_REASON as CLOSED_R2_24_, session0_.ENDED as ENDED3_24_, session0_.REOPENED as REOPENED4_24_, ...

14:35:52,745 DEBUG [org.hibernate.SQL] (default task-43) select session0_.ID as ID1_24_, session0_.CLOSED_REASON as CLOSED_R2_24_, session0_.ENDED as ENDED3_24_, session0_.REOPENED as REOPENED4_24_, ...

14:35:52,745 DEBUG [org.hibernate.SQL] (default task-45) select session0_.ID as ID1_24_, session0_.CLOSED_REASON as CLOSED_R2_24_, session0_.ENDED as ENDED3_24_, session0_.REOPENED as REOPENED4_24_, ...   

     The second problem... as I mentioned above, we only care about the very first time the API was called for given record. So it would suite us very well if all consecutive attempts fail... To achieve this, we tried to specify javax.persistence.lock.timeout=0 in lock properties... but it does not produce any difference in behavior... it seems that this hint is completely ignored. I know that mySQL does not support anything like `... FOR UPDATE NO WAIT` ... so I wonder if this kind of 'quick fail' behavior is not supported for mySQL?

    All your comments will be highly appreciated.

     Andrei.

[mayerw01]

I am not absolutely sure having understood your 1st issue. But  have you set 'autocommit = off'?

Regarding your 2nd issue you could try to reduce the 'innodb_lock_wait_timeout'

[atchijov-vgw]

Hi Wolfgang,

     Could u please elaborate. Why `autocommit = off` should make a difference in the way lock() functions? And to answer your question, it seems that this is not the case (I don't do it and I don't see `set autocommit...` in org.hibernate.SQL log entries.

     In regards to innodb_lock_wait_timeout - does your answer means that in case of mySQL javax.persistence.lock.timeout=0 is ignored?

Cheers,

     Andrei

[mayerw01]

The EntityManager.lock() functionality depends on the persistence implementation (I guess in your case Hibernate) while "autocommit" and "innodb_lock_wait_timeout" are variables in MySQL and not linked to Hibernate (or persistence.xml). With autocommit = on all changes to a table take effect immediately (this is usually not recommended for jdbc connections).

As mentioned in the Hibernate docu 'javax.persistence.lock.timeout is a hint used by Hibernate but requires support by your underlying database.