English
English
The basic concept of a transaction is “either all succeed or all fail.” If one operation in a transaction fails, all operations within it will be rolled back (undone). Transactions usually follow four key characteristics, also known as the ACID principles:
Atomicity: All operations within a transaction must either be completed in full or not executed at all.
Consistency: A transaction must transform the database from one consistent state to another consistent state.
Isolation: The execution of one transaction should not interfere with another.
Durability: Once a transaction is committed, the changes should be permanent and not lost.
In MySQL, transactions rely mainly on the InnoDB storage engine, since InnoDB supports transactions, while other engines like MyISAM do not.
In PHP, the mysqli::begin_transaction function is used to explicitly start a transaction. After calling this function, MySQL begins a transaction and will not commit or roll back changes until the developer explicitly calls commit() or rollback().
<span><span>mysqli::</span><span><span class="hljs-title function_ invoke__">begin_transaction</span></span><span>();
</span></span>This function takes no parameters and returns void. It is primarily used to start a transaction, after which you can execute a series of database operations.
Connect to the database: First, establish a connection using mysqli_connect or the object-oriented approach.
Start the transaction: Call begin_transaction to begin.
Execute SQL operations: Perform a sequence of SQL operations within the transaction, such as insert, update, or delete.
Commit or roll back: Based on the result, decide whether to commit (commit) or roll back (rollback) the transaction.
<span><span><span class="hljs-meta"><?php</span></span><span>
</span><span><span class="hljs-comment">// Create connection</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">mysqli</span></span><span>(</span><span><span class="hljs-string">"localhost"</span></span><span>, </span><span><span class="hljs-string">"user"</span></span><span>, </span><span><span class="hljs-string">"password"</span></span><span>, </span><span><span class="hljs-string">"database"</span></span><span>);
<p></span>// Check connection<br>
if ($mysqli->connect_error) {<br>
die("Connection failed: " . $mysqli->connect_error);<br>
}</p>
<p>// Begin transaction<br>
$mysqli->begin_transaction();</p>
<p>try {<br>
// Execute SQL operations<br>
$mysqli->query("INSERT INTO users (name, email) VALUES ('Alice', '<a class="cursor-pointer" rel="noopener">[email protected]</a>')");<br>
$mysqli->query("UPDATE users SET email = '<a class="cursor-pointer" rel="noopener">[email protected]</a>' WHERE name = 'Bob'");</p>
</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">commit</span></span><span>();
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Transaction committed successfully!"</span></span><span>;
} catch (Exception $e) {
// Rollback if error occurs
$mysqli->rollback();
echo "Transaction rolled back: " . $e->getMessage();
}
// Close connection
$mysqli->close();
?>
Error handling is crucial when using transactions. Typically, we use try-catch statements to catch exceptions and roll back the transaction if an error occurs. This ensures that even if one operation fails midway, all previous operations are undone, maintaining database consistency.
In the example above, if an SQL operation fails (e.g., due to a constraint violation), the catch block captures the error and calls $mysqli->rollback() to undo all database operations.
Commit: When all operations in the transaction succeed, you can call the commit() method to make all changes permanent in the database.
Rollback: If an error occurs during the transaction, you can call the rollback() method to undo all SQL operations, restoring the database to its state before the transaction began.
Commit and rollback are key controls in determining the final outcome of a transaction.
<span><span><span class="hljs-comment">// Commit transaction</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">commit</span></span><span>();
<p></span>// Rollback transaction<br>
$mysqli->rollback();<br>
</span>Autocommit Mode: By default, MySQL runs in autocommit mode, meaning each SQL statement is immediately committed. To use transactions, you need to disable autocommit manually with mysqli->autocommit(FALSE).
Example:
<span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">autocommit</span></span><span>(</span><span><span class="hljs-literal">FALSE</span></span><span>); </span><span><span class="hljs-comment">// Disable autocommit</span></span><span>
</span></span>Nested Transactions: MySQL does not support true nested transactions. If you start a new transaction within an existing one, the commit or rollback of the inner transaction will not affect the outer transaction.
Related Tags:
mysqli
