Search code examples
javamysqlhibernatejboss

Select for update not working

My following native query is not working:

Query createNativeQuery = entityManager.createNativeQuery(
   "select id from cscache where id=? for update ");

Environment Details:

  1. Mysql 5.6
  2. Jboss 5.1
  3. JPA 1.0

Error:

2014-12-12 10:20:14,581 WARN [org.hibernate.util.JDBCExceptionReporter]

SQL Error: 1064, SQLState: 42000 (http-0.0.0.0-8543-3:)

2014-12-12 10:20:14,581 ERROR [org.hibernate.util.JDBCExceptionReporter] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'limit 2' at line 1 (http-0.0.0.0-8543-3:)

Solution

  • I haven't actually done a for update in hibernate, but I believe you can (and should?) do it on the Session or query object. Why not let hibernate do it instead of executing a native query? According to the documentation on locking (Chapter 5: Locking, you can set the lock mode on either the session or the query.

    Cscache  cscache  = (Cscache )session.get( Cscache.class, id, LockOptions.UPGRADE );

    Specifying the LockOptions should result in a SELECT ... FOR UPDATE being executed.