MyBatis Inner query
<p>In my code base, we have a MyBatis query like below,</p>

<pre><code>INSERT INTO TABLE_A(
        ID,
        NAME,
        VERSION
    )
    VALUES  (
        #id#,
        #name#,
        (select NVL(max(version), 0 ) + 1 from SAE_PROC_SETTING where name = #name#)
    )
</code></pre>

<p>The unique constraint is on columns name and version. If multiple users are inserting values at same time, sometimes we are getting unique constraint error. When we checked, the name and version is coming as same and throwing the error as expected. </p>

<p>My question is, how MyBatis handles the inner query </p>

<pre><code>(select NVL(max(version), 0 ) + 1 from TABLE_Awhere name = #name#)
</code></pre>

<p>Does this query is submitted by MyBatis to Oracle and then oracle first executes the SELECT and then INSERT query OR Does MyBatis executes the SELECT first, then substitute the value in INSERT and submits the INSERT query in Oracle (2 steps by MyBatis) ? </p>

<p>If it's handled like second option, when 2 people are inserting with same name at same TIME, there is a probability that both got the same version. Then when trying to insert, we will get UniqueConstraint error.</p>

<p>Please let me know how MyBatis handles this internally. Any pointers are fine.</p>

<p>Thanks,
SD</p>