How to Properly Use mysqli::begin_transaction for Database Transaction Control?

gitbox 2025-08-21
<?php // This section is unrelated to the article content // For example, it could contain author info, copyright notices, or load certain configurations date_default_timezone_set('Asia/Shanghai'); $author = "Database Transaction Research Group"; $publish_date = date('Y-m-d'); ?>

How to Properly Use mysqli::begin_transaction for Database Transaction Control?

When developing with a MySQL database, transaction control is a crucial method to ensure data consistency and integrity. PHP's mysqli

: Optional, specifies transaction behavior, such as MYSQLI_TRANS_START_READ_ONLY or MYSQLI_TRANS_START_READ_WRITE.
$name: Optional, assigns a name to the transaction (mainly used for XA transactions or advanced scenarios).

3. Basic Example

</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">'testdb'</span></span><span>);

</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$mysqli</span></span><span>->connect_errno) {
    </span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">'Connection failed: '</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>->connect_error);
}

</span><span><span class="hljs-keyword">try</span></span><span> {
    </span><span><span class="hljs-comment">// Explicitly start a transaction</span></span><span>
    </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">begin_transaction</span></span><span>();

    </span><span><span class="hljs-comment">// Execute multiple related SQL statements</span></span><span>
    </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">query</span></span><span>(</span><span><span class="hljs-string">"UPDATE accounts SET balance = balance - 100 WHERE id = 1"</span></span><span>);
    </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">query</span></span><span>(</span><span><span class="hljs-string">"UPDATE accounts SET balance = balance + 100 WHERE id = 2"</span></span><span>);

    </span><span><span class="hljs-comment">// Commit the 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>();
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Transfer successful"</span></span><span>;
} </span><span><span class="hljs-keyword">catch</span></span><span> (</span><span><span class="hljs-built_in">Exception</span></span><span> </span><span><span class="hljs-variable">$e</span></span><span>) {
    </span><span><span class="hljs-comment">// Roll back on error</span></span><span>
    </span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">rollback</span></span><span>();
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Transfer failed: "</span></span><span> . </span><span><span class="hljs-variable">$e</span></span><span>-></span><span><span class="hljs-title function_ invoke__">getMessage</span></span><span>();
}

</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">close</span></span><span>();

4. Notes

Ensure autocommit is turned off (using begin_transaction automatically disables autocommit for the current session).
Always handle transactions within a try...catch block to ensure rollback on errors.
In high-concurrency environments, use locks and isolation levels appropriately (can be set via SET TRANSACTION ISOLATION LEVEL).

5. Conclusion
Using mysqli::begin_transaction allows PHP developers to manage transaction flows more intuitively and controllably. In real-world projects, always use transactions when performing multiple logically related database operations, and ensure proper error handling and rollback to maintain data consistency and security.
Related Tags:
mysqli
mysqli::begin_transaction