title: How to use LOCK IN SHARE MODE with hibernate/spring?
body: <p>I'm running into a deadlock scenario when using MySQL and Hibernate (with Spring). I'm getting the following exception message:</p>
  
  <blockquote>
    <p>could not get or update next value;
    SQL[null]; nested exception is
    org.hibernate.exception.LockAcquisitionException
    ...Deadlock found when trying to get
    lock; try restarting transaction</p>
  </blockquote>
  
  <p>My MySQL db has its transaction isolation level configured to READ COMMITTED, and I did some looking into the INNODB status to see what was happening when the deadlock occurred. Essentially I see two transactions where one is trying to select a row from a table while the other is trying to update that row. The table in contention here is my "unique id" table. I have a common, root object which other @Entity objects extend. This root object defines the "id" property and is annotated with @Id, @TableGenerator and @GeneratedValue.</p>
  
  <p>Some digging around led me to believe that setting the LockMode to UPGRADE for the "select" on that table might help and/or changing the "select" so that instead of it using "select .... <strong>for update</strong>" it would use "select .... <strong>lock in share mode</strong>".</p>
  
  <p>Any ideas if setting the Lockmode.upgrade would really help or changing "for update" to "lock in share mode"? Secondly, how would I do this with spring + hibernate...I was leaning towards implementing a custom LoadEventListener (which would extend the DefaultLoadEventListener).</p>