title: iBatis selectKey and transactions
body: <p>I'm using iBatis with MySQL 5 in my Java app.</p>
  
  <p>I have a persistent entity class</p>
  
  <pre><code>public class Entity {
      private int id;
      private Stirng property;
      // setters and getters are omitted        
  }
  </code></pre>
  
  <p>Inserting new entity is done as follows:</p>
  
  <pre><code>&lt;insert id="insert" parameterClass="MyEntity"&gt;
      &lt;selectKey resultClass="int" type="post" keyProperty="id" &gt;
          select LAST_INSERT_ID() as value
      &lt;/selectKey&gt;
      {CALL insert_entity(#property#)}
  &lt;/insert&gt;
  </code></pre>
  
  <p>Transactions are managed <em>inside the stored procedure</em> as follows:</p>
  
  <pre><code>CREATE DEFINER=`user`@`%` PROCEDURE `insert`(IN p_property VARCHAR(255))
  BEGIN
      START TRANSACTION;
          INSERT INTO entities (property) VALUES (p_property);
          -- Do more stuff that requires transaction: update more tables etc.
      COMMIT; 
  END;
  </code></pre>
  
  <p>What I'm trying to achieve is getting newly inserted entity id back to my Java code. While working with no concurrent DB updates, the setup above will do exactly what I want. The unclear part is what happens with concurrent DB updates - i.e. what is the exact timing and context of iBatis executing <code>selectKey</code> statement - I'd guess it will not be executed within the same transaction that defined in stored procedure, so it is possible that id returned will not be the id of the entity I want.</p>
  
  <p>The only possible solution I can think about is avoid usage of <code>selectKey</code>:</p>
  
  <pre><code>&lt;insert id="insert" parameterClass="MyEntity"&gt;
      {CALL insert_entity(#property#, #id,mode=OUT#)}
  &lt;/insert&gt;
  </code></pre>
  
  <p>Returning the last inserted id from the stored procedure:</p>
  
  <pre><code>CREATE DEFINER=`user`@`%` PROCEDURE `insert`(
         IN p_property VARCHAR(255),
         OUT p_id INTEGER(11),
  )
  BEGIN
      START TRANSACTION;
          INSERT INTO entities (property) VALUES (p_property);
          SELECT LAST_INSERT_ID() INTO p_id;
          -- Do more stuff that requires transaction: update more tables etc.
      COMMIT; 
  END;
  </code></pre>
  
  <p>Is there any better solution for this problem?</p>
  
  <hr>
  
  <p><strong>Edited:</strong> MySQL documentation for <code>LAST_INSERT_ID</code> states:</p>
  
  <blockquote>
    <p>The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.</p>
  </blockquote>
  
  <p>So it seems like the originals solution with <code>selectKey</code> will work in all the cases. However, for the complex stored procedures with multiple <code>INSERT</code> statements the second approach is safer.</p>