<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="innodb-autocommit-commit-rollback">
      </a>
      17.7.2.2 autocommit, Commit, and Rollback
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045164266752">
  </a>
  <a class="indexterm" name="idm46045164265360">
  </a>
  <p>
   In
   <code class="literal">
    InnoDB
   </code>
   , all user activity occurs inside a
        transaction. If
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   mode
        is enabled, each SQL statement forms a single transaction on its
        own. By default, MySQL starts the session for each new
        connection with
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   enabled, so MySQL does a commit after each SQL statement if that
        statement did not return an error. If a statement returns an
        error, the commit or rollback behavior depends on the error. See
   <a class="xref" href="innodb-error-handling.html" title="17.20.5 InnoDB Error Handling">
    Section 17.20.5, “InnoDB Error Handling”
   </a>
   .
  </p>
  <p>
   A session that has
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   enabled can perform a multiple-statement transaction by starting
        it with an explicit
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     START
        TRANSACTION
    </code>
   </a>
   or
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     BEGIN
    </code>
   </a>
   statement and ending it with a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   or
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     ROLLBACK
    </code>
   </a>
   statement. See
   <a class="xref" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    Section 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”
   </a>
   .
  </p>
  <p>
   If
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   mode is disabled
        within a session with
   <code class="literal">
    SET autocommit = 0
   </code>
   , the
        session always has a transaction open. A
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   or
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     ROLLBACK
    </code>
   </a>
   statement ends the current transaction and a new one starts.
  </p>
  <p>
   If a session that has
   <a class="link" href="server-system-variables.html#sysvar_autocommit">
    <code class="literal">
     autocommit
    </code>
   </a>
   disabled ends
        without explicitly committing the final transaction, MySQL rolls
        back that transaction.
  </p>
  <p>
   Some statements implicitly end a transaction, as if you had done
        a
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   before executing the
        statement. For details, see
   <a class="xref" href="implicit-commit.html" title="15.3.3 Statements That Cause an Implicit Commit">
    Section 15.3.3, “Statements That Cause an Implicit Commit”
   </a>
   .
  </p>
  <p>
   A
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   means that the changes
        made in the current transaction are made permanent and become
        visible to other sessions. A
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     ROLLBACK
    </code>
   </a>
   statement, on the other hand, cancels all modifications made by
        the current transaction. Both
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     COMMIT
    </code>
   </a>
   and
   <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
    <code class="literal">
     ROLLBACK
    </code>
   </a>
   release all
   <code class="literal">
    InnoDB
   </code>
   locks that were set during
        the current transaction.
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="innodb-transactions-with-different-apis">
       </a>
       Grouping DML Operations with Transactions
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045164238512">
   </a>
   <p>
    By default, connection to the MySQL server begins with
    <a class="link" href="glossary.html#glos_autocommit" title="autocommit">
     autocommit
    </a>
    mode
          enabled, which automatically commits every SQL statement as
          you execute it. This mode of operation might be unfamiliar if
          you have experience with other database systems, where it is
          standard practice to issue a sequence of
    <a class="link" href="glossary.html#glos_dml" title="DML">
     DML
    </a>
    statements and commit them
          or roll them back all together.
   </p>
   <p>
    To use multiple-statement
    <a class="link" href="glossary.html#glos_transaction" title="transaction">
     transactions
    </a>
    , switch
          autocommit off with the SQL statement
    <code class="literal">
     SET autocommit
          = 0
    </code>
    and end each transaction with
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      COMMIT
     </code>
    </a>
    or
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      ROLLBACK
     </code>
    </a>
    as
          appropriate. To leave autocommit on, begin each transaction
          with
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      START
          TRANSACTION
     </code>
    </a>
    and end it with
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      COMMIT
     </code>
    </a>
    or
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      ROLLBACK
     </code>
    </a>
    .
          The following example shows two transactions. The first is
          committed; the second is rolled back.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa16846410"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> test</code></pre>
   </div>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67721008"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> customer <span class="token punctuation">(</span>a <span class="token datatype">INT</span><span class="token punctuation">,</span> b <span class="token datatype">CHAR</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">INDEX</span> <span class="token punctuation">(</span>a<span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token comment" spellcheck="true">-- Do a transaction with autocommit turned on.</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">START</span> <span class="token keyword">TRANSACTION</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> customer <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span> <span class="token string">'Heikki'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">COMMIT</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token comment" spellcheck="true">-- Do another transaction with autocommit turned off.</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> autocommit<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> customer <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">15</span><span class="token punctuation">,</span> <span class="token string">'John'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> customer <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span> <span class="token string">'Paul'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">DELETE</span> <span class="token keyword">FROM</span> customer <span class="token keyword">WHERE</span> b <span class="token operator">=</span> <span class="token string">'Heikki'</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token comment" spellcheck="true">-- Now we undo those last 2 inserts and the delete.</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">ROLLBACK</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token operator">*</span> <span class="token keyword">FROM</span> customer<span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> a    <span class="token punctuation">|</span> b      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>   10 <span class="token punctuation">|</span> Heikki <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output">1 row in set (0.00 sec)</span>
<span class="token prompt">mysql&gt;</span></code></pre>
   </div>
   <h6>
    <a name="idm46045164213216">
    </a>
    Transactions in Client-Side Languages
   </h6>
   <p>
    In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C
          call interface of MySQL, you can send transaction control
          statements such as
    <a class="link" href="commit.html" title="15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements">
     <code class="literal">
      COMMIT
     </code>
    </a>
    to
          the MySQL server as strings just like any other SQL statements
          such as
    <a class="link" href="select.html" title="15.2.13 SELECT Statement">
     <code class="literal">
      SELECT
     </code>
    </a>
    or
    <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
     <code class="literal">
      INSERT
     </code>
    </a>
    . Some APIs also offer
          separate special transaction commit and rollback functions or
          methods.
   </p>
  </div>
 </div>
 <br/>
</div>
