title: Lock Table at the begin of a Transaction
body: <p>Due to legacy code issues I need to calculate a unique index manually and can't use auto_increment, when inserting a new row to the database.</p>
  
  <p>The problem is that multiple inserts of multiple clients (different machines) can occur simultaneously. Therefore I need to lock the row with the highest id from being read by other transactions while the current transaction is active. Alternatively I could lock the whole table from any reads. Time is not an issue in this case because writes/reads are very rare (&lt;1 op per second)</p>
  
  <p>It tried to set the isolation level to 8 (Serializable), but then MySQL throws a DeadLockException. Interestingly the SELECT to determine the next ID is still done, which contradicts my understanding of serializable.</p>
  
  <p>Also setting the LockMode to PESSIMISTIC_READ of the select, doesn't seem to help.</p>
  
  <pre><code>public void insert(T entity) {
      EntityManager em = factory.createEntityManager();
      try {
          EntityTransaction transaction = em.getTransaction();
          try {
              transaction.begin();
  
              int id = 0;
              TypedQuery&lt;MasterDataComplete&gt; query = em.createQuery(
                      "SELECT m FROM MasterDataComplete m ORDER BY m.id DESC", MasterDataComplete.class);
              query.setMaxResults(1);
              query.setLockMode(LockModeType.PESSIMISTIC_READ);
              List&lt;MasterDataComplete&gt; results = query.getResultList();
              if (!results.isEmpty()) {
                  MasterDataComplete singleResult = results.get(0);
  
                  id = singleResult.getId() + 1;
              }
  
              entity.setId(id);
  
              em.persist(entity);
              transaction.commit();
          } finally {
              if (transaction.isActive()) {
                  transaction.rollback();
              }
          }
      } finally {
          em.close();
      }
  }
  </code></pre>
  
  <p>Some words to the application:<br>
  It is Java-Standalone, runs on multiple clients which connect to the same DB Server and it should work with multiple DB servers (Sybase Anywhere, Oracle, Mysql, ...)</p>
  
  <p>Currently the only idea I've got left is just to do the insert and catch the Exception that occurs when the ID is already in use and try again. This works because I can assume that the column is set to primary key/unique.</p>