5

We have a system in which we must use pessimistic locking in one entity. We are using hibernate, so we use LockMode.UPGRADE. However, it does not lock.

  • The tables are InnoDB
  • We have checked that locking works correctly in the database (5.0.32), so this bug http://bugs.mysql.com/bug.php?id=18184 seems to be no problem.
  • We have checked that datasource includes the autoCommit = false parameter.
  • We have checked that the SQL hibernate (version 3.2) generates includes the " FOR UPDATE".

Thanks,

Andres Rodriguez
  • 111
  • 1
  • 7
  • We have found the same problem in the hibernate forums, https://forum.hibernate.org/viewtopic.php?f=1&t=996902, but there are no responses. – Andres Rodriguez Feb 15 '10 at 11:10
  • How to you tell that it doesn't lock? and since hibernate has generated the appropriate query, the problem is in the database I guess. – Bozho Feb 15 '10 at 11:34
  • Because the pessimistic locking is used to generate a trnasactional sequence and duplicaple values are being generaled. Using the same SQL in an interactive session does effectively lock, so it seems to be some problem in the way the connection is performed or the session managed. – Andres Rodriguez Feb 15 '10 at 11:57
  • If the correct SQL is generated, you should check your isolation level, also if you could comment on your environment: like do you use external transaction management, or hibernate provided/manual transaction management. I don't think you should explicitly set autoCommit value to false, hibernate or the transaction manager should do that. – Zoran Regvart Feb 15 '10 at 17:41
  • Tx management is provided by Spring. The autocommit settings is in the connection properties, as we have found that in some situations spring tx mgmt does not properly if not explicitly set (don't know why either). – Andres Rodriguez Feb 15 '10 at 18:26
  • Basically, you'll need to provide your spring/hibernate configuration. I very much doubt that spring transaction management does not set the auto commit flag properly. By what you're saying that SELECT FOR UPDATE works from the database tool, and does not from your code, I would suspect that there is a problem in your code or configuration. – Zoran Regvart Feb 16 '10 at 14:19

3 Answers3

2

I'm running into something very similar. I'm using the @Transactional annotation in Spring and I'm issuing a select for update and the update lock is not being acquired (I have other threads that are issuing the same select for update and have verified that they're not blocking on a lock). When I explicitly get the Hibernate session and issue a beginTransaction and commit around the code block, it all works.

This does not give me a warm and fuzzy feeling about Spring container managed transactions.

DaveR
  • 21
  • 1
1

When I've had similar problems, it was because I had the Spring managed transactions misconfigured. Double check your Spring tx config.

<tx:annotation-driven transaction-manager="txManager"/>
<bean id="txManager" class="org.springframework.orm.hibernate.HibernateTransactionManager">
  <property name="sessionFactory" ref="sessionFactory" />
</bean>

The fact that you need autocommit = false may also indicate that you're not operating within a transaction. Do you also get lazy initialization exceptions when you try to access one-to-many collections?

The most direct way I've found to figure out if the Spring tx aspect is actually working is to use the debugger. Put a breakpoint in the method that issues the FOR UPDATE SQL. Upstack until you hit your @Transactional class/method. You should see the Spring aspect proxy in the next call up in the call stack.

leedm777
  • 23,444
  • 10
  • 58
  • 87
0

Lately I have had an issue like this. We where working with 2 tx managers coz we had different databases and the problem was that the transaction was using the txmanager configured to the other database and because of that on the connection to the queried database was not being disabled the autocommit mode before performing the select for update. Using the correct txmanager solved that. Hope it helps others in future.

Rupert
  • 1
  • 1