<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>API support for transactions</title>
</head>
<body><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqli.quickstart.multiple-statement.html">Multiple Statements</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqli.quickstart.metadata.html">Metadata</a></div>
<div class="up"><a href="mysqli.quickstart.html">Quick start guide</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div><hr /><div id="mysqli.quickstart.transactions" class="section">
<h2 class="title">API support for transactions</h2>
<p class="para">
The MySQL server supports transactions depending on the storage engine used.
Since MySQL 5.5, the default storage engine is InnoDB.
InnoDB has full ACID transaction support.
</p>
<p class="para">
Transactions can either be controlled using SQL or API calls.
It is recommended to use API calls for enabling and disabling the
auto commit mode and for committing and rolling back transactions.
</p>
<p class="para">
<div class="example" id="example-1643">
<p><strong>Example #1 Setting auto commit mode with SQL and through the API</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"example.com"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br />if (</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">connect_errno</span><span style="color: #007700">) {<br /> echo </span><span style="color: #DD0000">"Failed to connect to MySQL: (" </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">connect_errno </span><span style="color: #007700">. </span><span style="color: #DD0000">") " </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">connect_error</span><span style="color: #007700">;<br />}<br /><br /></span><span style="color: #FF8000">/* Recommended: using API to control transactional settings */<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">autocommit</span><span style="color: #007700">(</span><span style="color: #0000BB">false</span><span style="color: #007700">);<br /><br /></span><span style="color: #FF8000">/* Won't be monitored and recognized by the replication and the load balancing plugin */<br /></span><span style="color: #007700">if (!</span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">'SET AUTOCOMMIT = 0'</span><span style="color: #007700">)) {<br /> echo </span><span style="color: #DD0000">"Query failed: (" </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">errno </span><span style="color: #007700">. </span><span style="color: #DD0000">") " </span><span style="color: #007700">. </span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">error</span><span style="color: #007700">;<br />}<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
Optional feature packages, such as the replication and load balancing plugin,
can easily monitor API calls. The replication plugin offers transaction
aware load balancing, if transactions are controlled with API calls.
Transaction aware load balancing is not available if SQL statements are
used for setting auto commit mode, committing or rolling back a transaction.
</p>
<p class="para">
<div class="example" id="example-1644">
<p><strong>Example #2 Commit and rollback</strong></p>
<div class="example-contents">
<div class="phpcode"><code><span style="color: #000000">
<span style="color: #0000BB"><?php<br />$mysqli </span><span style="color: #007700">= new </span><span style="color: #0000BB">mysqli</span><span style="color: #007700">(</span><span style="color: #DD0000">"example.com"</span><span style="color: #007700">, </span><span style="color: #DD0000">"user"</span><span style="color: #007700">, </span><span style="color: #DD0000">"password"</span><span style="color: #007700">, </span><span style="color: #DD0000">"database"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">autocommit</span><span style="color: #007700">(</span><span style="color: #0000BB">false</span><span style="color: #007700">);<br /><br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO test(id) VALUES (1)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">rollback</span><span style="color: #007700">();<br /><br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">query</span><span style="color: #007700">(</span><span style="color: #DD0000">"INSERT INTO test(id) VALUES (2)"</span><span style="color: #007700">);<br /></span><span style="color: #0000BB">$mysqli</span><span style="color: #007700">-></span><span style="color: #0000BB">commit</span><span style="color: #007700">();<br /></span><span style="color: #0000BB">?></span>
</span>
</code></div>
</div>
</div>
</p>
<p class="para">
Please note, that the MySQL server cannot roll back all statements.
Some statements cause an implicit commit.
</p>
<p class="para">
<em class="emphasis">See also</em>
</p>
<p class="para">
<ul class="simplelist">
<li class="member"> <span class="methodname"><a href="mysqli.autocommit.html" class="methodname">mysqli::autocommit()</a></span></li>
<li class="member"> <span class="methodname"><strong>mysqli_result::commit()</strong></span></li>
<li class="member"> <span class="methodname"><strong>mysqli_result::rollback()</strong></span></li>
</ul>
</p>
</div><hr /><div class="manualnavbar" style="text-align: center;">
<div class="prev" style="text-align: left; float: left;"><a href="mysqli.quickstart.multiple-statement.html">Multiple Statements</a></div>
<div class="next" style="text-align: right; float: right;"><a href="mysqli.quickstart.metadata.html">Metadata</a></div>
<div class="up"><a href="mysqli.quickstart.html">Quick start guide</a></div>
<div class="home"><a href="index.html">PHP Manual</a></div>
</div></body></html>
