API support for transactions (original) (raw)
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.
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.
Example #1 Setting auto commit mode with SQL and through the API
<?php $mysqli = new mysqli("example.com", "user", "password", "database"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>m</mi><mi>y</mi><mi>s</mi><mi>q</mi><mi>l</mi><mi>i</mi><mo>−</mo><mo>></mo><mi>c</mi><mi>o</mi><mi>n</mi><mi>n</mi><mi>e</mi><mi>c</mi><msub><mi>t</mi><mi>e</mi></msub><mi>r</mi><mi>r</mi><mi>n</mi><mi>o</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">"</mi><mo stretchy="false">)</mo><mi mathvariant="normal">"</mi><mi mathvariant="normal">.</mi></mrow><annotation encoding="application/x-tex">mysqli->connect_errno . ") " . </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">m</span><span class="mord mathnormal">ys</span><span class="mord mathnormal" style="margin-right:0.01968em;">ql</span><span class="mord mathnormal">i</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">></span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">co</span><span class="mord mathnormal">nn</span><span class="mord mathnormal">ec</span><span class="mord"><span class="mord mathnormal">t</span><span class="msupsub"><span class="vlist-t vlist-t2"><span class="vlist-r"><span class="vlist" style="height:0.1514em;"><span style="top:-2.55em;margin-left:0em;margin-right:0.05em;"><span class="pstrut" style="height:2.7em;"></span><span class="sizing reset-size6 size3 mtight"><span class="mord mathnormal mtight">e</span></span></span></span><span class="vlist-s"></span></span><span class="vlist-r"><span class="vlist" style="height:0.15em;"><span></span></span></span></span></span></span><span class="mord mathnormal" style="margin-right:0.02778em;">rr</span><span class="mord mathnormal">n</span><span class="mord mathnormal">o</span><span class="mord">."</span><span class="mclose">)</span><span class="mord">".</span></span></span></span>mysqli->connect_error; }/* Recommended: using API to control transactional settings */ $mysqli->autocommit(false);/* Won't be monitored and recognized by the replication and the load balancing plugin */ if (!$mysqli->query('SET AUTOCOMMIT = 0')) { echo "Query failed: (" . <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>m</mi><mi>y</mi><mi>s</mi><mi>q</mi><mi>l</mi><mi>i</mi><mo>−</mo><mo>></mo><mi>e</mi><mi>r</mi><mi>r</mi><mi>n</mi><mi>o</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">"</mi><mo stretchy="false">)</mo><mi mathvariant="normal">"</mi><mi mathvariant="normal">.</mi></mrow><annotation encoding="application/x-tex">mysqli->errno . ") " . </annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">m</span><span class="mord mathnormal">ys</span><span class="mord mathnormal" style="margin-right:0.01968em;">ql</span><span class="mord mathnormal">i</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">></span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal" style="margin-right:0.02778em;">err</span><span class="mord mathnormal">n</span><span class="mord mathnormal">o</span><span class="mord">."</span><span class="mclose">)</span><span class="mord">".</span></span></span></span>mysqli->error; } ?>
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.
Example #2 Commit and rollback
<?php $mysqli = new mysqli("example.com", "user", "password", "database"); <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>m</mi><mi>y</mi><mi>s</mi><mi>q</mi><mi>l</mi><mi>i</mi><mo>−</mo><mo>></mo><mi>a</mi><mi>u</mi><mi>t</mi><mi>o</mi><mi>c</mi><mi>o</mi><mi>m</mi><mi>m</mi><mi>i</mi><mi>t</mi><mo stretchy="false">(</mo><mi>f</mi><mi>a</mi><mi>l</mi><mi>s</mi><mi>e</mi><mo stretchy="false">)</mo><mo separator="true">;</mo></mrow><annotation encoding="application/x-tex">mysqli->autocommit(false);</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">m</span><span class="mord mathnormal">ys</span><span class="mord mathnormal" style="margin-right:0.01968em;">ql</span><span class="mord mathnormal">i</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">></span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">a</span><span class="mord mathnormal">u</span><span class="mord mathnormal">t</span><span class="mord mathnormal">oco</span><span class="mord mathnormal">mmi</span><span class="mord mathnormal">t</span><span class="mopen">(</span><span class="mord mathnormal" style="margin-right:0.10764em;">f</span><span class="mord mathnormal">a</span><span class="mord mathnormal" style="margin-right:0.01968em;">l</span><span class="mord mathnormal">se</span><span class="mclose">)</span><span class="mpunct">;</span></span></span></span>mysqli->query("INSERT INTO test(id) VALUES (1)"); <span class="katex"><span class="katex-mathml"><math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>m</mi><mi>y</mi><mi>s</mi><mi>q</mi><mi>l</mi><mi>i</mi><mo>−</mo><mo>></mo><mi>r</mi><mi>o</mi><mi>l</mi><mi>l</mi><mi>b</mi><mi>a</mi><mi>c</mi><mi>k</mi><mo stretchy="false">(</mo><mo stretchy="false">)</mo><mo separator="true">;</mo></mrow><annotation encoding="application/x-tex">mysqli->rollback();</annotation></semantics></math></span><span class="katex-html" aria-hidden="true"><span class="base"><span class="strut" style="height:0.8889em;vertical-align:-0.1944em;"></span><span class="mord mathnormal">m</span><span class="mord mathnormal">ys</span><span class="mord mathnormal" style="margin-right:0.01968em;">ql</span><span class="mord mathnormal">i</span><span class="mord">−</span><span class="mspace" style="margin-right:0.2778em;"></span><span class="mrel">></span><span class="mspace" style="margin-right:0.2778em;"></span></span><span class="base"><span class="strut" style="height:1em;vertical-align:-0.25em;"></span><span class="mord mathnormal">ro</span><span class="mord mathnormal" style="margin-right:0.01968em;">ll</span><span class="mord mathnormal">ba</span><span class="mord mathnormal">c</span><span class="mord mathnormal" style="margin-right:0.03148em;">k</span><span class="mopen">(</span><span class="mclose">)</span><span class="mpunct">;</span></span></span></span>mysqli->query("INSERT INTO test(id) VALUES (2)"); $mysqli->commit(); ?>
Please note, that the MySQL server cannot roll back all statements. Some statements cause an implicit commit.
See also
- mysqli::autocommit()
- mysqli_result::commit()
- mysqli_result::rollback()